Page 1 of 1

MySQL between Problem

Posted: Fri Apr 07, 2006 5:34 pm
by sulen
I am using this query for an advanced search on a page with many parameters

Code: Select all

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

Code: Select all

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

Re: MySQL between Problem

Posted: Fri Apr 07, 2006 6:01 pm
by RobertGonzalez
sulen wrote:

Code: Select all

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...

Code: Select all

<?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";
?>

Posted: Fri Apr 07, 2006 6:08 pm
by sulen
I tried this

Code: Select all

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.

Posted: Fri Apr 07, 2006 6:13 pm
by RobertGonzalez
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.