PHP PDO Insert and lastInsertId(): Examples That Work

Inserting a row with PDO takes three lines. Getting the ID of the row you just created is where people trip up, because lastInsertId() behaves a little differently on MySQL, PostgreSQL and SQLite. This guide shows a safe insert with a prepared statement, then how to read the new ID correctly on each database, plus the mistakes I see most often in code reviews.

If you are new to PDO, read PHP PDO prepared statements explained first. This post builds on it.

The short answer

$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$stmt->execute(['name' => $name, 'email' => $email]);
$id = (int) $pdo->lastInsertId();

Call lastInsertId() on the PDO connection, not on the statement, and call it straight after the insert on the same connection.

Step 1: connect with exceptions turned on

$pdo = new PDO(
    'mysql:host=localhost;dbname=shop;charset=utf8mb4',
    $user,
    $pass,
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
);

Since PHP 8.0, exception mode is the default, but setting it explicitly makes the intent obvious. Turning off emulated prepares makes MySQL do real server-side prepares, so integers come back as integers.

Step 2: insert with named or positional placeholders

Both styles are equally safe. Pick one per query and do not mix them.

// Named
$stmt = $pdo->prepare('INSERT INTO orders (user_id, total) VALUES (:uid, :total)');
$stmt->execute(['uid' => 42, 'total' => '19.99']);

// Positional
$stmt = $pdo->prepare('INSERT INTO orders (user_id, total) VALUES (?, ?)');
$stmt->execute([42, '19.99']);

Never build the SQL with string concatenation such as "VALUES ('$name')". That is the classic SQL injection hole that prepared statements exist to close.

Step 3: get the new ID on MySQL and MariaDB

On MySQL, lastInsertId() returns the AUTO_INCREMENT value from the last insert on this connection. Other users inserting at the same moment do not affect it, so you do not need a SELECT MAX(id) (which is a race condition and should be avoided).

Two details worth knowing:

It returns a string, for example "105". Cast it with (int) if you need a number.
For a multi-row insert such as INSERT ... VALUES (...), (...), (...), MySQL returns the ID of the first row inserted, not the last.

Step 4: PostgreSQL needs the sequence name or RETURNING

PostgreSQL uses sequences. You can pass the sequence name:

$id = $pdo->lastInsertId('users_id_seq');

The cleaner way is RETURNING, which gives you the ID (or any column) in the same round trip:

$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (?, ?) RETURNING id');
$stmt->execute([$name, $email]);
$id = (int) $stmt->fetchColumn();

Step 5: SQLite

SQLite works like MySQL: $pdo->lastInsertId() returns the rowid of the last inserted row. Recent SQLite versions (3.35 and later) also support RETURNING id, so the PostgreSQL pattern above works there too.

Inserting many rows fast

Prepare once, execute many times, and wrap the loop in a transaction. Without the transaction, each insert is committed on its own, which is much slower.

$pdo->beginTransaction();
$stmt = $pdo->prepare('INSERT INTO tags (name) VALUES (?)');
foreach ($names as $n) {
    $stmt->execute([$n]);
}
$pdo->commit();

I explain commit and rollback properly in PHP PDO transactions with commit and rollback.

Handling duplicate keys

If email has a unique index, inserting the same email twice throws a PDOException with SQLSTATE 23000. Catch it and show a friendly message instead of a stack trace:

try {
    $stmt->execute([$name, $email]);
} catch (PDOException $e) {
    if ($e->getCode() === '23000') {
        $error = 'That email is already registered.';
    } else {
        throw $e;
    }
}

Common mistakes

Calling it on the statement. $stmt->lastInsertId() does not exist. Use $pdo.
Running another query first. Another insert on the same connection (for example a logging insert) replaces the value. Read the ID immediately.
Getting "0". The table has no auto-increment column, the insert failed silently because exceptions were off, or you are on PostgreSQL without a sequence name.
Storing passwords in plain text. If this is a users table, hash first with password_hash().

FAQs

Is lastInsertId() safe with many users at once?

Yes. It is scoped to the current database connection, so concurrent requests each get their own value.

Why does lastInsertId() return a string?

PDO returns it as a string on every driver so very large IDs are not lost. Cast it with (int) when you know it fits.

Does lastInsertId() work after UPDATE?

No. It reports IDs generated by inserts. After an update, use $stmt->rowCount() to see how many rows changed.

How do I get the IDs of all rows in a bulk insert?

On PostgreSQL and SQLite 3.35+, use RETURNING id and fetch all rows. On MySQL, insert in a loop and collect lastInsertId() after each execute.

Want to skip the typing? My free PDO prepared statement generator writes the INSERT, SELECT, UPDATE and DELETE code for your table.