SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Multiple BETWEEN statements in a WHERE query

    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
    Code MySQL:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. :-) 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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by josh.sheffield View Post
    is there any way you could give an example of how those conditional statements would look synactically?
    me? only in coldfusion, if that's any help

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,200
    Mentioned
    105 Post(s)
    Tagged
    1 Thread(s)
    OK, my PHP is still rusty, but this should get you started...

    Code PHP:
    $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;
    }
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  6. #6
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #9
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,200
    Mentioned
    105 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by r937 View Post
    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)
    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...
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  10. #10
    SitePoint Member
    Join Date
    Oct 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:
    Code MySQL:
    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. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DaveMaxwell View Post
    Plus I always hate seeing seemingly pointless code executed.
    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

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •