Updating Many to Many

[Steve Hallam]

Hey all

First post here be kind.

I am just learning PHP/MYSQL. And have a question about how to update a Many to many SQL Table.

I am making a DVD Collection.

Have an Movie Table with ID, Name, About

Then i have an Actor table with ID and Name

Then i have a Movie Actor Link table with ID for movie and ID for Actor.

I can add new Movies and Actors But Unsure how I can update the Movie Actor Link Table so the actors_id link to the movie_id.

Not sure if this is the right area to post this, But any help would be great

 

The code i have right now is

 

//create short variables
$movie=$_POST['movie_name'];
$date=$_POST['date'];
$about=$_POST['movie_info'];
$actor1=$_POST['actor1'];
$actor2=$_POST['actor2'];
$actor3=$_POST['actor3'];

if (!$movie || !$date || !$about || !$actor1 || !$actor2) {
echo "You have not entered all the fileds, please go back and try again";
exit();
}
if (get_magic_quotes_gpc()){
$movie = addslashes($movie);
$date = addslashes($date);
$about = addslashes($about);
$actor1 = addslashes($actor1);
$actor2 = addslashes($actor2);
$actor3 = addslashes($actor3);
}

@ $db = new mysqli('', '', '', '');
if (mysqli_connect_errno()) {
echo "Could not connect to database, Check settings or try again later";
exit();
}
$query = "INSERT into movie VALUES
('NULL', '".$movie."', '".$date."', '".$about."')";
$result = $db->query($query);
if ($result) 
echo $db->affected_rows.'Movie inserted into database.';

$query2 = "INSERT into movie_actor VALUES
('NULL', '".$actor1."')";
$result = $db->query($query2);
if ($result) 
echo $db->affected_rows.'Actor inserted into database.';

$query3 = "INSERT into movie_actor VALUES
('NULL', '".$actor2."')";
$result = $db->query($query3);
if ($result) 
echo $db->affected_rows.'Actor inserted into database.';

$query4 = "INSERT into movie_actor VALUES
('NULL', '".$actor3."')";
$result = $db->query($query4);
if ($result) 
echo $db->affected_rows.'Actor inserted into database.';



$db->close();

?>

[fenway]

Help with what?

[Steve Hallam]

What i want to do is, when i add a movie to the collection i insert the movie name, date to one movie table, actors to another actor table. But i have a movie_actor_link table that needs updating. It will link all the actors to the movie that i just added. That table holds the Actor_ID and Movie_ID.

How can I update the movie_actor_link table so all the actors i just inserted will join with the movie i just added? Hope i described my issue correctly.

[fenway]

Now I understand... you'll need to collect the LAST_INSERT_ID() as you go along.