Date Range Filter ServerProcessing

Emeka

hello all,

JQuery/JS/Datatables newbie here!

I've already done a search but could't find what needed, My range search is not working for some reason, this is quite similar to what I'm doing but I get lost when I get to the fnServerData bit! http://datatables.net/forums/comments.php?DiscussionID=1946&page=1

Here's my setup:
I have 2 fields for the start/end dates (with a javascript date picker) and data coming via server.

Javascript:
[code]$.fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
var iMin = document.getElementById('min').value * 1;
var iMax = document.getElementById('max').value * 1;
var iVersion = aData[0] == "-" ? 0 : aData[0]*1;
if ( iMin == "" && iMax == "" )
{
return true;
}
else if ( iMin == "" && iVersion < iMax )
{
return true;
}
else if ( iMin < iVersion && "" == iMax )
{
return true;
}
else if ( iMin < iVersion && iVersion < iMax )
{
return true;
}
return false;
}
);

$(document).ready(function() {
var oTable = $('#userstats').dataTable({
"bServerSide": true,
"bProcessing": true,
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"sAjaxSource": "../include/server_processing.php"
} );
$('#min').keyup( function() { oTable.fnDraw(); } );
$('#max').keyup( function() { oTable.fnDraw(); } );
} );
[/code]

server_processing.php
[code]$r=dbRow("select count(EntryID) as c from toil Where UserID = $id");
$total_records=$r['c'];

//filter
$total_after_filter=$total_records;
if( mysql_real_escape_string( $_GET['sSearch'] ) != "" AND $_GET['min'] != "" AND $_GET['max'] != ""){
$sWhere = "WHERE UserID = $id AND Date LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"UserID = $id AND Justification LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"UserID = $id AND Status LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"UserID = $id AND MComments LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' AND ";
"UserID = $id AND (Date >= $_GET[min] && Date <= $_GET[max])";
} elseif ($_GET['start'] != "" OR $_GET['end'] != ""){
$sWhere = "WHERE UserID = $id AND (Date >= $_GET[min] && Date <= $_GET[max])";
} elseif ( $_GET['sSearch'] != "" ) {
$sWhere = "WHERE UserID = $id AND Date LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"UserID = $id AND Justification LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"UserID = $id AND Status LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"UserID = $id AND MComments LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%'";
} else {
$sWhere = "WHERE UserID = $id";
}
[/code]

Any help appreciated.

codingavenue

Hi,

Does the min and max parameter have values on your server side script?

if not then you probably need to do something like this.

[code]
//add this to your DataTables initialization.
//not tested!!!
'fnServerData' : function ( sSource, aoData, fnCallback ) {
aoData.push( { 'name' : 'min', 'value' : $("#min").val() } );
aoData.push( { 'name' : 'max', 'value' : $("#max").val() } );

$.ajax({
'dataType' : 'json',
'data' : aoData,
'type' : 'GET',
'url' : sSource,
'success' : fnCallback
});
}
[/code]

Emeka

I'm sorry, posted this in the wrong thread: the code works (minus date range filter), until I insert fnServerData - the table then fails to work at all...

Tried these 2 codes with no luck - not sure what i'm doing wrong...
[code]
/* Custom filtering function which will filter data in column four between two values */
$.fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
var iMin = document.getElementById('min').value * 1;
var iMax = document.getElementById('max').value * 1;
var iVersion = aData[0] == "-" ? 0 : aData[0]*1;
if ( iMin == "" && iMax == "" )
{
return true;
}
else if ( iMin == "" && iVersion < iMax )
{
return true;
}
else if ( iMin < iVersion && "" == iMax )
{
return true;
}
else if ( iMin < iVersion && iVersion < iMax )
{
return true;
}
return false;
}
);

$(document).ready(function() {
var oTable = $('#userstats').dataTable({
"bServerSide": true,
"bProcessing": true,
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"sAjaxSource": "../include/server_processing2.php"
"fnServerData" : function ( sSource, aoData, fnCallback ) {
aoData.push( { 'name' : 'min', 'value' : $("#min").val() } );
aoData.push( { 'name' : 'max', 'value' : $("#max").val() } );
$.getJSON( sSource, aoData, function (json) {
/* Do whatever additional processing you want on the callback, then tell DataTables */
fnCallback(json)
});
}
} );
$('#min').keyup( function() { oTable.fnDraw(); } );
$('#max').keyup( function() { oTable.fnDraw(); } );
} );
[/code]


[code]
/* Custom filtering function which will filter data in column four between two values */
$.fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
var iMin = document.getElementById('min').value * 1;
var iMax = document.getElementById('max').value * 1;
var iVersion = aData[0] == "-" ? 0 : aData[0]*1;
if ( iMin == "" && iMax == "" )
{
return true;
}
else if ( iMin == "" && iVersion < iMax )
{
return true;
}
else if ( iMin < iVersion && "" == iMax )
{
return true;
}
else if ( iMin < iVersion && iVersion < iMax )
{
return true;
}
return false;
}
);

$(document).ready(function() {
var oTable = $('#userstats').dataTable({
"bServerSide": true,
"bProcessing": true,
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"sAjaxSource": "../include/server_processing2.php"
'fnServerData' : function ( sSource, aoData, fnCallback ) {
aoData.push( { 'name' : 'min', 'value' : $("#min").val() } );
aoData.push( { 'name' : 'max', 'value' : $("#max").val() } );
$.ajax({
'dataType' : 'json',
'data' : aoData,
'type' : 'GET',
'url' : sSource,
'success' : fnCallback
});
}
} );
$('#min').keyup( function() { oTable.fnDraw(); } );
$('#max').keyup( function() { oTable.fnDraw(); } );
} );
[/code]

Emeka

really need some help - completely stumped with this!

allan

There is no filtering done on the client-side when you are using server-side processing, so I would drop your 'afnFiltering' code completely (it will work to some degree, but it will almost certainly have undesired effects).

With server-side processing, all DataTables is doing is acting as a presentation and event handling layer - it is not doing any processing on the data itself (nor should it) - it will simply display what it is told to by the server-side. So if you want to do range filtering, it needs to be done on the server-side. This will typically involve a customisation of the example scripts, and possibly sending extra fields to the server using fnServerData to allow it to perform the required filtering.

Regards,
Allan

Emeka

Phew... got it! - here is my working code:

[code]
$(document).ready(function() {
var oTable = $('#table_name').dataTable({
"bServerSide": true,
"bProcessing": true,
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"sAjaxSource": "ajax.php",
"fnServerData": function ( sSource, aoData, fnCallback ) {
/* Add some data to send to the source, and send as 'POST' */
aoData.push( { "name": "min", "value": $('#min').val() } );
aoData.push( { "name": "max", "value": $('#max').val() } );
$.ajax( {
"dataType": 'json',
"type": "GET",
"url": "ajax.php",
"data": aoData,
"success": fnCallback
} );
}
} );
$('#min').change( function () { oTable.fnFilter( $(this).val(), 0 );} );
$('#max').change( function () { oTable.fnFilter( $(this).val(), 0 );} );
});
[/code]

I bid you goodnight!

mkatyal

Emeka possible to get the code for the server processing php? I have everthing else set so far for the min and max daterange .