MySQL Query Question

[clamum]

I am inserting some data into a MySQL database with the standard INSERT statement. One of the fields in the table is a customer_id field, with an auto incrementing id.

After inserting the record successfully, I would like to retreive that customer_id to use in a subsequent INSERT statement.

Right now I'm doing a SELECT statement which matches every other field except the id field to find the just added record... however this could technically fail because a record could have all the same values except that customer_id field (which is the primary key).

So, any ideas on how I could do this? While typing out this post I thought I could find the largest customer_id in the table (since it's auto incrementing) after the first INSERT operation and that should be the one that was just inserted. But it is possible that a record could have been inserted in this time I suppose... though the website this is for is not a high traffic site, so I may be safe.

 

[Armitage]

Use the last_insert_id() function - this is session specific so if another session (client) is also inserting data it won't get screwed up.

 

[imported_LoKe]

SELECT id FROM table ORDER BY id DESC LIMIT 0,1

That will select the entry corresponding to the higest ID number.

 

[Armitage]

Originally posted by: LoKe
SELECT id FROM table ORDER BY id DESC LIMIT 0,1

That will select the entry corresponding to the higest ID number.

Or simpler
SELECT max(id) FROM table;

But he's concerned about concurrent inserts from other processes which may take place between the insert, and the second query to get the ID created by that insert.

 

[clamum]

Armitage that looks like it'll do... thanks guys.