My Date Range Search Implementation using Server Side Processing

marina

I thought I would post how I implemented date range using server side processing since I spent a good deal of time putting together different hints though out this forum. So here are the bits and pieces that made my basic version work (before I go on and make it fancy :)

//import one of those jquery css styles that has datepicker defined in it.

//first I added some search fields for min and max date:

[code]
Min date:
Max date:

[/code]

//Below is the part where you initialize your table (I have multiple tables that are launched with buttons.. so ignore the displayTable function and the passing of table name and key.. unless this is a functionality your looking for then by all means have a look :)

//pay attention to the fnServerParams section, this is where the min and max variables are pushed to the serverside_processing script.

[code]


var oTable;
var asInitVals = new Array();

function displayTable(table,key,css_table){

$(document).ready(function(){
$.datepicker.regional[""].dateFormat = 'yy-mm-dd'; //Here I do this because the date format i use in mysql is yyyy-mm-dd ( I know i said //yyyy-mm-dd and the code says yy-mm-dd... don't know why but somehow datepicker translates yy to yyyy.
$.datepicker.setDefaults($.datepicker.regional['']);
oTable = $(css_table).dataTable({
"sPaginationType": "full_numbers",
"bjQueryUI":true,

"sScrollY": "100%",
"sScrollXInner": "150%",
"bScrollCollapse": true,

"sDom": 'T<"clear"><"fg-toolbar ui-widget-header ui-corner-tl ui-corner-tr ui-helper-clearfix"lfr>RtS<"fg-toolbar ui-widget-header ui-corner-bl ui-corner-br ui-helper-clearfix"ip>',
"oTableTools": {
"sSwfPath": "datatables/extras/TableTools-2.0.1/media/swf/copy_cvs_xls_pdf.swf"
},
"aLengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
"oLanguage": {
"sSearch": "Search:"
},

'bProcessing':true,
'bServerSide':true,

'bAutoWidth': true,

'sAjaxSource':'server_processing.php',
"fnInitComplete": function() {
oTable.fnAdjustColumnSizing();
},
"fnServerParams": function (aoData, fnCallback) {
aoData.push( { "name": "table", "value": table } );
aoData.push( { "name": "key", "value": key } );
aoData.push( {"name": "min", "value": $('#min').val() } );
aoData.push( {"name": "max", "value": $('#max').val() } );
},


});

[/code]
//Below this I have the following, this makes those fancy calendars appear when you click your min/max input boxs

[code]
$( "#min" ).datepicker( {
"onSelect": function(date) {
min = new Date(date).getTime();
oTable.fnDraw();
}
} ).keyup( function () {
min= new Date(this.value).getTime();
oTable.fnDraw();
} );

$( "#max" ).datepicker( {
"onSelect": function(date) {
max = new Date(date).getTime();
oTable.fnDraw();
}
} ).keyup( function () {
max = new Date(this.value).getTime();
oTable.fnDraw();
} );


});

}//end displayTable functions

[/code]

//Now for the section I added to server_processing.php... its very basic.. so should help you get a good understanding of how this all works.
//I added this right below the filter section that is already there

[code]


if(isset($_GET['min']) && isset($_GET['max']) && $_GET['min'] != '' && $_GET['max'] != ''){
$sWhere = "WHERE usage_date BETWEEN '$_GET[min]' AND '$_GET[max]'";
}

[/code]
Hope this helps someone down the line!
Enjoy,
Marina

marina

Note: This is intended as an example to illustrate how server side date range works in general. If you want to ensure that the main filter works with your date range filter you will have to combine the code above into the existing filter code.

vishalmelmatti

Hi Marina,

Greate Job !!!

I was also trying to achieve same thing but could not succeed. Would you please help me to figure out what I am missing. Datatable woks for me but the daterange parameter I am trying to send to server is not working. I am using daterange picker. Below is my code.




$(function() {

/* Formating function for row details */
function fnFormatDetails ( scheduledJobsGridTable, nTr )
{
var jid = $(nTr).find('.scheduled_job_id').val();
var jtype = $(nTr).find('.scheduled_job_type').val();
var sOut = $.ajax({
url: '<?php echo site_url('jobs/jobDetails'); ?>',
data: 'jid='+jid+'&jtype='+jtype,
type: 'POST',
dataType: "html",
async: false
}).responseText;
return sOut;
}

/*
* Insert a 'details' column to the table
*/
var nCloneTh = document.createElement( 'th' );

$('#scheduledJobsGrid thead tr').each( function () {
this.insertBefore( nCloneTh, this.childNodes[0] );
} );


scheduledJobsGridTable = $('#scheduledJobsGrid').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "<?php echo site_url('jobs/scheduledGrid'); ?>",
"sServerMethod": "POST",
"bPaginate": true,
"sPaginationType": "full_numbers",
"bLengthChange": false,
"bFilter": true,
"bSort": true,
"bInfo": false,
"bAutoWidth": false,
"sScrollY": "450px",
"fnServerParams": function ( aoData ) {
aoData.push( { "name": "datetime", "value": $('#scheduled-jobs-filter-daterange').val() } );
},
"aoColumns": [
{ "bSortable": false, "aTargets": [ 0 ], "sClass": "left", "sWidth": "2%"},
{ "bSortable": false, "sClass": "center", "sWidth": "2%"},
{ "bSortable": false, "sClass": "center", "sWidth": "2%"},
{"sClass": "left", "sWidth": "10%"},
{"sClass": "left", "sWidth": "10%"},
{"sClass": "left", "sWidth": "8%"},
{"sClass": "left", "sWidth": "15%"},
{"sClass": "left", "sWidth": "8%"},
{"sClass": "left", "sWidth": "7%"},
{"sClass": "left", "sType": "date-euro", "sWidth": "8%"},
{"sClass": "left", "sWidth": "8%"},
{"sClass": "left", "sWidth": "8%"},
{ "bSortable": false, "sClass": "left", "sWidth": "8%"}
]
} );

$('#scheduled-jobs-filter-daterange').daterangepicker({
dateFormat: 'mm-dd-yy',
onOpen: function(){
$('a', $('.ui-daterangepicker-dateRange:visible').not('.ui-state-active')).each(function(){
$(this).trigger('click')
})
$('ul.ui-widget-content').hide()
},
onChange:function (){
scheduledJobsGridTable.fnDraw();
}
});


/* Add event listener for opening and closing details
* Note that the indicator for showing which row is open is not controlled by DataTables,
* rather it is done here
*/
$('.scheduled_job_details').live('click', function () {
var nTr = this.parentNode.parentNode;
if ( this.src.match('details_close') )
{
/* This row is already open - close it */
this.src = "<?php echo base_url();?>img/details_open.png";
scheduledJobsGridTable.fnClose( nTr );
}
else
{
/* Open this row */
this.src = "<?php echo base_url();?>img/details_close.png";
scheduledJobsGridTable.fnOpen( nTr, fnFormatDetails(scheduledJobsGridTable, nTr), 'details');
}
} );
});



Thanks for your help in advance.

marina

Hi, sorry for the late response, have not been here for a while.

What does your server side implementation look like?

allan

@marina - sorry I didn't reply back at the time when you first posted this. This is fantastic! Thanks very much for writing your implementation up like this and sharing it with the community!

Regards,
Allan

marina

no problem! thanks for the awesome API Allan!

ablonashid

Thank you Marina !. can you please provide the full code

marina

Hi Ablonashid,

The best way to get this to work is to start with Alan's provided server side code: tables.html & server_processing.php. First make that code work with a simple example database of your own... That way you know the base is working. Then follow my tutorial above in order to alter your code, you will recognize where to put the changes once you have a server side example up and running.

Let me know if you need anymore detailed help, sorry this reply was so late,
Marina

eanzures

Hey Mariana, thank you! You saved my life :P

marina

Glad I could help eanzures!

shafiqkr

@marina:what is table and key in below statment

aoData.push( { "name": "table", "value": table } );
aoData.push( { "name": "key", "value": key } );

marina

The reason I am pushing those particular values is because my setup has more than 1 table , so they each have a name and a key. (table = table's name) and (key = key of that particular table). You can happily ignore those if you are displaying only one table.

cihat74

Hi Marina,

I did everything you wrote but couldn't make it work. I am struggling with it, changing this-turning back, changing that-turning back. I think there is nothing I can do more. I have one table with the id of "example" and I don't understand what to ignore and what to change. Looking forward to your help.

marina

Hello,

Would you like to post your code so I can take a closer look?

-Marina