0

I have created two tables in SQL.

First one is for the movies (movie title, id, description, year).

Second one is for the directors (director name, id, birthday).

Now the first thing I'm doing right now is that I can add a new movie and a new director separately. So let's say at first I insert a new movie and then I insert a new director. What I want to do is that having inserted the movie before the director I want to be able to insert a new director and also create a relation between the two.

So at first I have the movie insertion. Where I can insert the title, description and the year.

Then I have the director insertion. Where I can insert the name of the director and his/her birthday. And also I should have a dropdown menu with the existing movies. So I could choose a movie for the director.

How can I create a relation between those two? I already have the thing built up, kinda so right now I have the insertions separately and I also have the dropdown with the existing movie titles. What I don't know is how to connect those two so if I want to display the movie I could also display the right director for it. And same thing goes when I need to add more than one directors for the movie, so then it would also display those two directors for that movie.

Okay, I hope You can understand what I meant. Thanks.

Improve this question
2
  • create "one to many" mapping between director->movie in your tables Commented Aug 16, 2017 at 11:20
  • better to show db structure and explain relation which help people to better understand your question. Commented Aug 16, 2017 at 11:23

2 Answers 2

Reset to default
1

You need to create a third table, with the id of the movie and the director.

[Movie] ---< [Director_Movie ] >--- [Director]

So lets say you have the following Directors

ID Name
01 Bob
02 Alice

and the following Movies

ID Name
01 Movie 1
02 Movie 2
03 Movie 3

So lets say Movie 1 and Movie 2 were made by Bob and Movie 3 by Alice, the data stored in Director_Movie should be something like this.

ID_Director ID_Movie
01          01
01          02
02          03

So, if you wanna know which movies were made by Bob, your SQL should look something like this.

Select *
From Movie
where Movie.id = Director_Movie.id_movie and
      Director.id = Director_Movie.id_director and
      Director.id = 01;

Since you already created a way to store data in Movie and Director tables with PHP, create a new one to save data in Director_Movie isn't different.

The first think come to my mind is create another form to pick a Movie and a Director and store both Ids in the new table.

You just need to remember, in the new table Director_Movie both Movie_ID and Director_ID are Primary and Foreign key at the same time, I think you already know what a Primary Key is, but if you don't know what a Foreign key you can check this

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for this. It gave me some sense now.
0

it's hard to answer without knowing the table structure but if you want to make relation to the table you need to add 1 field that will tell the program which director for that movie. so that movie must have 1 field called director_id then you will need to add which director for that movie. or if you want to create the movie first then you need to update the director_id field

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.