Search min price or max price problem

[tha_ripmasta]

Hello,

I'm building a car site. And i'm trying to search for a car between min price (. example $10,000) and max price (. example $20,000).... which should return cars only with a price between or equal to the min and max search price.

But for some reason it doesn't work very wel. Can any one help me!!!!

The price field in the dB is price (varchar 20)

Here is the form:

<table width="100%" border="0" cellspacing="0" cellpadding="2">
		  <tr>
		    <td colspan="2" valign="top">Price Range </td>
	      </tr>
		  <tr>
			<td width="21%" valign="top">minimum $:<br />
		    <input name="min_price" type="text" id="min_price" value=""/></td>
			<td width="79%" valign="top">maximum $:<br />

	      <input name="max_price" type="text" id="max_price" value="" />
	      <br />
	    <br /></td>
	  </tr>
	</table>

Here is my code:

$Searchstartyear=$_POST["start_year"]; // Get the search tearm for make
	if ($Searchstartyear == "") $Searchstartyear=$_GET["start_year"]; // 

$Searchendyear=$_POST["end_year"]; // Get the search tearm for make
if ($Searchendyear == "") $Searchendyear=$_GET["end_year"]; 

$Searchminprice=$_POST["min_price"]; // Get the search tearm for make
if ($Searchminprice == "") $Searchminprice=$_GET["min_price"]; 

$Searchmaxprice=$_POST["max_price"]; // Get the search tearm for make
if ($Searchmaxprice == "") $Searchmaxprice=$_GET["max_price"]; 



if ($Searchminprice == '' && $Searchmaxprice == ''){	
//Get the number of results
$SearchResult=mysql_query("Select * from car_listings where display_list = '1'  AND (year BETWEEN '$Searchstartyear' AND '$Searchendyear')") or die(mysql_error());
$NumberOfResults=mysql_num_rows($SearchResult); 


} else{

//Get the number of results
$SearchResult=mysql_query("Select * from car_listings where display_list = '1'  AND (price >= '" . $Searchminprice . "' AND price <= '" . $Searchmaxprice . "') OR (price >= '" . $Searchminprice . "' OR price <= '" . $Searchmaxprice . "') AND (year BETWEEN '$Searchstartyear' AND '$Searchendyear') ") or die(mysql_error());
$NumberOfResults=mysql_num_rows($SearchResult); 


}

I don't get the results i'm looking for, myabe someone can help me out with my code.
Thanks in advance

[sid]

i think the 2nd query needs some revamping:

"SELECT * 
FROM car_listings 
WHERE display_list = '1'  

    AND price >= '" . $Searchminprice . "' 
    AND price <= '" . $Searchmaxprice . "'
    AND (year BETWEEN '$Searchstartyear' AND '$Searchendyear');"

[tha_ripmasta]

thanks for your quick responds.

But the results i'm getting are still the same.

I got two cars in my database, one car is price at $ 15,000 and the other car is price at $8,000.

When I type $10,000 in the minimum field of my form, I get only the car for $ 15,000.

But if i type minimum $ 7,000 in the minimum field, I get no results

And if I type $10,000 in the maximum field, I get both cars, which should only show the car for $8,000

Please what can be the problem... is it that i'm using varchar for my pricefield or something wrong with my query...

Thnx in advance

[laserlight]

I think the problem lies with your column datatype. VARCHAR means that the comparison is done lexicographically, so "10000" < "7000".

[tha_ripmasta]

Ok, laserlight, I understand what you mean.

So which datatype will I ave to use?????

[sid]

INTEGER would be appropriate... or BIGINT, if you go for the high-end car-market ;-)
actually DOUBLE might be even better, since it allows you to use floating-point numbers as well.

http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

[laserlight]

Use an integer type, or a decimal type that does not rely on binary floating point. Chances are you need to store cents, and with binary there's a (small) chance that floating point inaccuracy might result in inaccurate results in computations.

[tha_ripmasta]

thanks alot for the help, I will try decimal or double later tonight, and let you'll know about the resultsd