0

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.

2 Answers 2

1

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

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

Comments

0

Try this little debug (RETRIEVE THE COMMA AT THE END "post" )

"ajax" : {
           url: "sidebar/apt_table admin.php",
           method: "POST"
}

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.