getting id of inserted record

[blasterstudios]

I am trying to create a new record in a users table. At the same time, I want to insert 2 of the values into a different table. At the same time, I want to get the id (which is an auto-increment field) of the new user and insert it along with 2 other fields into the 2nd table. Is it possible to find out what ID is being inserted?

An idea was to just run a query after it's done inserting the first record and sort it DESCending by ID and the highest one will be the newest one. Anybody see any problems with that or know of a better way?

 

[sleipnir214]

Your way is not multi-user safe.

Let's say two people are running your app at the same time, you and Fred. Your run of the script inserts a user record, then Fred's run of the script inserts a user record. Then your run of the script performs the DESC select. Your run of the script gets the ID for the record inserted by Fred.

MySQL has a very nice workaround in its last_insert_id() function, which returns the value of the last-generated auto_increment ID on your connection to the database. It's designed to be concurrency-safe. See

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html

about half-way down. You'll have to issue a second query to MySQL to fetch this value.

Also, your backend scripting language may have a function to fetch this value without issuing another query. PHP, for example, provides the mysql_insert_id() function.

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[blasterstudios]

awesome, that works even better. thanks.