retrieve last updated column in mysql

Question

Hi, I have a MySQL query that goes as follows (using Zend_Db):

 $sql = $handle->quoteInto("UPDATE board SET rank=rank+1 WHERE post_id=?", $postid);
 $handle->query($sql);

(Rank isn't an auto-incrementing PK). I would like to now retrieve the value of rank without preforming another query. I've tried $handle->lastInsertId(); but it doesn't seem to work , since I didn't use MySQL's natural auto-incrementing method (I can't - rank is the rank of a post. I either ++ or -- it.)

Any way to do this with preforming another query? A function that will return the last changed value?

Answer 1

I don't believe this is possible - you'll just have to do a SELECT.

Answer 2

You can use the LAST_INSERT_ID function that MySQL provides to set a value and then make it available via the mysql_insert_id() C function that the $handle->lastInsertId(); relies on.

The following is an updated version of your code snippet with the LAST_INSERT_ID changes made to it:

 $sql = $handle->quoteInto("UPDATE board SET rank=LAST_INSERT_ID(rank+1) WHERE post_id=?", $postid);
 $handle->query($sql);

Let me know if you have any questions.

HTH,

-Dipin