MySQL/PHP: How to retrieve the last INSERT ID

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.