How to Get the Last Inserted ID in MySQL Using PHP (Complete Guide)

When working with MySQL tables that use an AUTO_INCREMENT primary key, it’s often necessary to retrieve the ID of the most recently inserted record. This is crucial when you need to reference that ID in related tables or for further processing in your application.

In this guide, we’ll walk through three reliable methods to get the last inserted ID in MySQL using PHP:

• MySQLi Object-Oriented
• MySQLi Procedural
• PDO (PHP Data Objects)

We’ll also cover how to establish a database connection, verify if a table exists, and create a table using PHP.

✅ Step 1: Connect to MySQL Database with PHP

Before you can execute any SQL queries, you need to establish a database connection. Here’s an example using MySQLi (Object-Oriented):

define ("DB_HOST", "localhost");
define ("DB_USER", "root");
define ("DB_PASS", "");
define ("DB_NAME", "posnew");

$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if ($db->connect_errno > 0) {
    die("Database connection failed: " . $db->connect_error);
}

✅ Step 2: Check if Table Exists (Optional)

If you’re unsure whether the target table exists, you can run this check:

if ($db->query("SELECT 1 FROM user_meta") === FALSE) {
    echo "Table does not exist.";
} else {
    echo "Table exists.";
}

✅ Step 3: Create a Table with AUTO_INCREMENT Primary Key

Here’s how to create a table if it doesn’t already exist:

$query = "CREATE TABLE IF NOT EXISTS user_meta (
    user_meta_id BIGINT(20) NOT NULL AUTO_INCREMENT,
    user_id BIGINT(20) NULL,
    message_email VARCHAR(50),
    last_login_time DATETIME,
    last_login_ip VARCHAR(120),
    login_attempt BIGINT(20),
    login_lock VARCHAR(50),
    PRIMARY KEY (user_meta_id)
)";
$db->query($query) or die($db->error);

The user_meta_id is the auto-increment field whose value you’ll retrieve after insert operations.

🧪 Method 1: Get Last Insert ID Using MySQLi (Object-Oriented)

$query = "INSERT INTO user_meta (user_id, message_email, last_login_time)
          VALUES ('$user_id', '$message_email', '$last_login_time')";
$db->query($query);

$lastInsertId = $db->insert_id;
echo "Last inserted ID is: " . $lastInsertId;

🧪 Method 2: Get Last Insert ID Using MySQLi (Procedural)

$db = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME);

$query = "INSERT INTO user_meta (user_id, message_email, last_login_time)
          VALUES ('$user_id', '$message_email', '$last_login_time')";

if (mysqli_query($db, $query)) {
    $lastInsertId = mysqli_insert_id($db);
    echo "Inserted successfully. Last ID: " . $lastInsertId;
} else {
    echo "Insert Error: " . mysqli_error($db);
}

🧪 Method 3: Get Last Insert ID Using PDO

try {
    $pdo = new PDO("mysql:host=localhost;dbname=posnew", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $pdo->prepare("INSERT INTO user_meta (user_id, message_email, last_login_time)
                           VALUES (:user_id, :message_email, :last_login_time)");

    $stmt->execute([
        ':user_id' => $user_id,
        ':message_email' => $message_email,
        ':last_login_time' => $last_login_time
    ]);

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

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

🔄 Getting Insert ID After an UPDATE Query?

For most UPDATE operations where you’re using a known ID, you don’t need the last insert ID. But if you’re updating based on an alternate field (e.g., email), and want to retrieve the affected row ID, you’d typically use the same logic as insert, but it’s recommended to use SELECT after update to get the record’s ID.

💡 Summary

• Use $db->insert_id (MySQLi OO) or mysqli_insert_id($db) (MySQLi Procedural) to get the last inserted ID.
• PDO also provides $pdo->lastInsertId() for the same purpose.
• Always validate that the table exists and ensure it has an AUTO_INCREMENT primary key.

Need help debugging MySQL or PHP code?
You may need to Hire a PHP developer for great results.