Get Last insert id from MySQL Table with PHP

There are various approaches to selecting the last insert ID from the MySQL table:

• Fetch the ID by selecting a row in descending order and storing the ID.
• Retrieve the maximum value.
• The query below provides the subsequent AUTO_INCREMENT value for the chosen table, useful for acquiring the last ID.

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user'

For example, if your AUTO_INCREMENT column’s last value is 8, executing the query will return 9.

In PHP there is an inbuilt method that simply returns the last insert id according to the previous insert query.

---

Table of Content

1. Using mysqli_insert_id() to Get Last insert ID
2. Using insert_id to Get Last insert ID
3. Conclusion

---

1. Using mysqli_insert_id() to Get Last insert ID

It returns the most recent AUTO_INCREMENT column value from the last successfully executed INSERT query.

Syntax –

mysqli_insert_id($connection-variable)

In scenarios where the table lacks an AUTO_INCREMENT column or a new record insertion fails, the return value is 0.

Example:

Inserts data into a users table, and using mysqli_insert_id() to display the last insert ID if the insertion is successful.

<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */

$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
    die("Connection failed: " . mysqli_connect_error());
}

// Insert query 
$query = "insert into users(username,fname,lname) values('sonarika','Sonarika','Bhadoria')"; 

if(mysqli_query($con,$query)){ 

    // Get last insert id 
    $lastInsertID = mysqli_insert_id($con); 

    echo "Last insert ID : ".$lastInsertID; 
}
?>

Output:

Last insert ID : 4

---

2. Using insert_id to Get Last insert ID

If your connection is object-oriented, you have the option to utilize insert_id to acquire the ID of the most recently inserted record.

Syntax –

$conn->insert_id;

Here, $conn represents the variable name for the connection. If the table doesn’t contain an AUTO_INCREMENT column or an insertion isn’t successful, the return value is 0.

Example:

Insert data into a users table, and uses $conn->insert_id to show the last inserted ID upon success.

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorials"; /* Database name */

// Create connection
$conn = new mysqli($host, $user, $password, $dbname);

// Check connection
if ($conn->connect_error) {
     die("Connection failed: " . $conn->connect_error);
}

// Insert query 
$sql = "INSERT INTO users(username,fname,lname) VALUES('sonarika','Sonarika','Bhadoria')"; 

if ($conn->query($sql) === TRUE) {

     $lastInsertID = $conn->insert_id;
     echo "Last insert ID : ".$lastInsertID; 
} else {
     echo "Error: " . $conn->error;
}

?>

Output:

Last insert ID : 5

---

3. Conclusion

You’ve gained insight into retrieving the last insert ID using a built-in function. Remember to invoke it right after the insert query, as it corresponds to the latest operation.

In situations where you’re manually managing the ID column and not utilizing AUTO_INCREMENT in your MySQL table, alternative methods might be more suitable.

Follow this link to learn more about mysqli_insert_id() and insert_id.

If you found this tutorial helpful then don't forget to share.