PHP MySQLi Last Insert ID

How it works

An insert’s ID and affected-row count answer different questions. Read both through the API with their range and driver rules in mind. This disposable lab uses a temporary table and a single inserted row, so the result is easy to verify independently. Real retry handling needs a unique business reference or another idempotency contract, not just a new auto-increment value for each attempt.

Run it locally

Use a separate disposable MySQL/MariaDB lab, never a production or existing application database. The example requires pdo_mysql or mysqli as appropriate and the explicit lab settings below. The review uses a private MariaDB instance on an ephemeral loopback port. SQLite lessons need no database service. No live credentials are embedded in the source.

Create an empty bhylu_php_lab database on your separate lab server and set BHYLU_LAB_DSN to mysql:host=127.0.0.1;port=YOUR_LAB_PORT;dbname=bhylu_php_lab;charset=utf8mb4. Set BHYLU_LAB_USER, BHYLU_LAB_PASSWORD and BHYLU_LAB_PORT outside source. On Windows PowerShell use $env:NAME = "value" in that terminal. The schema-administration lesson additionally requires BHYLU_ALLOW_SCHEMA_LAB=yes and creates/deletes only its own randomly named schema.

php -l mysqli-insert-id.php
php mysqli-insert-id.php

Download this source as a local PHP lab file. Keep lab files separate from the Bhylu project and production data.

Example

<?php
declare(strict_types=1);

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$port = filter_var(getenv('BHYLU_LAB_PORT'), FILTER_VALIDATE_INT, ['options'=>['min_range'=>1024,'max_range'=>65535]]);
if ($port === false) throw new RuntimeException('Set the separate lab port');
$db = new mysqli('127.0.0.1', getenv('BHYLU_LAB_USER'), getenv('BHYLU_LAB_PASSWORD'), 'bhylu_php_lab', $port);
$db->set_charset('utf8mb4');
$db->query('CREATE TEMPORARY TABLE lending_sample (id INT PRIMARY KEY AUTO_INCREMENT, item VARCHAR(60) NOT NULL, quantity INT NOT NULL)');
$db->query("INSERT INTO lending_sample (item,quantity) VALUES ('Spade',2),('Drill',1),('Spade',3)");
$insert = $db->prepare("INSERT INTO lending_sample (item,quantity) VALUES (?,?)");
$item = "Rake"; $quantity = 4;
$insert->bind_param("si",$item,$quantity);
$insert->execute();
echo "New id: ", $db->insert_id, "\n";
echo $insert->affected_rows, " inserted\n";
$insert->close(); $db->close();

Expected result

New id: 4
1 inserted

The fixture created the first three IDs, making four reproducible within this isolated connection. The affected-row count confirms one insert, not the total size of the table. An arbitrary live auto-increment sequence can contain gaps, so the allocated ID must not be presented as a row-count calculation.