Get last inserted auto increment using php?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Get last inserted auto increment using php?

Post 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?
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Get last inserted auto increment using php?

Post 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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
chadhaajay
Forum Newbie
Posts: 2
Joined: Sat Jul 28, 2007 4:45 am

Post 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
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Get last inserted auto increment using php?

Post 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`).
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

What would that return if the INSERT failed? In a transaction you could roll it back.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
Post Reply