MySQL between Problem

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
sulen
Forum Commoner
Posts: 79
Joined: Wed Jul 09, 2003 4:55 pm
Location: los angeles
Contact:

MySQL between Problem

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: MySQL between Problem

Post 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";
?>
User avatar
sulen
Forum Commoner
Posts: 79
Joined: Wed Jul 09, 2003 4:55 pm
Location: los angeles
Contact:

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

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