After inserting an order, your PHP code often needs its generated ID to add order items or show a confirmation. PDO and MySQLi expose that ID through their own APIs. The important details are when you read it, which connection performed the insert, and what kind of insert you used. This reference explains single-row IDs, repeated inserts, multi-row inserts, and transaction boundaries. PDO stays the main course approach, while MySQLi examples are optional. You will also learn why gaps are normal and why you should never discover a new row's identity by selecting MAX(id).
Read the key from the inserting session
For PDO, lastInsertId() returns a string or a driver-specific failure value under its contract. MySQLi exposes insert_id on the connection. Read it on the connection that performed the successful insert, before another relevant operation obscures the intended relationship. Driver behavior differs, including sequence-name arguments in some databases, so document your engine. A generated ID can exceed a PHP integer's range or a JavaScript safe-integer range. Preserve it as a string where the system requires that range, rather than blindly casting every key. An insert into a table without a generated key does not magically provide one. Know the schema and verify the insert succeeded before treating an ID as an application result.
Never use MAX(id) to identify your row
Two sessions can insert concurrently. If you insert a row and then query SELECT MAX(id), another session may already have created a larger ID. Your code can attach items to the wrong order, which is a serious correctness and privacy failure. Session-specific insert APIs avoid that race for the supported generated-key behavior. An alternative is an application-generated unique key when the design calls for it, or an engine's INSERT ... RETURNING capability where supported. Do not assume all SQL engines provide the same RETURNING syntax. The correct method depends on the database and driver. If your business needs a human-facing sequential invoice number, design that separately from the internal generated primary key.
Expect gaps and understand rollback
Auto-generated keys are identifiers, not promises of consecutive counting. Failed inserts, rolled-back transactions, deleted rows, and concurrent activity can leave gaps. Do not recycle gaps casually or infer missing business transactions solely from a key sequence. A rollback may undo the inserted row while the engine does not reuse its allocated ID. If a reporting requirement needs an audited numbering scheme, build and review that specific business process instead of treating AUTO_INCREMENT as a financial ledger. The last insert ID can remain observable in session state even if you later roll back, so only publish it as a successful business result after the transaction commits. Keep failure responses from implying the order exists when it was undone.
Repeat a prepared statement for a batch
Prepare one INSERT and execute it for each validated row. This keeps values separate from SQL and can simplify per-row key collection. Decide whether all rows must succeed together. For an all-or-nothing batch, begin a supported transaction, collect keys during the transaction, and return them only after commit. Validate the complete bounded batch first when possible so a predictable bad field does not trigger unnecessary database work. Large imports may need staging or batches instead of one enormous transaction. A repeated statement is not the same as multiple SQL statements packed into one string. Avoid enabling multi-query APIs merely for convenience; they add complexity and are unnecessary for ordinary prepared batch inserts.
Understand multi-row and duplicate-key behavior
A single multi-row INSERT can be faster but generated-key reporting is engine-specific. MySQL's insert ID behavior for multi-row inserts should not be generalized into a portable list of every inserted key. Deriving a range by adding row numbers can fail under statement modes, server settings, triggers, or mixed outcomes. INSERT IGNORE and ON DUPLICATE KEY UPDATE introduce further distinctions among inserted, skipped, and updated rows. Do not combine them with assumed key arithmetic without verified semantics. If the application needs an exact mapping for every input record, use stable application keys, supported RETURNING behavior, or repeated inserts with carefully collected results. Label each returned result according to what actually happened.
Coordinate parent and child rows
Insert the parent order, read its key, then insert child order items using that key within one transaction. Foreign keys protect the relationship, and the transaction prevents a half-created order when an item fails. Do not start a second database connection for child inserts and expect it to share the parent's transaction. External notifications should be coordinated after commit or through an outbox design so a retry does not send a confirmation for an order that was rolled back. Check commit success under the driver behavior and preserve an appropriate error boundary. A generated key is part of the transaction's work, not proof that the complete business action has finished.
Practice task and worked solution
Design an order save function with a parent order and three order items. The solution validates all item quantities, starts a transaction, inserts the parent, reads its key on the same connection, and inserts each item using that key. If any item violates a constraint, roll back and return no successful order ID. Test a normal order and a deliberately invalid item in a development database. Confirm no parent remains after rollback, while recognizing that the allocated numeric key may still leave a gap. Replace a proposed SELECT MAX(id) implementation and explain the concurrency bug it would create.
Continue learning
Continue with PDO, CRUD, and exceptions.
The PDO lastInsertId reference explains driver-dependent generated-key behavior.
Last Insert ID and Multiple Inserts Examples
Each example shows the code, the output it produces and what each part does.
Collect keys for a bounded PDO batch
Assume $pdo is a configured MySQL connection and labels has an auto-generated ID. Run against a disposable fixture; the statements insert data.
A successful run reports two saved labels and the generated keys. Their actual values depend on the fixture and concurrent activity; they are not guaranteed to start at one or be gapless.
<?php
$names = ['Office', 'Travel'];
$ids = [];
$pdo->beginTransaction();
try {
$stmt = $pdo->prepare('INSERT INTO labels (name) VALUES (?)');
foreach ($names as $name) {
$stmt->execute([$name]);
$ids[] = $pdo->lastInsertId();
}
$pdo->commit();
} catch (Throwable $error) {
if ($pdo->inTransaction()) $pdo->rollBack();
throw $error;
}
echo count($ids) . ' labels saved' . PHP_EOL;
echo 'IDs: ' . implode(', ', $ids);2 labels saved
IDs: database-generated values for this connectionLast Insert ID and Multiple Inserts FAQ
Why did an ID skip a number?
Generated identifiers can have gaps for ordinary reasons: a failed or rolled-back insert still uses up a value, deleted rows leave holes, and some engines reserve blocks of values for speed. Treat IDs as unique labels, not as a count of rows or a gap-free sequence for invoices.
Can I use another connection to read the key?
Use the inserting session's API, such as $pdo->lastInsertId() on the same connection straight after the insert. The value is tracked per connection, so another connection either sees nothing useful or the ID of its own last insert, and querying MAX(id) can return another visitor's row.
Can a multi-row ID reveal every key?
Not portably; verify engine semantics. After a multi-row INSERT, MySQL's lastInsertId() returns the first generated ID of the batch, and whether the rest are consecutive depends on the engine and its settings. When you need every key, insert rows one at a time, or use RETURNING where the database supports it.
Does an allocated ID prove commit?
No. The ID is assigned when the row is inserted, but the transaction can still roll back afterwards, and the row then never exists. Publish success, send confirmation emails or redirect to the new record only after the transaction succeeds.
Last Insert ID and Multiple Inserts Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
A rolled-back insert leaves a gap. What should the application assume?
Last Insert ID and Multiple Inserts Quiz
2 questions to check what you learned.
Why is SELECT MAX(id) unsafe for identifying your insert?
- MAX always returns text
- Primary keys cannot be selected
- Another session may insert a larger key first
- PHP automatically deletes rows
When should collected transaction keys be reported as successful?
- Before any insert
- After a rolled-back insert
- When a browser timer ends
- After the transaction commits successfully