How to Retrieve the Auto-Incrementing ID After a MySQL Insert in PHP Without Race Conditions?

Getting the 'id' Field after Inserting a Row in PHP/MySQL without Race Conditions

When inserting a row into a MySQL table with an auto-incrementing 'id' field, it is often desirable to retrieve that 'id' value to use in subsequent operations. However, simply inserting the row and then querying the database for the 'id' can lead to race conditions if another row is inserted in the meantime.

A reliable method to ensure the integrity of the 'id' retrieval is to use the mysqli_insert_id() function. This function returns the ID generated by the last INSERT statement performed on the current connection.

Here's how to implement it:

$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);

Another approach, but less recommended, is to insert both the row into the primary table and a corresponding row into a second table that references the 'id' of the primary table in a single atomic transaction using the LAST_INSERT_ID() method.

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

Note that each MySQL connection keeps track of the 'id' value separately, preventing conflicts from occurring with concurrent inserts.

The above is the detailed content of How to Retrieve the Auto-Incrementing ID After a MySQL Insert in PHP Without Race Conditions?. For more information, please follow other related articles on the PHP Chinese website!