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

Retrieving ID of Last Updated Row in MySQL

When working with databases, it can be essential to identify the latest updated row. In MySQL, there are various ways to accomplish this task using PHP.

Method 1: Manual ID Updation

One approach involves manually updating the ID field within the update query. This can be achieved with the following syntax:

SET @update_id := 0;
UPDATE some_table SET column_name = 'value', id = (SELECT @update_id := id)
WHERE some_other_column = 'blah' LIMIT 1; 
SELECT @update_id;

This query first sets a variable @update_id to 0. It then updates the id field with the current id value, effectively capturing the ID of the last updated row. Finally, the @update_id variable is selected to retrieve the captured ID.

Method 2: Retrieving Multiple Affected Row IDs

An alternative method allows for retrieving the IDs of all rows affected by an update statement. This can be useful in scenarios where multiple rows are updated simultaneously:

SET @uids := null;
UPDATE footable
   SET foo = 'bar'
 WHERE fooid > 5
   AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) );
SELECT @uids;

This query uses a similar technique to the previous example, setting a variable @uids to null initially. The update statement then updates the foo field and appends the fooid of each affected row to the @uids variable. Finally, the @uids variable is selected, providing a comma-separated string containing the IDs of all updated rows.

By implementing these techniques, you can effectively retrieve the ID of the last updated row or the IDs of all affected rows in MySQL using PHP.

The above is the detailed content of How to 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!