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
Getting the auto increment key value
Moderator: General Moderators
-
mikeashfield
- Forum Contributor
- Posts: 159
- Joined: Sat Oct 22, 2011 10:50 am
Re: Getting the auto increment key value
Yes:
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 WHERE a_field_name_you_know = a_value_in_that_field_you_know;
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
Immediately after the insert use the function mysql_insert_id()
See http://php.net/mysql_insert_id for more information.
-Greg
Code: Select all
$rsInsert = mysql_query('INSERT INTO `tblName` (`Field1`,`Field2`) VALUES ("data1","data2")');
$intID = mysql_insert_id();-Greg
Re: Getting the auto increment key value
Hi.mikeashfield wrote:Yes:
This is not the situation I have.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: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.Code: Select all
SELECT auto_increment_field_name FROM auto_increment_table ORDER BY auto_increment_field_name DESC LIMIT 1;
Please see my comments.
Thanks for your time.
S
Re: Getting the auto increment key value
That seems like just the thing I was after.twinedev wrote:Immediately after the insert use the function mysql_insert_id()See http://php.net/mysql_insert_id for more information.Code: Select all
$rsInsert = mysql_query('INSERT INTO `tblName` (`Field1`,`Field2`) VALUES ("data1","data2")'); $intID = mysql_insert_id();
-Greg
Many thanks,
S
Re: Getting the auto increment key value
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
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