[SOLVED] MySQL ordering problem

[jesse_james]

I have a repeating region on a page that lists hotels.  It's supposed to list them from cheapest to most expensive.  I have a table set up in MySQL that is simply the numerical price (no dollar signs or anything else - just a number).  But, to my surprise, I see the hotels aren't being ordered right.

 

The $100 hotel shows up first, a $400 hotel next, then a $41 hotel, then a $90 hotel.  OK, it looks like PHP is looking at the first digit of the number and sorting based on that.  So does PHP not know how to count properly?  How can I teach it?

 

Here's the code in the recordset:

 

$query_rsRepeat = "SELECT id, city, placename, category, price FROM travel WHERE category = 'Chad hotels' ORDER BY price ASC";

[jesse_james]

It looks like I can change the MySQL "price" field type to some kind of int (smallint, etc), and it will order the hotels by price properly.  BUT, NO DICE.  I don't have price data for all of the hotels, so I want to put "unavailable" in the price field for a few hotels.  Also, I would like to put something like "90-110" in the price field for one.  But if I use the int field type, it will change any wording ("unavailable" in this case) to a 0. 

 

Is there any workaround for this problem?  I would like it to order my numbers properly, but I would also like to be able to put "unavailable" for some of the hotels' price field.

[jesse_james]

I posted this in the PHP Help section, and it got a lot of replies.  One user submitted a working solution.

 

See thread - http://www.phpfreaks.com/forums/index.php/topic,258289.0.html