insert id

[lonelydragon]

i am insert some record using "insert select", and in some case, there are several record are inserted once, i am wondering is it possible to get the all the insert id without having to write down sother queries "select .....";
thank you in advance.

 

[sleipnir214]

What database are you using?


Want the best answers? Ask the best questions! TANSTAAFL!

 

[lonelydragon]

mysql

 

[sleipnir214]

No, if you want to deterministically know the auto_increment insert IDs of each record inserted, you'll have to collect each auto_increment ID after each insert. My design, MySQL only tracks the last-generated ID for each connection. If you don't pull that ID out after every insert, the next insert will overwrite the value.

However, it's not necessary to issue a SELECT query after every INSERT query. Just use PHP's mysql_insert_id() function (

http://www.php.net/manual/en/function.mysql-insert-id.php

) after each insert.


Want the best answers? Ask the best questions! TANSTAAFL!

 

[lonelydragon]

the point is the inserts are done with just one query. so how to collect the ids? thanks

 

[sleipnir214]

You don't.

If you do it as a single query, you will only ever be able to fetch the last ID generated. This is a limitation of MySQL, but a reasonable one.


Want the best answers? Ask the best questions! TANSTAAFL!

 

[lonelydragon]

how is this:
the max id : last insert id;
the first id : max id - afexted row;

list of ids: between "the first id" and "the max id

 

[Olavxxx]

You can simply build up an array as your script retrieves insert id's.

Or, you can add them all to a string, which you later explode into an array

ref.:

http://php.net/explode

http://php.net/array

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster

 

[sleipnir214]

lonelydragon:
The problem with doing the math is that it may not be multiuser safe. I do not know whether a multi-insert is handled by MySQL as a single transaction or not.


Want the best answers? Ask the best questions! TANSTAAFL!

 

[skiflyer]

lonelydragon:
There's a second problem with that... if you're removing records & compacting the database you could technically be returning sequence values to the range of new IDs.

If I remember right MySQL actually does do this, whereas many other DBs just increment a sequence... either way it's one of the realm of possibilities you should consider.