Columns are not sortable after ajax returns data

ConorHiggins

Hi I am new to datatables and have come across something which i can't seem to figure out. It is probably something very simple so excuse my stupidity if it is.
I am trying to create a datatable which is populated by JSON encoded by a php file. The JSON seems to be formatted correctly and includes the necessary parameters, and the table is being filled up correctly, but for some reason, after the table is filled, the columns are not sortable. The arrows will change but the order of the rows will not.

Here is the returned JSON:

[code]
{
"sEcho":2,
"iTotalRecords":5,
"iTotalDisplayRecords":5,
"aaData":[
["238","Michael Davis","50","","","N\/A","0","0","0","","","00:00","00:00","No","Yes","No","No","No"],
["387","Nicholas Anelka","127.5","","","N\/A","3.3","2.1","3"," Left Pectoral - Front (5) ,"," Left Knee - Rear (5) ,","00:30","00:00","No","No","No","Yes","No"],
["385","Sean Johnson","70.62","","","N\/A","0","2.2","0"," Left Forearm - Rear (2) ,"," Thoracic (4) ,","22:50","08:25","","","","",""],
["237","Owen Smith","121.37","","","N\/A","4","2.7","3.1"," Lower Abdominals - Front (5) ,"," Lower Back (5) , Left Shoulder - Rear (5) ,","05:25","10:00","","","","",""],
["388","David Price","50","","","N\/A","0","0","0","","","00:00","00:00","","","","",""]
]
}
[/code]

And here is my DT declaration:

[code]
var oTable = $('#example').dataTable( {
"sScrollY": 300,
"bJQueryUI": true,
"bStateSave": true,
"bProcessing": true,
"bServerSide": true,
"bSortable": true,
"aLengthMenu": [[-1, 10, 25, 50, 100], ["All", 10, 25, 50, 100]],
"asSorting": [[ 4, "desc" ]],
"sPaginationType": "full_numbers",
"sAjaxSource": "/blah/script.php",

"fnRowCallback": function( nRow, aData, iDisplayIndex ) {
/* Append the grade to the default row class name */
if ( aData[1] == "Owen Smith" )
{
//console.log('nRow is: '+nRow);
$('td:eq(1)', nRow).css('background-color','#ff0000');
}
return nRow;
},
"fnDrawCallback": function () {
$('#example tbody td').editable( 'examples_support/editable_ajax.php', {
"callback": function( sValue, y ) {
/* Redraw the table from the new data on the server */
oTable.fnDraw();
},
"height": "14px"
} );
}
} );
[/code]

Any help would be greatly appreciated. Also note I have tried this without the asSorting parameter and it still works the same way.

Conor

ConorHiggins

Would this have anything to do with the type of values being stored in the table? I would imagine that it just takes everything as being alphanumeric.

fbas

[quote]ConorHiggins said: I am trying to create a datatable which is populated by JSON encoded by a php file.[/quote]

When you say your JSON is encoded by a php file do you mean you have a static file with the contents of your JSON?

If this is the case, you aren't really using server side processing which would imply that all the sorting and filtering is done by server side scripts. If you tell DataTables to use serverside processing with an ajax call to a page that never changes, your data will always look the same (same sort order).

If this is the case, rather than making a call to another script for the "server" data, you should include the data in your initial page. On http://www.datatables.net/release-datatables/examples/data_sources/js_array.html you can see how to use a javascript array (basically the contents that you have in your JSON file) to initialize the DataTable.


[code]

var oTable = $('#example').dataTable( {
"sScrollY": 300,
"bJQueryUI": true,
"bStateSave": true,
"bProcessing": true,
"bSortable": true,
"aLengthMenu": [[-1, 10, 25, 50, 100], ["All", 10, 25, 50, 100]],
"asSorting": [[ 4, "desc" ]],
"sPaginationType": "full_numbers",
"aaData": [
["238","Michael Davis","50","","","N\/A","0","0","0","","","00:00","00:00","No","Yes","No","No","No"],
["387","Nicholas Anelka","127.5","","","N\/A","3.3","2.1","3"," Left Pectoral - Front (5) ,"," Left Knee - Rear (5) ,","00:30","00:00","No","No","No","Yes","No"],
["385","Sean Johnson","70.62","","","N\/A","0","2.2","0"," Left Forearm - Rear (2) ,"," Thoracic (4) ,","22:50","08:25","","","","",""],
["237","Owen Smith","121.37","","","N\/A","4","2.7","3.1"," Lower Abdominals - Front (5) ,"," Lower Back (5) , Left Shoulder - Rear (5) ,","05:25","10:00","","","","",""],
["388","David Price","50","","","N\/A","0","0","0","","","00:00","00:00","","","","",""]
]
}); // etc.
[/code]


I took out the serverside and ajax values. I don't remember what all the others do (like bProcessing), so I left them in. If this doesn't work, check with those other vars.

If you still want your JSON code in a separate file (i.e. you regenerate it from time to time) you can use php includes to include it in your code.

fbas

If this is not the case (regarding a static JSON file), then you need to look at your server side script, and what it's doing to re-order the data. In server side processing, all that is done by the server side script, none of it is done by the client side javascript. For instance, You'll need to craft ORDER BY clauses for your SQL, if you're drawing from a database.

ConorHiggins

@fbas, thank you, you're a life saver. I had set that serverside property to be true, assuming that it would be necessary when I was loading the data via a PHP script. In fact what the PHP script is doing is really just performing a large complex MySQL query on my database and returning a result set which I then needed to fill the datatable with. This is now working perfectly thank you.

Conor