PDO - lastInsertId() for insert query with multiple rows

Question

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.

Ola

Answer 1

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

Charbonnier

what about this? dev.mysql.com/doc/refman/5.1/en/… – Olivaolivaceous

it's possible get last_insert id and add count of row inserted(-1) to got the real last id, isn't? – Olivaolivaceous

@Peymanabdollahy not it's not. You cannot guarantee nothing was inserted between you inserted your first and second row. – Charbonnier

do you read this article? dev.mysql.com/doc/refman/5.1/en/… – Olivaolivaceous

@Peymanabdollahy do you have anything particular to say - if yes, say that explicitly. I don't have time to play your games, sorry. – Charbonnier

that article sayed for every statement the table gone be locked and this guarantee every record save consecutively – Olivaolivaceous

@Peymanabdollahy I have not actually knew that. If you add your detailed answer with that I'll put +50 bounty for this really helpful information. Thanks. – Charbonnier

INSERT IGNORE will 'burn' ids (skip over them) if they hit duplicate pet names. – Ger

Answer 2

you can add 1 to last insert id to achieve the real last record id.and if you insert more than 2 record just add rowCount - 1 to last_insert_id.

With innodb_autoinc_lock_mode set to 0 (“traditional”) or 1 (“consecutive”), the auto-increment values generated by any given statement will be consecutive, without gaps, because the table-level AUTO-INC lock is held until the end of the statement, and only one such statement can execute at a time.

and

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

for more info read this document http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

Olivaolivaceous

this is the answer, just read the article. why negative point? – Olivaolivaceous

As promised - I will put bounty here (100, not 50 though, since 50 is not available) in 24 hours (SO does not let me do that now) – Charbonnier

@Charbonnier I not recommend to use this approach. Try insert: INSERT INTO test_table` (id, name) VALUES (null, 'Example'), (15, 'Example'), (null, 'Example');` This code will not work for this case. – Dart

@Dart ... it's true the code will not work when explicitly inserting auto_increment Id's. But if you are letting mysql manage the auto_increment id's this approach will work fine. – Tasker

Answer 3

Please, please avoid the type of solutions given by Bruce. The auto increment value can be set to be different than 1. For instance in case of master to master replication. A little while back a host where one of our applications runs on changed their db setup. I happened to notice -purely by coincidence- that all of a sudden the id's incremented with two instead of one. Would we have had any code like this it could have caused us serious problems.

Carbrey

Answer 4

The documentation states: Returns the ID of the last inserted row or sequence value

You will need to perform two queries to get the id for each inserted row.

Galactopoietic

Answer 5

Well, since 1000 inserts is a bit longer than 1 insert, the issue of topic is still interesting. Possible workaround would be to make 2 queries. The first one is to insert 1000 rows and the second one is to select them if there is something unique in those inserted rows. For example with pets:

INSERT INTO pets ( name ) VALUES ( 'coco', 'jojo' );
SELECT id FROM pets WHERE name IN ( 'coco', 'jojo' );

This could give benefit only for big data sets.

Fellows

(You would want SELECT name, id FROM ... so that you can be sure of how they match.) (My experiences says that anything over 100 rows in a batch is about 10 times as fast as 1 row at a time.) – Ger

Answer 6

I tried to assume that is is not possible until I tried it on my own and figured that IT IS POSSIBLE.

After each execute, add the lastInsertId() and assign a key.

For my example:

// First query
$sql = "INSERT INTO users
(username, email)
values
(:username, :email)";
$sth = $this->db->prepare($sql);
$sth->bindParam(':username', $data['username'], PDO::PARAM_STR);
$sth->bindParam(':email', $data['email'], PDO::PARAM_STR);

// Second Query
$sql2 = "INSERT INTO users
(username, email)
values
(:username, :email)";
$sth2 = $this->db->prepare($sql2);
$sth2->bindParam(':username', $data['username'], PDO::PARAM_STR);
$sth2->bindParam(':email', $data['email'], PDO::PARAM_STR);

// Start trans
$this->db->beginTransaction();

// Execute the two queries
$sth->execute();
$last['ux1'] = $this->db->lastInsertId(); // <---- FIRST KEY

$sth2->execute();
$last['ux2'] = $this->db->lastInsertId(); // <---- SECOND KEY

// Commit
$this->db->commit();

And I was able to retrieve the two last inserted ids

Array ( [create] => Array ( [ux1] => 117 [ux2] => 118 )

)

I hope this will help others who are seeking the right answer.

Upstairs

You have 2 prepared statements, OP has only one with only one query with bulk insert. So it's not wrong, but it does not match to what OP has. – Charbonnier

What about performance if instead of two queries would 100 queries? – Marybellemarybeth

Answer 7

I used the array in the first statement and in the second add if condition. So If insert record get its last id then make the second statement. If insert the second array get its last id then repeat the second statement

    for ($count=0; $count < count($_POST["laptop_ram_capacity"]); $count++) { 
        $stmt = $con->prepare('INSERT INTO `ram` 
                (ram_capacity_id, ram_type_id, ram_bus_id, ram_brand_id, ram_module_id)
                VALUES (?,?,?,?,?)');

            //excute query//
            $stmt->execute(array(
                            $_POST['laptop_ram_capacity'][$count],
                            $_POST["laptop_ram_type"][$count],
                            $_POST["laptop_ram_bus"][$count],
                            $_POST["laptop_ram_brand"][$count],
                            $_POST["laptop_ram_module"][$count]
                            ));
            //fetsh the data//
            $rows = $stmt->rowCount();
            // i add the below statment in if condition to repeat the insert and pass the repeated of the last statement//
            if ($rows > 0) {
            $LASTRAM_ID = $con->lastInsertId();

            $stmt = $con->prepare('INSERT INTO `ram_devicedesc_rel` 
                (ram_id, Parent_device_description_id)
                VALUES (?,?)');

            //excute query//
            $stmt->execute(array(
                            $LASTRAM_ID,
                            $LASTdevicedesc_ID
                            ));
            //fetsh the data//
            } else {
                echo "Error: " . $sql . "<br>" . $conn->error;
            }                                    
        }

Amour

Answer 8

you can use 'multi_query()' add all the query into one variable like : '$sql1 = "first insert";' '$sql2 = "second insert";' '$sql3 = "third insert";' and so on... take the count of number of inserts your gonna make. now execute all the '$sql' queries using 'multi_query()'. get the last insert id. now all you have to do is '$rowno = $lastinsertid - $countofinserts'. so basically you will get a number. add 1 to it and from the resulting number to the lastinsertid are the insert id's of the insert queries you ran

Squashy

Do not use multi_query; it has security implications. – Ger

Answer 9

It is possible! All you need to do is call the PDO lastInsertId() method. This will return the first inserted id when doing multiple or bulk INSERT. Next we need to perform a simple addition subtraction operation with the number of rows affected by the last INSERT statement.

$firstInsertedId = $this->lastInsertId();
$InsertedIds = range($firstInsertedId, ($firstInsertedId + ($stmt->rowCount() - 1)) );
print_r($InsertedIds);

Jotter

I would not recommend to do math on it. The best way is to do each per insert. – Upstairs

@RickJames I not recommend to use this approach. Try insert: INSERT INTO test_table (id, name) VALUES (null, 'Example'), (15, 'Example'), (null, 'Example'); This code will not work for this case. – Dart