Take the 2-minute tour ×

How do I get the ID of the last updated row in MySQL using PHP?

share|improve this question
if you liked my answer, you might consider upvoting and setting it as accepted answer.. (: –  peirix Sep 8 '09 at 6:54
Please Don't be "ASK AND FORGET" type person, Give the Good Answers some reward by accepting/upvoting.Its my advice to you as a friend, no offense intended –  TheNoble-Coder Feb 5 '13 at 6:22
mysqli_insert_id($link) will return exactly the id of last updated row. I am using this funtion in my projects for the same purpose. You can use it both in synchronous or asynchronous queries. Just insert $lastupdated_row_id = mysqli_insert_id($link) into your code and it will work for you. –  TheNoble-Coder Feb 5 '13 at 6:23
add comment

9 Answers

I've found an answer to this problem :)

SET @update_id := 0;
UPDATE some_table SET row = 'value', id = (SELECT @update_id := id)
WHERE some_other_row = '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 colon.

share|improve this answer
Can't believe this had 0 upvotes, and the mysql_insert_id() comment which is not what the OP was asking at all has 13. Thanks Pomyk, a clever trick! –  Jaka Jančar Jan 11 '11 at 15:47
Since you have a WHERE clause you can simply use the same WHERE clause in your next query SELECT id FROM footable WHERE fooid > 5 –  Salman A Jul 15 '11 at 7:08
Maybe everyone understands this, but if you use mysql_query(); you have to divide this into three different calls, but it will work. –  Lex May 15 '12 at 10:15
@Lex - the improved mysql extension allows multiple statements: php.net/manual/en/mysqli.multi-query.php –  zupa Nov 29 '12 at 10:27
Anyone with a version that could be passed into a PDO Statement? –  Nicolas Manzini Apr 19 '13 at 8:24
show 1 more comment

This is the same method as Salman A's answer, but here's the code you actually need to do it.

First, edit your table so that it will automatically keep track of whenever a row is modified. Remove the last line if you only want to know when a row was initially inserted.

ADD lastmodified TIMESTAMP 

Then, to find out the last updated row, you can use this code.

SELECT id FROM mytable ORDER BY lastmodified DESC LIMIT 1;

This code is all lifted from MySQL vs PostgreSQL: Adding a 'Last Modified Time' Column to a Table and MySQL Manual: Sorting Rows. I just assembled it.

share|improve this answer
This method may fetch the wrong id if the second insert is done just after the first insert query. However If we use 'WHERE' with this query i.e. SELECT id FROM mytable ORDER BY lastmodified DESC LIMIT 1 WHERE (some conditions related to last insert query), then It may prevent fetching the wrong id. –  TheNoble-Coder Jan 30 '13 at 15:14
@TheNoble-Coder This is true. If you need to get the id of a row affected a particular update query, then use Pomyk's technique. This technique here is more useful when used asynchronously, where you just want the absolute last update. –  Chad von Nau Feb 2 '13 at 20:03
add comment

If you are only doing insertions, and want one from the same session, do as per peirix's answer. If you are doing modifications, you will need to modify your database schema to store which entry was most recently updated.

If you want the id from the last modification, which may have been from a different session (i.e. not the one that was just done by the PHP code running at present, but one done in response to a different request), you can add a TIMESTAMP column to your table called last_modified (see http://dev.mysql.com/doc/refman/5.1/en/datetime.html for information), and then when you update, set last_modified=CURRENT_TIME.

Having set this, you can then use a query like: SELECT id FROM table ORDER BY last_modified DESC LIMIT 1; to get the most recently modified row.

share|improve this answer
This suffers from race conditions with concurrent edits. –  Ryaner Jul 27 '12 at 12:02
add comment

mysql insert id function returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query. However, there is no corresponding function to get the "last updated row" in php.

One possibility is to use the mysql TIMESTAMP column with "ON UPDATE CURRENT_TIMESTAMP" set. The column is changed to current time by mysql on any modification for each record, so you can sort by this column to get the recently modified record. You must read the documentation carefully.

share|improve this answer
add comment

Hey, I just needed such a trick - I solved it in a different way, maybe it'll work for you. Note this is not a scalable solution and will be very bad for large data sets.

Split your query into two parts -

first, select the ids of the rows you want to update and store them in a temporary table.

secondly, do the original update with the condition in the update statement changed to where id in temp_table.

And to ensure concurrency, you need to lock the table before this two steps and then release the lock at the end.

Again, this works for me, for a query which ends with limit 1, so I don't even use a temp table, but instead simply a variable to store the result of the first select.

I prefer this method since I know I will always update only one row, and the code is straightforward.

share|improve this answer
add comment

Hm, I am surprized that among the answers I do not see the easiest solution.

Suppose, 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',
WHERE asd = 'asd';

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.

share|improve this answer
add comment
SET @uids := "";
UPDATE myf___ingtable
   SET id = id
   WHERE id < 5
  AND ( SELECT @uids := CONCAT_WS(',', CAST(id AS CHAR CHARACTER SET utf8), @uids) );
SELECT @uids;

I had to CAST the id (dunno why)... or I cannot get the @uids content (it was a blob) Btw many thanks for Pomyk answer!

share|improve this answer
add comment

ID of the last updated row is the same ID that you use in the 'updateQuery' to found & update that row. So, just save(call) that ID on anyway you want.

last_insert_id() depends of the AUTO_INCREMENT, but the last updated ID not.

share|improve this answer
What if the update is not done from an id but rather another set of attributes? –  Sebas Jan 24 at 1:19
add comment

No need for so long Mysql code. In PHP, query should look something like this:

$updateQuery = mysql_query("UPDATE table_name SET row='value' WHERE id='$id'") or die ('Error');
$lastUpdatedId = mysql_insert_id();
share|improve this answer
...that's wrong, that'll get the last -insert- id... –  Kzqai Mar 28 '12 at 19:18
agree, you get last insertion ID, not updated row id –  Macumbaomuerte Mar 18 '13 at 21:19
add comment

Your Answer


