Problem
This code for giving out +1 point was created by me, but it does not operate properly.
mysql_query("
UPDATE member_profile
SET points= ' ".$points." ' + 1
WHERE user_id = '".$userid."'
");
Right now, the $points variable represents the user’s points. I’d like to see it add one to it. So, if he had 5 points, it should be 5+1 = 6, but it doesn’t; instead, it simply switches to 1.
What have I done incorrectly?
Asked by Karem
Solution #1
You may also do it this way:
mysql_query("
UPDATE member_profile
SET points = points + 1
WHERE user_id = '".$userid."'
");
Answered by Tomas Markauskas
Solution #2
This will save you time and resources during the script execution because you won’t have to query the real number of points.
mysql_query("UPDATE `member_profile` SET `points`= `points` + 1 WHERE `user_id` = '".intval($userid)."'");
Otherwise, you were doing something incorrect by passing the old number of points as a string (points=’5’+1), and you can’t add a number to a string.
Answered by Daan
Solution #3
I hope I’m not straying too far from the topic of my initial article, but I’d like to elaborate on the casting of integers to strings, as several replies appear to be confused.
MySQL will convert any strings in the expression to integers because the expression in this query employs an arithmetic operator (the plus symbol +).
As an example, the following will provide the outcome 6:
SELECT ' 05.05 '+'.95';
The CONCAT() function is required for string concatenation in MySQL, so there is no misunderstanding here, as MySQL converts the strings to floats before adding them together.
The $points variable was most likely not set to the user’s current points, which is why the initial query didn’t succeed. MySQL will cast an empty string to zero if it is set to zero or unset. For example, the following will result in a 0:
SELECT ABS('');
As I already stated, I hope I am not straying too far from the topic. Daan and Tomas, in my opinion, have the finest remedies for this problem.
Answered by user272563
Solution #4
"UPDATE member_profile SET points = points + 1 WHERE user_id = '".$userid."'"
Answered by Mark Byers
Solution #5
Also, when updating a string, use CONCAT to “increment” it.
update dbo.test set foo=CONCAT(foo, 'bar') where 1=1
Answered by bushkonst
Post is based on https://stackoverflow.com/questions/2259155/increment-value-in-mysql-update-query