Datatables warning table id=datatables-example – invalid json response

Here I am new to ajax DataTables. I am trying to connect my database and fetch records to the dataTable. But I got this error:

datatables warning table id=datatables-example – invalid json responseFor more information about this error, please see http://datatables.net/tn/1

I have followed the tutorial called web lesson, in case I tried to find a solutions on internet but didn’t help me to solve my problem. Here is my HTML segments:

   <table id="datatables-example" class="table table-striped table-bordered" width="100%" cellspacing="0">
      <thead>
           <tr>
             <th>Appointment ID</th>
             <th>Doctor Name</th>
             <th>Specialization</th>            
             <th>Patient Name</th>
             <th>Fees</th>
             <th>Appointment Date</th>
             <th>Appointment Time</th>
             <th>Status</th>
             <th class="text-right">Action</th>
           </tr>
      </thead>

  </table>

Below is the Ajax segments:

<script>
$(document).ready(function() {

load_data();

function load_data(){

   var datatable=  $('#datatables-example').DataTable({
       "processing": true,
       "serverSide": true,  
       "order" :[],
        "ajax" : {
           url: "sidebar/apt_table admin.php", // Url to which the request is send
           method: "POST",             // Type of request to be send, called as method
    }

    });
}

});

</script>

The apt_table admin.php page should be:

<?php

session_start();
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
  $conn = new mysqli("localhost", "root", "", "hmsproject");
  $conn->set_charset("utf8mb4");
} catch(Exception $e) {
  error_log($e->getMessage());
  exit('Error connecting to database'); //Should be a message a typical user could understand
}

$columns= array('apt_id','doctor_name','specilization','patient_name','fees','apt_date','apt_time','admin_status');

$query="SELECT * FROM appointment as a,users as u  WHERE a.user_id= u.user_id ORDER BY apt_id DESC";    

if(isset($_POST["search"]["value"])){

    $query .= '
    WHERE apt_id LIKE "%'.$_POST["search"]["value"].'%"
    OR  doctor_name LIKE "%'.$_POST["search"]["value"].'%"
    OR  specilization LIKE "%'.$_POST["search"]["value"].'%"
    OR  patient_name LIKE "%'.$_POST["search"]["value"].'%"
    OR  fees LIKE "%'.$_POST["search"]["value"].'%"
    OR  apt_date LIKE "%'.$_POST["search"]["value"].'%"
    OR  apt_time LIKE "%'.$_POST["search"]["value"].'%"
    OR  admin_status LIKE "%'.$_POST["search"]["value"].'%"

    ';
}
if (isset($_POST["order"])) {

    $query .= ' ORDER BY '.$columns[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].'    ';

}else{

    $query .= ' ORDER BY apt_id DESC';

}

$query1='';

if ($_POST["length"] != -1) {

    $query1 =  'LIMIT '.$_POST['start'] .' , '.$_POST['length'];

}

$number_filter_row= mysqli_num_rows(mysqli_query($conn,$query));
$result =mysqli_query($conn,$query.$query1);

$data=array();

while($row = mysqli_fetch_array($result))
{

    $sub_array =array();
    $sub_array[] =  '<td> '. $row["apt_id"].' </td>';
    $sub_array[] ='<td> '. $row["doctor_name"].' </td>';
    $sub_array[] ='<td> '. $row["specilization"].' </td>';
    $sub_array[] ='<td> '. $row["patient_name"].' </td>';
    $sub_array[] ='<td> '. $row["fees"].' </td>';
    $sub_array[] ='<td> '. $row["apt_date"].' </td>';
    $sub_array[] ='<td> '. $row["apt_time"].' </td>';

    if($row["admin_status"]=="0") {  
        $sub_array[] ='<td> <span class="custom-badge status-red">Cancel</span>';
         } else if($row["admin_status"]=="1") {    
            $sub_array[] ='<td> <span class="custom-badge status-green">Active</span>';
             } else {   
                $sub_array[] ='<td> <span class="custom-badge status-blue">Pending</span>';

         } 

         $sub_array[] = '<td class="text-right">
         <div class="dropdown dropdown-action">
             <a href="#" class="action-icon dropdown-toggle" data-toggle="dropdown" aria-expanded="false"><i class="fa fa-ellipsis-v"></i></a>
             <div class="dropdown-menu dropdown-menu-right">
                 <!-- <a class="dropdown-item" href="edit-appointment.php"><i class="fa fa-pencil m-r-5"></i> Edit</a> -->
                 <a class="dropdown-item" href="" data-toggle="modal" id="rep1" data_id= '.$row['apt_id'] .'  data-target="#active_appointment"><i class="fa fa-trash-o m-r-5"></i> Active</a>
                 <a class="dropdown-item" href="" data-toggle="modal" id="rep2" data_id='. $row['apt_id'].' data-target="#delete_appointment"><i class="fa fa-trash-o m-r-5"></i> Delete</a>

             </div>
         </div>
     </td>';
     $data[]=$sub_array;


}

function get($conn)
{

    $query="SELECT * FROM appointment as a,users as u  WHERE a.user_id= u.user_id ORDER BY apt_id DESC";    
    $result =mysqli_query($conn,$query);
return mysqli_num_rows($result);

}           
$output= array(
    "draw"  => intval($_POST['draw']),
    "recordsTotal"  => get($conn),
    "recordsFiltered"  => $number_filter_row,
    "data"  => $data
    );

    echo json_encode($output);

            ?>             

I don’t know where I went wrong. Please help me. Thanks in advance.

Answer

to implement server-side ajax datatable your script should return json only you are sending html with some <tr> and <td>

Refer: https://datatables.net/examples/data_sources/server_side

in above example source is giving json output only.

So, try to fix your apt_table admin.php