increment value in database using PHP and AJAX

Question

I am trying to make value +1 in database every time use use button. my function in HTML:

function onClick(arg){
             alert("thx fo click");
              $.ajax({
                  type: 'POST',
                  url: 'data.php',
                  data: {
                     'arg': arg,
                  },
                  success: function(response) {
                  }
              });
         }

arg means value of button and it is a ID for a row in the database and PHP:

<?php
$link = mysql_connect($servername, $username, $password);

$id = $_POST['arg'];

$sql = "UPDATE Buttons(SUMA) SET SUMA = SUMA + 1 WHERE ID = '$id'";
$conn->query($sql);

mysql_close($link);
?>

And that make nothing. How can i fix it?

Answer 1

What is 1 + NULL it's still NULL.

IF you didn't default the column to '0' , then you can't increment it.

This can best be shown in a simple DB fiddle

Starting Null:

CREATE TABLE t(
  id INT(10),
   v INT(10) 
);

INSERT INTO t (id)VALUES(1);

SELECT * FROM t;

UPDATE t SET v = v+1 WHERE id=1;

SELECT * FROM t;

In Both selects you will get a value of null for v as seen below in the fiddle:

https://www.db-fiddle.com/f/m1vgKpov1oiRJEfZEgmk1j/0

In simple terms, you cannot add 1 (or any number) to a NULL value. Well you can but it's still null. null + 1 = null

Starting 0:

CREATE TABLE t(
  id INT(10),
   v INT(10) default 0 
);

INSERT INTO t (id)VALUES(1);

SELECT * FROM t;

UPDATE t SET v = v+1 WHERE id=1;

SELECT * FROM t;

In this case the first Select return 0 for v and the second returns 1 for v. As seen in the modified fiddle.

https://www.db-fiddle.com/f/m1vgKpov1oiRJEfZEgmk1j/1

Also (SQLInjection)

As I said in the comments:

What if arg = "' OR 1 --"

Or in other words don't inject user variables (or any clientside data) into your SQL or it winds up looking like this:

 UPDATE `Buttons(SUMA)` SET SUMA = SUMA + 1 WHERE ID = '' OR 1 --'"

Which will increment every row in your DB that is not null. Basically the ' closes the other quote, then OR 1 is always true(for every row). Unfortinalty I cant show the -- comment part in the fiddle (it comments out the second select), but here is the end result.

https://www.db-fiddle.com/f/m1vgKpov1oiRJEfZEgmk1j/3

This is why we use prepared statements. This example is somewhat trivial but I have seen login code on here I was able to bypass simple by putting "' OR 1 LIMIT 1 --'" in, and with offset you could iterate though each user. They were looking for 1 row to be returned on the match of a username and password.

A few other things:

• Table name Buttons(SUMA) is that really the name, as it will only work if escaped with the backtic. As I did in the above SQLInjection example.
• $link = mysql_connect($servername, $username, $password); are these defined, the open tag is right above them. I generally chock that up to simplified example code. But it's worth asking. Obviously you can't connect to the DB if those are undefined.

Cheers!

Answer 2

You have several syntax errors here. First and foremost though, check out mysqli_ (or PDO) and start using that instead of mysql_ For why to use mysqli_ - MySQL vs MySQLi when using PHP Comparing mysqli_ and PDO - https://websitebeaver.com/php-pdo-vs-mysqli

With that out of the way....

You're defining your database connection without selecting a schema, but don't reference your schema in the query, meaning mysql won't know what to update. Either reference your schema in the connection or in each query. Also check on your table name, is it really Buttons(SUMA)?

You defined your database connection as $link, but are using $conn to attempt the query. Probably a 'typo' from copy and paste. Be careful of this...

As Artistic Phoenix mentioned, you have to make sure you're column cannot be set to NULL, and starts at 0 to begin. While you're at it and we're going through, make sure your datatype is set to int for the increment count.

After making those changes if you don't have success, I'd try running your query outside your code in a DB manager to ensure that portion is having the intended affect on your data, before looking at the errors in your code.

I'm guessing the arugment is passing correctly to your script, but to confirm, you can always echo it on the backend, and to be doubly sure alert() it in JS before it's passed through.

Take the time to go through that reading, update your script to use mysqli_ or PDO, and if you're still having troubles, I'm more than happy to jump back in here and help you further.