problem with column filters after using fnFilter

jimv

I'm using DataTables on our website at http://registrar.wisc.edu/deans_list_working.htm. The page displays a list of students who have received awards (honor/deans list) at UW-Madison, what award they have recieved, and what school/college gave them the award. The data comes from a MySQL db via a PHP server side script, and is then displayed by DataTables.

Originally, the school/college was part of the table, but that made the table too wide, and we decided to move it to a drop down above the table. I'm using the fnFilter function of the API to filter the hidden school/college column in the table, so that it only displays the selected school/college.

Inside my Document.Ready, I have the following code to filter the table based on the drop down (sel_college) selection:
[code]
$("#sel_college").change(function()
{
//alert($(this).val());//debug
oTable.fnFilter($(this).val());
});
[/code]

The above code works fine to filter the table based on the school/college drop down.

After I started using the fnFilter function, I have run into some strange behavior. The page has column filtering at the bottom of each column. The column filter originally worked fine; after adding in fnFilter, the column filtering works sometimes:
- When I first load the page, the column filter works.
- If I select a school/college from the drop down, the column filtering no longer works.
- If I put something into the "search all columns", then the column filter starts working again, until I select a different school/college.

Also inside my document ready , I have the following DataTable information:
[code]
oTable = $('#deans_list').dataTable( {
"bStateSave": true,
"bProcessing": true,
"bServerSide": true,
"aaSorting": [[0,'asc'], [1,'asc']],
"sPaginationType": "full_numbers",
"bAutoWidth": false,
"bSortClasses": true,
"oLanguage": {
"sSearch": "Search all columns:"
},
"aoColumns": [ {"sWidth": "151PX" },
{ "bSortable": false, "sWidth": "151PX" },
{ "bSortable": false, "sWidth": "151PX" },
{"sWidth": "250PX" },
null,
{ "sType": "numeric" },
{ "bVisible": false },
{"sWidth": "300PX" }],
"sAjaxSource": '/honors.php'
} );
[/code]

Any suggestions on what is causing the strange behavior for column filtering?
Am I using fnFilter incorrectly?
Please let me know if you need more information
Thanks
Jim

allan

Hi Jim,

I think you might have two different issues here. Firstly your point about the select menu and column search not working at the same time - it appears that DataTables is correctly sending the information to be read from the database in it's parameters, for example:

[code]
sSearch College of Engineering
sSearch_0
sSearch_1 Syed
...
[/code]
However, the filtering isn't being applied as you would expect - therefore I suspect that there is problem with your SQL. Perhaps printing out your SQL statement to see if it is what you expect for this situation would help.

Secondly your use of fnFilter - it is correct what you have done, however, you need to be aware that DataTables uses that filter internally as well! Therefore once you start typing into the text box, it will blow away anything that you have put into the fnFilter from before!

The way around this is that you need to combine the two search strings. Since you are using server-side processing, I would be very tempted to send the school as a custom parameter ( http://datatables.net/examples/server_side/custom_vars.html ) rather than as part of DataTables internal filtering - the only downside that I can think of off the top of my head is that the information element wouldn't reflect this - but you could update that as well, if this was important to you.

Hope this helps,
Allan

jimv

Alan,
Sorry, I think I understand what you are suggesting, but not how to utilize fnServerData to do it. I looked through the example code, but not sure I'm getting the picture.

Passing additional data to the php app so it can add it into the sql query makes sense, it was what I first though of, before I found fnFilter in the API.

But, fnServerData looks like something you use when you initialize the datatable.
What I need to do is to pass in the additional parameterns whenever the drop down is changed, as shown below.
[code]
$("#sel_college").change(function()
{
//alert($(this).val());//debug
oTable.fnFilter($(this).val());
});
[/code]

How would I put the fnServerData inside of that?
Thanks for the help, really appreciate it!
Jim

allan

Hi Jim,

Taking my code as the basis for this example:

[code]
$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../examples_support/server_processing.php",
"fnServerData": function ( sSource, aoData, fnCallback ) {
/* Add some extra data to the sender */
aoData.push( { "name": "school", "value": $('#sel_college').val() } );
$.getJSON( sSource, aoData, function (json) {
fnCallback(json)
} );
}
} );
} );
[/code]
Does that clarify it?

Regards,
Allan

jimv

Alan,
Sorry, I'm still having problems with this.
I need to change the table filter when someone changes a selection drop down.

How would I trigger fnServerData off of the $("#sel_college").change(function() , rather then the document ready function?

Thanks for all your help,
Jim

allan

Something like:

[code]
var oTable;
$(document).ready(function() {
oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "../examples_support/server_processing.php",
"fnServerData": function ( sSource, aoData, fnCallback ) {
/* Add some extra data to the sender */
aoData.push( { "name": "school", "value": $('#sel_college').val() } );
$.getJSON( sSource, aoData, function (json) {
fnCallback(json)
} );
}
} );

$('select').change( function () {
oTable.fnFilter( $(this).val() );
} );
} );
[/code]
Not tested - but the principle is to use the oTable variable to reference the DataTable and access it's API functions.

Allan