How to Retrieve the Last Inserted ID in PHP and MySQL

When you insert a row into a MySQL table whose primary key is an AUTO_INCREMENT column, the database generates the new ID for you. PHP gives you a way to read that ID back immediately after the insert, without running a second SELECT query. This page shows how to do that with both the MySQLi and PDO extensions, why the value is reliable, and the gotchas to watch for.

The last inserted ID is most often used to link related records: insert a new user, get its ID, then insert rows into orders or profiles that reference it as a foreign key.

How it works

Both functions described below return the ID generated by the most recent successful INSERT on the current database connection:

• mysqli_insert_id($conn) — MySQLi (procedural or object-oriented).
• PDO::lastInsertId() — PDO.

Two important details:

• The value is per connection. Even if other users insert rows at the same time, you always get the ID generated by your connection, so there is no race condition.
• It only returns a non-zero value when the inserted table has an AUTO_INCREMENT column. If the table has no auto-increment key, you get 0.

Prerequisites

Before we start, there are a few prerequisites that you should have in place:

• A web server with PHP installed
• A MySQL database
• Access to the phpMyAdmin interface or similar database management tool
• A table with an AUTO_INCREMENT primary key. The examples below assume this one:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100)
);

See Create a MySQL Table for more on defining tables.

Establishing a Connection

The first step in retrieving the last inserted ID is to establish a connection to the database. With MySQLi this is done using the mysqli_connect() function, which requires the following parameters:

• The name of the database server
• The username used to access the database
• The password for the database user
• The name of the database to connect to

Here is an example of how to establish a connection to a database:

<?php
$server = "localhost";
$username = "root";
$password = "password";
$database = "database_name";

$conn = mysqli_connect($server, $username, $password, $database);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";
?>

For a deeper look at connecting, see Connect to a MySQL Database.

Writing the SQL Statement

Once a connection to the database has been established, the next step is to write the SQL statement that will be used to insert the data into the database.

Here is an example of a SQL statement that inserts data into a database table:

INSERT INTO table_name (column1, column2, column3)
VALUES ('value1', 'value2', 'value3');

Executing the SQL Statement

Once the SQL statement has been written, the next step is to execute it. This is done by using the mysqli_query() function, which requires the following parameters:

• The connection to the database
• The SQL statement to be executed

Here is an example of how to execute the SQL statement:

<?php
$sql = "INSERT INTO table_name (column1, column2, column3)
VALUES ('value1', 'value2', 'value3')";

if (mysqli_query($conn, $sql)) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}
?>

Security Note: In production environments, always use prepared statements to prevent SQL injection attacks.

Retrieving the Last Inserted ID

Once the data has been inserted into the database, the next step is to retrieve the ID of the last inserted record. This is done by using the mysqli_insert_id() function, which requires the following parameter:

• The connection to the database

Here is an example of how to retrieve the last inserted ID:

<?php
$last_id = mysqli_insert_id($conn);
echo "Last inserted ID is: " . $last_id;
?>

Closing the Connection

Once the last inserted ID has been retrieved, it is important to close the connection to the database to avoid any security risks. This is done by using the mysqli_close() function, which requires the following parameter:

• The connection to the database

Here is an example of how to close the connection to the database:

<?php
mysqli_close($conn);
?>

Full example (MySQLi, prepared statement)

In real code you should pass user data through a prepared statement rather than concatenating it into the SQL string. The pattern is identical: run the insert, then read the ID. Here everything is tied together:

<?php
$conn = mysqli_connect("localhost", "root", "password", "database_name");
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$stmt = mysqli_prepare($conn, "INSERT INTO users (name, email) VALUES (?, ?)");
mysqli_stmt_bind_param($stmt, "ss", $name, $email);

$name = "Jane Doe";
$email = "[email protected]";
mysqli_stmt_execute($stmt);

$last_id = mysqli_insert_id($conn);
echo "New user inserted with ID: " . $last_id;

mysqli_stmt_close($stmt);
mysqli_close($conn);
?>

After the insert succeeds, $last_id holds the value MySQL generated for the id column — for example 1 on the first insert, 2 on the next, and so on.

Getting the last ID with PDO

If you connect with PDO instead of MySQLi, use the lastInsertId() method on the connection object:

<?php
$pdo = new PDO("mysql:host=localhost;dbname=database_name", "root", "password");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->execute(["John Doe", "[email protected]"]);

$last_id = $pdo->lastInsertId();
echo "New user inserted with ID: " . $last_id;
?>

Note that PDO::lastInsertId() returns the ID as a string, so cast it with (int) if you need a numeric type.

Common gotchas

• Call it right after the insert. Run mysqli_insert_id() / lastInsertId() before you execute any other query on the same connection — a later INSERT overwrites the stored value.
• No auto-increment column → 0. The function returns 0 (not an error) when the table you inserted into has no AUTO_INCREMENT column.
• UPDATE and DELETE don't change it. Only INSERT statements that create an auto-increment value update the result.
• Don't use SELECT MAX(id). A common mistake is to read the highest ID with SELECT MAX(id) FROM users. Under concurrent inserts this can return another user's row. The dedicated functions are connection-scoped and safe.

Conclusion

In this article, we have shown you how to retrieve the last inserted ID in PHP and MySQL. By following these steps, you should be able to retrieve the ID of the last inserted record in your database. For best results, always insert data through prepared statements and read the ID back on the same connection immediately after the insert.

Related chapters: Insert Data into MySQL, Prepared Statements, and Select Data from MySQL.

Practice