What's wrong with this query?

[xentrino]

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:

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?

[r937]

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

[xentrino]

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!