PDO lastInsertId()

Also Known As

TL;DR

Explanation

lastInsertId() must be called on the PDO connection object (not the statement) immediately after the INSERT. It returns a string, not an integer. In a transaction, it returns the last inserted ID even before commit(). With PostgreSQL, a sequence name must be passed as the argument. lastInsertId() returns '0' if the table has no AUTO_INCREMENT column or no row was inserted.

Watch Out

Common Misconception

Why It Matters

Common Mistakes

• Calling lastInsertId() on the statement object ($stmt->lastInsertId()) — it's a PDO method, not PDOStatement.
• Calling it after a second query — it reflects the most recent INSERT on the connection.
• Using it with tables that have no AUTO_INCREMENT column — returns '0'.

Avoid When

• Do not call lastInsertId() after any query other than the INSERT you care about — it reflects the most recent INSERT on the connection.
• Do not use with tables that have no AUTO_INCREMENT column — always returns '0'.

When To Use

• Use immediately after an INSERT to retrieve the generated primary key for linking related records.
• Use inside a transaction before commit() — the ID is available within the transaction.

Code Examples

// Wrong — called on statement, not connection
$stmt->execute([$userId, $total]);
$orderId = $stmt->lastInsertId(); // Fatal error — method doesn't exist on PDOStatement

$stmt = $pdo->prepare('INSERT INTO orders (user_id, total) VALUES (?, ?)');
$stmt->execute([$userId, $total]);
$orderId = (int) $pdo->lastInsertId(); // cast to int for type safety

// Now use $orderId for line items
$itemStmt = $pdo->prepare('INSERT INTO order_items (order_id, product_id, qty) VALUES (?, ?, ?)');
foreach ($items as $item) {
    $itemStmt->execute([$orderId, $item['id'], $item['qty']]);
}

References

• ↗ https://www.php.net/manual/en/pdo.lastinsertid.php

Tags

Related Terms