How to Retrieve the ID of a Newly Inserted Row in PHP/MySQL?

Retrieve the ID of an Inserted Row in PHP/MySQL

When inserting a row into a MySQL table with an auto-incrementing id field, it's essential to retrieve the newly generated id for subsequent operations.

Avoiding Race Conditions

One approach to obtaining the id is to query the database for the row that matches the inserted information. However, this method introduces a race condition where another row could be inserted before the query is executed, resulting in inaccurate results.

Using mysqli_insert_id()

A reliable solution is to utilize MySQL's LAST_INSERT_ID() function. The mysqli_insert_id() method in PHP returns the last generated id for the current connection.

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

Multiple Queries in One Operation

An alternative approach is to execute multiple queries in a single operation, taking advantage of MySQL's LAST_INSERT_ID() method. This ensures that the id is correctly assigned even when multiple tables are involved.

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

This method eliminates the need to worry about race conditions and provides a reliable way to retrieve the id of the inserted row.

The above is the detailed content of How to Retrieve the ID of a Newly Inserted Row in PHP/MySQL?. For more information, please follow other related articles on the PHP Chinese website!