[php] 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 plus one to it.. so example if he had like 5 points, it should be 5+1 = 6.. but it doesnt, it just changes to 1

What have i done wrong? thank you

You can do this without having to query the actual amount of points, so it will save you some time and resources during the script execution.

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

Else, what you were doing wrong is that you passed the old amount of points as a string (points='5'+1), and you can't add a number to a string. ;)

Remove the ' around the point:

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

You are "casting" an integer value to string in your original query...

Why don't you let PHP do the job?

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

Who needs to update string and numbers SET @a = 0; UPDATE obj_disposition SET CODE = CONCAT('CD_', @a:=@a+1);

Also, to "increment" string, when update, use CONCAT

update dbo.test set foo=CONCAT(foo, 'bar') where 1=1

"UPDATE member_profile SET points = points + 1 WHERE user_id = '".$userid."'"

Hope I'm not going offtopic on my first post, but I'd like to expand a little on the casting of integer to string as some respondents appear to have it wrong.

Because the expression in this query uses an arithmetic operator (the plus symbol +), MySQL will convert any strings in the expression to numbers.

To demonstrate, the following will produce the result 6:

SELECT ' 05.05 '+'.95';

String concatenation in MySQL requires the CONCAT() function so there is no ambiguity here and MySQL converts the strings to floats and adds them together.

I actually think the reason the initial query wasn't working is most likely because the $points variable was not in fact set to the user's current points. It was either set to zero, or was unset: MySQL will cast an empty string to zero. For illustration, the following will return 0:

SELECT ABS('');

Like I said, I hope I'm not being too off-topic. I agree that Daan and Tomas have the best solutions for this particular problem.

You should use PDO to prevent SQL injection risk.

You can connect to DB like this :

try {
    $pdo_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
    $bdd = new PDO('mysql:host=xxxx;dbname=xxxx', 'user', 'password', $pdo_options);
    $bdd->query('SET NAMES "utf8"');
} catch (PDOException $e) {
    exit('Error');
}

No need to query DB to get the number of points. You can increment directly in the update query (points = points + 1).

(note : Also, it’s not a good idea to increment the value with PHP because you need to select first the data and the value can changed if other users are updated it.)

$req = $bdd->prepare('UPDATE member_profile SET 
            points = points + 1
            WHERE user_id = :user_id');

$req->execute(array(
    'user_id' => $userid
));