Get the MySQL AUTO_INCREMENT ID after INSERT

After inserting a row into a table with an AUTO_INCREMENT column, applications often need the ID MySQL generated. For example, a help desk may insert a ticket and then display or email its new ticket number.

At SQL level, MySQL provides LAST_INSERT_ID():

INSERT INTO help_desk
    (userid, type, domain, detail)
VALUES
    (24, 'Support', 'example.com', 'Login problem');

SELECT LAST_INSERT_ID() AS ticket_id;

In PHP PDO, call $dbo->lastInsertId() immediately after the successful INSERT on the same database connection.

MySQL LAST_INSERT_ID() Top ↑

If MySQL generated an AUTO_INCREMENT value, retrieve it from the same session:

INSERT INTO help_desk
    (userid, type, domain, detail)
VALUES
    (24, 'Support', 'example.com', 'Login problem');

SELECT LAST_INSERT_ID() AS ticket_id;

If the generated value is 10524, that number can be stored in application state, displayed to the user, used to create related records, or included in a confirmation message.

PHP PDO lastInsertId() Top ↑

The old version of this tutorial used the removed PHP mysql_insert_id() extension. Modern PHP should use PDO or MySQLi instead.

With PDO, use a prepared statement and call lastInsertId() after execute() succeeds:

<?php
require 'config.php';

$userid=24;
$type='Support';
$domain='example.com';
$detail='Login problem';

$stmt=$dbo->prepare(
    "INSERT INTO help_desk
        (userid, type, domain, detail)
     VALUES
        (:userid, :type, :domain, :detail)"
);

$stmt->bindValue(
    ':userid',
    $userid,
    PDO::PARAM_INT
);

$stmt->bindValue(
    ':type',
    $type,
    PDO::PARAM_STR
);

$stmt->bindValue(
    ':domain',
    $domain,
    PDO::PARAM_STR
);

$stmt->bindValue(
    ':detail',
    $detail,
    PDO::PARAM_STR
);

$stmt->execute();

$ticket_id=(int)$dbo->lastInsertId();

echo 'Your trouble ticket number is: '.$ticket_id;

This is safer than the legacy example because external values are bound rather than inserted directly into the SQL string.

Why call lastInsertId() on the connection? Top ↑

The generated identifier belongs to the database connection, so the method is called on $dbo, not on the prepared-statement object:

$ticket_id=$dbo->lastInsertId();

MySQLi insert_id Top ↑

MySQLi exposes the generated AUTO_INCREMENT value through the connection's insert_id property:

<?php
$stmt=$connection->prepare(
    "INSERT INTO help_desk
        (userid, type, domain, detail)
     VALUES (?, ?, ?, ?)"
);

$stmt->bind_param(
    'isss',
    $userid,
    $type,
    $domain,
    $detail
);

$stmt->execute();

$ticket_id=(int)$connection->insert_id;

echo 'Your trouble ticket number is: '.$ticket_id;

Both PDO and MySQLi solve the same application problem: retrieve the ID generated by the successful INSERT on the current connection.

Create a Display Ticket Number Top ↑

The database ID does not have to be the exact text shown to the customer. Keep the stored key numeric and create a separate display reference when the interface needs a prefix or date.

<?php
$ticket_id=10524;

$display_ticket=
    'HD-'.
    date('Ymd').
    '-'.
    $ticket_id;

echo 'Your trouble ticket ID is: '.
     htmlspecialchars(
         $display_ticket,
         ENT_QUOTES,
         'Windows-1252'
     );

An example display value is:

HD-20260909-10524

The numeric database key remains 10524. The formatted ticket number is a presentation or business reference built from that key.

The ticket ID can then be included in a confirmation message such as an email sent from PHP.

Why the Same Connection Matters Top ↑

Insert-ID APIs are designed to avoid a concurrency problem. Consider two application connections:

-- Connection A inserts ID 10524
INSERT INTO help_desk
    (userid, type, domain, detail)
VALUES
    (24, 'Support', 'example.com', 'Login problem');

-- Connection B may insert ID 10525 immediately afterwards

A query such as:

-- Do not use this to identify your own inserted row
SELECT MAX(ticket_id)
FROM help_desk;

can return another connection's row. LAST_INSERT_ID(), PDO lastInsertId(), and MySQLi insert_id use the current connection's insert state instead.

Insert ID after a Multi-row INSERT Top ↑

MySQL can generate several AUTO_INCREMENT values in one statement:

INSERT INTO help_desk
    (userid, type, domain, detail)
VALUES
    (24, 'Support', 'a.example', 'Issue A'),
    (25, 'Support', 'b.example', 'Issue B'),
    (26, 'Support', 'c.example', 'Issue C');

For a multi-row INSERT that generates AUTO_INCREMENT values, MySQL's insert-ID mechanism reports the first automatically generated value for the statement. Do not assume you can always reconstruct every generated ID simply by adding 1, because configuration and concurrent allocation behavior can make such assumptions unsafe.

Explicit IDs and Tables without AUTO_INCREMENT Top ↑

The insert-ID feature is primarily for values generated by an AUTO_INCREMENT column. If your application explicitly supplies the primary key:

INSERT INTO help_desk
    (ticket_id, userid, type, domain, detail)
VALUES
    (90001, 24, 'Support', 'example.com', 'Login problem');

then the application already knows the supplied ID. Do not build logic that assumes lastInsertId() is meaningful for every INSERT statement or every table design.

Insert IDs and Transactions Top ↑

When an INSERT is part of a transaction, capture the generated ID immediately after the successful INSERT on the same connection, then use it for related statements if required.

<?php
$dbo->beginTransaction();

try{
    $stmt=$dbo->prepare(
        "INSERT INTO help_desk
            (userid, type, domain, detail)
         VALUES
            (:userid, :type, :domain, :detail)"
    );

    $stmt->execute([
        ':userid' => $userid,
        ':type' => $type,
        ':domain' => $domain,
        ':detail' => $detail
    ]);

    $ticket_id=(int)$dbo->lastInsertId();

    // Related INSERTs can safely use $ticket_id here.

    $dbo->commit();
}
catch(Throwable $e){
    if($dbo->inTransaction()){
        $dbo->rollBack();
    }

    error_log($e->getMessage());
    echo 'Unable to create the ticket.';
}

See PDO transactions when several database changes must succeed or fail together.

Common Insert-ID Mistakes Top ↑

Using the old mysql_insert_id() API Top ↑

The old PHP mysql_*() extension was removed from PHP. Use PDO or MySQLi in modern PHP.

Using MAX(id) after INSERT Top ↑

MAX(id) is a table-wide query and can return another connection's row. Use the current connection's generated insert ID.

Calling lastInsertId() on the statement Top ↑

With PDO, call $dbo->lastInsertId() on the PDO connection.

Calling the insert-ID method on a different connection Top ↑

The generated ID is connection-specific. Retrieve it from the same connection that executed the successful INSERT.

Assuming AUTO_INCREMENT IDs are gapless Top ↑

Generated identifiers can contain gaps. See MySQL AUTO_INCREMENT.

Assuming a multi-row INSERT returns every generated ID Top ↑

The insert-ID mechanism identifies the first automatically generated value for a multi-row INSERT. Design batch workflows accordingly.

Displaying database errors directly to users Top ↑

Log exception details on the server and show a safe application message instead of exposing database internals.

Related Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: