Introduction: Why Getting the Last Inserted ID Is Crucial

When you insert data into a MySQL table—such as a new user, order, or blog post—you often need to know the ID of that new record. This is especially useful when:

• Linking related records (e.g., inserting into a second table)

• Redirecting users to the detail page of the newly created item

• Logging or auditing database actions

Thankfully, PHP provides simple ways to get the last inserted ID after an INSERT operation. In this article, we’ll explore how to do that using both MySQLi and PDO with real-world examples and tips.

How MySQL Auto-Increment IDs Work

MySQL allows tables to use an AUTO_INCREMENT column, typically for the id field:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

Each new row inserted will get a unique, sequential ID, which PHP can retrieve immediately after the insert.

Method 1: Get Last Inserted ID Using MySQLi

✅ Basic Example with MySQLi

<?php
// Connect to MySQL
$conn = new mysqli("localhost", "root", "", "test_db");

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Insert a new record
$sql = "INSERT INTO users (name) VALUES ('Alice')";
if ($conn->query($sql) === TRUE) {
    // Get the last inserted ID
    $last_id = $conn->insert_id;
    echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
    echo "Error: " . $conn->error;
}

$conn->close();
?>

How It Works:

• $conn->insert_id is used immediately after an INSERT query to retrieve the last auto-generated ID from the same connection.

Method 2: Get Last Inserted ID Using PDO

✅ Basic Example with PDO

<?php
try {
    // Connect with PDO
    $pdo = new PDO("mysql:host=localhost;dbname=test_db", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Insert a new user
    $stmt = $pdo->prepare("INSERT INTO users (name) VALUES (:name)");
    $stmt->execute([':name' => 'Bob']);

    // Get last inserted ID
    $last_id = $pdo->lastInsertId();
    echo "New user inserted. Last ID: " . $last_id;
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}
?>

How It Works:

• PDO::lastInsertId() returns the last ID inserted on the current connection.

• Works with tables that have AUTO_INCREMENT columns.

Complete Functional Example: Form + Insert + Get Last ID

<?php
// Simulate form input
$name = "Charlie";

// Connect to MySQLi
$conn = new mysqli("localhost", "root", "", "form_db");

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Insert form data into the database
$stmt = $conn->prepare("INSERT INTO contacts (name) VALUES (?)");
$stmt->bind_param("s", $name);

if ($stmt->execute()) {
    $last_id = $conn->insert_id;
    echo "Contact saved successfully with ID: $last_id";
} else {
    echo "Error: " . $stmt->error;
}

$stmt->close();
$conn->close();
?>

Feature Comparison: MySQLi vs PDO for Last Insert ID

Tips & Common Pitfalls

✅ Best Practices

• Always check if the insert was successful before accessing the last ID.

• Use prepared statements to prevent SQL injection.

• Retrieve the ID immediately after the insert, before another query runs.

• For advanced systems, consider using UUIDs instead of auto-increment.

❌ Common Mistakes

Conclusion: Wrapping It Up

Retrieving the last inserted ID in PHP is an essential part of working with databases. It helps you keep track of newly added records, maintain relationships between tables, and build robust web apps.

Key Takeaways

• Use $conn->insert_id with MySQLi

• Use $pdo->lastInsertId() with PDO

• Always verify that your INSERT query succeeded

• Stick to prepared statements for secure inserts