Is is possible use the LAST_INSERT_ID()
in php for-loop. I need to get the last playground
PK as a FK in guardian
table. Both needs to insert at a same time. Pardon me for not using PDO
, i just want to get this thing to work first.
$query = "INSERT INTO playground (parent, children, amount) VALUES ('John','susy','2000');";
$levelarray = array ("One", "Two", "Three");
for ($i = 0; $i < count($levelarray); $i++) {
$level = $levelarray[$i];
$query .= "INSERT INTO guardian (playgroundid, level) VALUES (LAST_INSERT_ID(),'$level');";
}
mysqli_multi_query($con, $query);
I have also tried this. But the one below outputs the last id, but not the newly inserted id.
$query = "INSERT INTO playground (parent, children, amount) VALUES ('John','Susy','2000');";
$sql = "SELECT playground_id AS playgroundid FROM playground ORDER BY playground_id DESC LIMIT 1";
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_array($result);
$playId = $row['playgroundid'];
$levelarray = array ("One", "Two", "Three");
for ($i = 0; $i < count($levelarray); $i++) {
$level = $levelarray[$i];
$query .= "INSERT INTO guardian (playgroundid, level) VALUES ('$playId','$level');";
}
mysqli_multi_query($con, $query);
You need to get the last inserted id using mysqli_insert_id()
And then use it to the next query like below:-
$query = "INSERT INTO playground (parent, children, amount) VALUES ('John','susy','2000');";
if(mysqli_query($con,$query)){
$id = mysqli_insert_id($con); //get last inserted id
$levelarray = array ("One", "Two", "Three");
for ($i = 0; $i < count($levelarray); $i++) {
$level = $levelarray[$i];
$query = "INSERT INTO guardian (playgroundid, level) VALUES ($id,'$level');";
mysqli_query($con,$query) ;
}
}
Or you can still use mysqli_multi_query()
like below:-
$query = "INSERT INTO playground (parent, children, amount) VALUES ('John','susy','2000');";
if(mysqli_query($con,$query)){
$id = mysqli_insert_id($con); //get last inserted id
$query1 = '';
$levelarray = array ("One", "Two", "Three");
for ($i = 0; $i < count($levelarray); $i++) {
$level = $levelarray[$i];
$query1 .= "INSERT INTO guardian (playgroundid, level) VALUES ($id,'$level');";
}
mysqli_multi_query($con, $query1);
}
Note:- Your query is wide open for SQL INJECTION
. so try to use prepared statement
to prevent from it.