Is it possible to use LAST_INSERT_ID() in php for-loop?

Question

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);

@TommyBs sorry for the typo, When i entered here i missed it, but the code column name was alright. — Dexter, Apr 25 '18 at 11:15
do like this:- `$query = "INSERT INTO playground (parent, children, amount) VALUES ('John','susy','2000');"; if(mysqli_query($con,$query)){ $id = mysqli_insert_id($con); $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_query($con,$query) } }` — Alive to die - Anant, Apr 25 '18 at 11:16
May this is useful to you https://stackoverflow.com/a/13849004/2893413 — Sadikhasan, Apr 25 '18 at 11:17
FYI, lines 4 and 5 can be written much easier as foreach ($levelarray as $level) { — Cuagau, Apr 25 '18 at 11:17
Using prepared statements and bind variables will make your code more secure and you can prepare the insert once and execute it with the individual values. — Nigel Ren, Apr 25 '18 at 11:22

Alive to die - Anant · Accepted Answer · 2018-04-25T11:48:46.780

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.

Hey! Is it possible to use it from database like `$_POST['levels]` — Dexter, Apr 25 '18 at 14:01

score 2 · Answer 2 · answered Apr 25 '18 at 11:14

2

No, this won't work. The first guardian will get the id of the playground, and the next two will get the id of the previous guardian.

Use mysqli_insert_id() instead to get the id and save it as a variable outside of the for loop.

answered Apr 25 '18 at 11:14

Cuagau

548
3
7

yes you are right, the id was mixed up when inserting three rows. – Dexter Apr 25 '18 at 11:16

Is it possible to use LAST_INSERT_ID() in php for-loop?

2 Answers2