Pulling JSON data into datatables

zxarr

Greetings,

I am new to jquery and javascript and am attempting to pull some JSON formatted data into datatables. I have validated my json data using 'http://jsonlint.com/'

When I load the page, I get no data within the table; just a default 'Processing...' message. I'm hoping someone else can look at what I've written and ensure it's all okay, or if I'm just missing something.

Here is the HTML I am using to create the table header/footer:

[code]



Order
Case ID
Req
Status
Priority
Region
User
Date
Assignee
Start
End




Loading data from server





Order
Case ID
Req
Status
Priority
Region
User
Date
Assignee
Start
End



[/code]

My datatables jquery:

[code]$(document).ready(function() {
oTable = $('#search').dataTable({
"bStateSave": true,
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "process_search.php",
"aoColumns": [
{ "sName": "Req_Order", "sTitle": "Order", "sWidth":"10%"} ,
{ "sName": "Req_CaseID", "sTitle": "Case ID"} ,
{ "sName": "Req_No","sTitle": "Req"} ,
{ "sName": "Req_Status", "sTitle": "Status"} ,
{ "sName": "Req_Priority","sTitle": "Priority"} ,
{ "sName": "Req_Region", "sTitle": "Region"} ,
{ "sName": "Req_User","sTitle": "User"} ,
{ "sName": "Req_Date", "sTitle": "Date"} ,
{ "sName": "Req_Assignee","sTitle": "Assignee"} ,
{ "sName": "Req_Start", "sTitle": "Start"} ,
{ "sName": "Req_End","sTitle": "End"}
],
"sPaginationType": "full_numbers",
"bJQueryUI": true,
"iDisplayLength": 25,
"aLengthMenu": [
[25, 50, 100, -1],
[25, 50, 100, "All"]
]
});
[/code]

process_search.php:

[code]
<?php

ini_set('display_errors', 0);
error_reporting(E_ALL);

include ("config.php");
require ("funcs.inc.php");
require ("functions.php");

$link=dbconn('LALI');
$query="SELECT Req_order, Req_CaseID, Req_No, Req_Status, Req_Priority, Req_Region, Req_User, Req_Date, Req_Assignee, Req_Start, Req_End, Req_AllAvailable FROM case_info ORDER BY Req_order";

$result = mysql_query($query, $link) or
die('(01)Query failed: ' . mysql_error());

$rows = array();
while ($r = mysql_fetch_assoc($result)) {
$rows[] = $r;
}

print json_encode($rows);

?>

[/code]

And some of my JSON data:

[code]
[
{"Req_order":"1","Req_CaseID":"XXXX-05-110125-01","Req_No":"1","Req_Status":"Completed","Req_Priority":"","Req_Region":null,"Req_User":" ","Req_Date":"2011-01-25 00:00:00","Req_Assignee":null,"Req_Start":"2011-01-21 00:00:00","Req_End":"0000-00-00 00:00:00","Req_AllAvailable":null},
{"Req_order":"2","Req_CaseID":"XXXX-04-091224-01","Req_No":"1","Req_Status":"Completed","Req_Priority":"","Req_Region":null,"Req_User":" ","Req_Date":"2009-12-24 00:00:00","Req_Assignee":null,"Req_Start":"0000-00-00 00:00:00","Req_End":"0000-00-00 00:00:00","Req_AllAvailable":null},
{"Req_order":"3","Req_CaseID":"XXXX-04-091224-01","Req_No":"2","Req_Status":"Completed","Req_Priority":"","Req_Region":null,"Req_User":" ","Req_Date":"2010-01-12 00:00:00","Req_Assignee":null,"Req_Start":"0000-00-00 00:00:00","Req_End":"0000-00-00 00:00:00","Req_AllAvailable":null}
]


[/code]

GregP

The JSON is just not complete. There are really 2 criteria that need to be met:

1. Valid JSON. But this is not quite enough:
2. JSON in one of the formats DataTables can accept. It looks like you're mostly there, but your data isn't in the aaData key, and you're missing the other required keys.

Samples found here: http://datatables.net/usage/server-side

fbas

GregP summed it up perfectly.

What you want your server side script to do is add more structure to the JSON. it should have a var named aaData, and your aaData should be numeric array (since you're not using mDataProp)

[code]
<?php

ini_set('display_errors', 0);
error_reporting(E_ALL);

include ("config.php");
require ("funcs.inc.php");
require ("functions.php");

$link=dbconn('LALI');
$query="SELECT Req_order, Req_CaseID, Req_No, Req_Status, Req_Priority, Req_Region, Req_User, Req_Date, Req_Assignee, Req_Start, Req_End, Req_AllAvailable FROM case_info ORDER BY Req_order";

$result = mysql_query($query, $link) or
die('(01)Query failed: ' . mysql_error());

$rows = array();
while ($r = mysql_fetch_row($result)) { // changed from mysql_fetch_assoc
$rows[] = $r;
}


$output = array();
$output["aaData"] = $rows; // put rows into aaData 'field'
print json_encode($output);

?>


[/code]

zxarr

Thanks for the help. I now managed to get properly formatted json for datatables, however I have another issue. (Go figure, right?)

This is what my code ended up being:

[code]
$link=dbconn('LALI');
$query="SELECT Req_order, Req_CaseID, Req_No, Req_Status, Req_Priority, Req_Region, Req_User, Req_Date, Req_Assignee, Req_Start, Req_End, Req_AllAvailable FROM case_info ORDER BY Req_order";

$result = mysql_query($query, $link) or
die('(01)Query failed: ' . mysql_error());

$arr = array();

while ($row = mysql_fetch_row($result)) {
array_push($arr, $row);
}

$totalrecords = count($arr);

$json = array('sEcho' => '3', 'iTotalRecords' => $totalrecords, 'iTotalDisplayRecords' => $totalrecords, 'aaData' => $arr);

print json_encode($json);

[/code]

And whether or not I have the additional fields (total records, etc) datatables dumps all 1500 records and will not sort, or limit the records in any way. I would like to mention that before I made the move to json, I had a simple table built, which datatables had no issue with.

This is the json I ended up with:

[code]
{
"sEcho": "3",
"iTotalRecords": 1551,
"iTotalDisplayRecords": 1551,
"aaData": [
[
"1",
"CaseID",
"0",
"Completed",
"",
null,
"ReqUser",
"0000-00-00 00:00:00",
null,
"0000-00-00 00:00:00",
"0000-00-00 00:00:00",
null
]
]
}
[/code]

GregP

That's an "easy" one. You have bServerSide set to true. All this does is say to the DataTables API "Don't worry about sorting and stuff, the server will take care of it. Just show the data that comes back and set the pagination cosmetics properly."

So, until your server-side script has sorting and filtering based on the parameters that the DataTables request sends it, the default will be to send back the whole dataset. On the server side, you need to read in the related variables ("iSortCol_0" for example) that are sent in the GET and act upon them in your PHP. Otherwise, you've told DT that the server is handling stuff when the server actually isn't handling stuff. ;-)

So, "easy" to know what's going wrong... but not necessarily "easy" to implement. There should be some PHP examples to start you off with, and this might help, too:

http://tote-magote.blogspot.com/2011/08/serverprocessingphp-for-datatables.html

fbas

detect iDisplayStart and iDisplayLength, construct a LIMIT clause for your query

[code]
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
mysql_real_escape_string( $_GET['iDisplayLength'] );
}
[/code]

see the script on http://www.datatables.net/release-datatables/examples/data_sources/server_side.html for help

this script is already written for you - no reason you need to write one yourself.

zxarr

Thanks for all the reply's. I've got it all working now.