DataTables
Advanced interaction
features for your tables.
Editor
Comprehensive editing
library for DataTables.
Manual
Download
≡ Show site navigation

Date Range Filter ServerProcessing

Date Range Filter ServerProcessing

EmekaEmeka Posts: 13Questions: 0Answers: 0
edited June 2010 in General
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.

Replies

  • codingavenuecodingavenue Posts: 22Questions: 0Answers: 0
    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]
  • EmekaEmeka Posts: 13Questions: 0Answers: 0
    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]
  • EmekaEmeka Posts: 13Questions: 0Answers: 0
    really need some help - completely stumped with this!
  • allanallan Posts: 63,405Questions: 1Answers: 10,452 Site admin
    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
  • EmekaEmeka Posts: 13Questions: 0Answers: 0
    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!
  • mkatyalmkatyal Posts: 2Questions: 0Answers: 0
    Emeka possible to get the code for the server processing php? I have everthing else set so far for the min and max daterange .
This discussion has been closed.

DataTables

DataTables designed and created by SpryMedia Ltd.
© 2007-2023 MIT licensed. Privacy policy. Supporters.
SpryMedia Ltd is registered in Scotland, company no. SC456502.