We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
MODX Community Forums Archive

Answered Database query sorting by number

    • 1934
    • 23 Posts
    Appeltje Reply #1, 9 years, 11 months ago
    Hello,

    I'am having a problem with database query sorting numbers.
    I'am collecting data from external database, the last part of the call is my problem (ORDER BY field_house_kavels_number ASC)

    this is the call in php snippet :
    $result = $modx->db->query( 'SELECT nid, field_house_kavels_number, field_house_kavels_price, field_house_kavels_surface, field_house_kavels_livingsurface, field_house_kavels_content, field_house_kavels_sold FROM `content_field_house_kavels` WHERE nid = ' . $node . ' ORDER BY field_house_kavels_number ASC' );


    this is the output :

    17 (beschikbaar)
    21 (verkocht)
    22 (beschikbaar)
    23 (verkocht)
    25 (beschikbaar)
    9 (beschikbaar)

    How can I get the 9 above the 17 ?? ( field_house_kavels_number is where i'am sorting on)

    Appeltje

    This question has been answered by appeltje. See the first response.

    [ed. note: appeltje last edited this post 9 years, 11 months ago.]
      • 16278
      • 928 Posts
      kp52 Reply #2, 9 years, 11 months ago
      Looks as if your numbers are stored as strings, hence the sort order. Try using the MySQL CONVERT() function to turn them into integers:
      ORDER BY CONVERT(field_house_kavels_number, UNSIGNED) ASC

      (Not tested)
      :) KP
      • discuss.answer
        • 1934
        • 23 Posts
        Appeltje Reply #3, 9 years, 11 months ago
        kp52,

        Thanks very much, its working !!!!

        Appeltje