Coder Perfect

In MySQL update queries, increment the value.


This code for giving out +1 point was created by me, but it does not operate properly.

    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:

    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:


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