1

I am using datatable server-side processing to display large data from database.

Here is my page 

<html>
 <head>
  <title>Datatable</title>
  <link rel="stylesheet" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css" />
  <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
  <script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script> 
 </head>
 <body>
   <div class="table-responsive">
   <table id="example" class="display" style="width:100%">
        <thead>
            <tr>
                <th>Country</th>
                <th>CustomerName</th>
                <th>OrderNumber</th>
                <th>OrderDate</th>
                <th>Address</th>
                <th>Qty</th>
            </tr>
        </thead>
    </table>
   </div>

 </body>
</html>

<script type="text/javascript" language="javascript" >
 $(document).ready(function(){
    $('#example').DataTable( {
        "processing": true,
        "serverSide": true,
        "ajax": {
     url:"fetchData.php",
     type:"POST" }
    } );
 });
</script>

And here is my fetchData.php

<?php   
include("connection.php");

 $querySelect= "SELECT Country, CustomerName, OrderNumber, OrderDate, Address, Qty";

 $queryWhere = " FROM OrdersTable WHERE ShipDate > '2018-04-01'";

if(isset($_POST["search"]["value"]))
{
 $queryWhere .= '
 AND ( Country LIKE "%'.$_POST["search"]["value"].'%" 
 OR CustomerName LIKE "%'.$_POST["search"]["value"].'%" 
 OR OrderNumber LIKE "%'.$_POST["search"]["value"].'%" 
 OR OrderDate LIKE "%'.$_POST["search"]["value"].'%" 
 OR Address LIKE "%'.$_POST["search"]["value"].'%" 
 OR Qty LIKE "%'.$_POST["search"]["value"].'%" ) 
 ';
}

if(isset($_POST["order"]))
{
 $queryWhere .= 'ORDER BY '.$columns[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' 
 ';
}

$res = $dbDB->query('SELECT COUNT(*)' .$queryWhere);
$number_filter_row = $res->fetchColumn();

$result = $dbDB->query($querySelect . $queryWhere);
$data = array();

while($row = $result->fetch(PDO::FETCH_ASSOC)) {
  $sub_array = array();
  $sub_array[] = $row["Country"];
  $sub_array[] = $row["CustomerName"];
  $sub_array[] = $row["OrderNumber"];
  $sub_array[] = $row["OrderDate"];
  $sub_array[] = $row["Address"];
  $sub_array[] = $row["Qty"];
  $data[] = $sub_array;
}

$r = $dbDB->query("SELECT COUNT(*)FROM OrdersTable WHERE ShipDate > '2018-04-01'");
$get_all_data = $r->fetchColumn();

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

echo json_encode($output);

?>

When I load my page I receive this error DataTables warning: table id=example - Ajax error. For more information about this error, please see http://datatables.net/tn/7

and in my console i get this Failed to load resource: the server responded with a status of 500 (Internal Server Error)

When I load just fetchData.php it works fine I can see the expected data. Based on the previous threads I was checking it's an error in the server request. Any ideas please how to fix this error ? Thank you.

JuniorDeveloper
  • 57
  • 1
  • 2
  • 6
  • add `error_reporting(E_ALL)` on top of your php page and from developer console (within firefox or chrome or similar browser) look at the net tab to see what your ajax returns. – Cemal Apr 27 '18 at 13:59
  • @Cemal Yes I can see in the response the data I want – JuniorDeveloper Apr 27 '18 at 14:16
  • if the result you're looking at isn't responding with 500, you're looking at somewhere wrong. – Cemal Apr 27 '18 at 14:20

0 Answers0