How do I get all the ids of the row created by one multiple row insert statement.

Question

Hi All. I'm new to php. So, please forgive me if this seems like a dumb question.

Say i have a MySQL insert statement insert into table (a,b) values (1,2),(3,4),(5,6). table 'table' has a auto increment field called 'id'.

how can I retrieve all the ids created by the insert statement above?

It will be great if i get an example that uses mysqli.

Answer 1

You can't. I would suggest that you maintain your own ids (using guid or your own auto-increment table) and use it when you insert into the table.

But it's possible to get the auto-increment value for the last inserted using LAST_INSERT_ID():

http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

Answer 2

as a follow up to AngeDeLaMort: You could seperate your inserts and do it something like this:

$data = array (
    array(1,2),
    array(3,4),
    array(5,6)
);
$ids = array();

foreach ($data as $item) {
   $sql = 'insert into table (a,b) values ('.$item[0].','.$item[1].')';
   mysql_query ($sql);
   $id[] = mysql_insert_id();
}

Now all your new id's are in the $id array.

Answer 3

AngeDeLaMort's answer is almost right. Certainly, the most appropriate way to deal with the problem is to insert one row at a time and poll the insert_id or generate the sequence elsewhere (which has additional benefits in terms of scalability).

I'd advise strongly against trying to determine the last insert_id and comparing this the most recent insert_id after the insert - there's just too may ways this will fail.

But...an alternative approach would be:

....
"INSERT INTO destn (id, data, other, trans_ref) 
 SELECT id, data, other, connection_id() FROM source";
....
"SELECT id FROM destn WHERE trans_ref=connection_id()";
....
"UPDATE destn SET trans_ref=NULL where trans_ref=connection_id()";

The second query will return the ids generated (note that this assumes that you use the same connection for all 3 queries). The third query is necessary because connection ids to go back into the pool when you disconnect (i.e. are reused).

C.

Answer 4

Maybe I can do this

$insert = "insert into table (a,b) values (1,2),(3,4),(5,6)";
$mysqli->query($insert);
$rows_to_be_inserted=3;
$inserted_id = $mysqli->insert_id // gives me the id of the first row in my list
$last_row_id = ($inserted_id+$rows_to_be_inserted)-1;
$mysql->query("select * from table where id between  $inserted_id and $last_row_id");

what to you guys say?