I've got a web site in beta testing right now. I've come across a bug
that is turning up some strange results.

I'm doing a query "select SQL_CALC_FOUND_ROWS
ml,City,ListPrice,TotalSQFT,NumberofBedrooms,Total Bathrooms,NumberofPhotos
from residential where City like "McMinnville" OR City like "Yamhill"
and ListPrice >= 1000 and ListPrice <= 500000 and NumberofBedrooms >= 1
AND TotalBathrooms >= 1 order by ListPrice desc LIMIT 10,10"

But the return list is spitting back results that are out size of the
range specified by listprice.

You can view the qery and result here:

http://www.oregon-properties.net/cgi...re=1&sort=desc

Any ideas where I may be going wrong?

salvador wrote:


You have a precedence issue. The ANDs are evaluated before ORs. Your query
is effectively this:

select SQL_CALC_FOUND_ROWS
ml,City,ListPrice,TotalSQFT,NumberofBedrooms,Total Bathrooms,NumberofPhotos
from residential where City like "McMinnville" OR (City like "Yamhill" and
ListPrice >= 1000 and ListPrice <= 500000 and NumberofBedrooms >= 1 AND
TotalBathrooms >= 1) order by ListPrice desc LIMIT 10,10"

So if the City is McMinnville it will match regardless.

When you probably want:

select SQL_CALC_FOUND_ROWS
ml,City,ListPrice,TotalSQFT,NumberofBedrooms,Total Bathrooms,NumberofPhotos
from residential where (City like "McMinnville" OR City like "Yamhill") and
ListPrice >= 1000 and ListPrice <= 500000 and NumberofBedrooms >= 1 AND
TotalBathrooms >= 1) order by ListPrice desc LIMIT 10,10"

Another issue is you are using like with no wildcard, you may as well use =
instead.


--
Brian Wakem
Email: http://homepage.ntlworld.com/b.wakem/myemail.png

Brian Wakem wrote:
Yep. That was the problem.

The data is set up so that a user can also select "all cities", in
which case the wildcard is used. I suppose I should rewrite the output
from the conditional to be a little more graceful.

Thanks a bunch.