CODEWITHSUNDEEP
phpmysqlarrays
user3560703
user3560703

Reputation:

Get array of Last Inserted Ids mysql php

I have a query like Insert into tbl(str)values('a'),('b'),('c')

if i had a single insert then by using mysqli_insert_id($con) i could get last id inserted but how get all ids inserted in this multiple insert query?

Upvotes: 1

Views: 1321

Answers (2)

flaschenpost
flaschenpost

Reputation: 2235

If it is just for a few rows, you could switch to sending individual inserts with last_insert_id(). Otherwise it will slow down your application notably. You could make a marker for those bulk inserts, which gets set to a number identifying this bulk insert at the bulk insert itself and you can fetch those ids later on:

insert into tbl (stuff, tmp_marker) values ("hi",1715),("ho",1715),("hu",1715);

select group_concat(id) from tbl where tmp_marker = 1715;

update tbl set tmp_marker=0 where tmp_marker=1715;

If those bulk inserts have a meaning, you could also make a table import_tbl with user and time and filename or whatever and keep the 1715 as reference to that import.

EDIT: After discussion, I would go to an import-table

CREATE TABLE import (id int(11) not null auto_increment primary key, user_id int(11), tmstmp timestamp);

When an import starts, insert that:

INSERT INTO import set user_id = $userId;

in php:

$importId = last_insert_id();

$stmt = "insert into tbl (str, import_id) values ('a',$import_id), ('b', $import_id),('c',$importId);

Then you can do whatever you want with the id of your recently imported rows.

I have not made research if a multi-row-insert is guaranteed to lead to a consecutive row of IDs, as in a combination of last_insert_id() and num_rows is presupposed. And if that stays so, even when MySQL increases parallelization. So I would see it as dangerous to depend on it.

Upvotes: 0

underscore
underscore

Reputation: 6887

This behavior of last_insert_id() is documented in the MySQL docs:

The currently executing statement does not affect the value of LAST_INSERT_ID(). Suppose that you generate an AUTO_INCREMENT value with one statement, and then refer to LAST_INSERT_ID() in a multiple-row INSERT statement that inserts rows into a table with its own AUTO_INCREMENT column. The value of LAST_INSERT_ID() will remain stable in the second statement; its value for the second and later rows is not affected by the earlier row insertions. (However, if you mix references to LAST_INSERT_ID() and LAST_INSERT_ID(expr), the effect is undefined.)

IF you really need it you can test it using foreach with array_push

 <?php

$InsetQueryArray = array(
    "Insert into tbl(str) values('a')",
    "Insert into tbl(str) values ('b')",
    "Insert into tbl(str) values('c')"
);

$allLasIncrementIds = array();

foreach ($InsetQueryArray as $value) {

    //execute it mysql 
    //Then use array_push 

    array_push($allLastIncrementIds, mysqli_insert_id($con));

}

?>

Upvotes: 2

Related Questions