How to Properly Increment Values in MySQL UPDATE Queries?

Incrementing Values in MySQL Update Queries

When attempting to increment a value in a MySQL update query, you may encounter issues if you use the approach presented in the code snippet provided. The code:

mysql_query("
    UPDATE member_profile 
    SET points= ' ".$points." ' + 1 
    WHERE user_id = '".$userid."'
");

does not correctly increment the existing points value. Instead of adding one to the current value, it simply sets it to 1.

Solution

To correctly increment the points value, you need to use MySQL's built-in increment operator. The modified code below increments the points column by 1:

$sql = "UPDATE member_profile SET points = points + 1 WHERE user_id = ?";
$db->prepare($sql)->execute([$userid]);

This code will work with both the PDO (PHP Data Objects) and mysqli (MySQL Improved Extension) database libraries in modern PHP versions.

The above is the detailed content of How to Properly Increment Values in MySQL UPDATE Queries?. For more information, please follow other related articles on the PHP Chinese website!