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.

score 16 · Accepted Answer · answered Sep 25 '12 at 00:31

16

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

answered Sep 25 '12 at 00:31

zerkms

249,484
69
436
539

what about this? http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html – Peyman abdollahy Jun 24 '15 at 13:36
1

it's possible get last_insert id and add count of row inserted(-1) to got the real last id, isn't? – Peyman abdollahy Jun 25 '15 at 09:48
@Peymanabdollahy not it's not. You cannot *guarantee* nothing was inserted *between* you inserted your first and second row. – zerkms Jun 25 '15 at 10:19
1

do you read this article? http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html – Peyman abdollahy Jun 25 '15 at 13:20
@Peymanabdollahy do you have anything particular to say - if yes, say that explicitly. I don't have time to play your games, sorry. – zerkms Jun 25 '15 at 19:50
that article sayed for every statement the table gone be locked and this guarantee every record save consecutively – Peyman abdollahy Jun 25 '15 at 20:24
@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. – zerkms Jun 25 '15 at 21:10
`INSERT IGNORE` will 'burn' ids (skip over them) if they hit duplicate pet names. – Rick James Jul 02 '15 at 20:40

Peyman abdollahy · Answer 2 · 2015-06-26T03:44:29.673

9

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

edited Jun 26 '15 at 03:44

answered Jun 26 '15 at 03:35

Peyman abdollahy

799
1
8
18

this is the answer, just read the article. why negative point? – Peyman abdollahy Jun 30 '15 at 08:33
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) – zerkms Jul 02 '15 at 04:44
@zerkms 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. – IStranger Sep 20 '16 at 19:26
2

@IStranger ... 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. – mcmurphy Sep 11 '18 at 18:55

score 3 · Answer 3 · answered Apr 17 '14 at 11:33

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.

score 2 · Answer 4 · answered Sep 25 '12 at 00:32

2

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.

answered Sep 25 '12 at 00:32

Trevor

6,659
5
35
68

zelibobla · Answer 5 · 2015-07-03T06:05:10.510

1

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.

edited Jul 03 '15 at 06:05

answered Dec 04 '13 at 10:32

zelibobla

1,498
1
16
23

(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.) – Rick James Jul 02 '15 at 20:36

score 1 · Answer 6 · answered May 30 '14 at 00:01

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.

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. — zerkms, Jul 02 '15 at 04:41
What about performance if instead of two queries would 100 queries? — user2360831, Jul 24 '21 at 17:43

score 0 · Answer 7 · edited Oct 22 '18 at 10:07

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;
            }                                    
        }

score -1 · Answer 8 · answered Jun 05 '15 at 14:05

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

Do not use `multi_query`; it has security implications. – Rick James Jul 02 '15 at 20:37 — Rick James, Jul 02 '15 at 20:37

Bruce · Answer 9 · 2014-01-07T10:16:58.463

-3

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);

edited Jan 07 '14 at 10:16

answered Mar 15 '13 at 19:49

Bruce

63
1
5

I would not recommend to do math on it. The best way is to do each per insert. – Louie Miranda May 29 '14 at 08:55
@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. – IStranger Sep 20 '16 at 19:27

PDO - lastInsertId() for insert query with multiple rows

9 Answers9

Linked