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

I'm new to php. So, please forgive me i开发者_C百科f 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.

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

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.

In some cases, if you have another identifier of sort such as a UserID, you could filter your query by UniqueID's greater than or equal to mysql_insert_id(), limit by the number of affected rows and only display those by the user. This would really only work inside of a transaction.

$SQL = "INSERT INTO Table
       (UserID, Data)
       VALUES
       (1,'Foo'),
       (1,'Bar'),
       (1,'FooBar')";

$Result = mysql_query($SQL);
$LastID = mysql_insert_id();
$RowsAffected = mysql_affected_rows();

$IDSQL = "SELECT RecordID
          FROM Table
          WHERE UserID = 1
          AND RecordID >= '$LastID' 
          LIMIT '$RowsAffected'";
$IDResult = mysql_query($IDSQL);

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.

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?