phpmysqlsqlsql-update

Increment value in MySQL update query

I have made this code for giving out +1 point, but it doesn't work properly.

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

The $points variable is the user's points right now. I want it to add one to it. So example if he had like 5 points, it should be 5+1 = 6, but it doesn't, it just changes to 1.

What have I done wrong?

Solution

  • Simply increment the value that already exists in the database

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

    This code is 100% secure and would work for both PDO and mysqli in all supported PHP versions.