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

You are specifying dataType: "json", but the actual format that you are using is application/x-www-form-urlencoded (name=value, separated with &). That alone might be the cause of your php script's failure. Remove dataType property and check if that helps. — Kamil Latosinski
– Kamil Latosinski, Commented Mar 16, 2017 at 9:08
@KamilLatosinski commented the //datatype line but still same — Gawai
– Gawai, Commented Mar 16, 2017 at 9:19
Make var_dump($_POST) and inspect the data format. You are creating data like: ['Text_1' => 'value'], but your php script expects ['Text' => ['value', 'value']]. Btw., you have a huge SQL Injection hole. Google it out and replace mysql_ function with PDO and bindValue. — Kamil Latosinski
– Kamil Latosinski, Commented Mar 16, 2017 at 9:25

Kamil Latosinski · Accepted Answer · 2017-03-16 09:55:45Z

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.

Thanks for the SQL Injection Part. The given code was a good example how not to do Communication between Website and Database
@Kamil Latosinski thanks for the code and information of sql injection, This is still under testing phase will take care of security measure as per your advise. I dont find any reference to '$i' . $Text = $_POST['Text'][$i];
Well, there isn't one. The PHP code sample just demonstrates how to use PDO in your case. You have to adjust the code to your needs.

Nikit Barochiya · Accepted Answer · 2017-03-16 09:25:08Z

0

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

answered Mar 16, 2017 at 9:25

Nikit Barochiya

1,18512 silver badges15 bronze badges

3 Comments

Liam Hammett Over a year ago

Any explanations of what you did or what the problem was? A code block alone isn't so helpful.

Gawai Over a year ago

@Nikit Barochiya data: $(this).serialize(), does not help

Nikit Barochiya Over a year ago

work is properly And serialize() get form all data. check js file work is properly

Collectives™ on Stack Overflow

PHP and jQuery insert into MySQL

2 Answers 2

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related