How to get id of the last updated row in mysql

Question

Hi friends,

I just want to get id of the last updated row in the mysql using PHP.

Thanks in advance...

Answer 1

If you did the update with php recently, you could simply do:

mysql_query($query, $db);
$lastId = mysql_insert_id($db);

Answer 2

This should do the trick:

mysql_query("SELECT id FROM table_name ORDER BY id DESC LIMIT 0,1");

It simply orders the rows in descending order, sorting by the ID column. So the first result in the query will be the row with the highest ID (assuming you're using an auto_increment ID structure).

Then LIMIT'ing it to one result only returns the ID of that particular row.

If you're not using auto_increment on your IDs, let me know and I can show you a different approach.

Answer 3

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.

Answer 4

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.

Answer 5

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

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

Answer 6

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;

Answer 7

Rly?:)) Weeeeeee :))

Answer 8

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

$updateQuery = mysql_query("UPDATE table_name SET row='value' WHERE id='$id'") or die ('Error'); $lastUpdatedId = mysql_insert_id();

Answer 9

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.