Increment value in mysql update query

04/08/2020 04:30:02

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

Verified Answer (310 Votes)

02/14/2010 02:40:52

You could also just do this:

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

Answer #2 (21 Votes)

02/14/2010 02:40:36

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. ;)

21

Answer #3 (10 Votes)

02/14/2010 03:40:26

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.

10
3
Hack Hex uses Stack Exchance API by the Stack Exchange Inc. to scrape questions/answers under Creative Commons license.