server-side processing + individual column filtering: how do I do this?

Mithrus

I am new to jQuery and even newer with DataTables. I have my own PHP solution for doing essentially the same thing as DataTables, but I figure I'd bite the bullet and migrate it to jQuery (so far no regrets:)). I got the global search function to work correctly, but I'm not sure how to identify the individual column search values by the sAjaxSource. I'm sure I'm just missing something obvious, but I hoped someone could at least steer me in the right direction on where to look.

...

Ok, I've made some progress. The number of entries in the info line shows the correct number, but the actual table contents isn't being affected. I'm guessing I need to do something else, but anyone have an idea what it might be?

Here is my code (it's in a smarty template file):
[code]// <![CDATA[
{literal}
var oTable;
var asInitVals = new Array();

$(document).ready(function() {
oTable = $("table#{/literal}{$smarty.get.formid}{literal}_summary").dataTable( {
{/literal}{jqColumns formid=$smarty.get.formid summary=1}{literal},
"sPaginationType": "full_numbers",
"oLanguage": {
"sSearch": "Search all columns:"
},
"bPaginate": true,
"bLengthChange": true,
"bFilter": true,
"bSort": true,
"bInfo": true,
"bAutoWidth": false,
"bProcessing": false,
"bServerSide": true,
"sAjaxSource": "query.htm?formid={/literal}{$smarty.get.formid}{literal}&summary=Y"
} );

$("tfoot input").keyup( function () {
/* Filter on the column (the index) of this element */
oTable.fnFilter( this.value, $("tfoot input").index(this) );
} );
$("tfoot input").each( function (i) {
asInitVals[i] = this.value;
} );

$("tfoot input").focus( function () {
if ( this.className == "search_init" )
{
this.className = "";
this.value = "";
}
} );

$("tfoot input").blur( function (i) {
if ( this.value == "" )
{
this.className = "search_init";
this.value = asInitVals[$("tfoot input").index(this)];
}
} );
} );{/literal}
// ]]>[/code]

Mithrus

Ok, I got the filtering to work right. Looks like i just had to work it thru. This is an awesome widget!

mQ

Hello Mithrus, could you share example for your server-side processing + individual column filtering ?
I try it, but unsuccessfully.
Thanks

Mithrus

My actual code is rather complex, since it's designed to handle many different entities (customers, items, orders, etc as well as viewable and editable variations), but a simplified version would be like this:[code]
//values passed in from dataTable

// vars for LIMIT clause
$iDisplayStart = isset($_REQUEST['iDisplayStart']) ? $_REQUEST['iDisplayStart'] : 1;
$iDisplayLength = isset($_REQUEST['iDisplayLength']) ? $_REQUEST['iDisplayLength'] : '10';
$iDisplayLength = min($iDisplayLength, 100);

// global filter
$sSearch = addslashes($_REQUEST['sSearch']);
if($sSearch != '') {
//CODE: build query WHERE clause to compare each filterable field with $sSearch
}

// column filters
$sColumns = explode(',', $_REQUEST['sColumns']);
foreach($formdef['fields'] as $f) {
$name = $f['name'];
$i = array_search($name, $sColumns);
$sSearch = addslashes($_REQUEST["sSearch_$i"]);
if($sSearch != '') {
// CODE: add specfic field comparison to WHERE clause of query
}
}
}

$tablecnt = // CODE: get total record count for table

// $fieldlist is just an array containing field definition info (similar to a data dictionary)
// CODE: perform query, store in $entries as an array of keyed arrays for each row
$sColumns = array();
foreach($fieldlist as $f) {
$sColumns[] = $f['name'];
}

$querycnt = count($entries);
$aaData = array();
foreach($entries as $entry) {
$row = array();
foreach($fieldlist as $f) {
$name = $f['name'];
$value = $entry[$name];
$row[] = $value;
}
}
$aaData[] = $row;
}

$sOutput = array(
'sEcho' => intval($_GET['sEcho']),
'sColumns' => $_REQUEST['sColumns'],
'iTotalRecords' => ($tablecnt),
'iTotalDisplayRecords' => ($querycnt),
'aaData' => $aaData,
);
echo json_encode($sOutput);
exit(0);
[/code]

mQ

thank you :}