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:

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'

CodePudding user response：

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)

CodePudding user response：

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