Page 1 of 1

Getting the auto increment key value

Posted: Sat Nov 12, 2011 4:38 am
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

Re: Getting the auto increment key value

Posted: Sat Nov 12, 2011 5:03 am
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;

Re: Getting the auto increment key value

Posted: Sat Nov 12, 2011 5:25 am
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

Re: Getting the auto increment key value

Posted: Sat Nov 12, 2011 6:00 am
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

Re: Getting the auto increment key value

Posted: Sat Nov 12, 2011 6:13 am
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

Re: Getting the auto increment key value

Posted: Sun Nov 13, 2011 5:33 am
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