[SOLVED] Problem with Searching a table

[darknessmdk]

So I'm making a search that looks for houses that are between two prices set by the user, one is a minimum price and the other one the max price. there is only one price in my database right now and its 269,000 I get results returned only when the minimum price is 100,000 and the max is 999,999, if the max price goes up or the min price goes down it doesnt work. Here's the code, Its my first time searching with a range like this, I cant figure it out, anyone come across this before?

 

if($town == "") {
	$town = '%';
	}
	if($proptype == "") {
	$proptype = '%';
	}

     $result = mysql_query("SELECT hvtid, paddress, town, state, mls, remarks, proptype, tbrooms, listprice, lotdim, annualt FROM properties WHERE hvtid = '$hvtid' OR (proptype LIKE '%$proptype%' AND town LIKE '%$town%' AND state = '$state' AND listprice >= '$mprice' AND listprice <= '$maxprice' AND areaint >= '$areaint' OR proptype = '$proptype' AND zip = '$zip' AND listprice >= '$mprice' AND listprice <= '$maxprice') AND status = 'Active'");

	$countrows = mysql_num_rows($result);

	 if($countrows == NULL) {
	echo ("Your search found no results.");

[effigy]

- What data type is listprice in your table?

- What does your SQL statement look like after the variables have been interpolated?

- You may want to use BETWEEN.

 

[darknessmdk]

listprice is text in my table

and I tried between and got the same results

[effigy]

Why are you storing numeric data in a text column? MySQL has to make a conversion, which gives you the unexpected results.

 

I'm not sure if this is the best way--other than changing the database--but it should work if a comma is the only non-digit character in the field.

 

CAST(REPLACE(listprice, ',', '') AS UNSIGNED) BETWEEN 1 AND 100000

[darknessmdk]

I made the field text because there is the possibility of N/A being inserted into the field.

[darknessmdk]

Can you use BETWEEN with a field that contains text and intigers? 

[effigy]

See BETWEEN.

[darknessmdk]

Thanks I got it working now!