Server-side Individual Column Filtering

dr00bie

I can't seem to get the server-side individual column filtering working properly...

Here is my code,

[code]
var oCache = {
iCacheLower: -1
};

function fnSetKey( aoData, sKey, mValue )
{
for ( var i=0, iLen=aoData.length ; i

dr00bie

I had a mistake in the code I posted, please disregard the last init code, even when I remove that, I get the same error on this line, [code]if (bFiltered == true) aiRows = oSettings.aiDisplay; [/code]

Also, the commented out init code is just for testing... it works fine when I use that commented out code, but when I modify the code to include the individual column filtering, that is when I get the error.

Thanks,
Drew

dr00bie

OK... the error was being generated because I renamed my table, "data" instead of "example", so I renamed that and I am not getting the error anymore.

Now I am getting empty select lists, I did some research on this issue and found this, http://www.datatables.net/forums/discussion/5599/datatables-individual-column-filtering-example-using-select-menus-with-server-side-processing-s/p1, but apart from saying that a call to the server to get a distinct list of values needs to be completed, but there isn't much info about how to get that list.

Can anyone supply an example like, http://www.datatables.net/examples/api/multi_filter_select.html, that uses server-side source of the data?

Thanks,
Drew

dr00bie

I found this, http://www.datatables.net/forums/discussion/3931/server-side-filtering-on-specific-columns-with-input-and-select/p1, which says, "basically in the JSON for the first request (sEcho == 1) it assumes that an array called 'select' is passed back as part of the JSON object. The array length is equal to the number of columns, and each array element contains an array of the values you want to search for:"

I am not sure what the format of the JSON should look like... currently I have,

[code]
{"sEcho":1,"iTotalRecords":"180","iTotalDisplayRecords":"180","select":"{'1': [['1001','1001'],['1002','1002'],['1003','1003']],'2': [['Lura Baggs','Lura Baggs'],['Dominga Conary','Dominga Conary'],['Randolph Kiester','Randolph Kiester']]},","aaData":[["1001","Selma Aquirre"],["1002","Minerva Hepner"],["1003","Harvey Yeadon"],["1004","Alexandria Butter"],["1005","Lorie Sawtelle"],["1006","Adger"],["1007","Mitchell Uyehara"],["1008","Erik Puglisi"],["1009","Jared Uriarte"],["1010","Elmer Knouse"],["1011","Bettye Vandermeer"],["1012","Behn"],["1013","Leila Worthley"],["1014","Karl Mauck"],["1015","Margret Lawry"],["1016","Mitchell Fonder"],["1017","Claude Lawhead"],["1018","Brad Valliere"],["1019","Milton Parekh"],["1020","Mia Tenner"],["1021","Georgina Butz"],["1022","Claude Yarger"],["1023","Marcy Choat"],["1024","Concepcion Goodridge"],["1025","Arnold Dora"],["1026","Brad Sustaita"],["1027","Andre Pick"],["1028","Edgar Ferrera"],["1029","Margo Dollard"],["1030","Karl Heater"],["1031","Erik Maitland"],["1032","Selma Soderlund"],["1033","Winnie Palencia"],["1034","Bridgette Deatherage"],["1035","Margo Christon"],["1036","Cornelia Moitoso"],["1037","Berberich"],["1038","Leann Kulpa"],["1039","Raul Hansard"],["1040","Gabriel Molock"],["1041","Deana Pouncy"],["1042","Edgar Hillary"],["1043","Gabriel Groll"],["1044","Ladonna Mutter"],["1045","Ladonna Kummer"],["1046","Cecil Muench"],["1047","Rosalyn Pasillas"],["1048","Harvey Araiza"],["1049","Lorie Shotts"],["1050","Leila Kouba"]]}
[/code]

Can anyone supply an example of what the select JSON should look like?

Thanks,
Drew

dr00bie

Finally got it sorted out...

The code below will generate a distinct list for each $aColumns member and load that data into the "select" section of the JSON.

Here is the PHP code,

[code]
"select" => array(),
"aaData" => array()
);

//select lists
for ( $i=0 ; $i

Driver

Thanks for this, but in my case I use HTTP POST (post.php) and I don't know how and where to put the PHP code you provided.

A part of my post.php:
[code]/*
* Output
*/
$sOutput = '{';
$sOutput .= '"sEcho": '.intval($_POST['sEcho']).', ';
$sOutput .= '"iTotalRecords": '.$iTotal.', ';
$sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
$sOutput .= '"aaData": [ ';
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$sOutput .= "[";
for ( $i=0 ; $i

Driver

Anyone can help?

fbas

move the logic from .getJSON to .ajax's "success" method

[code]
$(document).ready(function() {
/* Initialise the DataTable */
var oTable = $('#example').dataTable( {
"bProcessing": true,

/* Use HTTP POST */
"bServerSide": true,
"sAjaxSource": "scripts/post.php",
"fnServerData": function ( sSource, aoData, fnCallback ) {
$.ajax( {
"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": function(json, textStatus, jqXHR) {
/* Create the select elements on the first run */
if ( json.sEcho == 1 ) {
$("tfoot th").each( function (i) {
/* Insert the select menu */
this.innerHTML = fnCreateSelect(json.select[i]);

/* Add the event listener for the newly created element */
$('select', this).change( function () {
oTable.fnFilter( $(this).val(), i );
} );
} );
}

/* DataTables callback */
fnCallback(json)
}
} );

},

[/code]

Driver

Now I've got this error:
[quote]Uncaught TypeError: Cannot read property 'length' of undefined;[/quote]
near line: [quote]var r='', i, iLen=aData.length;[/quote]

http://nevada.pl/datatables/server_side_test2.html
And in json the select element is empty:
http://nevada.pl/datatables/scripts/post2.php

Driver

Anyone have an idea how to solve this problem?

I think something is wrong with [quote]$sOutput .= '"select": [ ';[/quote]

Driver

please help...

fbas

your json.select = []. probably an error in your server side script.

json.select[0] is not defined, hence your error

read this debugger guide and learn to use the debugger. http://tote-magote.blogspot.com/2011/08/debugger-basics-for-datatables.html

Driver

Yes, I know that json select is empty, but I don't know why. I just used the dr00bie's server side script. Where is an error in this code?
[code]$sOutput .= '"select": [ ';

//select lists
for ( $i=0 ; $i

fbas

$output (an array of arrays) is not the same variable as $sOutput;
echoing $sOutput does nothing but echo an empty array literal.

you are building $output and probably intend to convert it to json and echo that:
[code]echo json_encode($output);[/code]

Driver

Now it works! :)
I also just had to remove one extra bracket from json processing:
[code]echo $sOutput;
echo str_replace(array('{'), '', json_encode($output));[/code]
Thank you!