Multiple BETWEEN statements in a WHERE query

Databases
1

I know I run the risk of getting flamed like TV Evangelist on Judgment Day, but my Ox is in a Ditch and I need help ASAP. I have a real estate property table…has about 120 fields. I have a search form written. It searches keywords, Property Type or Sub Type, minimum price, maximum price, square ft, etc…
What I need is to have the search find anything that meets criteria OR return all if they just select any on the drop down. I’m pushing the ‘%’ character if they select any from the list.

Here’s my 1st stab which results in 0 results


select * from properties 
where AdvSubDiv LIKE '%' 
AND PropType = '%' 
AND Sub_Type = '%' 
AND ListPrice BETWEEN '100000' AND '150000' 
AND NumBeds = '%' 
AND NumFBath = '' 
AND SqrFeet BETWEEN '%' AND '%' 
ORDER by SRENumber

variables are being passed by php as $_POST. The example above is an “Any” search…basically leaving the form blank and hitting the submit button…

Any help would be GREATLY appreciated.

2

when the form is submitted, you must inspect each form field to see if nothing is in it, or if the “any” option has been chosen (both of which actions amount to the same thing, right?)

okay, for each form field where the user wants all rows for that field, here’s what you do – omit the corresponding column condition from the WHERE clause

don’t try to use a wildcard or anything – just omit that condition from the WHERE clause

problem solved

:slight_smile:

3

Thanks for that! So cool that you guys are willing to help.

I know beggars can’t be choosers…but I’ll beg any way. :slight_smile: is there any way you could give an example of how those conditional statements would look synactically?

You have already helped tremendously. thanks again

4

me? only in coldfusion, if that’s any help

:slight_smile:

5

OK, my PHP is still rusty, but this should get you started…


$AdvSubDiv = mysql_real_escape_string($_POST['AdvSubDiv']);
$PropType = mysql_real_escape_string($_POST['PropType']);
$Sub_Type = mysql_real_escape_string($_POST['Sub_Type']);
$NumBeds = mysql_real_escape_string($_POST['NumBeds']);
$SqrFeetLo = mysql_real_escape_string($_POST['SqrFeetLo']);
$SqrFeetHi = mysql_real_escape_string($_POST['SqrFeetHi']);
$ListPriceLo = mysql_real_escape_string($_POST['ListPriceLo']);
$ListPriceHi = mysql_real_escape_string($_POST['ListPriceHi']);
$count = 0;
$query = "SELECT * FROM properties"
if ($strlen($AdvSubDiv) > 0) {
 $query = sprintf($query . " WHERE AdvSubDiv LIKE '%s'", '%' . $AdvSubDiv . '%');
 $count++;
}
if ($strlen($PropType) > 0) {
 $query = sprintf($query . IIF($count == 0, " WHERE ", " AND ") . "PropType LIKE '%s'", '%' . $PropType . '%');
 $count++;
}
if ($strlen($Sub_Type) > 0) {
 $query = sprintf($query . IIF($count == 0, " WHERE ", " AND ") . "Sub_Type LIKE '%s'", '%' . $Sub_Type . '%');
 $count++;
}
if ($strlen($NumBeds) > 0) {
 $query = sprintf($query . IIF($count == 0, " WHERE ", " AND ") . "NumBeds LIKE '%s'", '%' . $NumBeds . '%');
 $count++;
}
// need to add additional checks on values here (numeric, Hi > Lo, etc.)
if ($strlen($ListPriceLo) > 0) {
 if ($strlen($ListPriceHi) > 0) {
  $query = sprintf($query . IIF($count == 0, " WHERE ", " AND ") . "ListPrice BETWEEN %d and %d", $ListPriceLo, $ListPriceHi);
  $count++;
 } else {}
  $query = sprintf($query . IIF($count == 0, " WHERE ", " AND ") . "ListPrice >= %d", $ListPriceLo);
  $count++;
 }
} else if  ($strlen($ListPriceHi) > 0) {
  $query = sprintf($query . IIF($count == 0, " WHERE ", " AND ") . "ListPrice <= %d", $ListPriceHi);
  $count++;
}
if ($strlen($SqrFeetLo) > 0) {
 if ($strlen($SqrFeetHi) > 0) {
  $query = sprintf($query . IIF($count == 0, " WHERE ", " AND ") . "SqrFeet BETWEEN %d and %d", $SqrFeetLo, $SqrFeetHi);
  $count++;
 } else {}
  $query = sprintf($query . IIF($count == 0, " WHERE ", " AND ") . "SqrFeet >= %d", $SqrFeetLo);
  $count++;
 }
} else if  ($strlen($SqrFeetHi) > 0) {
  $query = sprintf($query . IIF($count == 0, " WHERE ", " AND ") . "SqrFeet <= %d", $SqrFeetHi);
  $count++;
}
$query = $query . " ORDER BY SRENumber";
mysql_query($query);

// WARNING - this needs tested to ensure it works as expected.  If should return the true value if the passed condition is true, otherwise it returns the false value...
function IIF($condition, $trueValue, $falseValue) {
 return $test ? $trueValue : $falseValue;
}
6

I owe you both a beer! Thanks so much for the help. You’ve both put me back on the right track after I had severely derailed.

Thanks again!

7

dave, i have a little tip for ya

start your WHERE clause off with WHERE 1=1

then every additional condition can simply use AND and you won’t have to test the count each time

if no conditions are appended, then WHERE 1=1 by itself will return all rows (which is what you want when no additional conditions are appended)

8

Just to update everybody, this worked perfectly once it was tailored to suit it’s purpose. Kudos to Dave! I wouldn’t worry about your php skills…that’s what httpd error_log is for, eh?

BTW, this was ported into a customized Drupal mod with just a few hacks to get the pager working on the results list…who says you can’t learn something on a Friday?

9

Yeah, I’ve used 1=1 in the recent past, but old habits die hard. Plus I always hate seeing seemingly pointless code executed.

Josh - glad you got it working…

10

It’s very slick little idea there that I had never thought of…

I do have this one weird tick with it now tho…if someone searches for a keyword that starts with the letter n (e.g. the word new), the SQL statement looks like this:

SELECT * FROM properties WHERE AdvSubDiv LIKE '%0ew%' ORDER BY SRENumber LIMIT 0, 5
0.91

I’ve made sure that it’s escaping characters…but that’s the only thing that’s still stumpin’ me. and this may very well be a PHP issue now and not relevant to these forums.

11

if this is your last issue with it, i can dispel this for you too

WHERE 1=1 is “optimized away” by the database engine prior to execution

so now you have no reason not to use it

:slight_smile: