How to retrieve a column after doing an UPDATE command in php and mysql

Question

I'm new to php and sql. I have a table with three columns. One 256 bit hash number and two ints. I want to search for the row that matches my hash and then retrieve one int and increment the other. So, I thought I'd kill two birds with one stone by using first the UPDATE command.

$query = sprintf("UPDATE %s SET activationcount = (activationcount+1) WHERE hash='%s'", "activations", mysql_real_escape_string($hashv));
$result = mysql_query($query,$dbhandle);

then I use mysql_affected_rows to see if it was successful. If affected rows returns 1, then I know that it was present in the database and that its been auto-incremented. So far so good.

Now I want to retrieve another column in that row. Do I need to do a select to get the same row again or is the row somehow retrievable from the result object returned by my UPDATE command? I can't find a good example for this scenario.

This is basic stuff but it's all new to me.

Answer 1

You need to run a separate SELECT statement.

In PostgreSQL 9.0 you can use UPDATE ... RETURNING to update a row and return some values in one query. There is not yet any equivalent feature in MySQL.

As a workaround you can put the UPDATE and SELECT in a stored procedure so that you only need to make one call to the database.

Answer 2

no it cant be done in mysql, you will have to issue a separate select

Answer 3

If you're concerned about wasting time on two queries you can use UPDATE LOW_PRIORITY combined with PHP's mysql_unbuffered_query()

You cannot get an ID of the modified row because the update may modify none, or multiple rows.