sort product size in interger order but it is a alphanumeric field

Discussion in 'PHP' started by aayybb, Jul 13, 2009.

0
  1. #1
    Hi,

    I have mysql DB with product size with char type. ex. 1 oz, 2 oz, 16 oz, 28 oz.

    If I use "....order by proudctsize" then it show 1 oz, 16 oz, 2 oz, 28 oz in order.

    What should I do if I want them to show 1 oz, 2 oz, 16 oz, 28 oz in order since 2 < 16 so 2 should appear before 16?

    Any help is appreciated.
     
    aayybb, Jul 13, 2009 IP
  2. ThePHPMaster

    ThePHPMaster Well-Known Member

    Messages:
    737
    Likes Received:
    52
    Best Answers:
    33
    Trophy Points:
    150
    #2
    Make the field of type INT, then they would be sortable.
     
    ThePHPMaster, Jul 13, 2009 IP
  3. wd_2k6

    wd_2k6 Peon

    Messages:
    1,740
    Likes Received:
    54
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Yes change from varchar to int, cause if everything is oz there is no need to store this.

    Another way around this would be to sort it aftre the results are returned with some php functions
     
    wd_2k6, Jul 13, 2009 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    You could do something like this, but switching to an int or other numeric field would be better.

    ORDER BY CAST(proudctsize AS UNSIGNED)
     
    jestep, Jul 13, 2009 IP
  5. aayybb

    aayybb Peon

    Messages:
    128
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    Thank you very very much, jestep. It works.
    Now I have a situation. 1 oz, 5/8 oz. The result shows 1 oz first. Is there anywhere to have 5/8 oz shows up first since it is smaller than 1 oz?
    Another situation is having 1 oz and 1 gallon. The result shows 1 gallon first. Is there anywhere to have 1 oz shows up first?

    Thanks for any suggestion.
     
    aayybb, Jul 14, 2009 IP
  6. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #6
    In this case I dont think there's any way to sort without having the unit in a separate column. Additionally there's no way to get around the fraction unless you can convert it into a decimal.

    Last option would be to make a relative volume column, where you would put a converted amount. You may be able to use some sort or trigger to do this automatically.
     
    jestep, Jul 14, 2009 IP
  7. aayybb

    aayybb Peon

    Messages:
    128
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #7
    $result = mysql_query("SELECT round(5/8,2) ");

    result --> Array ( [0] => 0.63 [round(5/8,2)] => 0.63 )

    $result = mysql_query("SELECT round('5/8',2) "); // 5/8 is a string

    result --> Array ( [0] => 5.00 [round('5/8',2)] => 5.00 )

    Is there anyway to use the 2nd query but to get 0.63?

    Thanks for any help in advance.
     
    aayybb, Jul 16, 2009 IP