How do I get the ID of the last updated row in MySQL using PHP?
I've found an answer to this problem :)
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;
EDIT by aefxx
This technique can be further expanded to retrieve the ID of every row affected by an update statement:
SET @uids := null; UPDATE footable SET foo = 'bar' WHERE fooid > 5 AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) ); SELECT @uids;
This will return a string with all the IDs concatenated by a comma.
Hm, I am surprised that among the answers I do not see the easiest solution.
item_id is an integer identity column in
items table and you update rows with the following statement:
UPDATE items SET qwe = 'qwe' WHERE asd = 'asd';
Then, to know the latest affected row right after the statement, you should slightly update the statement into the following:
UPDATE items SET qwe = 'qwe', item_id=LAST_INSERT_ID(item_id) WHERE asd = 'asd'; SELECT LAST_INSERT_ID();
If you need to update only really changed row, you would need to add a conditional update of the item_id through the LAST_INSERT_ID checking if the data is going to change in the row.