How do you retrieve the updated value after a MySQL update query?

Obtaining Updated Value from MySQL Update

In a MySQL update query, it's common to receive the number of rows affected instead of the updated value. However, there is a method to retrieve the updated value directly from the query.

Solution: Using a Stored Procedure

A stored procedure can be created to perform the update and then retrieve the updated value using an output parameter. The following stored procedure increments the score column in the item table for a given id and returns the new value:

<code class="sql">CREATE PROCEDURE increment_score(
   IN id_in INT
)
BEGIN
    UPDATE item SET score = score + 1 WHERE id = id_in;
    SELECT score AS new_score FROM item WHERE id = id_in;
END</code>

PHP Implementation

In PHP, the stored procedure can be called and the updated value can be obtained as follows:

<code class="php">$sql = "CALL increment_score($id)";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
echo $row['new_score'];</code>

This solution allows you to update the value and retrieve the updated value in a single query, reducing the number of queries required to accomplish the task.

The above is the detailed content of How do you retrieve the updated value after a MySQL update query?. For more information, please follow other related articles on the PHP Chinese website!