Server-side processing does not work with columnFilter enabled

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Enable "bServerSide": true, in DataTables initialization.

What is the expected output? What do you see instead?

I expect filters to work correctly, instead they all return 0 results.

What version of the product are you using? On what operating system?

ColumnFilter 1.3.0

Please provide any additional information below.

I am using the example PHP script from the DataTables website for server side 
processing.  Without "bServerSide": true, enabled everything works as expected 
with the columnfilter plugin.  All my text filters, range filters, select 
filters are all perfectly operable.  However due to the need for large datasets 
(20,000+) I would like to use server side processing.  When I set 
"bServerSide": true, any searches return 0 results, and when the search text is 
removed, does not return to the full dataset.

When I disable the columnfilter initialization code, server side processing 
works fine with the standard singular datatables search field.

I have attached a copy of my full datatables/columnfilter initialization code 
along with some jqueryUI slider code I am using to control the range filter.

Original issue reported on code.google.com by gordonrankin82@gmail.com on 4 Oct 2011 at 8:07

Attachments:

• example.js

[GoogleCodeExporter]

Hi,

Could you check your server side page maybe there is a problem? I cannot see 
anything wrong in your code, however column filter works correctly in the 
server side processing mode see 
http://jquery-datatables-column-filter.googlecode.com/svn/trunk/serverSideJSONP.
html where I have even sent the cross-site scripting calls.

Jovan

Original comment by joc...@gmail.com on 5 Oct 2011 at 7:47

• Added labels: Priority-Low
• Removed labels: Priority-Medium

[GoogleCodeExporter]

I have tried with the exact server side code as posted in that example as well 
as my own modified version (to enable multiple keyword filtering)

However, I just tried setting my Range selector to "text" and all of a sudden 
server-side filtering works fine on individual columns.

It turns out it was sending 0~300 (my selected range) with every query, even 
wen trying to filter globally or for a different column.  The parameter for the 
range column would just always get sent.  Therefore...

How do get server side range riltering to work?

Original comment by gordonrankin82@gmail.com on 5 Oct 2011 at 8:11

[GoogleCodeExporter]

If you use range filtering it will send start~end pairs and you will need to 
parse it on the server side. 
Range filtering is not standard DataTables filter option so this is the only 
way to send two values from the plugin to server. column filter expects that 
you parse these ranges and return correct values back.

Original comment by joc...@gmail.com on 5 Oct 2011 at 4:10

[GoogleCodeExporter]

Thanks, I feel so dumb now...

I managed to set up server side number range filtering using the following code 
if it helps anyone.  

    /* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
    {
        if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
        {
            if ( $sWhere == "" )
            {
                $sWhere = "WHERE ";
            }
            else
            {
                $sWhere .= " AND ";
            }
            $columnFilterValue = mysql_real_escape_string($_GET['sSearch_' . $i]);
            // check for values range
            $rangeSeparator = "~";
            if (!empty($rangeSeparator) && strstr($columnFilterValue, $rangeSeparator)) {
                // get min and max

                 $columnFilterRangeMatches =  explode('~', $columnFilterValue);

                // get filter
                if (empty($columnFilterRangeMatches[0]) && empty($columnFilterRangeMatches[1]))
                    $sWhere .= " 0 = 0 ";
                else if (!empty($columnFilterRangeMatches[0]) && !empty($columnFilterRangeMatches[1]))
                    $sWhere .= $aColumns[$i] . " BETWEEN '" . $columnFilterRangeMatches[0] . "' and '" . $columnFilterRangeMatches[1] . "' ";
                else if (empty($columnFilterRangeMatches[0]) && !empty($columnFilterRangeMatches[1]))
                    $sWhere .= $aColumns[$i] . " < '" . $columnFilterRangeMatches[1] . "' ";
                else if (!empty($columnFilterRangeMatches[0]) && empty($columnFilterRangeMatches[1]))
                    $sWhere .= $aColumns[$i] . " > '" . $columnFilterRangeMatches[0] . "' ";
            } else {
                $sWhere .= $aColumns[$i] . " LIKE '%" . $columnFilterValue . "%' ";
            }
        }
    }

I actually also found a similar example in another closed issue here.  But this 
variation should be a bit faster as i'm using explode to split the string 
rather than preg_match.

Anyway, job done! thanks!

Original comment by gordonrankin82@gmail.com on 5 Oct 2011 at 4:56

[GoogleCodeExporter]

I'm glad that it works now.

Regards,
Jovan

Original comment by joc...@gmail.com on 6 Oct 2011 at 8:21

• Changed state: Done
• Added labels: Type-Other
• Removed labels: Type-Defect

[GoogleCodeExporter]

@gordonrankin82@gmail.com Thanks a lot! :-) Faced the same problem and your 
solution worked like a charm!

Original comment by NaeemulH...@gmail.com on 22 Jun 2014 at 12:31