Individual column filtering issue

Rob

Thanks for such a great jQuery application! I just began using it for a project I'm working on. I haven't used a lot of JavaScript in the past, but decided to adopt jQuery as it seems like it can help speed development and enhance the user experience. Everything with DataTables is working as it should, except for individual column filtering. I think and hope this is a simple oversight on my part.

I've added the code/HTML as per the example (http://www.datatables.net/examples/example_multi_filter.html). I'm using PHP and MySQL with server-side processing. Here is the initialzation code:

// Initialize DataTables
oTable = $('#grid1').dataTable({
"oLanguage": {
"sSearch": "Search all columns:"
},
"bProcessing": true,
"bServerSide": true,
"bStateSave": true,
"sPaginationType": "full_numbers",
"sAjaxSource": 'INCLUDES/grid_test.php',
});

Using FireBug, I can see a page request and the search words are showing up in the appropriate column (sSearch_0 = Test):

bEscapeRegex true
bEscapeRegex_0 true
bEscapeRegex_1 true
bEscapeRegex_2 true
bEscapeRegex_3 true
bEscapeRegex_4 true
bEscapeRegex_5 true
iColumns 6
iDisplayLength 10
iDisplayStart 0
iSortCol_0 0
iSortDir_0 asc
iSortingCols 1
sColumns
sSearch
sSearch_0 Test
sSearch_1
sSearch_2
sSearch_3
sSearch_4
sSearch_5

Unfortunately, the results don't change. After reviewing the code, I don't see where the column data is used in the filtering function. Should I change something within grid_test.php (the "server processing" page) to allow for this (e.g., "if ( mysql_real_escape_string( $_GET['sSearch_0'] ) != "" )" followed by the appropriate code)? I hope this makes sense. I imagine there's a more efficient way to cycle through the search fields and to use them in conjunction with the global search (sSearch) to ensure there's no conflict. An example of some sort would be helpful and appreciated.

Thanks,

Rob

allan

Hi Rob,

I presume that you are using the PHP script from my example? Because my example doesn't make use of column filtering, I haven't implemented this in the server-side script - it's left as "an exercise for the reader" ;-)

It should be fairly trivial to do, although how it is actually implemented is somewhat dependant on the database (as all server-side processing is!). You can see from the variables that you posted that "sSearch_0" is "Test", with that you need to match what column 0 is onto your database an then add something like this to your where expression:

WHERE col0 LIKE '%${sSearch_0}%'

Note that you can use the "sName" for each column in order to help you with this - you can see the "sColumns" is blank in this case, but if you set a name for each column, that information will be posted to the server as well - this would allow you to give a mapping name between the database column and the DataTable output.

Hope this helps,
Allan

Rob

Thanks for the feedback, Allan! I really appreciate it.

I added the column filtering code to the server-side script. It was a fun exercise. ;) The code is pasted below, in case someone else may benefit from it. If someone reading this has written more efficient code, let me know.

/* Individual Column Filtering */
for ( $i=0 ; $i<$_GET['iColumns'] ; $i++ )
{
if ( mysql_real_escape_string( $_GET['sSearch_'.$i] ) != "" )
{
$sWhere .= " AND ".fnColumnToField($i)." LIKE '%".mysql_real_escape_string( $_GET['sSearch_'.$i] )."%'";
}
}

Note: If you're using the fnSetFilteringDelay plug-in, don't forget to incorporate individual column searches (Which I haven't done yet - if you have, let me know).

Rob

allan

Hi Rob,

Great to hear you got it sorted - and thanks for sharing your code :-). I'm sure others will find it most useful!

Regards,
Allan