How to Efficiently Retrieve the Auto-Increment ID After an INSERT in PHP/MySQL?

Inserting a Row and Retrieving Its 'id' in PHP/MySQL

When inserting a row into a MySQL table where the 'id' field auto-increments, retrieving the inserted row's 'id' is a common task. While one approach involves inserting the row and then querying for the matching row, this introduces a potential race condition.

Solution: Using mysqli_insert_id()

To avoid this issue, PHP provides the mysqli_insert_id() function, which directly retrieves the ID of the last inserted row for a given MySQL connection. This method ensures that the ID is obtained reliably without the risk of race conditions or duplicate results.

<?php
$link = mysqli_connect('127.0.0.1', 'my_user', 'my_pass', 'my_db');
mysqli_query($link, "INSERT INTO mytable (1, 2, 3, 'blah')");
$id = mysqli_insert_id($link);
?>

Alternative Solution: LAST_INSERT_ID()

Another method involves using MySQL's LAST_INSERT_ID() function, which can be utilized within a multi-table insert operation. In this approach, the row is inserted into multiple tables, and the LAST_INSERT_ID() function ensures that the 'id' is propagated across all the tables modified within the single query.

<?php
mysqli_query($link, "INSERT INTO my_user_table ...;
  INSERT INTO my_other_table (`user_id`) VALUES (LAST_INSERT_ID())");
?>

Note: Each MySQL connection maintains its own 'id' tracking, preventing conflicts between different connections.

The above is the detailed content of How to Efficiently Retrieve the Auto-Increment ID After an INSERT in PHP/MySQL?. For more information, please follow other related articles on the PHP Chinese website!