Re: mysql ORDER BY problem

["Rob W." <rob@xxxxxxxxxxxxxxx>]

Yeah, I got this problem fixed. The datbase was listed under [chr] instead of [integer].

Thanks for the help though.
  ----- Original Message ----- 
  From: Xavier Casto 
  To: Rob W. 
  Cc: php-general@xxxxxxxxxxxxx 
  Sent: Sunday, June 18, 2006 7:30 PM
  Subject: Re:  mysql ORDER BY problem


  Rob,
  Your problem is the data type you are trying to sort. You have a few solutions that can be used depending on your skills, access, and intent of the data. As some had suggested, the problem may be best handled at the database level, but it all depends on what your intent is. 

  When sorting a datatype of Character (or string) "10" will always come before "2", you would either need to convert the "string" to a "number" or left pad the string with "0" to get the result your are looking for. 

  Some good information for your issue can be found here:
  http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html 

  try the CAST or CONVERT functions in your query: 

  $query="SELECT * FROM db ORDER BY CONVERT(numbers, DECIMAL) ASC";



  On 6/18/06, Rob W. < rob@xxxxxxxxxxxxxxx> wrote:
    Ok, here's what i got in my mysql db. I got a table listed with numbers as follows 

    1
    2
    3
    4
    5
    6
    7
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    25

    These numbers I can display fine. I'm using ..

    $query="SELECT * FROM db ORDER BY numbers ASC"; 

    Right now it displays it as

    1
    10
    11
    12
    .....
    2
    22
    23
    25
    ....
    3
    4
    5
    6
    7

    Is there a way with my mysql query so that I can list the numbers in correct order?

    Any help is appricated.

    - Rob