Getting the auto increment key value

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
eazyGen
Forum Commoner
Posts: 46
Joined: Mon Aug 29, 2011 4:32 am
Location: Central London

Getting the auto increment key value

Post by eazyGen »

Hi Guys,

Using MySQL, I have a table with an auto increment key. I add rows to this table happily enough.

However, is there a simple way of obtaining from the db the value that was allocated by the auto increment?

Many thanks in advance,

S
mikeashfield
Forum Contributor
Posts: 159
Joined: Sat Oct 22, 2011 10:50 am

Re: Getting the auto increment key value

Post by mikeashfield »

Yes:

Code: Select all

SELECT auto_increment_field_name FROM auto_increment_table WHERE a_field_name_you_know = a_value_in_that_field_you_know;
Or if you mean you want to know the value of the last inserted record, then:

Code: Select all

SELECT auto_increment_field_name FROM auto_increment_table ORDER BY auto_increment_field_name DESC LIMIT 1;
User avatar
twinedev
Forum Regular
Posts: 984
Joined: Tue Sep 28, 2010 11:41 am
Location: Columbus, Ohio

Re: Getting the auto increment key value

Post by twinedev »

Immediately after the insert use the function mysql_insert_id()

Code: Select all

$rsInsert = mysql_query('INSERT INTO `tblName` (`Field1`,`Field2`) VALUES ("data1","data2")');
$intID = mysql_insert_id();
See http://php.net/mysql_insert_id for more information.

-Greg
User avatar
eazyGen
Forum Commoner
Posts: 46
Joined: Mon Aug 29, 2011 4:32 am
Location: Central London

Re: Getting the auto increment key value

Post by eazyGen »

mikeashfield wrote:Yes:

Code: Select all

SELECT auto_increment_field_name FROM auto_increment_table WHERE a_field_name_you_know = a_value_in_that_field_you_know;
This is not the situation I have.

Or if you mean you want to know the value of the last inserted record, then:

Code: Select all

SELECT auto_increment_field_name FROM auto_increment_table ORDER BY auto_increment_field_name DESC LIMIT 1;
This is fine, except that you either have to have an new index, or you sort the whole file every time you do this, which is something that I would rarely ever do. Also, this is very much a coded method - whereas I was hoping for a database feature as this will almost certainly (I would have thought) be more efficient and tidier.
Hi.

Please see my comments.

Thanks for your time.

S
User avatar
eazyGen
Forum Commoner
Posts: 46
Joined: Mon Aug 29, 2011 4:32 am
Location: Central London

Re: Getting the auto increment key value

Post by eazyGen »

twinedev wrote:Immediately after the insert use the function mysql_insert_id()

Code: Select all

$rsInsert = mysql_query('INSERT INTO `tblName` (`Field1`,`Field2`) VALUES ("data1","data2")');
$intID = mysql_insert_id();
See http://php.net/mysql_insert_id for more information.

-Greg
That seems like just the thing I was after.

Many thanks,

S
User avatar
eazyGen
Forum Commoner
Posts: 46
Joined: Mon Aug 29, 2011 4:32 am
Location: Central London

Re: Getting the auto increment key value

Post by eazyGen »

Back again on this chaps.

I have looked at the documentation for mysql_insert_id() and it says:

mysql_insert_id() will convert the return type of the native MySQL C API function mysql_insert_id() to a type of long (named int in PHP). If your AUTO_INCREMENT column has a column type of BIGINT (64 bits) the conversion may result in an incorrect value. Instead, use the internal MySQL SQL function LAST_INSERT_ID() in an SQL query.

I have a bigint as the key if my table, so I looked at the LAST_INSERT_ID(). However, during testing I noticed that LAST_INSERT_ID() returned a value with a type of long, which I believe has a maximum value up to: 2,147,483,648. So this doesn't seem to be the solution.

Has anyone overcome this issue?

Many thanks in advance.

S
Post Reply