I'm building a table to display order information and I'm having trouble with understanding why the JSON data returns blank when sorting in descending or changing to the second to last page in the table.
The parsing error happens when I sort certain columns from descending to ascending and when I try to go to the second to last page of the table. However when I search something all of the functions come back and work perfectly fine. I tried looking up solutions and found a few articles however none were having the exact same issue as mine or even close to it and the closest one has been closed (https://datatables.net/forums/discussion/9220/ajax-sort-descending-on-load). What am I doing wrong?
You can see this happen at: http://aframewerx.com
JS:
$(document).ready(function(){
var dataTable = $('#orderTable').DataTable( {
"processing": true,
"serverSide": true,
"pageLength": 10,
"autoWidth": false,
"responsive": {
"details": {
renderer: function ( api, rowIdx ){
var data = api.cells( rowIdx, ':hidden' ).eq(0).map( function ( cell ) {
var header = $( api.column( cell.column ).header() );
return '<p style="color:#00A">'+header.text()+' : '+api.cell( cell ).data()+'</p>';
}).toArray().join('');
return data ? $('<table/>').append( data ) : false;
}
}
},
"ajax":{
url :"fetch_data.php", // json datasource
type: "post", // method , by default get
error: function (request, error) {
console.log(arguments);
alert("Error: " + error);
$(".orderTable-error").html("");
$("#orderTable").append('<tbody class="orderTable-error"><tr><th colspan="3">No data found in the server</th></tr></tbody>');
$("#orderTable_processing").css("display","none");
}
}
});
});
PHP:
<?php
$servername = "";
$username = "";
$password = "";
$dbname = "";
$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());
$requestData= $_REQUEST;
$columns = array(
0 => 'order_date',
1 => 'due_ship_date',
2 => 'ordered_by',
3 => 'pick_status',
4 => 'order_number',
5 => 'sku',
6 => 'qty',
7 => 'product_title',
8 => 'variant_title',
9 => 'product_type',
10 => 'id'
);
$sql = "SELECT id";
$sql.=" FROM kess_orders";
$query=mysqli_query($conn, $sql) or die("Failed to connect");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData;
$sql = "SELECT order_date,due_ship_date,ordered_by,pick_status,order_number,sku,qty,product_title,variant_title,product_type,id ";
$sql.=" FROM kess_orders WHERE 1=1";
if( !empty($requestData['search']['value']) ) {
$sql.=" AND ( order_date LIKE '".$requestData['search']['value']."%' ";
$sql.=" OR due_ship_date LIKE '".$requestData['search']['value']."%' ";
$sql.=" OR ordered_by LIKE '".$requestData['search']['value']."%' ";
$sql.=" OR pick_status LIKE '".$requestData['search']['value']."%' ";
$sql.=" OR order_number LIKE '".$requestData['search']['value']."%' ";
$sql.=" OR sku LIKE '".$requestData['search']['value']."%' ";
$sql.=" OR product_title LIKE '".$requestData['search']['value']."%' ";
$sql.=" OR product_type LIKE '".$requestData['search']['value']."%' )";
}
$query=mysqli_query($conn, $sql) or die("Failed to connect");
$totalFiltered = mysqli_num_rows($query);
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]." ".$requestData['order'][0]['dir']." LIMIT ".$requestData['start']." ,".$requestData['length']." ";
$query=mysqli_query($conn, $sql) or die("fetch_orders.php: get order_number");
$data = array();
while( $row=mysqli_fetch_array($query) ) {
$nestedData=array();
$nestedData[] = '<input type="hidden" value="'.$row["id"].'" name="id">'.$row["order_date"];
$nestedData[] = $row["due_ship_date"];
$nestedData[] = $row["ordered_by"];
$nestedData[] = $row["pick_status"];
$nestedData[] = $row["order_number"];
$nestedData[] = $row["sku"];
$nestedData[] = $row["qty"];
$nestedData[] = $row["product_title"];
$nestedData[] = $row["variant_title"];
$nestedData[] = $row["product_type"];
$nestedData[] = '<div id=' . $row["id"] . '"><div class="btn-group" id="'.$row["id"].'"><a class="btn dropdown-toggle" data-toggle="dropdown" href="#'.$row["id"].'" style="padding:0px !important;"><div class="wrapper"><div id="'.$row["id"].'" class="top bar"></div><div id="'.$row["id"].'" class="middle bar"></div><div id="'.$row["id"].'" class="bottom bar"></div></div></a> <ul class="dropdown-menu pull-right arrow_box" aria-labelledby="dLabel" style=" margin-right: 35px; top: -6px; min-width:105px;"><li><input type="hidden" value="'.$row["id"].'" name="id"><button class="pull-left btn btn-danger remove-item button-hover" style="margin-right:10px; margin-left:10px; height: 31px;border-radius: 3px;font-size: 12px;background-color: #2E112D;color:#fff;border:1px solid #2E112D;"><i class="fa fa-trash-o" aria-hidden="true"></i></button></li><li><input type="hidden" value="'.$row["id"].'" name="id"><button data-toggle="modal" data-target="#edit-item" class="pull-left button-hover btn btn-primary edit-item" style="margin-right:10px; height: 31px;border-radius: 3px;font-size: 12px;background-color: #2E112D;color:#fff;border:1px solid #2E112D;"><i class="fa fa-pencil" aria-hidden="true"></i></button></li></ul></div></div>';
$data[] = $nestedData;
}
$json_data = array(
"draw" => intval( $requestData['draw'] ),
"recordsTotal" => intval( $totalData ),
"recordsFiltered" => intval( $totalFiltered ),
"data" => $data
);
echo json_encode($json_data);
?>
Error:
"parsererror", SyntaxError: Unexpected end of JSON input at parse