How Can I Get the ID of the Last Updated Row in MySQL Using PHP?

Retrieving the ID of the Last Updated Row in MySQL

When working with a database, it is often necessary to retrieve the ID of the row that was most recently updated. This information can be useful for a variety of purposes, such as tracking changes to the data or determining which rows have been affected by a specific update query.

Solution Using PHP

To obtain the ID of the last updated row in MySQL using PHP, one can employ the following technique:

1. Define a variable to store the update ID: SET @update_id := 0;
2. Update the table, setting the column value and simultaneously assigning the current row ID to the @update_id variable: UPDATE some_table SET column_name = 'value', id = (SELECT @update_id := id) WHERE some_other_column = 'blah' LIMIT 1;
3. Retrieve the update ID: SELECT @update_id;

Additional Enhancement

This technique can be expanded to retrieve the IDs of multiple rows affected by an update query using the following approach:

1. Define a variable to store the collected IDs: SET @uids := null;
2. Update the table, setting the desired column value and accumulating the affected row IDs in the @uids variable: UPDATE footable SET foo = 'bar' WHERE fooid > 5 AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) );
3. Retrieve the concatenated string of affected row IDs: SELECT @uids;

This will produce a comma-separated list of the IDs of all rows affected by the update statement.

The above is the detailed content of How Can I Get the ID of the Last Updated Row in MySQL Using PHP?. For more information, please follow other related articles on the PHP Chinese website!