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

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

Inserting a row into a MySQL table often involves a requirement to retrieve the newly generated 'id' for the inserted row. Typically, this entails a subsequent query to fetch the row that matches the inserted data, but this method introduces the possibility of duplicate rows with varying 'id' values.

A more efficient approach is to leverage MySQL's auto-increment functionality and the PHP mysqli_insert_id() function. This function allows you to retrieve the ID of the last inserted row within the current connection:

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

This approach eliminates the risk of race conditions and provides a reliable way to obtain the 'id' of the newly inserted row.

Another technique involves executing two queries in a single transaction, leveraging MySQL's LAST_INSERT_ID() method:

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

This method modifies both tables within a single transaction, ensuring consistent data across the affected tables.

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