PHP foreach with resetting lastInsertId breaks the loop

Question

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

Json input:

[{"count":"3","item":"Product 1"},{"count":"1","item":"Product 5"},{"count":"1","item":"Product 7"},{"count":"1","item":"Product 44"}]

Php code:

$items = json_decode($_POST["json"]); 
$parent =  null;
$sql = "INSERT INTO orders (parent,quantity,html) VALUES (?,?,?)"; 
if(is_array($items)) {
    foreach ($items as $key=>$i) {
        $pdo->prepare($sql)->execute([$parent,$i->count,$i->item]);
        if (count($items) > 1 && $key == 0) {
            $parent = $pdo->lastInsertId(); 
        };
    };
};

The problem I have with this code is that the insert code breaks after 2 runs of the loop. No matter the amount of items in the JSON, the code only inserts first two items. I believe it has something to do with the if statement within the loop. If I remove it all items get inserted but then the parent column is null for all of them. This should be the case only for the first item in the group. All following ones should take the first id and reuse it in the parent column. Any ideas?

Finally, when switching error mode on I see this is thrown:

Uncaught PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1753' for key 'parent'

Could you also add a sample JSON (similar to the one related to the issue)? — Eduard Uta, Jan 19 '22 at 23:24
There's nothing here that breaks out of the loop. Is it getting an error? — Barmar, Jan 19 '22 at 23:24
BTW, there's no need to prepare the statement every time through the loop. Since the statement doesn't change, prepare it once before the loop. — Barmar, Jan 19 '22 at 23:27
Changed the post to add the example of the JSON array. If i print it within the loop I see it iterates through all of them but only two of them get inserted into the database. — jq1080, Jan 19 '22 at 23:28
This seems like a strange way to structure your data. The usual way is with two tables: `orders` and `order_items`. Then `order_items` has a foreign key to `orders`. — Barmar, Jan 19 '22 at 23:29
I know, but I want to keep two dimension in one table and then I just group it together from two columns in the same table. I also added the sample table definition. It might have something to do with the order of the loop. — jq1080, Jan 20 '22 at 00:00

score 0 · Accepted Answer · answered Jan 20 '22 at 00:41

0

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)

answered Jan 20 '22 at 00:41

jq1080

47
6

score 0 · Answer 2 · answered Jan 20 '22 at 02:52

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

PHP foreach with resetting lastInsertId breaks the loop

2 Answers2