How to Retrieve Auto-Generated IDs After MySQL Inserts?

MySQL Insert with Automatic ID Retrieval

When inserting a new row into a MySQL table, it's often necessary to retrieve the newly assigned 'id' value. The 'id' field typically auto-increments, ensuring the uniqueness of each row. However, it can be a concern if there is a delay between inserting the row and fetching the 'id'.

One method to mitigate this issue is to utilize MySQL's INSERT ... SELECT statement. This statement allows you to insert a row from a SELECT query, obtaining the 'id' value in a single transaction.

$link = mysqli_connect('127.0.0.1', 'my_user', 'my_pass', 'my_db');
mysqli_query($link, "INSERT INTO mytable SELECT 1, 2, 3, 'blah' FROM DUAL");
$id = mysqli_insert_id($link);

In this example, the INSERT ... SELECT statement inserts a row into the mytable with the specified data, while the mysqli_insert_id() function retrieves the 'id' value of the newly inserted row.

Another approach is to use MySQL's LAST_INSERT_ID() function. This function returns the 'id' value of the last inserted row for the current connection. However, it's important to note that the LAST_INSERT_ID() value is connection-specific, so it's essential to use it within the same connection that performed the INSERT operation.

The above is the detailed content of How to Retrieve Auto-Generated IDs After MySQL Inserts?. For more information, please follow other related articles on the PHP Chinese website!