PHP PDO::lastInsertId Method

last modified April 19, 2025

The PDO::lastInsertId method retrieves the ID of the last inserted row. It’s commonly used after INSERT operations with auto-increment columns.

Basic Definition

PDO::lastInsertId returns the ID generated by the last INSERT query. It works with auto-increment columns in MySQL, SQLite, PostgreSQL etc.

Syntax: public PDO::lastInsertId(?string $name = null): string|false. For most databases, the $name parameter can be omitted. Returns false on failure.

Basic lastInsertId Example

This shows the simplest usage of lastInsertId after an INSERT operation.

pdo_lastinsertid_basic.php

<?php

declare(strict_types=1);

try { $pdo = new PDO(‘mysql:host=localhost;dbname=testdb’, ‘user’, ‘password’); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (?, ?)');
$stmt->execute(['Jane Smith', 'jane@example.com']);

$lastId = $pdo->lastInsertId();
echo "Last inserted ID: " . $lastId;

} catch (PDOException $e) { echo “Error: " . $e->getMessage(); }

This inserts a new user and retrieves the auto-generated ID. The ID comes from the auto-increment column in the users table. Always check for errors.

Multiple Tables with lastInsertId

When working with multiple tables, lastInsertId gets the most recent ID.

pdo_lastinsertid_multiple.php

<?php

declare(strict_types=1);

try { $pdo = new PDO(‘mysql:host=localhost;dbname=testdb’, ‘user’, ‘password’); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Insert into first table
$pdo->exec("INSERT INTO products (name, price) VALUES ('Laptop', 999.99)");
$productId = $pdo->lastInsertId();

// Insert into second table
$pdo->exec("INSERT INTO orders (product_id, quantity) VALUES ($productId, 1)");
$orderId = $pdo->lastInsertId();

echo "Product ID: $productId, Order ID: $orderId";

} catch (PDOException $e) { echo “Error: " . $e->getMessage(); }

This shows inserting into two tables and getting both IDs. Each INSERT operation updates the lastInsertId value. The IDs are used to link records.

lastInsertId with Transactions

lastInsertId works within transactions, returning IDs before commit.

pdo_lastinsertid_transaction.php

<?php

declare(strict_types=1);

try { $pdo = new PDO(‘mysql:host=localhost;dbname=testdb’, ‘user’, ‘password’); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->beginTransaction();

$stmt = $pdo->prepare('INSERT INTO logs (message) VALUES (?)');
$stmt->execute(['System started']);
$logId = $pdo->lastInsertId();

$pdo->commit();

echo "Log entry created with ID: $logId";

} catch (PDOException $e) { $pdo->rollBack(); echo “Error: " . $e->getMessage(); }

This demonstrates lastInsertId within a transaction. The ID is available before committing. If the transaction fails, the ID won’t be valid.

lastInsertId with SQLite

SQLite handles lastInsertId slightly differently than MySQL.

pdo_lastinsertid_sqlite.php

<?php

declare(strict_types=1);

try { $pdo = new PDO(‘sqlite:test.db’); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec('CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)');

$stmt = $pdo->prepare('INSERT INTO items (name) VALUES (?)');
$stmt->execute(['Sample Item']);

$lastId = $pdo->lastInsertId();
echo "SQLite last inserted ID: " . $lastId;

} catch (PDOException $e) { echo “Error: " . $e->getMessage(); }

SQLite requires AUTOINCREMENT for auto-increment columns. lastInsertId returns the ROWID which is always available for SQLite tables.

lastInsertId with PostgreSQL

PostgreSQL requires sequence names for lastInsertId to work properly.

pdo_lastinsertid_postgresql.php

<?php

declare(strict_types=1);

try { $pdo = new PDO(‘pgsql:host=localhost;dbname=testdb’, ‘user’, ‘password’); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('INSERT INTO customers (name) VALUES (?)');
$stmt->execute(['Acme Corp']);

// PostgreSQL requires sequence name
$lastId = $pdo->lastInsertId('customers_id_seq');
echo "PostgreSQL last inserted ID: " . $lastId;

} catch (PDOException $e) { echo “Error: " . $e->getMessage(); }

PostgreSQL uses sequences for auto-increment. The sequence name follows the pattern tablename_columnname_seq. This must be passed to lastInsertId.

Handling lastInsertId Errors

lastInsertId can fail and return false, which should be handled.

pdo_lastinsertid_error.php

<?php

declare(strict_types=1);

try { $pdo = new PDO(‘mysql:host=localhost;dbname=testdb’, ‘user’, ‘password’); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Table without auto-increment
$pdo->exec('CREATE TEMPORARY TABLE temp_data (id INT, value TEXT)');
$pdo->exec("INSERT INTO temp_data VALUES (1, 'Test')");

$lastId = $pdo->lastInsertId();
if ($lastId === false) {
    echo "No auto-increment value available";
} else {
    echo "Last ID: $lastId";
}

} catch (PDOException $e) { echo “Error: " . $e->getMessage(); }

This shows handling when lastInsertId fails. Without an auto-increment column, it returns false. Always check the return value for errors.

lastInsertId with Multiple Connections

Each PDO connection maintains its own lastInsertId value.

pdo_lastinsertid_multiconn.php

<?php

declare(strict_types=1);

try { // First connection $pdo1 = new PDO(‘mysql:host=localhost;dbname=testdb’, ‘user’, ‘password’); $pdo1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Second connection
$pdo2 = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$pdo2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo1->exec("INSERT INTO users (name) VALUES ('Connection 1 User')");
$id1 = $pdo1->lastInsertId();

$pdo2->exec("INSERT INTO users (name) VALUES ('Connection 2 User')");
$id2 = $pdo2->lastInsertId();

echo "Connection 1 ID: $id1, Connection 2 ID: $id2";

} catch (PDOException $e) { echo “Error: " . $e->getMessage(); }

This demonstrates that each PDO instance tracks its own lastInsertId. Operations on one connection don’t affect another connection’s lastInsertId.

Best Practices

• Check Return Value: Always verify lastInsertId didn’t return false.

• Use Immediately: Call it right after the INSERT operation.

• Database Specifics: Know your database’s requirements.

• Transactions: Remember IDs may roll back with transactions.

• Connection Scope: IDs are connection-specific.

Source

PHP PDO::lastInsertId Documentation

This tutorial covered the PDO::lastInsertId method with practical examples showing its usage across different database systems and scenarios.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all PHP PDO Functions.