undefined value of ranges in the server side mode

[GoogleCodeExporter]

What steps will reproduce the problem?
1. add numeric range column filter

What is the expected output?
 $_POST['sSearch_7']: '0~9999'

What do you see instead?
 $_POST['sSearch_7']: 'undefined~undefined'

What version of the product are you using? On what operating system?
1. jQuery 1.6.1
2. jquery.dataTables 1.8.0
3. Ubuntu 11.04 x64
4. Firefox 5

I change code at line 305 to this:

for (j = 0; j < aiCustomSearch_Indexes.length; j++) {
  var index = aiCustomSearch_Indexes[j];
  var tablePrefix = oTable.attr("id");
  var fnSearch_ = function () {
    return $("#" + tablePrefix + "_range_from_" + index).val() + properties.sRangeSeparator + $("#" + tablePrefix + "_range_to_" + index).val()
  }
  afnSearch_.push(fnSearch_);
}

Original issue reported on code.google.com by riu...@gmail.com on 23 Jun 2011 at 1:32

[GoogleCodeExporter]

I accidentally duplicated this bug report, but ran into a separate issue. My 
duplicate report with separate issue is Issue 26. I do not see how your or my 
solution yields a functioning filter as long as there are more than one number 
range. Were you able to resolve this?

Original comment by g.duerrm...@gmail.com on 1 Aug 2011 at 1:04

[GoogleCodeExporter]

I use only number range + date range, try this:

@@ -304,8 +305,8 @@

             for (j = 0; j < aiCustomSearch_Indexes.length; j++) {
                 var index = aiCustomSearch_Indexes[j];
-                var fnSearch_ = function () {
-                    return $("#range_from_" + index).val() + 
properties.sRangeSeparator + $("#range_to_" + index).val()
+                               var fnSearch_ = function (fieldIndex) {
+                    return $("#" + oTable.attr("id") + "_range_from_" + 
fieldIndex).val() + properties.sRangeSeparator + $("#" + oTable.attr("id") + 
"_range_to_" + fieldIndex).val();
                 }
                 afnSearch_.push(fnSearch_);
             }
@@ -321,7 +322,7 @@

                         for (k = 0; k < aoData.length; k++) {
                             if (aoData[k].name == "sSearch_" + index)
-                                aoData[k].value = afnSearch_[j]();
+                                aoData[k].value = afnSearch_[j](index);
                         }
                     }
                     aoData.push({ "name": "sRangeSeparator", "value": properties.sRangeSeparator });

Original comment by riu...@gmail.com on 1 Aug 2011 at 3:57

[GoogleCodeExporter]

Thank you! That is _infinitely_ better than the eval() solution I came up with!

Original comment by g.duerrm...@gmail.com on 1 Aug 2011 at 5:32

[GoogleCodeExporter]

i am sorry to say but this did not fix my issue with server side range 
filtering.  I have:

            for (j = 0; j < aiCustomSearch_Indexes.length; j++) {
                var index = aiCustomSearch_Indexes[j];
            var fnSearch_ = function (fieldIndex) {
                    return $("#" + oTable.attr("id") + "_range_from_" + fieldIndex).val() + properties.sRangeSeparator + $("#" + oTable.attr("id") + "_range_to_" + fieldIndex).val();
                }
                afnSearch_.push(fnSearch_);
            }
and

for (k = 0; k < aoData.length; k++) {
     if (aoData[k].name == "sSearch_" + index) {
          oData[k].value = afnSearch_[j](index);
      }
}

did i forget something?  The database field i am trying to sort is a float, 
does that matter?

Do i need to define the min and max in the html?

Original comment by a...@schrafelpaper.com on 12 Aug 2011 at 1:06

[GoogleCodeExporter]

What you get from this script on the server side?

Original comment by riu...@gmail.com on 12 Aug 2011 at 2:52

[GoogleCodeExporter]

sRangeSeparator ~
sSearch 
sSearch_0   
sSearch_1   
sSearch_2   
sSearch_3   undefined~undefined
sSearch_4   
sSortDir_0  asc

is what firebug shows as parameters sent to the server when i begin typing 
something

Original comment by a...@schrafelpaper.com on 12 Aug 2011 at 3:57

[GoogleCodeExporter]

sorry, i looked at my code some more and found a mistake.
The parameters are now being sent correctly, but i am still not getting any 
records when i type anything i get no matching records.

sRangeSeparator ~
sSearch 
sSearch_0   
sSearch_1   
sSearch_2   
sSearch_3   1.1~9.9
sSearch_4   
sSortDir_0  asc

these are the parameters now being sent.

Original comment by a...@schrafelpaper.com on 12 Aug 2011 at 4:28

[GoogleCodeExporter]

You can modify your server-side scripts.

jQuery.DataTables server-side example 
(http://www.datatables.net/release-datatables/examples/server_side/server_side.h
tml) don't work with ranges.
For example, lines from 88 to 106: 
https://github.com/riuson/eaaphp2/blob/313491b1bb9a8d997dc586b581e58eda8b6b03b1/
classes/datatables_common.php

Original comment by riu...@gmail.com on 12 Aug 2011 at 5:50

[GoogleCodeExporter]

Thank you for your suggestion.  I see exactly what you are talking about.

Do you have a simple example of where to put that in my server side script to 
get range filtering to work correctly?  I tried to follow along with your 
project on git hub, but it is very sophisticated.

Original comment by a...@schrafelpaper.com on 12 Aug 2011 at 3:07

[GoogleCodeExporter]

Vanilla server-side script from 
http://www.datatables.net/release-datatables/examples/server_side/server_side.ht
ml

Modified with this:

--- vanilla.php 2011-08-12 21:18:34 +0500
+++ modified.php        2011-08-12 21:29:28 +0500
@@ -107,7 +107,24 @@
                        {
                                $sWhere .= " AND ";
                        }
-                       $sWhere .= $aColumns[$i]." LIKE 
'%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
+                       $columnFilterValue = 
$this->registry['db']->escape($_POST['sSearch_' . $i]);
+                       // check for values range
+                       $rangeSeparator = "~";
+                       if (!empty($rangeSeparator) && 
strstr($columnFilterValue, $rangeSeparator)) {
+                               // get min and max
+                               preg_match("/(.*)\~(.*)/", $columnFilterValue, 
$columnFilterRangeMatches);
+                               // get filter
+                               if (empty($columnFilterRangeMatches[1]) && 
empty($columnFilterRangeMatches[2]))
+                                       $sWhere .= " 0 = 0 ";
+                               else if (!empty($columnFilterRangeMatches[1]) 
&& !empty($columnFilterRangeMatches[2]))
+                                       $sWhere .= $aColumns[$i] . " BETWEEN '" 
. $columnFilterRangeMatches[1] . "' and '" . $columnFilterRangeMatches[2] . "' 
";
+                               else if (empty($columnFilterRangeMatches[1]) && 
!empty($columnFilterRangeMatches[2]))
+                                       $sWhere .= $aColumns[$i] . " < '" . 
$columnFilterRangeMatches[2] . "' ";
+                               else if (!empty($columnFilterRangeMatches[1]) 
&& empty($columnFilterRangeMatches[2]))
+                                       $sWhere .= $aColumns[$i] . " > '" . 
$columnFilterRangeMatches[1] . "' ";
+                       } else {
+                               $sWhere .= $aColumns[$i] . " LIKE '%" . 
$columnFilterValue . "%' ";
+                       }
                }
        }

Original comment by riu...@gmail.com on 12 Aug 2011 at 3:31

Attachments:

• vanilla.zip
• modified.zip

[GoogleCodeExporter]

Thank you so much for this explanation.  I had the error:

Using $this when not in object context in 
/examples/server_side/scripts/modified.php on line 109

but i fixed it with the following line and everything works!  Thank you for 
your help!

                        }
                       $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
-                       $columnFilterValue = 
$this->registry['db']->escape($_POST['sSearch_' . $i]);
+ $columnFilterValue = mysql_real_escape_string($_GET['sSearch_' . $i]);
                       // check for values range
                       $rangeSeparator = "~";

Original comment by a...@schrafelpaper.com on 12 Aug 2011 at 5:14

[GoogleCodeExporter]

Hi,

Could you please try it again with verison 1.2.3?

Thanks,
Jovan

Original comment by joc...@gmail.com on 24 Sep 2011 at 11:28

• Changed state: Started

[GoogleCodeExporter]

Please reopen this issue if it happens again with the latest version of code.

Thanks,
Jovan

Original comment by joc...@gmail.com on 25 Sep 2011 at 7:00

• Changed title: undefined value of ranges in the server side mode
• Changed state: Fixed
• Added labels: Priority-High
• Removed labels: Priority-Medium

[GoogleCodeExporter]

Do we still need to modify the server processing script from datatables?

I'm still not getting it to work, although it does go a lot further than it did 
toward working.

Original comment by dncha...@gmail.com on 26 Sep 2011 at 4:37

[GoogleCodeExporter]

I used the modified server processing PHP along with the one line change on 
comment 11 and it is now working.

Original comment by dncha...@gmail.com on 26 Sep 2011 at 5:08