filtering by date problem
filtering by date problem
Hi everybody!
First of all, thanks to Allan for this great job, I'm using Datatables for one of my projects and it has been really useful.
However I have a problem while trying to filter a column (data type).
This is the push of my custom filter (it works fine, dates are being well compared):
[code]
$.fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
//get dates from datepickers
var dateMin = $('#datepicker_start').attr("value");
var dateMax = $('#datepicker_end').attr("value");
//the column where my dates are.
var date = aData[3];
// run through cases
if ( dateMin <= date && date <= dateMax ){
return true;
}
//failed
return false;
}
);
[/code]
The creation of my datatable:
[code]
oTable = $('#table_id').dataTable({//constructor
"aoColumns": [
null,
null,
null,
{
"sType": "date",
"bSearchable": false
},
{
"sType": "date",
"bSearchable": false
}
],
"bProcessing": false,
"bSort": true,
"bServerSide": true,
"bFilter": true,
"bJQueryUI": true,
"sAjaxSource": "../../DAL/accounts_datatable.php",
"fnServerData": function ( sSource, aoData, fnCallback ) {
aoData.push({
"name": "franchise",
"value": franchise_id
} );
$.ajax( {
"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnCallback
} );
}
,
"fnDrawCallback": function() {
$(".select_footer").each( function ( i ) {//filter by fields
if($(this).text()==0){
this.innerHTML = fnCreateSelect( oTable.fnGetColumnData(i) );
$('select', this).change( function () {
oTable.fnFilter( $(this).val(), i );
} );
}
});
$(".footer_checkbox").each( function ( i ) {//filter by dates
$(".footer_checkbox:eq("+i+")").change( function () {
chooseDate(i, $(".footer_checkbox:eq("+i+")").attr('checked'))
} );
});
}
} );
[/code]
The thing is that the first 3 columns (strings) are using a sort input on the footer (working great). The other 2 columns are 2 date fields with a checkbox (one for each column) on the footer. When a checkbox is selected 2 datepickers appear and the user picks a couple of dates (the range), then I call oTable.fnDraw() in order to apply the filter. Notice that right now I'm only testing the filter for one of the data columns (the 3rd, starting form 0), so forget about the last column.
I've been looking your code Allan, and I think that the problem is that the customs filters are applied before requesting the data to the server (I'm using server process), but I'm not 100% sure. I've been looking your examples and I think I'm doing everything ok, any idea ??
Thanks in advance, and thanks for your work again!
First of all, thanks to Allan for this great job, I'm using Datatables for one of my projects and it has been really useful.
However I have a problem while trying to filter a column (data type).
This is the push of my custom filter (it works fine, dates are being well compared):
[code]
$.fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
//get dates from datepickers
var dateMin = $('#datepicker_start').attr("value");
var dateMax = $('#datepicker_end').attr("value");
//the column where my dates are.
var date = aData[3];
// run through cases
if ( dateMin <= date && date <= dateMax ){
return true;
}
//failed
return false;
}
);
[/code]
The creation of my datatable:
[code]
oTable = $('#table_id').dataTable({//constructor
"aoColumns": [
null,
null,
null,
{
"sType": "date",
"bSearchable": false
},
{
"sType": "date",
"bSearchable": false
}
],
"bProcessing": false,
"bSort": true,
"bServerSide": true,
"bFilter": true,
"bJQueryUI": true,
"sAjaxSource": "../../DAL/accounts_datatable.php",
"fnServerData": function ( sSource, aoData, fnCallback ) {
aoData.push({
"name": "franchise",
"value": franchise_id
} );
$.ajax( {
"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnCallback
} );
}
,
"fnDrawCallback": function() {
$(".select_footer").each( function ( i ) {//filter by fields
if($(this).text()==0){
this.innerHTML = fnCreateSelect( oTable.fnGetColumnData(i) );
$('select', this).change( function () {
oTable.fnFilter( $(this).val(), i );
} );
}
});
$(".footer_checkbox").each( function ( i ) {//filter by dates
$(".footer_checkbox:eq("+i+")").change( function () {
chooseDate(i, $(".footer_checkbox:eq("+i+")").attr('checked'))
} );
});
}
} );
[/code]
The thing is that the first 3 columns (strings) are using a sort input on the footer (working great). The other 2 columns are 2 date fields with a checkbox (one for each column) on the footer. When a checkbox is selected 2 datepickers appear and the user picks a couple of dates (the range), then I call oTable.fnDraw() in order to apply the filter. Notice that right now I'm only testing the filter for one of the data columns (the 3rd, starting form 0), so forget about the last column.
I've been looking your code Allan, and I think that the problem is that the customs filters are applied before requesting the data to the server (I'm using server process), but I'm not 100% sure. I've been looking your examples and I think I'm doing everything ok, any idea ??
Thanks in advance, and thanks for your work again!
This discussion has been closed.
Replies
And I also have a problem filling the select inputs for the column filtering. They got filled but only with the values displayed in the table. I modified the original script in order to get values from all the records, not only the visible ones:
[code]
$.fn.dataTableExt.oApi.fnGetColumnData = function ( oSettings, iColumn, bUnique, bFiltered, bIgnoreEmpty ) {
// check that we have a column id
if ( typeof iColumn == "undefined" ) return new Array();
// by default we only wany unique data
if ( typeof bUnique == "undefined" ) bUnique = true;
// by default we do want to only look at filtered data
if ( typeof bFiltered == "undefined" ) bFiltered = false; //HERE
// by default we do not wany to include empty values
if ( typeof bIgnoreEmpty == "undefined" ) bIgnoreEmpty = true;
// list of rows which we're going to loop through
var aiRows;
// use only filtered rows
if (bFiltered == true) aiRows = oSettings.aiDisplay;
// use all rows
else aiRows = oSettings.aiDisplayMaster; // all row numbers
// set up data array
var asResultData = new Array();
[/code]
But still only getting values from the displayed records. The return from the server side script is something like:
{"sEcho": 1, "iTotalRecords": 110, "iTotalDisplayRecords": 110, "aaData": [ ["1234","......
But aaData contains only the first 10 records (displayLength), so when fnGetColumnData is called to fill the selects it only gets values from the displayed rows.
Thanks if someone can enlight me with those things
The first thing to say is not to use client-side filtering (i.e. fn.dataTableExt.afnFiltering.push) when you are using server-side processing (ie. "bServerSide": true). If you are using server-side processing, the data processing (including the filtering) much be done at the server-side.
So what you need to do is to pass the information about the dates from the client-side to the server, when you make a call to draw the table - then your SQL can filter on the dates and return the data for DataTables to show. The fnServerParam function ( http://datatables.net/ref#fnServerParam ) is ideal for passing in parameters such as these.
Regards,
Allan
I checked the time format but change that but that still did not solve my problem.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<!--
Created using /
Source can be edited via /ilofuv/3/edit
-->
@import "/media/css/demo_page.css";
@import "/media/css/demo_table_jui.css";
Min date:
Max date:
id
Promo
datesubbed
<?php
$username="root";
$password="";
$database="carsubexporter";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * from carsubs2 ORDER BY datesubbed DESC";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
echo "Database Output
";
$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$promoid=mysql_result($result,$i,"promoid");
$datesubbed=mysql_result($result,$i,"datesubbed");
?>
<?php echo $id; ?>
<?php echo $promoid; ?>
<?php echo $datesubbed; ?>
<?php
$i++;
}
?>
var minDateFilter;
var maxDateFilter;
$.fn.dataTableExt.afnFiltering.push(
function( oSettings, aData, iDataIndex ) {
if ( typeof aData._date == 'undefined' ) {
aData._date = new Date(aData[1]).getTime();
}
if ( minDateFilter && !isNaN(minDateFilter) ) {
if ( aData._date < minDateFilter ) {
return false;
}
}
if ( maxDateFilter && !isNaN(maxDateFilter) ) {
if ( aData._date > maxDateFilter ) {
return false;
}
}
return true;
}
);
$(document).ready( function() {
var oTable = $('#example').dataTable( {
"bJQueryUI": true
} );
$( "#datepicker_min" ).datepicker ( {
dateFormat: 'yy-mm-dd',
"onSelect": function(date) {
minDateFilter = new Date(date).getTime();
oTable.fnDraw();
}
} ).keyup( function () {
minDateFilter = new Date(this.value).getTime();
oTable.fnDraw();
} );
$( "#datepicker_max" ).datepicker( {
dateFormat: 'yy-mm-dd',
"onSelect": function(date) {
maxDateFilter = new Date(date).getTime();
oTable.fnDraw();
}
} ).keyup( function () {
maxDateFilter = new Date(this.value).getTime();
oTable.fnDraw();
} );
} );