skip to content

With PDO, how do you get the auto-increment id of the row you just inserted, and what does lastInsertId() return?

level: juniorimportance: should knowfreq 48%

answer

  1. a method on the connection
  2. string|false, not int
  3. per connection, not per table
  4. PostgreSQL: LASTVAL() or a sequence name
  5. read it before the next insert

basics

~20 s

Call $pdo->lastInsertId() on the connection right after the INSERT. It returns the generated id as a string, scoped to that connection, so other requests cannot change it; on PostgreSQL you may pass the sequence name.

solid answer

~40 s

After the `INSERT` I call `$pdo->lastInsertId()` — a method of `PDO`, not of the statement — and cast the result, because it returns a **string** such as `"42"` (signature `lastInsertId(?string $name = null): string|false`). The value is tracked per connection, so another request inserting at the same moment cannot change it; only a later insert on my own connection can, so I read it immediately. On MySQL and SQLite no argument is needed. On PostgreSQL, calling it without a name runs `SELECT LASTVAL()`, so I either pass the sequence name, which runs `CURRVAL()`, or use `INSERT ... RETURNING id`. It works inside a transaction before `commit()`.

code

php · 13 lines
php
<?php
declare(strict_types=1);

$insert = $pdo->prepare(
    'INSERT INTO points_ledger (customer_id, delta, pair_id) VALUES (?, ?, ?)'
);

$pdo->beginTransaction();
$insert->execute([$fromCustomer, -$points, null]);
$debitId = (int) $pdo->lastInsertId();          // "17" becomes 17

$insert->execute([$toCustomer, $points, $debitId]);
$pdo->commit();

go deeper

for a junior

Recall that lastInsertId() is called on the PDO connection right after the insert and returns the id as a string.

for a middle

Explain why the value is safe under concurrency (it is per connection) and why a second insert on the same connection overwrites it.

for a senior

Know the PostgreSQL behaviour, LASTVAL() versus a named sequence versus RETURNING, and when triggers make the unnamed form unreliable.

for a principal

Decide whether the codebase standardises on RETURNING or lastInsertId() when it must run on more than one database driver.

## The method After an `INSERT` into a table with an auto-increment key, ask the **connection** for the generated id: ```php $pdo->prepare('INSERT INTO points_ledger (customer_id, delta) VALUES (?, ?)') ->execute([$customerId, -500]); $ledgerId = (int) $pdo->lastInsertId(); ``` Its signature is `PDO::lastInsertId(?string $name = null): string|false`. Three facts follow from it: - It lives on `PDO`, **not** on `PDOStatement`. The value belongs to the connection, whichever statement object ran the insert. - On success it returns a **string**, such as `"42"`. Cast it with `(int)` when the column is an integer and the rest of the code is strictly typed. - It returns `false` only when it fails and the error mode is silent; with the default exception mode, a failure throws `PDOException`. ## What it reports per driver The method asks the driver, and drivers mean different things by "last id": | Driver | Without `$name` | With `$name` | |---|---|---| | `pdo_mysql` | the auto-increment value the connection's last insert generated | ignored | | `pdo_pgsql` | runs `SELECT LASTVAL()` — the last value any sequence produced in this session | runs `SELECT CURRVAL($name)` for that sequence | | `pdo_sqlite` | the rowid of the last inserted row | ignored | On PostgreSQL, a trigger that inserts into another table with its own sequence changes what `LASTVAL()` sees, so passing the sequence name (for example `'points_ledger_id_seq'`) is safer. Many PostgreSQL codebases skip `lastInsertId()` entirely and write `INSERT ... RETURNING id`, then `fetchColumn()` on the statement. A driver with no notion of generated ids reports SQLSTATE `IM001` with the message "driver does not support lastInsertId()". ## Why concurrent requests do not mix ids A common worry is that two requests inserting at the same moment could read each other's ids. They cannot, because the value is **per connection**: 1. Each PHP-FPM worker handles one request at a time and has its own database connection. 2. MySQL keeps the last generated id per connection; PostgreSQL's `LASTVAL()` and `CURRVAL()` are per session. 3. Another request's insert therefore never changes what your connection reports. What **does** change it is another insert on the **same** connection. Read the id immediately after the insert it belongs to, before calling any helper that might write an audit row. ## Inside a transaction `lastInsertId()` works inside an open transaction: the id is generated by the `INSERT`, not by `commit()`. The transfer can insert the ledger row, read its id, and reference it from a second row before committing: - insert the debit ledger row and read its id; - insert the credit ledger row with `pair_id` set to that id; - commit both together. If the transaction is rolled back, the rows disappear, but the id value your code read stays in a PHP variable. Do not use it after a rollback — for example in a log message that suggests the row exists. ## Handing the id to the rest of the code The raw string is an implementation detail of PDO. Convert it once, at the edge of the data-access code, and return a typed value: - a repository method declared `: int` that returns `(int) $this->pdo->lastInsertId()`; - a value object such as `LedgerEntryId` built from that integer; - for UUID keys generated in PHP, no `lastInsertId()` call at all — the application already knows the key before the insert. Keeping the cast in one place means `declare(strict_types=1)` callers never meet a numeric string where they expect an `int`, and a later switch to `RETURNING` or to application-generated keys touches one method. ## Wrong ways to get the id - `SELECT MAX(id) FROM points_ledger` — returns another request's row whenever inserts overlap. - `$stmt->rowCount()` — returns the number of affected rows, `1` for a single insert, not the key. - Reading `lastInsertId()` after a second insert — returns the second row's id. - Comparing `lastInsertId()` with an `int` using `===` — the method returns a string, so the comparison is always false. ## Summary for the interview Call `$pdo->lastInsertId()` right after the insert, cast the string, and know that the value is scoped to the connection. On PostgreSQL, name the sequence or use `RETURNING`.

  • Why is SELECT MAX(id) not a substitute for lastInsertId()?
    `MAX(id)` reads the table, which every connection writes to. If another request inserts between your `INSERT` and your `SELECT`, you get its id. `lastInsertId()` reads a value kept per connection, so concurrent inserts elsewhere cannot change it.
  • What does lastInsertId() run on PostgreSQL when you pass a sequence name?
    It runs `SELECT CURRVAL($name)` for that sequence, which returns the value this session last took from it. Without a name it runs `SELECT LASTVAL()`, the last value any sequence produced in the session, which a trigger inserting into another table can change.

saying these in an interview costs you the question

  • Calling lastInsertId() on the PDOStatement instead of the PDO connection.
  • Expecting an int and comparing it with === to an integer.
  • Using SELECT MAX(id) to find the new row's id.
  • Worrying that other requests' inserts change the value on this connection.
  • Believing the id is only available after commit().