Reputation: 13
I have two tables in my database. When I insert record to first table I need to take id
that automatically generate by Mysql and add it to a new record in 2nd table.
So I need to take id
from this record:
$sql = "INSERT INTO movies (title, year, format);
and put it inside this record:
$sql = "INSERT INTO actors(name, last_name, movi_id);
-----------------------------------------------^
HERE
Upvotes: 0
Views: 722
Reputation: 2806
Use below in-built PHP function for getting id from your last Insert Query:
mysqli_insert_id();
Your code must be like below to resolve your issue:
$sql = "INSERT INTO movies (title, year, format) VALUES ($title, $year,
$format)";
$movi_id = mysqli_insert_id();
$sql = "INSERT INTO actors(name, last_name, movi_id) VALUES ($name,
$last_name, $movi_id)";
It's better if you can write above query in Prepared Statements to make it more secure:
// prepare and bind Movies Query
$queryMovies = $conn->prepare("INSERT INTO movies (title, year, format)
VALUES (?, ?, ?)");
$queryMovies->bind_param("sss", $title, $year, $format);
// execute Movies Query
$queryMovies->execute();
// Get last inserted Id of Movies Query
$movi_id = $queryMovies->insert_id;
// prepare and bind Actors Query
$queryActors = $conn->prepare("INSERT INTO actors (name, last_name, movi_id)
VALUES (?, ?, ?)");
$queryActors->bind_param("sss", $name, $last_name, $movi_id);
// execute Actors Query
$queryActors->execute();
// Close Connections
$queryMovies->close();
$queryActors->close();
Upvotes: 1