Update data in mysql for every input field using ajax

Question

I have mysql database. On my page, when I click on a button I retrive data from database. All data are displayed on page. I get one row with id, one row with name and one row with number (in input field). So one example is that I get:

51

Table

200

---

52

Head

320

---

53

Glass

456

---

Because all value number (3rd row) are in input fields they can be changed. I created submit button so I can submit changed values. Here is my js code:

$(document).ready(function () {
        $("#submit2").click(function () {
        event.preventDefault();
        $('form#list_prices_form input[type=number]').each(function () {
        default = $("#default").val();
        var value = $(this).val();
        var id = $(this).attr("name");
        console.log(id);
        console.log(value);
        });

        $.ajax({
                    type: "POST",
                    url: "save.php",
                    data: {default: default, value: value, id: id},
                    success: function (response) {
                        default = $("#default").val();
                    $.ajax({
                        type: "POST",
                        url: "retrive.php",
                        data: {default: default},
                        success: function (response) {
                            $('#list').html(response);
                        }  
                    });
                    }
                });
                return false;
        });
        });

So in console I get all necessary fields. They show correct id and value for each field. But it shows error in console:Uncaught ReferenceError: value is not defined

And php code:

        $default = $_POST['default'];
        $value = $_REQUEST['value'];
        $id = $_REQUEST['id'];

        $result = $conn->query("SELECT * FROM tbl_name WHERE default = '$default'";
            $query = "UPDATE tbl_name SET (value_row) VALUES(?) WHERE id='$id'";
            $statement = $conn->prepare($query);
            $statement->bind_param('i', $value);
            if ($statement->execute()) {
                echo 'Changed';
               //print 'Success! ID of last inserted record is : ' . $statement->insert_id . '<br />';
            } else {
                die('Error : (' . $conn->errno . ') ' . $conn->error);
            }
            $statement->close();

Answer 1

The first issue seems to be your <input> doesn't have a name. Therefore a browser won't submit that element. This is as per the W3 spec for successful controls.

Instead of setting the id try setting the name attribute as well.

For example

echo "$x: <input class='value' id='$id' name='$ID' value='$y' />";

Note you're much better submitting these variables via POST rather than GET. Doing so means that the data in the URI is much less likely to be logged by any middleware. Thus your JQuery AJAX would look like:

$.ajax(
  type: 'post',
  url: "update.php",
  data: {id: id, value: value},
  success: function(responseData) {
    console.log("Result" + responseData);
  }
});

Now, since you're posting JSON and not form-encoded data, you'll need to read in JSON from the PHP script:

$json = file_get_contents('php://input');
$obj = json_decode($json);
var_dump($obj); // show what data is in there for your debugging.

It will look a little like:

$obj->id; // the id POST'ed
$obj->value; // the value POST'ed

It may also be beneficial to reduce the number of POST requests you're making by aggregating the fields to send before POSTing.

e.g.

var values = [];
$(".value").each(function () {
  var getId = $('.value').prop('id');
  var id = $(getId).val();
  var getValue = $('.value').prop('value');
  var value = $(getValue).val();
  values.push({id: id, value: value});
});

$.ajax(
  type: 'get',
  url: "update.php",
  data: values,
  success: function(responseData) {
    console.log("Result" + responseData);
  }
});

If you do so then your PHP code will then need to look something like this:

$postData = file_get_contents('php://input');
$postJson = json_decode($postData);
$results = [];
foreach($postJson as $row) {
  // $row->id  and $row->value
  $sql = "UPDATE table SET y='{$row->value}' WHERE ID = '{$row->id}'";

  if ($conn->query($sql) === TRUE){
    $results[$row->id] = 'updated';
  } else {
    $results[$row->id] = 'error';
  }
}
$conn->close();
echo json_encode($results); // Return a list of ID => success/error

Note: All of the above is entirely untested given your database etc. etc

More importantly this code does not address any injection vulnerabilities. You should escape AND validate your input data before running ANY database operations

Answer 2

Your jQuery should be like this:

$(document).ready(function(){
    $('#submit').click(function(e) {
        e.preventDefault();
        $(".value").each(function(){
            var id = $(this).attr('id');
            var value = $(this).attr('value');
            $.ajax({
                type: 'get',
                url: "update.php",
                data: {id: id, value: value},
                success: function(){

                }
            });
        });
    });
});