How to Retrieve Updated Values in MySQL Instead of Affected Rows?

Retrieving Updated Values in MySQL Instead of Affected Rows

In MySQL, when executing an UPDATE statement, it typically returns the number of affected rows. However, there may be scenarios where you require the updated value instead.

To retrieve the updated value directly, you can utilize a stored procedure. Follow the steps below:

1. Create a Stored Procedure:
   DELIMITER $$ -- Change DELIMITER to use ; within 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 ;

2. Execute the Stored Procedure in PHP:
   $result = mysql_query("CALL increment_score($id)");
   $row = mysql_fetch_array($result);
   echo $row['new_score'];

The above is the detailed content of How to Retrieve Updated Values in MySQL Instead of Affected Rows?. For more information, please follow other related articles on the PHP Chinese website!