mysql ORDER BY problems

------=_NextPart_000_0070_01C692FB.4C2EA610
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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=3D"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
------=_NextPart_000_0070_01C692FB.4C2EA610--

re: mysql ORDER BY problems

are they defined as integer or varchars (string)?

try
a) exchange field definition varchar / integer
b) order by length(number) and later by number

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: mysql ORDER BY problems

if you have these as strings, I would recommend a column data type
conversion to int(or other numeric as the case may be)....failing that I
would use the CAST command to convert the data to numerics

see here

http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html

to use

select cast(fieldname as integer) from table where ... order by ...

bastien

>From: "Rob W."
>To:
>Subject: [PHP-DB] mysql ORDER BY problems
>Date: Sun, 18 Jun 2006 17:19:03 -0500
>
>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



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: re: mysql ORDER BY problems

I believe I did this one time without changing the database schema; simply
by just putting the type in the ORDER BY clause.

Something like
ORDER BY BINARY numbers ASC
or maybe
ORDER BY CAST(numbers AS BINARY) ASC

Regards,
Dwight

> -----Original Message-----
> From: J. Alejandro Ceballos Z. -JOAL-
> [mailto:buzon@alejandro.ceballos.info]
> Sent: Sunday, June 18, 2006 7:01 PM
> To: php-db@lists.php.net
> Cc: rob@fiberuplink.com
> Subject: [PHP-DB] re: mysql ORDER BY problems
>
> are they defined as integer or varchars (string)?
>
> try
> a) exchange field definition varchar / integer
> b) order by length(number) and later by number
>
> --

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php