Server Side datatables using PHP and Ajax

0
votes

This is my DataTable code:

$('#open').DataTable( {
        select: true,
        "processing": true,
        "sAjaxSource": "booked1.php",
        "serverside": true,
        "columns" :[ {
            "data" : "name"
        }, {
            "data" : "date1"
        }, {
            "data" : "bookingtoken"
        }, {
            "data" : "insurance"
        }]
    } );

This is my ajax call :

$("#submit").on('click', function () {

 $('#loadarModal').modal({backdrop: 'static', keyboard: false});
    var date = $("#date").val();
    //alert(date);
    if (date == '') {
        $("#dateText").show();
        $("#dateText").html("Please select date");
        $("#loadarModal").modal('hide');
    } else {
        $("#dateText").hide();
        //alert("can processd");
        var data = $("#form").serialize();
        $.ajax({
            type: 'POST',
            url: 'booked1.php',
            data: {
                date: date
            },
            cache: false,
            dataType: "html",
            success: function (response) {
                alert(response);
                if(response==''){

                }
                $("#booking").html(response);
                $("#loadarModal").modal('hide');

            }


        });
    }

    });

This is my PHP Script:

 include 'd_b_con.php';

  if(isset($_POST['date'])){

$date=$_POST['date'];

$query=mysqli_query($conn,"select date as date ,tokenno as tokenno ,inusrance as inusrance,bookingtoken as bookingtoken ,
                                  fname as fname,lname as lname , status as status from at_booking where date='$date'");
$data=array();
while($row1=mysqli_fetch_array($query)){
    $data[] = $row1;
    $date1=$row1['date'];
    $tokenno=$row1['tokenno'];
    $bookingtoken=$row1['bookingtoken'];
    $fname=$row1['fname'];
    $lname=$row1['lname'];
    $status=$row1['status'];
    $insurance=$row1['inusrance'];
    $name=$fname.' '.$lname;

    echo '<tr>';
    echo "<td>$name </td>";
    echo "<td> $date1 </td>";
    echo "<td>$bookingtoken </td>";
    echo "<td>$insurance </td>";
    echo '</tr>';

    $result=array(
        "name" => $name,
        "date1" => $date,
        "bookingtoken" => $bookingtoken,
        "insurance" => $insurance
    );
    echo json_encode($result);
}

This is the first time I am using server side data tables. I am getting error like "DataTables warning: table id=open - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1" Can anyone guide me how to use data tables server side for my code.

2
phpjqueryajaxdatatables
Shouldn't the echo json response be after the while loop closing brace? Also you should array push - Rotimi
$result is overriding its content with every loop - Rahul
@RahulMeshram could you please help in code where i have to change - karthik
@Akintunde how to do that??? - karthik
It clearly indicate that Invalid JSON response. In your php you also echo <td> tags which obviously not josn format echo '<tr>'; echo "<td>$name </td>"; echo "<td> $date1 </td>"; echo "<td>$bookingtoken </td>"; echo "<td>$insurance </td>"; echo '</tr>'; - B. Desai

2 Answers

0
votes

Try to change this code,

$result = [];
$result[]=array(
        "name" => $name,
        "date1" => $date,
        "bookingtoken" => $bookingtoken,
        "insurance" => $insurance
    );
echo json_encode($result);
die;

Once check in network->xhr whether you are getting any response or not.

0
votes

You really overcomplicate this. You should never echo out the actual <tr><td>.. markup, this is why you get the warning. And use mysqli_fetch_assoc instead :

$data = array();
while( $row1 = mysqli_fetch_assoc($query) ){
  $row1['name'] = $row1['fname'].' '.$row1['lname'];
  $row1['date1'] = $row1['date']; //??
  $data[] = $row1;
}
echo json_encode($data);

Update. You will probably need to use

echo json_encode( array('data' => $data) );

If you not set dataSrc to ''.