Get Last Insert ID in PHP with MySQL PDO and MySQLi

Keeping track of the last record inserted is often important. Whether you are managing user registrations, processing orders, or handling other types of data, knowing the last ID inserted allows you to link related records, retrieve specific data, or perform additional actions.

In this article, you will learn how to retrieve the last inserted ID in your database operations, and the common methods used to implement this functionality. You will also see examples and tips for ensuring accuracy when working with last insert IDs.

What is LAST_INSERT_ID()?

LAST_INSERT_ID() is a MySQL function that returns the last automatically generated value for the AUTO_INCREMENT column during an INSERT operation. This value is specific to the current connection, ensuring that it reflects only the most recent insert in your session.

SELECT LAST_INSERT_ID();

Here is an example:

INSERT INTO orders (product_id, quantity) VALUES (101, 5); SELECT LAST_INSERT_ID();

If you do not use LAST_INSERT_ID(), it can be hard to get the right value, especially when adding multiple records.

Let’s see how to use that with PHP.

Get the Last Inserted ID with mysqli_insert_id

The mysqli_insert_id function is one of the common ways to retrieve the ID of the last row inserted into a table. This function is really handy when your table has an auto-increment column, as it lets you get the generated ID right after running an INSERT query.

This is the syntax you will use:

$last_id = mysqli_insert_id($connection);

Here is an example:

// Set up a connection to your MySQL database.
$connection = new mysqli('localhost', 'username', 'password', 'database');

// Make sure the connection works.
if ($connection->connect_error) {
    die("Connection failed: " . $connection->connect_error);
}

// Add a new record to the table
$sql = "INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]')";

if ($connection->query($sql) === TRUE) {
    // Get the ID of the latest entry
    $last_id = mysqli_insert_id($connection);
    echo "The last inserted ID is: " . $last_id;
} else {
    echo "Error: " . $sql . "<br>" . $connection->error;
}

// This line will end the connection
$connection->close();

In the section below, I will show you how to get the ID using the lastInsertId method in PDO.

Using PDO lastInsertId Method

If you are working with PHP PDO (PHP Data Objects) for database interactions, the lastInsertId method is your best solution for retrieving the ID of the recently inserted row. This method is simple to use and provides a way to handle auto-incremented IDs in your database.

Here is its syntax:

$lastId = $pdo->lastInsertId();

Here is an example:

try {
    // Create a PDO connection
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'username', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Insert a new record into a table
    $sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        ':name' => 'Jane Doe',
        ':email' => '[email protected]'
    ]);

    // Retrieve the last inserted ID
    $lastId = $pdo->lastInsertId();
    echo "The last inserted ID is: " . $lastId;

} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

Did you think about what happens if no record is inserted? Let’s see how to handle errors when getting the last inserted ID from the database with PHP.

Handling Errors When Getting the Last Inserted ID in PHP

You can handle errors as shown in the following example when using the mysqli extension:

$connection = new mysqli('localhost', 'username', 'password', 'database');

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

// Execute the INSERT query
$sql = "INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]')";
if ($connection->query($sql) === TRUE) {
    // Attempt to retrieve the last inserted ID
    $last_id = mysqli_insert_id($connection);
    if ($last_id) {
        echo "The last inserted ID is: " . $last_id;
    } else {
        echo "Error: Unable to retrieve the last inserted ID.";
    }
} else {
    // Handle query failure
    echo "Error: " . $connection->error;
}

// Close the connection
$connection->close();

Here is how you can handle errors when using PDO:

try {
    // Create a PDO connection
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'username', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Execute the INSERT query
    $sql = "INSERT INTO users (name, email) VALUES (:name, :email)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        ':name' => 'Jane Doe',
        ':email' => '[email protected]'
    ]);

    // Attempt to retrieve the last inserted ID
    $lastId = $pdo->lastInsertId();
    if ($lastId) {
        echo "The last inserted ID is: " . $lastId;
    } else {
        echo "Error: Unable to retrieve the last inserted ID.";
    }
} catch (PDOException $e) {
    // Handle any exceptions
    echo "Error: " . $e->getMessage();
}

Let’s summarize it.

Wrapping Up

We went over how to grab the last inserted ID with some examples to help you get started. We also talked about handling errors so your app works smoothly, even when things go wrong.

Let’s quickly go over what we covered

• LAST_INSERT_ID(): This gets the last auto-generated ID in your current session.
• mysqli_insert_id: You can use this to get the last ID when you are working with MySQL in PHP.
• PDO lastInsertId: A method to get the last inserted ID when using PDO for database interactions.
• Error Handling: Make sure your queries run successfully and handle errors when grabbing the last ID.