MySQL/PHP: How to retrieve the last INSERT ID
Info by Axuter | 2012-12-13 at 18:25
When we insert a new record in a MySQL table and the table contains an AUTO_INCREMENT column, a new unique ID is automatically assigned to our record. However, sometimes, we need exactly this ID in our script. In this info, I would like to show you, how to obtain this ID.
First Way: mysql_insert_id
PHP provides us with the mysql_insert_id function, that will do this task for us. Here's an example:
mysql_query("INSERT INTO table (name) VALUES ('Anne')"); $lastid = mysql_insert_id(); mysql_query("UPDATE table SET name='Anna' WHERE id=$lastid");
Here, we add a new row to the table "table" and then, we determine the ID created during this process with mysql_insert_id(). Second, we use the ID to make a change to the previously stored data.
Important: mysql_insert_id() always refers to the last performed query. Therefore you should retrieve the ID - if required - always immediately after the INSERT query, because otherwise, the ID can no longer be accessed. If the last query does not generate an AUTO_INCREMENT ID, mysql_insert_id() returns the value 0.
Second Way: LAST_INSERT_ID
Another option, I want to introduce here:
SELECT LAST_INSERT_ID() FROM table
LAST_INSERT_ID() always gives us the last assigned ID. Unlike mysql_insert_id(), this also works, when there where performed other queries in the meantime. Thus, with this, you can find out the ID, even if the last INSERT command is some time ago.
About the Author
The author has not added a profile short description yet.
Show Profile
Related Topics
MySQL: Reset AUTO_INCREMENT value of column
Question | 1 Answer
Firebird: INSERT with ON DUPLICATE KEY UPDATE
Question | 1 Answer
MySQL: Delete all data from table: Difference between TRUNCATE, DELETE and DROP
Tutorial | 0 Comments
PHP: Date Format for MySQL INSERT
Question | 1 Answer
MySQL: How can I empty all fields of a table?
Question | 1 Answer
MySQL: Select random record from table
Tip | 0 Comments
MySQL: Set initial Value for AUTO INCREMENT
Question | 1 Answer
Important Note
Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.
Participate
Ask your own question or write your own article on askingbox.com. That’s how it’s done.