PHP MySQL Get Last ID

Right after inserting a new row with an auto-incrementing primary key, you often need to know exactly which ID MySQL just assigned it -- to redirect to that record's page, link it to a related row in another table, or simply confirm it to the user. mysqli_insert_id() returns exactly that value.

In this page:

Getting the ID of a Newly Inserted Row
Using the New ID to Insert a Related Row
When mysqli_insert_id() Returns 0
Getting the Last ID with PDO Instead
Common Uses for the New ID

Getting the ID of a Newly Inserted Row

Immediately after a successful INSERT into a table with an AUTO_INCREMENT primary key, mysqli_insert_id($conn) returns exactly the ID value MySQL generated for that new row -- no separate SELECT query needed.

Note: Call mysqli_insert_id() right after the INSERT it relates to, before running any other query on that connection.

Warning: mysqli_insert_id() reflects the connection's own most recent insert -- it is not a general way to look up any arbitrary row's ID after the fact.

Example: Getting the ID of a Newly Inserted Row

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE orders (id INTEGER PRIMARY KEY AUTOINCREMENT, item TEXT)");
$db->exec("INSERT INTO orders (item) VALUES ('Book')");
echo $db->lastInsertRowID(); // mysqli_insert_id($conn) in MySQL
?>

Using the New ID to Insert a Related Row

A very common pattern is inserting a parent row (like an order), capturing its new ID with mysqli_insert_id(), and then using that ID immediately to insert related child rows (like order items) that reference it via a foreign key.

Note: Wrap the parent-then-children insert sequence in a transaction if the children absolutely must exist whenever the parent does, so a failure partway through can be rolled back cleanly.

Warning: Inserting child rows before capturing the parent's ID (or after running another unrelated query in between) risks using the wrong or a stale ID value.

Example: Using the New ID to Insert a Related Row

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE orders (id INTEGER PRIMARY KEY AUTOINCREMENT)");
$db->exec("CREATE TABLE items (order_id INTEGER, product TEXT)");
$db->exec("INSERT INTO orders DEFAULT VALUES");
$orderId = $db->lastInsertRowID();
$db->exec("INSERT INTO items (order_id, product) VALUES ($orderId, 'Pen')");
echo "Linked item to order #$orderId";
?>

When mysqli_insert_id() Returns 0

mysqli_insert_id() returns 0 if the most recent query was not an INSERT into an auto-increment column -- like an UPDATE, a SELECT, or an insert into a table with no auto-increment primary key at all -- which is worth checking for if your code assumes an ID was always generated.

Note: If a 0 result would be ambiguous or unexpected, check the return value explicitly rather than assuming it is always a valid new ID.

Warning: Calling mysqli_insert_id() after a query that is not the relevant INSERT (like an intervening SELECT for validation) can return 0 or an unrelated stale value.

Example: When mysqli_insert_id() Returns 0

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");
$db->exec("INSERT INTO users (name) VALUES ('Alice')");
$db->exec("UPDATE users SET name = 'Alicia' WHERE id = 1");
echo $db->lastInsertRowID(); // still 1 in SQLite, but mysqli_insert_id() returns 0 after a non-insert query
?>

Getting the Last ID with PDO Instead

PDO, PHP's other database abstraction layer, offers the equivalent functionality through $pdo->lastInsertId(), called the same way -- immediately after the relevant insert, on the same PDO connection object.

Note: Use $pdo->lastInsertId() consistently if your project uses PDO rather than mysqli, since the two are not interchangeable on the same connection.

Warning: lastInsertId() behaves slightly differently across different database drivers in PDO -- always test against the specific database engine you are targeting.

Example: Getting the Last ID with PDO Instead

php

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE orders (id INTEGER PRIMARY KEY AUTOINCREMENT, item TEXT)");
$pdo->exec("INSERT INTO orders (item) VALUES ('Book')");
echo $pdo->lastInsertId();
?>

Common Uses for the New ID

Beyond linking related rows, the newly inserted ID is often used to redirect a user to a detail page for the record they just created (like /orders/42), to build a confirmation message, or to return the ID as part of a JSON API response after a successful create operation.

Note: Include the new ID in your API's response body after a successful create endpoint, so client-side code knows exactly which record was created without a separate lookup.

Warning: Redirecting to a record's detail page using a stale or incorrect ID (from calling mysqli_insert_id() too late) sends the user to the wrong page or a 404.

Example: Common Uses for the New ID

php

<?php
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE orders (id INTEGER PRIMARY KEY AUTOINCREMENT, item TEXT)");
$db->exec("INSERT INTO orders (item) VALUES ('Book')");
$id = $db->lastInsertRowID();
echo json_encode(["status" => "created", "order_id" => $id]);
?>

Common Mistakes

Calling mysqli_insert_id() long after other queries have run, when it only reliably reflects the ID from the most recent successful insert on that same connection.
Assuming mysqli_insert_id() works for tables without an AUTO_INCREMENT column -- it only has a meaningful value for inserts into such a column.
Using mysqli_insert_id() in a context involving multiple concurrent connections and expecting it to somehow return another connection's last insert ID.

Chapter Summary

mysqli_insert_id($conn) returns the auto-increment ID generated by the most recent INSERT on that specific connection.
It must be called immediately after the relevant INSERT, before any other query runs on the same connection.
It returns 0 if the last query did not generate a new auto-increment value.

Browser Support

mysqli_insert_id() has been part of the mysqli extension since PHP 5, replacing the older mysql_insert_id() function.

🔒

Chapter Quiz — Complete all 21 topics to unlock

0/21 topics done

Complete these topics first:

← Previous Lesson PHP MySQL Insert Data Next Lesson → PHP MySQL Insert Multiple