sort ASC or DESC help....!

[mfacer]

Hey,
I have a text field in my mysql DB and the PHP statement I use is

SELECT processID, name FROM processes ORDER BY processID ASC

the problem is because it's a text field, it returns the following

1
10
11
12
13
2
3
4
5
6
7
8
9

so I need a way to get it to output the numbers in logical order, rather than text field order... I cant really change the DB field type as it was designed like that ... 😕

cheers for any help!

[bastien]

should not be a text field...bad design

you can use the sort functions in php if you take the entire resultset and dump it into an array

[mfacer]

I know - it was an early test database that ended up being used and too much code now relies on it .... :rolleyes:

We might be able to get around it actually...
the statements are like

SELECT * FROM processes WHERE processID = '44';

if the database field is changed to an int field, and we use the '' (quotation) - would that matter, or would it return an error?

thanks

[bastien]

that could work, you could also have a look at the CAST/CONVERT function in mysql

[uffis]

SQL standard does not use quotes around numeric literals. MySQL supports them with quotes, but handles them the same as without quotes. Better stick to the standard and not use quotes.

A solution might be to cast the text column to an integer column using the SQL function CAST. Maybe something like this:

SELECT CAST(processID AS INTEGER) AS proc_id, name 
FROM processes 
ORDER BY proc_id ASC

The CAST type should be INT or INTEGER, but I can't remember which one or if both are accepted by the SQL standard.

Also, you could try to alter the column type to a numeric one. But before you do that, make sure you have a backup of the data.