Let's say I am doing a MySQL INSERT
into one of my tables and the table has the column item_id
which is set to autoincrement
and primary key
.
How do I get the query to output the value of the newly generated primary key item_id
in the same query?
Currently I am running a second query to retrieve the id but this hardly seems like good practice considering this might produce the wrong result...
If this is not possible then what is the best practice to ensure I retrieve the correct id?
If you are AUTO_INCREMENT with column, then you can use last_insert_id() method. This method gets the ID of the last inserted record in MySQL.
Get ID of The Last Inserted RecordIf we perform an INSERT or UPDATE on a table with an AUTO_INCREMENT field, we can get the ID of the last inserted/updated record immediately.
To obtain the value immediately after an INSERT , use a SELECT query with the LAST_INSERT_ID() function. For example, using Connector/ODBC you would execute two separate statements, the INSERT statement and the SELECT query to obtain the auto-increment value.
6 Answers. Show activity on this post. INSERT just returns true or false. to actually return something useful, you will need a SELECT or similar query.
You need to use the LAST_INSERT_ID()
function: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id
Eg:
INSERT INTO table_name (col1, col2,...) VALUES ('val1', 'val2'...); SELECT LAST_INSERT_ID();
This will get you back the PRIMARY KEY
value of the last row that you inserted:
The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client.
So the value returned by LAST_INSERT_ID()
is per user and is unaffected by other queries that might be running on the server from other users.
BEWARE !! of LAST_INSERT_ID()
if trying to return this primary key value within PHP.
I know this thread is not tagged PHP, but for anybody who came across this answer looking to return a MySQL insert id from a PHP scripted insert using standard mysql_query
calls - it wont work and is not obvious without capturing SQL errors.
The newer mysqli
supports multiple queries - which LAST_INSERT_ID()
actually is a second query from the original.
IMO a separate SELECT
to identify the last primary key is safer than the optional mysql_insert_id()
function returning the AUTO_INCREMENT ID
generated from the previous INSERT
operation.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With