CODEWITHSUNDEEP
phpmysql
user1823053
user1823053

Reputation: 69

Difficulty in showing data in ascending order from MySQL

I am using ORDER BY Lead_Price as below-

$query = "SELECT * FROM properties WHERE Type='For Sale' AND Live = 'yes' ORDER BY Lead_Price";

I then use the data in a while loop. the Lead_Price data is like £950 per week, £1050 per week, £900 per week, £400 per week.

When I display the prices on a page, the £1050 price appears first and the other 3 are in ascending order. Any ideas on why £1050 isnt at the bottom as I want to show them in ascending price order. Below is other code. Thanks.

while($row = mysql_fetch_array($result)){

$propertylink = str_replace(' ', '-', $row['Property_Name']);

echo "<div class=villas-i><a href=villas/".$propertylink.">";

echo "<img src=images/villas/".$propertylink."/Image-1.jpg width=240 height=155 border=0></a>"; 

echo "<div class=villa-name><h3>".$row['Property_Name']."</h3></div>";
echo "<div><h4>&nbsp;&nbsp;&nbsp;&nbsp;Sleeps: ".$row['Sleeps']."</h4></div>";
echo "<div><h4>Bedrooms: ".$row['Bedrooms']."</h4></div>";
echo "<div><h4>Bathrooms: ".$row['Bathrooms']."</h4></div>"; 
echo "<p>".$row['Property_Short_Description']."</p>"; 
echo "<div class=from>".$row['Lead_Price']."</div>"; 
echo "<div><a href=villas/".$propertylink."><img src=images/more-info.gif border=0></a></div></div>"; 

}

Upvotes: 0

Views: 116

Answers (2)

Piotr Pasich
Piotr Pasich

Reputation: 2639

This problem occures because, probably, you store the lead_price not as a integer or float (better is a integer option), but as a string. And it's compared as a string - so by char by char.

The best solution - change the column type to integer and store there a price * 100 (when you've got £1050,50 you should store 105050 in database (all programming langages where you're using floats have a huge problem with rounding prices), and the currency in a new column.

Or, not so good solution, ORDER BY CAST(Lead_Price AS DECIMAL(10,2)); , but it doesn't perform well.

Upvotes: 3

softsdev
softsdev

Reputation: 1509

$query = "SELECT * FROM properties WHERE Type='For Sale' 
AND Live = 'yes' ORDER BY CAST(Lead_Price AS DECIMAL(10,2))";

Please use CAST() function to make varchar price to make order by

Upvotes: 0

Related Questions