SELECT * FROM homes WHERE home_cty='$cy_id' AND home_city='$cty_id' AND home_status='$home_status' AND home_ptype = '$home_ptype1' OR home_ptype = '$home_ptype2' OR home_ptype = '$home_ptype3' OR home_ptype = '$home_ptype4' AND home_bed BETWEEN '$beds_from' AND '$beds_to' AND home_bth BETWEEN '$baths_from' AND '$baths_to' AND home_value BETWEEN '$from_price' AND '$to_price' order by home_id
The query seems to be working as far as this part of the query goes
SELECT * FROM homes WHERE home_cty='$cy_id' AND home_city='$cty_id' AND home_status='$home_status' AND home_ptype = '$home_ptype1' OR home_ptype = '$home_ptype2' OR home_ptype = '$home_ptype3' OR home_ptype = '$home_ptype4'
but then when the BETWEEN part of the query starts it doesnt narrow down the results based on those parameters. Any help on this will be appreciated. Thanks
SELECT * FROM homes WHERE home_cty='$cy_id' AND home_city='$cty_id' AND home_status='$home_status' AND home_ptype = '$home_ptype1' OR home_ptype = '$home_ptype2' OR home_ptype = '$home_ptype3' OR home_ptype = '$home_ptype4' AND home_bed BETWEEN '$beds_from' AND '$beds_to' AND home_bth BETWEEN '$baths_from' AND '$baths_to' AND home_value BETWEEN '$from_price' AND '$to_price' order by home_id
I think the reason you are getting this is because of how MySQL handles BETWEEN. Since you have three different fields you are using between on I would bet your query is returning a result set that satisfies at least one of the BETWEENS. This is probably not going to change anything, but try this...
<?php
$sql = "SELECT *
FROM homes
WHERE home_cty = '$cy_id'
AND home_city = '$cty_id'
AND home_status = '$home_status'
AND (home_ptype = '$home_ptype1'
OR home_ptype = '$home_ptype2'
OR home_ptype = '$home_ptype3'
OR home_ptype = '$home_ptype4')
AND (home_bed BETWEEN '$beds_from' AND '$beds_to')
AND (home_bth BETWEEN '$baths_from' AND '$baths_to')
AND (home_value BETWEEN '$from_price' AND '$to_price')
ORDER BY home_id";
?>
SELECT * FROM homes WHERE home_cty = 1 AND home_city = 11 AND home_status = 1 AND (home_ptype = 'RSFR' OR home_ptype = '' OR home_ptype = '' OR home_ptype = '') AND (home_bed BETWEEN 1 AND 7) AND (home_bth BETWEEN 1 AND 4) AND (home_value BETWEEN 100000 AND 600000) ORDER BY home_id
This is not returning anything when it should return 2 rows.
Sorry for assuming, I somehow thought I saw a MySQL in your post. If you are using MySQL you might want to look into how your version of MySQL handles AND, OR and BETWEENS and in what order. If you are using another RDBMS you should refer to their documentation. If that doesn't help, I say use process of elimination. Write the simplest of queries and slowly add one parameter to it until it breaks. Then you can locate your culprit.