Retrieving an auto_increment field after an insert

i3bargon

I have an id field that is auto incremented when a new record is inserted into a table. Is their a way i can retrieve the id number immediately after i perform the update. I undersatnd how to retreive fields from a database but this is the index I use for this table and i dont want to just retreive the last one id that was inserted becasue someone else could of inserted a record at the same time, which would lead to somewhat of a race condition. Is their something i could put in my Insert SQL command that will return the id that was just created for that insert.

Thanks in advanced.

planetsim

you can try after you insert the info to the database

mysql_query("SELECT * FROM tbl_name ORDER BY id DESC LIMIT 1");

But i havent Tested that..

Hope it works for you

i3bargon

Thanks for the response. What happens if at that same instance someone else inserts a record into the table, is it a possiblity that when i try the select with the order on id that i will retreive the other record that was inserted just after i did my insert. I know that is far fetched but, from what im thinking it could happen. But i could be wrong.

Ira

eddyk

I was doing a quick search to look for the same information you were, but then I remembered that I have the solution to it somewhere in my existing code.

I found it and am posting here, so others (including myself) can find the answer more easily.

look up the php function:
mysql_insert_id()

ah, hell. I'll look it up for you:
(PHP 3, PHP 4 )
mysql_insert_id -- Get the ID generated from the previous INSERT operation

Description
int mysql_insert_id ( [resource link_identifier])

mysql_insert_id() returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query using the given link_identifier. If link_identifier isn't specified, the last opened link is assumed.

gid

Ya, [man]mysql_insert_id()[/man] works great, I've been using it for quite awhile in lots of code without trouble. It takes care of any race conditions for you, I wouldn't doddle before grabbing the id tho.

I just wish postgresql had such an easy function like mysql_insert_id(), instead you have to mess around with transactions and select curval('sequence_name') or some such junk. It makes it hard to write database independent code 🙁 I'm pretty sure it's a postgresql deficiency, and not php's fault for not exposing it tho.