mysql - PHP foreach with resetting lastInsertId breaks the loop
I have a JSON data being sent to a php script. If there is more than one items in the array want to link them together in the parent field. First item's id should be taken for this and filled all the following items.
Table definition:
| id | parent | quantity | html |
|---|---|---|---|
| 2 | 1 | 3 | Product 2 |
| 1 | null | 2 | Product 44 |
Answer
Solution:
Finally, I found that the column had unique flag switched on and was refusing duplicates in this table. Hence only 2 items were inserted.
I changed my PDO details to this to discover the error:
array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
Answer
Solution:
You're not resetting $parent to be null at the start of each loop, or if thats not wanted you're giving it the wrong ID.
Your line initially works here
$pdo->prepare($sql)->execute([$parent,$i->count,$i->item]); because $parent is null.
but the next time it runs your $parent = $pdo->lastInsertId();
your $sql query being built outside of the loop is now getting the wrong ID.
Also, your $key == 0 that will only run on the first loop, so it doesn't actually have a lastInsertId anyway.
Im reading this to understand more: LAST_INSERT_ID() MySQL
Source