Get Updated Value in MySQL instead of affected rows

mysql, php

Solution

You can do it with a stored procedure that updates, and then selects the new value into an output parameter. The following returns one column `new_score` with the new value.

DELIMITER $$   -- Change DELIMITER in order to use ; withn the procedure
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
$$            -- Finish CREATE PROCEDURE statement
DELIMITER ;   -- Reset DELIMITER to standard ;

In PHP:

$result = mysql_query("CALL increment_score($id)");
$row = mysql_fetch_array($result);
echo $row['new_score'];

Problem

I've been trying to find an answer to this question, but haven't found any definitive "yes" or "no" in all my research. I'm running a simple MySQL query like this: ``` UPDATE item SET `score`=`score`+1 WHERE `id`=1 ``` Is there a way for that query to return the updated value, instead of the number of rows affected? Just as a reference, I'm doing this in PHP, so the actual code looks like: ``` $sql = "UPDATE item SET `score`=`score`+1 WHERE `id`=1"; $new_value = mysql_query($sql); //Unfortunately this does not return the new value ``` I know I could do a second query and just SELECT the value, but I'm trying to cut down on queries as much as possible. Is there a way?

Original source