Take the 2-minute tour ×

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.

share|improve this question
If you know how many you inserted, maybe you can do something with the MAX(id) summing query? –  Nitrodist May 28 '10 at 12:10
Why do you need all these id's? What is the data you inserting? –  Your Common Sense May 28 '10 at 12:10
@Nitrodist oh no! Write it thousands times: id is not a number! –  Your Common Sense May 28 '10 at 12:12
@Col. Sharpnel: I'm using php as backend for a flex client. So, I need to send back list of all the objects i just created. For that i need the ids of the items I just created so that I can run a select query and build the results –  Curious Jo May 28 '10 at 14:10
Myabe SELECT ID FROM table WHERE id > MAX(ID) - 3; –  Nitrodist May 28 '10 at 17:27
add comment

5 Answers

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():


share|improve this answer
I don't trust last_insert_id() as there might some other request that would insert rows into some other table. Please correct me if i am wrong. –  Curious Jo May 28 '10 at 14:18
@Curious Jo php.net/last_insert_id can correct you. –  Your Common Sense May 28 '10 at 14:57
add comment

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


share|improve this answer
add comment

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.

       (UserID, Data)

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

          FROM Table
          WHERE UserID = 1
          AND RecordID >= '$LastID' 
          LIMIT '$RowsAffected'";
$IDResult = mysql_query($IDSQL);
share|improve this answer
add comment

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

$data = array (
$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.

share|improve this answer
I was thinking of this too. But isns't it in efficient? i mean hitting the database many times? –  Curious Jo May 28 '10 at 14:13
add comment

Maybe I can do this

$insert = "insert into table (a,b) values (1,2),(3,4),(5,6)";
$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?

share|improve this answer
add comment

Your Answer


