46 Votes

MySQL/PHP: How to retrieve the last INSERT ID

Info by Stefan Trost | Last update on 2023-11-23 | Created on 2012-12-13

When we insert a new record into a MySQL table and the table contains an AUTO_INCREMENT column, a new unique ID is automatically assigned to our record, without us knowing at first which ID this is (if we have not explicitly specified an ID to use). However, sometimes, we need exactly this ID in our further script. Therefore, in this info, I would like to show you, how to obtain this ID.

We want to take a look at two different approaches - in our first example we use the PHP function mysqli_insert_id() to read out the ID. After that, in our second example, we have a look at an alternative possibility that works exclusively with MySQL.

First Way: mysqli_insert_id

PHP provides us with the function mysqli_insert_id(), that will do exactly this task for us. Here's an example:

mysqli_query($db, "INSERT INTO tab (fname) VALUES ('Anne')");
$id = mysqli_insert_id($db);
mysqli_query($db, "UPDATE tab SET fname = 'Anna' WHERE id = $id");

In this example, first we add a new row to the table "tab" and then, we determine the ID created during this process using mysqli_insert_id(). After that, we use this ID to make a change to the previously stored data.

Important: mysqli_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, mysqli_insert_id() returns the value 0. If an INSERT statement has generated multiple IDs, the first value successfully created by this instruction will be returned.

Second Way: LAST_INSERT_ID

Next, I would like to introduce you to another option that you can use directly in MySQL without PHP:

SELECT LAST_INSERT_ID()

LAST_INSERT_ID() always gives us the last assigned ID. Unlike mysqli_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 code ago.

With "SELECT LAST_INSERT_ID()" we directly get back the last ID as the result of our instruction. However, we can also use LAST_INSERT_ID() directly in other MySQL instructions, for example to insert the last ID into a table:

INSERT INTO tab (id, pid) VALUES (1, LAST_INSERT_ID());

Or to be able to access the data record just inserted in another instruction:

INSERT INTO tab (fname) VALUES ('Anne');
UPDATE tab SET fname = 'Anna' WHERE id = LAST_INSERT_ID();

With this, we have recreated the PHP example from the last section with pure MySQL without any detour via PHP.

ReplyPositiveNegative

About the Author

AvatarYou can find Software by Stefan Trost on sttmedia.com. Do you need an individual software solution according to your needs? - sttmedia.com/contact
Show Profile

 

Related Topics

Delphi: System-Wide HotKey

Tutorial | 1 Comment

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.