Getting index number of just added row.

[LucL]

Say I have a table, 'customers'

c_id INTEGER UNSIGNED AUTO_INCREMENT,
c_name VARCHAR(225),
c_phone VARCHAR(50),

Now I'm inserting a new item using the INSERT command, is it possible to within that same call get the c_id (last index) of the newly inserted row?

I know this is possible because I've done it before but I can't for the life of me remember how.

Thanks!
Luc L.

 

[dabruins]

Try:

"Select last_insert_id() AS Latestid"

If you are locking you table prior to the insert place this statement in before you release the lock otherwise you may get the id from another users insert to the table.

 

[LucL]

dabruins. Thanks. That works. Is there any way to insert it alongside the INSERT command so I don't have to have to mysql queries?

Thanks!

 

[johnwm]

Before using LAST_INSERT_ID() make sure you carefully read the appropriate section of:

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

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[dabruins]

It seems that this on a per-session-basis so locking may not play a part in this.

"The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client."

Thanks johnwm!

I don't think you can obtain the last_insert_id() while performing an insert. The insert statement must precede the call to last_insert_id().

 

[LucL]

so is it safe to use the last_insert_id if we are unable to lock the db? is it possible that if another user adds a record at the same time the wrong data may get pulled?

 

[dabruins]

That is what the documentation is saying. The last_insert_id() function returns the id of the last record inserted for the session it is being called in. That means that if the session is not shared by multiple users it will only return the id of a record you have inserted in your session.

 

[LucL]

cool. thanks a lot guys.

 

[guelphdad]

You can use last_insert_id() in an insert query. But first are you thinking you need it for the next insert into the customers table? If that is what you are thinking you don't need to do that obviously as the next insert for c_name and c_phone will just get a new c_id added for the next row.

If you mean you have another table where you want the just inserted c_id to include with your other columns for that table so you have the proper c_id to associate with the customer table then just do:

insert 
into anothertable (c_id, foo, bar, qux) 
values
(last_insert_id(),'grapes','snow','bears')

 

[LucL]

ahh. pretty cool trick. thanks.