Re: [PHP] mysql ORDER BY problem

[Rob W.]

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@lists.php.net 
  Sent: Sunday, June 18, 2006 7:30 PM
  Subject: Re: [PHP] 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. < [EMAIL PROTECTED]> 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