Practice Now
PHP
MySQL
SQL Insert
Auto Increment
Database ID

PHP/MySQL insert row then get 'id'

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Databases

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)

php
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)

php
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);

PDO provides a database-agnostic interface with better error handling:

php
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:

php
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:

php
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:

php
$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:

php
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:

php
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

php
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
Related reading
Course
Beginner
27 lessons
10 hours
System Design Fundamentals

Build a strong foundation in designing scalable, reliable distributed systems.

View the course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Codemia
© 2026 Codemia
Resources
Legal
Social
All Rights Reserved.