Get ID of previous "update" query

[EchoFool]

Similiar to mysql_insert_id  is there a way to get the ID of the row that updated from the previous query?

[JustLikeIcarus]

Not without some trickery... But if you are updating a record then you have access to its id at that time so you can set it to a variable and then reference it when you need it.  Really it's very hard to help out with this without seeing any of your code.

[zeodragonzord]

I'm not sure if there's a natural MySQL way to do it, but you can instead create a last_updated DATETIME column field that is changed each time you update a row in that table.  Then do a SELECT statement on it, sorting by that column DESC LIMIT 1 to get the last updated row.

[Zane]

Just run another query directly afterwards.. SELECTing the most recent addition. 

 

/*bllahal blah blha blah 
  UPDATE query.. with PHP           *////////
$lastID = mysql_result(   mysql_query("SELECT id FROM table ORDER BY id DESC LIMIT 1"),  0   );
echo  "The last updated id is " . $lastID;

[PFMaBiSmAd]

@zeodragonzord and @Zanus

 

Under modern interrupt driven multitasking operating systems (i.e. current ones  ) and also in situations where one or more servers are making use of a separate database server, other quires due to concurrent visitors can occur at any time and you cannot guarantee that executing a separate query to get the highest value will result in the value that was just operated on by the current invocation of a php script. This is why things like the mysql_insert_id() are maintained per connection so that they are accurate for the current visitor regardless of what other queries are being executed.

 

JustLikeIcarus already suggest the best solution for a deliberate UPDATE query. You just updated a specific record and you know which one because you know the WHERE condition that matched that record. You can also put a SET id=LAST_INSERT_ID(id) in the UPDATE query to cause the mysql_insert_id() to be set with the value of the id column that was just updated.

 

If the UPDATE query operated on more than one row, things like mysql_insert_id() only return the first row affected and you would need to individually update each affected row or SET a column to some unique flag value (time values are not unique) that was generated by the php script in order to be able to identify all the affected rows.

[zeodragonzord]

Knowing what you will update before you the update is a good idea since you can assure that the rows in focus are already in the database.  If you do a SELECT beforehand, you can verify that rows exist and keep a copy of the primary keys.  Run your update statement and you'll still have your primary key list of rows you've updated.

 

If you do a straight UPDATE, you can't guarantee that you've affected any rows, if criteria doesn't match any rows.

[PFMaBiSmAd]

You would need to lock the table to make that scheme work.