Replies

• nik Posts: 6Questions: 0Answers: 0

  August 2010

  Also, I should mention I am happy to pay for support, however I am getting a 500 error :)
• allan Posts: 63,180Questions: 1Answers: 10,411 Site admin

  August 2010

  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 Posts: 6Questions: 0Answers: 0

  August 2010 edited August 2010

  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 Posts: 6Questions: 0Answers: 0

  August 2010

  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 Posts: 63,180Questions: 1Answers: 10,411 Site admin

  August 2010

  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 Posts: 2Questions: 0Answers: 0

  September 2010 edited September 2010

  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 Posts: 63,180Questions: 1Answers: 10,411 Site admin

  September 2010

  Nice one - thanks for that evan :-)
  
  Allan