My Date Range Search Implementation using Server Side Processing
My Date Range Search Implementation using Server Side Processing
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
//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
This discussion has been closed.
Replies
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.
What does your server side implementation look like?
Regards,
Allan
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
aoData.push( { "name": "table", "value": table } );
aoData.push( { "name": "key", "value": key } );
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.
Would you like to post your code so I can take a closer look?
-Marina