SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Dec 2004
    Location
    Little Rock
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What's wrong with this query?

    I'm working on a real estate search form. The user can select any number of areas in which to search (from a multiple selection box) as well as define other search parameters like price, sq ft, etc. The query works if I either remove the Area portion or remove everything but the Area portion. As the query is now, the results are correct for all of the search parameters except the Area array with is completely ignored. I know the values for the array are being passed to the query because I can echo the array variable and the correct array is returned.

    Here's the query:
    Code:
    SELECT *
    FROM ListingsResidential
    WHERE 
       1=1 OR Area IN (choosearea) // allows for NO selection
       AND Price BETWEEN pricemin and pricemax 
       AND Beds >= bed 
       AND FullBaths >= bath 
       AND SqFt >= sqftmin
    ORDER BY Price ASC
    What am I missing?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    parentheses problem

    ANDs take precedence over ORs

    you have this --

    ... WHERE 1=1 OR a AND b AND c AND d AND e

    which is evaluated as though there were parentheses like this --

    ... WHERE ( 1=1 ) OR ( a AND b AND c AND d AND e )

    that "where 1=1 trick" works best if there are only ANDs after it

    otherwise, you would have to have --

    ... WHERE ( 1=1 OR a ) AND b AND c AND d AND e

    which doesn't make sense since anything OR'ed with 1=1 will always be true

    since it appears that you are using a scripting language, you should generate the sql in such a manner that it comes out either as --

    ... WHERE 1=1 AND b AND c AND d AND e

    or else as --

    ... WHERE a AND b AND c AND d AND e
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Dec 2004
    Location
    Little Rock
    Posts
    24
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    parentheses problem

    ANDs take precedence over ORs

    since it appears that you are using a scripting language, you should generate the sql in such a manner that it comes out either as --

    ... WHERE 1=1 AND b AND c AND d AND e

    or else as --

    ... WHERE a AND b AND c AND d AND e
    I didn't realize that ANDs take precedence. I'll try modifying my code so I only use ANDs. Thanks for your reply!


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
  •