Help with a array query

[pp4sale]

Basically what I have is a price field where prices of properties have been entered. At the moment in the min and max fields it has all the prices that have been entered and I am trying to tidy this up by having a query that will pull out properties with prices between two values ( min and max).

From the code below i get '10' in the min price box and 100000 in the max price box but no drop down. I deleted some old code: if(!empty($POST['MinPrice']))
{
$MinPrice = $POST['MinPrice'];
}etc

and left with:

// Variables
$prices = array();
$MinPrice = 10;
$MaxPrice = 100000;

// Check for the post values (re: your post above)
if(isset($POST['MinPrice']) && is_numeric($POST['MinPrice'])) {
$MinPrice = intval($POST['MinPrice']);
}
if(isset($POST['MaxPrice']) && is_numeric($POST['MaxPrice'])) {
$MaxPrice = intval($POST['MaxPrice']);
}

// Get the values from the database
$q1 = "SELECT Price FROM re_listings WHERE Price BETWEEN '$MinPrice' AND '$MaxPrice'";
$r1 = mysql_query($q1) or die(mysql_error());
if(mysql_num_rows($r1) > 0) {
while($a1 = mysql_fetch_array($r1)) {
$prices[] .= $a1['Price'];
}
}
mysql_free_result($r1);

// Generate the HTML for the dropdown
$menu = '<select name="price">';
foreach ($prices as $price)
{
$menu .= "\n\t".'<option value="'.$price.'">'.$price.'</option>';
}
$menu .= "\n".'</select>';

// Output the HTML
echo $menu;

If i change the code in the html area from <td>Price:</td>
<td width="66">
<?=$MinPrice?>
</td>
</tr>
to
<td>Price:</td>
<td width="66">
<?=$menu?>
</td>
</tr>

I get a drop down box but it's not pulling the right data out. i get numbers from 44 to around 90,000.00 in a random order.

Any body got any ideas ... sorry it was so long winded!!!!!
😕

[NogDog]

As far the "right data" I'm not sure since it sounds like the values are between the min and max values you specify. With regards to the "random order", you could just add an "ORDER BY Price" clause to your query to sort them in ascending order, or add a "DESC" to the end of the clause if you want them in descending order.

[pp4sale]

Sorry forgot to say, it doesn't select properties that are between the prices either though. it pulls all the prices out?

[NogDog]

What type of field is Price in the DB?

[pp4sale]

Thanks for your reply. It's a float(15,2)

[NogDog]

Not sure if it should matter, but you might try removing the quotes around the values in the BETWEEN clause, to make sure there is not some confusion between comparing floats with strings.

[pp4sale]

Thanks, but still doesn't work.

What should this code do? All I get are again all the prices between the two values that have been entered.

When all I really want is to tidy this up and have the ability for users to select prices between two values eg: 0 - 100,000 or 100,000 - 200,000

Is that what the code shoudl be doing?