Maverick Maverick - 2 months ago 16
MySQL Question

PDO - lastInsertId() for multiple insert query

I can insert 2 pets into a table, and get their lastInsertId() for further processing one at a time (2 queries).
I am wondering if there is a way to get two lastInsertIds() and assign them to variables if I am inserting 2 rows in 1 query:

$query = "INSERT INTO pets (pet_name) VALUES (':coco'),(':jojo')";
$pet_insert = $dbh->prepare($query);
$pet_insert->execute(array(':coco' => $coco,':jojo' => $jojo));
$New_PetID = $dbh->lastInsertId();


Is it possible to get the lastInsertId() for coco and for jojo? So something like:

$New_PetID1 = $dbh->lastInsertId();//coco
$New_PetID2 = $dbh->lastInsertId();//jojo


This will give the same ID, any way to get the 2 IDs? Just for reference, this is in a try block.

Answer

It's not possible. If you need generated ids for both rows - you need to perform 2 separated INSERT

Important If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.

http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id