MySQL Get Last ID

Why You Need the Last Inserted ID

In simple terms: When you insert a new row into a table with an AUTO_INCREMENT column, MySQL chooses the ID for you. Very often, you need that ID immediately: to redirect the user to their new order's page, to insert the order's items (which must reference the order), or to log what was created.

Technically: After an INSERT into a table with an AUTO_INCREMENT column, mysqli::$insert_id (or mysqli_stmt::$insert_id for prepared statements) returns the generated value. In SQL, the same value is available through LAST_INSERT_ID(). The value is tracked per connection, so concurrent users never see each other's IDs.

Reading insert_id

<?php
require __DIR__ . '/src/db.php';

$db = db();

$db->execute_query(
    'INSERT INTO customers (name, email, city) VALUES (?, ?, ?)',
    ['Farah', 'farah@example.com', 'Vellore']
);

$customerId = $db->insert_id;

echo "New customer ID: $customerId\n";
var_dump(is_int($customerId));

Output (example)

New customer ID: 7
bool(true)

How It Works

1. MySQL generates the next AUTO_INCREMENT value while inserting the row.
2. insert_id reads the value generated by the most recent INSERT on this connection.
3. The value is an integer (it is returned as a string only if it exceeds PHP's maximum integer).

(The ID is 7 rather than 6 because the failed duplicate insert in MySQL Insert Data used up the value 6 — see "Gaps in IDs Are Normal" below.)

When insert_id Is 0

Read insert_id immediately after the insert, before running other queries on the same connection.

Why Not SELECT MAX(id)?

A common mistake is to look up the newest ID after inserting:

// WRONG
$db->execute_query('INSERT INTO orders (customer_id) VALUES (?)', [1]);
$orderId = $db->query('SELECT MAX(id) FROM orders')->fetch_column();

If two customers place orders at almost the same time, both requests may read the same maximum ID — one of them receives the other customer's order number. insert_id and LAST_INSERT_ID() are tied to the connection that performed the insert, so they are always correct, even under heavy concurrency.

Practical Example: Creating an Order With Its Items

An order is stored in two tables: one row in orders and several rows in order_items that reference it. The order's ID is needed to insert the items, and everything should succeed or fail together, so a transaction wraps the steps:

<?php
require __DIR__ . '/src/db.php';

function createOrder(mysqli $db, int $customerId, array $items): int
{
    $db->begin_transaction();

    try {
        $db->execute_query('INSERT INTO orders (customer_id, status) VALUES (?, ?)', [$customerId, 'pending']);
        $orderId = $db->insert_id;

        $total = 0;
        foreach ($items as $item) {
            $db->execute_query(
                'INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (?, ?, ?, ?)',
                [$orderId, $item['product_id'], $item['quantity'], $item['unit_price']]
            );
            $total += $item['quantity'] * $item['unit_price'];
        }

        $db->execute_query('UPDATE orders SET total = ? WHERE id = ?', [$total, $orderId]);

        $db->commit();
        return $orderId;
    } catch (Throwable $e) {
        $db->rollback();
        throw $e;
    }
}

$db = db();
$db->query('ALTER TABLE orders AUTO_INCREMENT = 501');   // one-time setup: start order numbers at 501

$orderId = createOrder($db, 1, [
    ['product_id' => 2, 'quantity' => 3, 'unit_price' => 60.00],    // Notebook A5
    ['product_id' => 1, 'quantity' => 10, 'unit_price' => 20.00],   // Gel Pen
]);

$order = $db->execute_query('SELECT id, status, total FROM orders WHERE id = ?', [$orderId])->fetch_assoc();
echo "Created order #{$order['id']} ({$order['status']}), total ₹{$order['total']}\n";

Output (with the products from MySQL Insert Multiple in place)

Created order #501 (pending), total ₹380.00

Step-by-Step Explanation

1. ALTER TABLE ... AUTO_INCREMENT = 501 sets the starting value, so order numbers do not start at 1 (a one-time setup step, normally done in a migration).
2. Inside the transaction, the order row is inserted, and insert_id provides its ID — 501.
3. Each item is inserted with that ID as order_id; the foreign key guarantees the order exists.
4. The total (3 × 60 + 10 × 20 \= 380) is saved on the order.
5. commit() makes all changes permanent at once. If any step throws (for example, an invalid product ID violating a foreign key), rollback() undoes everything — no half-created order remains. Transactions are covered in PDO Transactions.
6. The total column is DECIMAL, which MySQLi returns as the string "380.00", preserving exact precision.

In a real shop, unit prices are read from the products table on the server, never taken from the client.

insert_id With Prepared Statements

When using prepare(), the statement object has its own insert_id:

<?php
$stmt = $db->prepare('INSERT INTO customers (name, email) VALUES (?, ?)');
$stmt->execute(['Kumar', 'kumar@example.com']);

echo $stmt->insert_id . "\n";   // same value as $db->insert_id

Multi-Row Inserts and LAST_INSERT_ID()

For an INSERT with several rows, insert_id returns the ID of the first inserted row:

<?php
$db->query("INSERT INTO customers (name, email) VALUES ('Lakshmi', 'lakshmi@example.com'), ('Mani', 'mani@example.com')");

$firstId = $db->insert_id;
$count = $db->affected_rows;

echo "Inserted IDs $firstId to " . ($firstId + $count - 1) . "\n";

Output (example)

Inserted IDs 9 to 10

With InnoDB's default settings, the IDs of a single multi-row insert are consecutive, so the range can be calculated. Inside SQL, LAST_INSERT_ID() returns the same value and can be used directly in follow-up statements:

INSERT INTO orders (customer_id) VALUES (1);
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (LAST_INSERT_ID(), 3, 1, 1299.00);

Gaps in IDs Are Normal

Auto-increment values are not reused. Failed inserts (for example, a duplicate email), rolled-back transactions, and deleted rows all leave gaps in the sequence. IDs identify rows; they are not a count and should never be expected to be continuous. Use a separate column if you need gap-free invoice numbers (a legal requirement in some countries).

Common Mistakes

Using SELECT MAX(id)

Effect: Wrong IDs under concurrent requests.

Fix: Use insert_id or LAST_INSERT_ID().

Reading insert_id Too Late

Effect: Another INSERT on the same connection has overwritten the value.

Fix: Store insert_id in a variable immediately after the insert.

Inserting Related Rows Without a Transaction

Effect: A failure leaves an order without items (or items without an order).

Fix: Wrap related inserts in a transaction.

Expecting Continuous IDs

Effect: Confusion about "missing" IDs.

Fix: Treat IDs as identifiers only; use dedicated numbering for gap-free sequences.

Best Practices

• Read insert_id directly after each insert and store it.
• Use the new ID to insert related rows and to redirect users to the created resource.
• Wrap multi-table inserts in a transaction.
• Never derive new IDs from MAX(id) or counts.
• Accept gaps in auto-increment sequences.

Official reference: https\://www.php.net/manual/en/mysqli.insert-id.php