Data doesn't fetch from the Database using DataTable

Question

I am new to jQuery DataTable. Here just I am trying to get the records from the database using DataTable. Also I am using a custom filter for advance searching option.

But the case is records doesn't fetch from the database. It always shows in the bottom of the table like: Showing 0 to 0 of 0 entries (filtered from 2 total entries). Nothing error occurred while processing.

Here is the HTML code.

  <div class="col-sm-4">
   <div class="form-group">
     <label>Enter Appointment Date </label>
      <input class="form-control" type="date" id="dates" name="dates">
          <span id="type" class="info text-danger"></span><br />

      </div>
   </div>

  <table id="example" style="width:100%" class="table table-hover">
      <thead>
       <tr>
          <th>Apt ID</th>
          <th>Doctor</th>
          <th>Specialist</th>
          <th>Patient</th>
          <th>Type</th>
          <th>Apt.Date</th>
          <th>Status</th>
          <th>Change</th>
          <th>Action</th>
      </tr>
   </thead>
   <tbody>
      <tr>
           <th>Apt ID</th>
           <th>Doctor</th>
           <th>Specialist</th>
           <th>Patient</th>
           <th>Type</th>
           <th>Apt.Date</th>
           <th>Status</th>
           <th>Change</th>
           <th>Action</th>
       </tr>
    </tbody>
</table>

Here is the query;

  $(document).ready(function () {

            fill_datatable();
  
  function fill_datatable(dates = '')
  {
   var dataTable = $('#example').DataTable({
    "processing" : true,
    "serverSide" : true,
    "order" : [],
    "searching" : false,
    "ajax" : {
     url:"adminquery/fetch/appointment/fetch_appointment.1.php",
     type:"POST",
     data:{
      dates:dates
     }
    }
   });
  }
  
  $('#dates').change(function(){
   var dates = $('#dates').val();
   
   if(dates != '')
   {
    $('#example').DataTable().destroy();
    fill_datatable(dates);
   }
   else
   {
    $('#example').DataTable().destroy();
    fill_datatable();
   }
  });

Below is the fetch.php

$conn = new PDO("mysql:host=localhost;dbname=hmsproject", "root", "");

$columns= array('apt_id','username','specilization','patient_name','type','apt_date','admin_status','Change');

// $query = "SELECT * FROM appointment as a,users as u WHERE a.user_id= u.user_id";

$query = " SELECT * FROM appointment as a INNER JOIN doctor_schedule as d ON a.user_id=d.user_id";

if(isset($_POST['dates'] ))
{
 $query .= 'AND a.apt_date = "'.$_POST['dates'].'" 
 ';
}

if(isset($_POST['order']))
{
 $query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
 $query .= 'ORDER BY No DESC ';
}

$query1 = '';

if($_POST["length"] != -1)
{
 $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}


$statement = $conn->prepare($query);

$statement->execute();

$number_filter_row = $statement->rowCount();

$statement = $conn->prepare($query . $query1);

$statement->execute();

$result = $statement->fetchAll();



$data = array();

foreach($result as $row)
{
 $sub_array = array();
 $sub_array[] = $row['apt_id'];
 $sub_array[] = $row['doctor_name'];
 $sub_array[] = $row['specilization'];
 $sub_array[] = $row['patient_name'];
 $sub_array[] = $row['type'];
 $sub_array[] = $row['apt_date'];
 $sub_array[] =' <span class="custom-badge status-red">Cancelled</span>';


 $data[] = $sub_array;
}

function count_all_data($conn)
{
 $query = "SELECT * FROM appointment as a INNER JOIN doctor_schedule as d ON a.user_id=d.user_id";
 $statement = $conn->prepare($query);
 $statement->execute();
 return $statement->rowCount();
}

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

echo json_encode($output);

The debug shows like:

{draw: 1, recordsTotal: 2, recordsFiltered: 0, data: []} data: [] draw: 1 recordsFiltered: 0 recordsTotal: 2

I don't know where I went wrong. Actually I am new to this. Any help may highly appreciated.

Answer 1

is your connection query is right? see you should not directly use ajax data / datatables >>> first of all see what output your page generating in your case check : url:"adminquery/fetch/appointment/fetch_appointment.1.php"

and use static values for Data table, if both working fine then only go further