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

Syntax

php

mysqli_query($conn, "INSERT INTO table_name (column1) VALUES ('value')");
$id = mysqli_insert_id($conn);   // AUTO_INCREMENT id of the new row

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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$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");
// Declare `$orderId`, set to `$db->lastInsertRowID()`
$orderId = $db->lastInsertRowID();
$db->exec("INSERT INTO items (order_id, product) VALUES ($orderId, 'Pen')");
// Print "Linked item to order #$orderId" to the output
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
// Create a new `PDO` instance with 'sqlite::memory:', stored in `$pdo`
$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')");
// Print `$pdo->lastInsertId()` to the output
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
// Create a new `SQLite3` instance with ':memory:', stored in `$db`
$db = new SQLite3(':memory:');
$db->exec("CREATE TABLE orders (id INTEGER PRIMARY KEY AUTOINCREMENT, item TEXT)");
$db->exec("INSERT INTO orders (item) VALUES ('Book')");
// Declare `$id`, set to `$db->lastInsertRowID()`
$id = $db->lastInsertRowID();
// Print `json_encode(["status" => "created", "order_id" => $id])` to the output
echo json_encode(["status" => "created", "order_id" => $id]);
?>

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