Custom Range Filtering on Server-Side Data

nik

Hi everyone. Very new here, so please bear with me and I do apologize if this has been asked a hundred times over - I searched the Forums and tried to make sense of it, but I just can't find exactly what I'm looking for and I'm not skilled enough yet to figure it out myself. :)

I would like to do a simple range filter (ie: only show the rows where column 3 (searchVolume) is between 40 and 50) on a server-side JSON table. I have the table pulling the data correctly and everything, and I think I'm even passing in the ranges via $_POST correctly, I'm just having a hard time putting all of the pieces together.

Here is my JS:
[code]
$(document).ready(function() {

var oTable = $('#example').dataTable({
"bServerSide": true,
"bProcessing": true,
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"sAjaxSource": "data/queryTable.php",
"fnServerData": function ( sSource, aoData, fnCallback ) {
/* Add some data to send to the source, and send as 'POST' */
aoData.push( { "name": "min", "value": $('#min').val() } );
aoData.push( { "name": "max", "value": $('#max').val() } );
$.ajax({
"dataType": 'json',
"type": "GET",
"url": "data/queryTable.php",
"data": aoData,
"success": fnCallback
});
}
});

$('#min').change(function() { oTable.fnDraw(); });
$('#max').change(function() { oTable.fnDraw(); });

});
[/code]

And my relevant PHP, written immediately after the "Individual column filtering" section of the server_processing.php example support file:
[code]
if($_POST['min'] != "") {
$sWhere .= " AND searchVolume > '" . $_POST['min'] . "' ";
}

if($_POST['max'] != "") {
$sWhere .= " AND searchVolume < '" . $_POST['max'] . "' ";
}
[/code]

nik

Also, I should mention I am happy to pay for support, however I am getting a 500 error :)

allan

Hi nik,

It looks like you are very close to a working solution with what you've got. Indeed this also exactly what I would suggest. One thing about you SQL, you will probably need brackets around the extra where statement, so it will work correctly with the OR statements in the example code.

The one thing that looks a little worrying however is your 500 error when the script runs. 500 is an internal server error and doesn't say anything about why it might be. It could be an http server error, or perhaps a pup misconfiguration - or how knows what else... The way to find out is to have a look at your server's error log. If you have a *nix server running a apache, the error log is usually at /var/log/apache/error_log . Hopefully that will yield a clue!

Regards,
Allan

nik

Oh, you may have misunderstood..I was getting a 500 error on the donations page while I was trying to donate. :)

Also, I am not quite sure what you mean by the extra brackets. Right now, I'm not really sure if the min/max variables are being sent in, and I'm not sure how to echo out the $sQuery variable, since if I do that it will mess up the JSON response. Any ideas?

nik

Okay, as it turns out, in my code example, the variables are being passed in via GET and not POST. I have the filtering working, but I am having trouble that range filter and the search filter working together, which I suspect has something to do with the way I am concatenating the query string.

allan

Oops - sorry, yes I did misunderstand. Is the 500 error from this page: http://datatables.net/support ? I did actually get your support request earlier on (thank you for the donation :-) ).

What what I was meaning with the where statement is you want something like this:

WHERE ( a LIKE %search% OR b LIKE %search% OR c LIKE %search% ) AND x > min AND x < max

So just sticking brackets around what is already in the source, and then add your extra bit in.

There should be no need to modify how the output of the query then works (unless you need do to so for other bits...) since this is all just SQL logic - simply that the result set will be smaller.

Allan

evan

Nik,

I'm guessing you got this all working correctly but in case you didn't or for anyone else looking at this thread who is still confused, here are some additional notes:

If no data is entered for the search filter then no WHERE clause is included in the query.

To check this you can echo out $sQuery in the server_processing.php file and run it. This is a good way to debug the SQL and not worry that it messes up the JSON return to your javascript.

If no WHERE clause is included in the query then it will fail. So your SQL should start with WHERE.

Also, I combined your two if statements into one and I added $min and $max variables instead of entering the $_GET parameter directly into the SQL. I find this will make it easier to sanitize the data for security purposes later on. Make sure you define these variables somewhere above.
[code]
if($_GET['min'] != "" || $_GET['max'] != "") {
$sWhere .= " WHERE searchVolume > '" . $min . "' AND searchVolume < '" . $max . "' ";
}
[/code]
You'll notice this doesn't allow range filtering and search filtering to work simultaneously. If data is entered for the search filter then a WHERE clause will already be included in the query. In this case you'll want to append an AND to $sWhere like you had in your code.

In order to make the two work together I check to see if $sWhere is blank. If it is then the WHERE clause is used, if not then the AND clause is used.
Here is the code:
[code]
if($sWhere != "" && $_GET['min'] != "" || $sWhere != "" && $_GET['max'] != "") {
$sWhere .= " AND searchVolume > '" . $min . "' AND searchVolume < '" . $max . "' ";
}

elseif($_GET['min'] != "" || $_GET['max'] != "") {
$sWhere .= " WHERE searchVolume > '" . $min . "' AND searchVolume < '" . $max . "' ";
}
[/code]
Remember to have this after the individual column filtering section of server_processing.php.

Another issue I ran into is if a min or a max value is entered but not both. This causes zero results to be returned in the set. To prevent this I set a default min and max value.

Here is the complete code added to server_processing.php right after the individual column filtering section:
[code]
/* Range filtering */

if($_GET['min'] == "")
$min = 0;
else {$min = $_GET['min'];}

if($_GET['max'] == "")
$max = 999999;
else {$max = $_GET['max'];}


if($sWhere != "" && $_GET['min'] != "" || $sWhere != "" && $_GET['max'] != "") {
$sWhere .= " AND searchVolume > '" . $min . "' AND searchVolume < '" . $max . "' ";
}

elseif($_GET['min'] != "" || $_GET['max'] != "") {
$sWhere .= " WHERE searchVolume > '" . $min . "' AND searchVolume < '" . $max . "' ";
}
[/code]
Hope that helps.

allan

Nice one - thanks for that evan :-)

Allan