anthony anthony - 1 month ago 7
MySQL Question

Insert data to SQL table through PHP and create a relation between the inserted data and other field in SQL table

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.

Answer Source

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.

You 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