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"; PHP:
Okay, okay, it wasn't PHP's fault. PHP is much smarter than I am, and I should never put the blame on it 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.
Hit me up on a messenger I can help you fix this quickly. I have a lot of SQL experience and if you look through my posts a high response level (i get the job done)
Hi, Try to refrase the query like so: $query_rsRepeat = "SELECT id, city, placename, category, price FROM travel WHERE category = 'Chad hotels' ORDER BY 5 ASC"; PHP: That should do the trick. Else you could try: $query_rsRepeat = "SELECT id, city, placename, category, price FROM travel WHERE category = 'Chad hotels' ORDER BY tablename.price ASC"; PHP: Adding the tablename sometimes helps.. Good luck!
Here are the numerical data types: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html Your query is fine...My offer still stands too.
i would change the unavailable to 0 ==> the price to int with default 0 ==> when showing the data show unavailable for 0 price.
Thanks to all users who posted. I hate to admit this, but I cheated on Digital Point with PHP freaks, and I got the answer in their PHP Help forum. If anyone's interested in looking at their posts, here's the link. http://www.phpfreaks.com/forums/index.php/topic,258289.0.html (Too bad that "Live links and signatures are not available to you yet"
It sounds like your datatype is set at varchar and not int, decimal or float. If you're trying to sort by price and your datatype is a varchar it will not work. A varchar will try and sort it based on price but all it cares about is the first number. This means that 9$ will appear to be greater then 400$.
Please read all posts before contributing to a thread. In the third post I wrote Thus, the INT versus VARCHAR issue was addressed very early on in this thread.
Well you can fix that issue a few different ways. If it was me I would have 2 int fields one for a low and one for a high price. Then when you pull the query do somthing like if($info['price2'] != 0){ echo $info['price1'] . "-" . $info['price2']; } elseif($info['price1'] != 0) echo $info['price1']; } else { echo "unavailable"; } There you go =) That just says if there is a value for both fields then use the 90-100 form. If there is only a value for the first field use the 90 form. If there is no value for either then it will say "unavailable"
bigrollerdave - Great script. Thanks for the contribution. I tried it out, and there's only one problem. Your script has 5 brackets, not 6. You're missing an open bracket after the "elseif" statement (added below in red). if($info['price2'] != 0){ echo $info['price1'] . "-" . $info['price2']; } elseif($info['price1'] != 0){ echo $info['price1']; } else { echo "unavailable"; } Otherwise, it works great. Thanks a lot.