Search code examples
mysqlpdolast-insert-id

PDO - lastInsertId() for insert query with multiple rows

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.

Solution

  • 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