Increment value in MySQL update query

Question

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?

I had a similar issue then realized the default type of the field was 'NULL', changed it to 0 and all was well. — Azmeer, Feb 01 '17 at 08:58

score 407 · Accepted Answer · edited Jun 04 '22 at 04:59

407

Simply increment the value that already exists in the database

$sql = "UPDATE member_profile SET points = points + 1 WHERE user_id = ?";
$db->prepare($sql)->execute([$userid]);

This code would work for both PDO and mysqli in the modern PHP versions

edited Jun 04 '22 at 04:59

Your Common Sense

156,878
40
214
345

answered Feb 13 '10 at 21:40

Tomas Markauskas

11,496
2
33
35

61

@Steve your comment might sound clever for someone who knows what PDO is, but for me who's just diving into PHP/MySQL, it doesn't really shine a lot of light into the matter. Does PDO make that code smaller or more elegant? If so, please edit the answer or post one of your own where you show how it's better with PDO. Thanks. – Camilo Martin Jan 21 '13 at 19:06
5

@CamiloMartin I was curious too. I found this helpful http://net.tutsplus.com/tutorials/php/pdo-vs-mysqli-which-should-you-use/ – PJ Brunet Jan 26 '13 at 06:41
11

@CamiloMartin the [php.net manuals](http://php.net/manual/en/function.mysql-query.php) page for mysql_query has the following note: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the [MySQLi](http://www.php.net/manual/en/book.mysqli.php) or [PDO_MySQL](http://www.php.net/manual/en/ref.pdo-mysql.php) extension should be used. See also [MySQL: choosing an API](http://www.php.net/manual/en/mysqlinfo.api.choosing.php) guide and [related FAQ](http://www.php.net/manual/en/faq.databases.php#faq.databases.mysql.deprecated) for more information. – aland Mar 24 '13 at 21:55
12

Concatenating user data as demonstrated into an SQL query is a major SQL injection risk. – le3th4x0rbot Nov 06 '15 at 01:20
Is there a solution to set a limit / max value using a similar approach? Would `MIN(points + 1, YOUR_LIMIT_VALUE_HERE)` be it? – chamberlainpi Jun 29 '16 at 01:38
1

@bigp: I tried `UPDATE xyz SET points = MIN(points + 1, YOUR_LIMIT_VALUE_HERE)` and it didn't work. What did work was: `UPDATE xyz SET points = points + 1 WHERE points < YOUR_LIMIT_VALUE_HERE`. – Jealie Jan 20 '17 at 22:39
2

You could do with using a library or having mysql_real_escape_string() around the userid, to save from injection attacks or coding mistakes – tristanbailey Feb 06 '17 at 08:26
This answer was provided a long time ago, I would advice not to directly copy and pasting this code to your PHP server. In modern days, people usually use **PDO** instead of just mysql query, this mysql query is also vulnerable to **SQL Injection** so you need to use **binding** in order to make the request safe. – Ng Sek Long Jan 10 '22 at 03:51
PHP Warning: mysqli_stmt::execute() expects exactly 0 parameters (this prepared statement no longer works in 2022). – tim92109 Dec 08 '22 at 18:55

score 27 · Answer 2 · edited Jun 04 '22 at 05:01

27

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

edited Jun 04 '22 at 05:01

Your Common Sense

156,878
40
214
345

answered Feb 13 '10 at 21:40

Daan

1,879
17
18

score 14 · Answer 3 · edited Jun 04 '22 at 05:02

14

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

edited Jun 04 '22 at 05:02

Your Common Sense

156,878
40
214
345

answered Feb 13 '10 at 21:39

Mark Byers

811,555
193
1,581
1,452

1

what if I used variable instead of value=1? should I do it this way "points = points + $variable" ? or "points = points + '$variable' " – Ivo San May 20 '13 at 06:12

score 11 · Answer 4 · answered Feb 13 '10 at 22:40

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.

+1 compton very good points, you are right about the cast working, be there quotes or not. Welcome to SO! — Pekka, Feb 13 '10 at 22:52

Rodolfo Souza · Answer 5 · 2022-06-03T21:48:48.667

8

Who needs to update string and numbers

SET @a = 0;
UPDATE obj_disposition SET CODE = CONCAT('CD_', @a:=@a+1);

edited Jun 03 '22 at 21:48

answered Nov 08 '16 at 02:19

Rodolfo Souza

300
4
5

score 7 · Answer 6 · edited Oct 27 '12 at 18:25

7

Also, to "increment" string, when update, use CONCAT

update dbo.test set foo=CONCAT(foo, 'bar') where 1=1

edited Oct 27 '12 at 18:25

Emil

7,220
17
76
135

answered Apr 02 '12 at 06:36

bushkonst

79
1
1

score 6 · Answer 7 · answered Sep 18 '21 at 05:27

6

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

More info on IFNULL

answered Sep 18 '21 at 05:27

Syed Waqas Bukhary

5,130
5
47
59

score 4 · Answer 8 · edited Jun 04 '22 at 04:53

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

score 0 · Answer 9 · answered Feb 13 '10 at 21:40

0

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

answered Feb 13 '10 at 21:40

Amirshk

8,170
2
35
64

score -4 · Answer 10 · answered Feb 13 '10 at 21:40

-4

Why don't you let PHP do the job?

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

answered Feb 13 '10 at 21:40

Petr Peller

8,581
10
49
66

8

Good point, but be carefull in a concurrent environment as the DB value might have changed in the meantime. – Vincent Nikkelen Dec 30 '13 at 21:00
1

Thanks @VincentNikkelen, you hit the nail on the head. Concurrency! – Jimmy Ilenloa Nov 12 '16 at 02:10
2

If you use this method, you should first SELECT the data, which means an additional access to the row. This is not the way to go if you just need to update the value. – Andres SK Jun 05 '17 at 06:53

Increment value in MySQL update query

10 Answers10

Linked

Related