How to get column after executing UPDATE command in php and mysql

Question

I am new to php and sql. I have a table with three columns. One 256-bit hash number and two inputs. I want to find a string that matches my hash, and then restore one int and increase it. So, I thought that I would kill two birds with one stone, using the UPDATE command first.

$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 make sure it was successful. If the affected rows return 1, I know that it is present in the database and that it was automatically added. So far, so good.

Now I want to get another column in this row. Do I have to make a choice to get the same row again or some row retrieved from the result object returned by my UPDATE command? I can not find a good example for this scenario.

This is the main material, but all this is new to me.

Answer 1

You need to run a separate SELECT statement.

In PostgreSQL 9.0, you can use UPDATE ... RETURNINGto update a row and return some values ​​in a single query. There is no equivalent function in MySQL.

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

Answer 2

, mysql, select

Answer 3

If you are worried about wasting time on two queries, you can use UPDATE LOW_PRIORITY in combination with PHP mysql_unbuffered_query ()

You cannot get the id of a changed row, because an update can change either one or several rows.