Page 1 of 1

Get last inserted auto increment using php?

Posted: Sun Jul 29, 2007 6:00 pm
by Citizen
I've heard of using "mysql_insert_id", but is that the best way of getting the id of the last inserted row?

Posted: Sun Jul 29, 2007 6:06 pm
by Chalks
The easiest way for an auto-increment is using max():

Code: Select all

SELECT max( id ) FROM `table`

That's assuming you're using an id field that auto increments... which makes the last inserted the largest number.

Re: Get last inserted auto increment using php?

Posted: Sun Jul 29, 2007 6:11 pm
by Benjamin
Citizen wrote:I've heard of using "mysql_insert_id", but is that the best way of getting the id of the last inserted row?
Yes

Posted: Sun Jul 29, 2007 7:03 pm
by superdezign
Chalks wrote:The easiest way for an auto-increment is using max():

Code: Select all

SELECT max( id ) FROM `table`

That's assuming you're using an id field that auto increments... which makes the last inserted the largest number.
That's a waste of a query.

Posted: Mon Jul 30, 2007 4:50 am
by chadhaajay
I agree with both appraoches. sometimes using a query can be of best use.

Sincerely,

Ajay Chadha (Director)
Chadha Software Technologies

Knowledge Base Software - PHPKB
Search Scripts - Scripts Resource Directory

Posted: Mon Jul 30, 2007 4:57 am
by volka
If a concurrent thread/process inserts another record "between" INSERT and SELECT Max(id) you have a race condition and the value returned might not be what you expect or want.

Re: Get last inserted auto increment using php?

Posted: Mon Jul 30, 2007 5:15 am
by onion2k
Citizen wrote:I've heard of using "mysql_insert_id", but is that the best way of getting the id of the last inserted row?
That will get the id of the auto_increment column value in the left insert made using your current connection. If you want to get the id of the last inserted row regardless of connection then you should use a query to fetch MAX(`id`).

Posted: Mon Jul 30, 2007 7:35 am
by volka
Which might also not be the value you expect if the record with the highest id is deleted. (possible even if unlikely).
e.g. the result of

Code: Select all

SHOW TABLE STATUS LIKE tablename
includes the auto_increment counter value.

Posted: Mon Jul 30, 2007 7:48 am
by superdezign
The surest way is to get the last entry that matches the data that you inserted into it (assuming all of them are generally unique). That's why I always use more information than I'll need, such as saving dates for almost all actions.

Posted: Mon Jul 30, 2007 8:21 am
by onion2k
superdezign wrote:The surest way is to get the last entry that matches the data that you inserted into it (assuming all of them are generally unique). That's why I always use more information than I'll need, such as saving dates for almost all actions.
Actually the surest way is to a database engine that supports transactions like InnoDB, and to use them.

Posted: Mon Jul 30, 2007 8:31 am
by superdezign
onion2k wrote:
superdezign wrote:The surest way is to get the last entry that matches the data that you inserted into it (assuming all of them are generally unique). That's why I always use more information than I'll need, such as saving dates for almost all actions.
Actually the surest way is to a database engine that supports transactions like InnoDB, and to use them.
Or if we could run a SELECT and an INSERT / UPDATE at the same time.

Code: Select all

INSERT INTO `table` SET `foo` = 1 ALSO SELECT LAST_INSERT_ID() FROM `table`
:-D

Posted: Mon Jul 30, 2007 9:07 am
by onion2k
What would that return if the INSERT failed? In a transaction you could roll it back.

Posted: Mon Jul 30, 2007 9:40 am
by superdezign
onion2k wrote:What would that return if the INSERT failed? In a transaction you could roll it back.
Transactions are a foreign concept to me... I should look into them.