Introduction
After inserting a row into a MySQL table with an AUTO_INCREMENT primary key, you need to retrieve the generated ID. PHP provides mysqli_insert_id() (procedural) or $mysqli->insert_id (object-oriented) for MySQLi, and $pdo->lastInsertId() for PDO. These return the ID from the last INSERT on the current connection, making them safe for concurrent applications.
Using MySQLi (Object-Oriented)
1$mysqli = new mysqli("localhost", "user", "password", "database");
2
3if ($mysqli->connect_error) {
4 die("Connection failed: " . $mysqli->connect_error);
5}
6
7// Prepared statement (safe from SQL injection)
8$stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
9$stmt->bind_param("ss", $name, $email);
10
11$name = "Alice";
12$email = "[email protected]";
13$stmt->execute();
14
15// Get the auto-generated ID
16$newId = $mysqli->insert_id;
17echo "New user ID: " . $newId;
18
19$stmt->close();
20$mysqli->close();
Using MySQLi (Procedural)
1$conn = mysqli_connect("localhost", "user", "password", "database");
2
3$stmt = mysqli_prepare($conn, "INSERT INTO users (name, email) VALUES (?, ?)");
4mysqli_stmt_bind_param($stmt, "ss", $name, $email);
5
6$name = "Bob";
7$email = "[email protected]";
8mysqli_stmt_execute($stmt);
9
10$newId = mysqli_insert_id($conn);
11echo "New user ID: " . $newId;
12
13mysqli_stmt_close($stmt);
14mysqli_close($conn);
Using PDO (Recommended)
PDO provides a database-agnostic interface with better error handling:
1try {
2 $pdo = new PDO("mysql:host=localhost;dbname=database", "user", "password");
3 $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
4
5 $stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (:name, :email)");
6 $stmt->execute([
7 ':name' => 'Charlie',
8 ':email' => '[email protected]'
9 ]);
10
11 $newId = $pdo->lastInsertId();
12 echo "New user ID: " . $newId;
13
14} catch (PDOException $e) {
15 echo "Error: " . $e->getMessage();
16}
Inserting Multiple Rows and Getting IDs
When inserting multiple rows, insert_id returns the ID of the first row in a multi-row insert:
1// Multi-row insert
2$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?), (?, ?), (?, ?)");
3$stmt->execute(['Alice', '[email protected]', 'Bob', '[email protected]', 'Charlie', '[email protected]']);
4
5$firstId = $pdo->lastInsertId();
6// $firstId = ID of 'Alice' row
7// Bob's ID = $firstId + 1
8// Charlie's ID = $firstId + 2 (if AUTO_INCREMENT step is 1)
For guaranteed individual IDs, insert one row at a time:
1$users = [
2 ['Alice', '[email protected]'],
3 ['Bob', '[email protected]'],
4 ['Charlie', '[email protected]'],
5];
6
7$ids = [];
8$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
9
10foreach ($users as $user) {
11 $stmt->execute($user);
12 $ids[] = $pdo->lastInsertId();
13}
14
15print_r($ids); // [1, 2, 3]
INSERT with RETURNING (MySQL 8.0.21+ / MariaDB)
MySQL does not support RETURNING natively, but you can use LAST_INSERT_ID() in a follow-up query:
$pdo->exec("INSERT INTO users (name, email) VALUES ('Dave', '[email protected]')");
$stmt = $pdo->query("SELECT LAST_INSERT_ID() AS id");
$id = $stmt->fetchColumn();
PostgreSQL supports RETURNING directly:
1// PostgreSQL only
2$stmt = $pdo->prepare("INSERT INTO users (name, email) VALUES (?, ?) RETURNING id");
3$stmt->execute(['Eve', '[email protected]']);
4$id = $stmt->fetchColumn();
Thread Safety and Concurrency
insert_id / lastInsertId() is per-connection, not per-table. This means:
1// Connection A inserts user → ID = 42
2// Connection B inserts user → ID = 43
3// Connection A calls lastInsertId() → returns 42 (correct!)
4
5// It does NOT return 43 even though 43 was inserted more recently.
6// Each connection tracks its own last insert ID.
This is safe for concurrent web applications where each request has its own database connection.
Using Transactions
1try {
2 $pdo->beginTransaction();
3
4 // Insert parent record
5 $stmt = $pdo->prepare("INSERT INTO orders (customer_id, total) VALUES (?, ?)");
6 $stmt->execute([1, 99.99]);
7 $orderId = $pdo->lastInsertId();
8
9 // Insert child records using the parent ID
10 $stmt = $pdo->prepare("INSERT INTO order_items (order_id, product, quantity) VALUES (?, ?, ?)");
11 $stmt->execute([$orderId, 'Widget', 3]);
12 $stmt->execute([$orderId, 'Gadget', 1]);
13
14 $pdo->commit();
15 echo "Order $orderId created with items";
16
17} catch (Exception $e) {
18 $pdo->rollBack();
19 echo "Error: " . $e->getMessage();
20}
Common Pitfalls
Using the wrong connection: insert_id returns the last ID for that specific connection object. If you have multiple connections, call it on the same connection that performed the insert.
No AUTO_INCREMENT column: If the table does not have an AUTO_INCREMENT column, lastInsertId() returns 0. Make sure the table has id INT AUTO_INCREMENT PRIMARY KEY.
INSERT ... ON DUPLICATE KEY UPDATE: If the row already exists and is updated (not inserted), lastInsertId() returns the existing row's ID only if LAST_INSERT_ID(id) is used in the ON DUPLICATE KEY UPDATE clause.
String return type: $pdo->lastInsertId() returns a string, not an integer. Cast to int if needed: (int) $pdo->lastInsertId().
SQL injection: Never concatenate user input into SQL strings. Always use prepared statements with parameterized queries. $pdo->query("INSERT INTO users VALUES ('$name')") is vulnerable.
Summary
Use $mysqli->insert_id (MySQLi) or $pdo->lastInsertId() (PDO) to get the auto-generated ID after an INSERT
The ID is per-connection, making it safe for concurrent applications
Always use prepared statements to prevent SQL injection
For multi-row inserts, lastInsertId() returns the first row's ID — insert individually to get each ID
Use PDO over MySQLi for database-agnostic code and better error handling
