2

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);
Improve this question
7
  • @TommyBs sorry for the typo, When i entered here i missed it, but the code column name was alright. Commented Apr 25, 2018 at 11:15
  • Why don't you try that and see what happens? Commented Apr 25, 2018 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) } } Commented Apr 25, 2018 at 11:16
  • May this is useful to you stackoverflow.com/a/13849004/2893413 Commented Apr 25, 2018 at 11:17
  • @AlivetoDie - Post it as an answer... Commented Apr 25, 2018 at 11:17

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Is it = or .=
it is =. query executed inside loop
@TheHive glad to help you :):)
Hey! Is it possible to use it from database like $_POST['levels]
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.

Improve this answer

1 Comment

yes you are right, the id was mixed up when inserting three rows.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.