MySQL ordering problem

Discussion in 'MySQL' started by mcarter, Jun 26, 2009.

0
  1. #1
    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:
     
    mcarter, Jun 26, 2009 IP
  2. mcarter

    mcarter Peon

    Messages:
    14
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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.
     
    mcarter, Jun 26, 2009 IP
  3. plog

    plog Peon

    Messages:
    298
    Likes Received:
    11
    Best Answers:
    1
    Trophy Points:
    0
    #3
    I would definitely change the price field to a numeric datatype. Then you are going to have to add some data or method to determine if the data in the price field is to be used as the price. My suggestion for that would be to either add another field called 'price_type' or use negative numbers in the price field to accomplish the same thing.

    Essentially you need a way to let your system know what exactly is in the price field and how that data should be used. If you use a whole new field the values for that would be 'Range', 'Unavailable', 'Available' and that data would then be used to display either the price, a price range or if the data is unavailable.
     
    plog, Jun 26, 2009 IP
  4. mcarter

    mcarter Peon

    Messages:
    14
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #4
    Thanks to plog for posting. 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" ;)
     
    mcarter, Jun 26, 2009 IP