PHP and jQuery insert into MySQL

Question

I am trying to insert data into mySQL using jQuery. The code does not return any error and no any result as well. Please help me to sort this out.

$(document).ready(function() {
  $("#submit").click(function() {
    var data;
    var eid = 101;
    data = "eid=" + eid;
    for (i = 0; i <= 10; i++) {
      data += "&Text_" + (i + 1) + "=" + $("#Text_" + (i + 1)).val();
      data += "&Amount_" + (i + 1) + "=" + $("#Amount_" + (i + 1)).val();
    }
    $.ajax({
      type: "POST",
      url: "process.php",
      cache: false,
      data: data,
      dataType: "json",
      success: function(response) {
        if (!response.error) {
          $("#msg").addClass('alert-success').html(response.msg);
        } else {
          $("#msg").addClass('alert-danger').html(response.msg);
        }
      }
    });
  });
});

<tr>
  <td><input type="text" value="Allowance1 " name="Text[]" id="Text_1" /></td>
  <td><input type="text" value="1001.00" name="Amount[]" id="Amount_1" /></td>
</tr>
<tr>
  <td><input type="text" value="Allowance 2" name="Text[]" id="Text_2" /></td>
  <td><input type="text" value="1002.00" name="Amount[]" id="Amount_2" /></td>
</tr>
<tr>
  <td><input type="text" value="Allowance 3" name="Text[]" id="Text_3" /></td>
  <td><input type="text" value="1003.00" name="Amount[]" id="Amount_3" /></td>
</tr>

I am adding the process.php snippet also in order to know where is the error.

process.php

$eid=$_POST['eid'];
$length = sizeof($_POST["Text"]);   
$i=1;
while ($i<=$length){
    if(!empty($_POST['Text'][$i])) {
        $Text = $_POST['Text'][$i];
        $Amount =  $_POST['Amount'][$i]; 

        $msg = array('status' => !$error, 'msg' => 'Failed! updation-1');           
        if(!$error) {
            $sql = "UPDATE TblCustom SET Text='" . $Text . "', Amount='" . $Amount ."' WHERE ID='$eid'";                
            $status = mysqli_query($conn, $sql) or die("database error:". mysqli_error($conn));
            $msg = array('error' => $error, 'msg' => 'Success! updation : '. $sql );
        }
        else {
            $msg = array('error' => $error, 'msg' => 'Failed! updation-2 ');
        }

    }
    echo json_encode($msg);

}

Thanks

Answer 1

You have 3 problems.

Problem number one and two are related. Firstly, you are specifying dataType: 'json', but you are passing your data in application/x-www-form-urlencoded format. Secondly, your php script expects data to be in the following format:

$_POST = [
    'Text' => ['text_1', 'text_2', 'text_3'],
    'Amount' => ['amount_1', 'amount_2', 'amount_3']
];

While your data looks something like this:

$_POST = [
    'Text_1' => 'text_1',
    'Text_2' => 'text_2'
    // and so on
];

The single fix to this problem is as follows:

$(document).ready(function() {
  $("#submit").click(function() {

    const data = {
      // we are grabbing all inputs with name=Text[] 
      // and mapping them to array containing their values.
      // The `...` is a spread operator introduced 
      // with the new js standard (ES6), 
      // that converts jQuery object to regular javascript
      // array of inputs.
      // you can do all of this with a for loop, but the map way
      // is prefered
      Text: [...$('input[name="Text[]"]')].map(input => input.value),
      Amount: [...$('input[name="Amount[]"]')].map(input => input.value)
    }

    $.ajax({
      type: "POST",
      url: "process.php",
      cache: false,
      data: data,
      dataType: "json",
      success: function(response) {
        if (!response.error) {
          $("#msg").addClass('alert-success').html(response.msg);
        } else {
          $("#msg").addClass('alert-danger').html(response.msg);
        }
      }
    });
  });
});

The third problem is that have created an SQL Injection vulnerability. That means some bad guy can inject and SQL statement into Text variable, which then you are putting directly into your sql update, thus he can do whatever he wants (for example drop all database).

More on SQL Injection

The solution is simple: use PDO and bindValue method.

$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

try {
    $conn = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    // 500 means internal server error
    // that's handy information for the client-side
    http_send_status(500);
    echo json_encode([
        'error' => [
            'message' => 'Unable to connect to database'
        ]
    ]);
    exit;
}

$eid = $_POST['eid'];
$Text = $_POST['Text'][$i];
$Amount = $_POST['Amount'][$i];

$sql = "UPDATE TblCustom SET Text = :text, Amount = :amount WHERE ID = :id";
$stmt = $conn->prepare($sql);

$stmt->bindValue(':text', $Text);
$stmt->bindValue(':amount', $Amount);
$stmt->bindValue(':id', $eid);

if (!$stmt->execute()) {
    // 400 means something went wrong when updating
    // also a handy information for the client-side
    http_send_status(400);
    echo json_encode([
        'error' => [
            'message' => 'Unable to update'
        ]
    ]);
    exit;
}

// 204 measn everything went okay, and we don't return anything
http_send_status(204);
exit;

Hint: if you are sending correct status codes the jQuery lets you handle errors like this:

$.ajax({
    // ...
    success: function(response) {
        // this code will be executed
        // only when status code == 2xx
    },
    error: function(response) {
        // this code will be executed
        // only when status code == 4xx | 5xx (if I remember correctly)
    },
    always: function(response) {
        // this code will be executed no matter what
        // as the name implies
    },
});

So there is no need for additional if statements.

Answer 2

index.php
<!DOCTYPE html>
<html>
<head>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
</head>
<body>
<form id="form_signup" name="form_signup">
   <tr>
  <td><input type="text" value="Allowance1 " name="Text[]" id="Text_1" /></td>
  <td><input type="text" value="1001.00" name="SAmount[]" id="Amount_1" /></td>
</tr>
<tr>
  <td><input type="text" value="Allowance 2" name="Text[]" id="Text_2" /></td>
  <td><input type="text" value="1002.00" name="SAmount[]" id="Amount_2" /></td>
</tr>
<tr>
  <td><input type="text" value="Allowance 3" name="Text[]" id="Text_3" /></td>
  <td><input type="text" value="1003.00" name="SAmount[]" id="Amount_3" /></td>
</tr>
 <input type="submit" name="signup" value="Sign Up!"/>
</form>
<script>
$(document).ready(function() {
  $("#form_signup").click(function() {
       $.ajax({
      type: "POST",
      url: "process.php",
      cache: false,
        data: $(this).serialize(),
      success: function(response) {
          alert(response);
        if (!response.error) {
          $("#msg").addClass('alert-success').html(response.msg);
        } else {
          $("#msg").addClass('alert-danger').html(response.msg);
        }
      }
    });
  });
});
</script>

</body>
</html>

process.php
<?php 
print_r($_POST);
 ?>