insert_id

When you insert a row into a table that has an AUTO_INCREMENT (or SERIAL) primary key, the database generates the new ID for you. The mysqli_insert_id() function lets you read that generated value back into PHP immediately after the insert, so you can use it as a foreign key, return it in an API response, or redirect the user to the new record.

This article covers what mysqli_insert_id() returns, both the procedural and object-oriented syntax, and the common pitfalls that lead to it returning the wrong value (or 0).

What mysqli_insert_id() returns

mysqli_insert_id() returns the ID generated by the last query that inserted into an AUTO_INCREMENT column, for the given connection.

• If the previous query stored a value into an AUTO_INCREMENT column, it returns that value.
• If the previous query was not an INSERT/UPDATE that touched an AUTO_INCREMENT column, it returns 0.
• The value is scoped to the current connection, so concurrent inserts from other clients never affect what you get back. This makes it safe even on a busy server.

Note: the value reflects the first automatically generated id of the statement. For a single-row insert that is the row's id; for a multi-row insert it is the id of the first row.

Syntax

mysqli_insert_id() takes the MySQLi connection as its only argument:

int|string mysqli_insert_id(mysqli $mysql)

In modern PHP (8.1+), if a query fails MySQLi throws a mysqli_sql_exception rather than returning false, so the examples below assume errors will surface as exceptions.

Procedural example

This is the most common style you will see in older tutorials and codebases:

<?php
$mysqli = mysqli_connect("localhost", "username", "password", "database");

mysqli_query(
    $mysqli,
    "INSERT INTO users (name, email) VALUES ('Ada', '[email protected]')"
);

$id = mysqli_insert_id($mysqli);

echo "Last inserted ID is: " . $id;

mysqli_close($mysqli);
?>

Here we open a connection with mysqli_connect(), run an INSERT with mysqli_query(), then read the generated id back with mysqli_insert_id() before closing the connection.

Object-oriented example

The same logic with the OOP interface, which most new code uses. The generated id is exposed as the insert_id property:

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");

$mysqli->query(
    "INSERT INTO users (name, email) VALUES ('Ada', '[email protected]')"
);

echo "Last inserted ID is: " . $mysqli->insert_id;

$mysqli->close();
?>

Using it with prepared statements

In real applications you should insert user data with a prepared statement to avoid SQL injection. The insert id is still available afterward, either from the connection or the statement object:

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");

$stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param("ss", $name, $email);

$name  = "Grace";
$email = "[email protected]";
$stmt->execute();

echo "New user id: " . $stmt->insert_id; // same as $mysqli->insert_id here

$stmt->close();
$mysqli->close();
?>

Common gotchas

• Read it immediately. The value reflects only the last insert on the connection. If you run another INSERT (even into an unrelated table) before reading, you get that one's id instead. Capture it in a variable right after the insert.
• No AUTO_INCREMENT column means 0. If your table's primary key is set manually, there is nothing generated, so the function returns 0.
• UPDATE and SELECT reset expectations. A successful UPDATE that does not touch an auto-increment column, or any SELECT, leaves no new generated id, and the function returns 0.
• Multi-row inserts return the first id. After INSERT INTO t VALUES (...),(...),(...), you get the id of the first inserted row; the rest follow sequentially.

Related functions

• mysqli_affected_rows() — how many rows the last query changed.
• mysqli_query() — runs the INSERT whose id you read.
• PHP MySQLi overview — the full MySQLi extension.
• Insert data into MySQL — a complete insert walkthrough.

Conclusion

mysqli_insert_id() is the standard way to recover the auto-generated primary key right after an insert. Read it immediately after the INSERT, remember it is per-connection (so it is concurrency-safe), and expect 0 whenever the last statement did not generate an auto-increment value.

Practice