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?
Simply increment the value that already exists in the database
$amount = 1;
$sql = "UPDATE member_profile SET points = points + ? WHERE user_id = ?";
$db->prepare($sql)->execute([$amount, $userid]);
This code is 100% secure and would work for both PDO and mysqli in all supported PHP versions.
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)."'");
"UPDATE member_profile SET points = points + 1 WHERE user_id = '".intval($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.
for 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
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."'
");
You should use PDO to prevent SQL injection risk.
You can connect to DB like this :
$pdo_options[PDO::ATTR_ERRMODE] = PDO::ERRMODE_EXCEPTION;
$bdd = new PDO('mysql:host=xxxx;dbname=xxxx;charset=utf8mb4', 'user', 'password', $pdo_options);
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
));
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."'"
