Increment value in MySQL update query
PhpMysqlSqlSql UpdatePhp Problem Overview
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?
Php Solutions
Solution 1 - Php
You could also just do this:
mysql_query("
UPDATE member_profile
SET points = points + 1
WHERE user_id = '".$userid."'
");
Solution 2 - Php
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. ;)
Solution 3 - Php
"UPDATE member_profile SET points = points + 1 WHERE user_id = '".$userid."'"
Solution 4 - Php
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.
Solution 5 - Php
Also, to "increment" string, when update, use CONCAT
update dbo.test set foo=CONCAT(foo, 'bar') where 1=1
Solution 6 - Php
Who needs to update string and numbers
SET @a = 0; UPDATE obj_disposition SET CODE = CONCAT('CD_', @a:=@a+1);
Solution 7 - Php
The accepted answer is good but not working with null values try this
mysql_query("
UPDATE member_profile
SET points = IFNULL(points, 0) + 1
WHERE user_id = '".$userid."'
");
Solution 8 - Php
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
));
Solution 9 - Php
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...
Solution 10 - Php
Why don't you let PHP do the job?
"UPDATE member_profile SET points= ' ". ($points+1) ." ' WHERE user_id = '".$userid."'"