Ajax datatable Update with PHP MySql

Question

I want to update data using Ajax and below is the sample code. The SweetAlert get displaced that it has been updated but it doesn't take effect in the database non thus it gives an error.

Ajax Code

This is the Ajax Script for the form submission when the submit button is clicked.

<script>
               $('#submit').on('click', function(event){
                event.preventDefault();
                var firstname = $('#firstname').val();
                var othername = $('#othername').val();
                var gender = $('#gender').val();
                var id_type = $('#id_type').val();
                var id_number = $('#id_number').val();
                var issue_date = $('#issue_date').val();
                var business_place = $('#business_place').val();
                var food_type = $('#food_type').val();
                var screened = $('#screened').val();
                var sub_metro = $('#sub_metro').val();
                var telephone = $('#telephone').val();
                var get_date = $('#get_date').val();
                var chit_number = $('#chit_number').val();
                var remarks = $('#remarks').val();
                var user_id = $('#user_id').val();
                var vendor_id = $('#vendor_id').val();

if (firstname!="" &&othername!="" && food_type!=""){
            $.ajax({
                url: "action/vendor_update.php",
                type: "POST",
                data: {
                    firstname:firstname, othername:othername, gender:gender, 
id_type:id_type, id_number:id_number, issue_date:issue_date,    
business_place:business_place, food_type:food_type, screened:screened, 
sub_metro:sub_metro, telephone:telephone, get_date:get_date,
                    chit_number:chit_number, remarks:remarks, user_id:user_id, vendor_id:vendor_id,
                },
                cache: false,
                success: function(data){
                    if(data.statusCode=200){
                        $('#dataForm').find('input:text').val('');
                            alert('Updated');
                            })      
                     }
                    else if (data.statusCode=201)
                     {
                            alert('Cannot Update');
                     }
                }
            });
        
        }else{
            alert('All fields are mandatory');
           }
    });

PHP Code ---> action/vendor_update.php

This code is for the php server side for data insertion into the database.

<?
        session_start();
        // include('../includes/session.php');
        include('./includes/connection.php');
        
        $query = $dbh->$prepare("UPDATE vendors SET Firstname=:firstname, Othername=:othername, Telephone=:telephone, Gender=:gender, IdType=:id_type, IdNumber=:id_number, IdDate=:issue_date, BusinessPlace=:business_place, FoodType=:food_type, Notes=:remarks, ScreenStatus=:screened, ChitNumber=:chit_number, UpdatedBy=:user_id WHERE VendorId=:vendor_id");

        $query->execute([
                $firstname = $_POST['firstname'];
                $othername = $_POST['othername'];
                $telephone = $_POST['telephone'];
                $gender = $_POST['gender'];
                $id_type = $_POST['id_type'];
                $id_number = $_POST['id_number'];
                $issue_date = $_POST['issue_date'];
                $business_place = $_POST['business_place'];
                $food_type = $_POST['food_type'];
                $remarks = $_POST['remarks'];
                $screened = $_POST['screened'];
                $chit_number = $_POST['chit_number'];
                $user_id =  $_POST['user_id'];
                $vendor_id = $_POST['vendor_id'];
          ]);
                
        // $query->execute($_POST);

if ($query->execute()){
   echo json_encode(array("statusCode"=>200));
   
} else {
   echo json_encode(array("statusCode"=>500));
}

                

?>

You are completely defeating the purpose of prepared statements by directly injecting the parameters into the query. See the proper way to do it. — El_Vanja
– El_Vanja, Commented Mar 24, 2021 at 12:45
As an aside, you could probably save yourself a lot of repetitive code here by learning about jQuery's serialize function - see api.jquery.com/serialize . Anyway, to answer your actual question we'll need some debugging information. And yes, your code is still vulnerable to SQL injections - simply using prepare() isn't enough, you need to use parameters too. — ADyson
– ADyson, Commented Mar 24, 2021 at 12:47
Shouldn't that be if (data.statusCode==200) { rather than the single = sign? — droopsnoot
– droopsnoot, Commented Mar 24, 2021 at 12:49
WARNING: Whenever possible use prepared statements with placeholder values to avoid injecting arbitrary data in your queries and creating SQL injection bugs. These are quite straightforward to do in mysqli and PDO where any user-supplied data is specified with a ? or :name indicator that’s later populated using bind_param or execute depending on which one you’re using. — tadman
– tadman, Commented Mar 24, 2021 at 12:58
Tip: If you're using PDO or mysqli you can bind placeholders directly to the $_POST values, there's absolutely no need for the intermediate variables. This saves a lot of code, reduces bugs, and makes your query vastly safer. — tadman
– tadman, Commented Mar 24, 2021 at 12:59

tadman · Accepted Answer · 2021-03-24 13:21:38Z

1

Here's the cleaned up PHP code:

<?php

// Tip:
// * Use named placeholders
// * Define the query string inside prepare() so you can't "miss" and run
//   the wrong query by accident
// * Use single quotes so you can't interpolate variables by accident
//   and create ugly SQL injection bugs
$query = $dbh->prepare('UPDATE vendors SET Firstname=:firstname, Othername=:othername, Telephone=:telephone, Gender=:gender, IdType=:id_type...');

// Use ONE of:

// A) if you have slight differences
$query->execute([
  'firstname' => $_POST['firstname'],
  'othername' => $_POST['othername'],
  'telephone' => $_POST['telephone'],
  ...
]);

// OR

// B) if you're confident the placeholders match 100% and no clean-up
//    such as trim() is necessary.
$query->execute($_POST);

if ($query->execute()) {
  echo json_encode(array("statusCode"=>200));
} else {
  echo json_encode(array("statusCode"=>500));
}
?>

Note: It's worth noting that code like this does not need to exist, that any decent ORM will make this trivial to do. It's worth exploring what options you have there as this could be so much easier.

answered Mar 24, 2021 at 13:21

tadman

212k23 gold badges237 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bigboss Over a year ago

I have uptimised the code to the answer you provided. It works. Thanks very much @tadman

Collectives™ on Stack Overflow

Ajax datatable Update with PHP MySql

Ajax Code

PHP Code ---> action/vendor_update.php

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Ajax Code

PHP Code ---> action/vendor_update.php

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related