ORDER text numerically

[supermaestro]

Hi

I'm amending an existing application in PHP. One of the fields in the database called 'mileage' has a list of values stored in a text column as follows:

75,000
51,000
80,000
32,000
100,000

How would you order these values numerically as follows?

32,000
51,000
75,000
80,000
100,000

Thanks

 

[DRJ478]

First of all, the table setup is silly and creates the problem. It is easier to format a number than take a string and mangle it somehow to be easy to order by numeric value.

I recommend to solve the problem with the SQL query.
If you have MySQL 4 check your version for the CONVERT function. It allows to change the type of the retrieved data and order by it numerically.

Otherwise, you probably need to trick MySQL by ordering primarily by the LENGTH and secondarily by the string itself. In that case you will receive an accurate numeric order.

Best, as siad before, is to adjust the column in the table.

 

[KempCGDR]

I have a similar question. I have a system for managing the downloading etc of programs, when a list needs to be created of previous versions of a program, the version numbers are stored in a text field (so you can denote a beta version by 1.0b for example), but they need to be ordered numerically. Any thoughts?

 

[DRJ478]

KempCGDR
If your version numbers are consistent like n.n.n then you could split them on the dots. There also is a substring_index function in MySQL.

In short:
Write the SQL to order by the split parts of the version number. Select the parts and alias them so you can order by them. (SELECT SUBSTRING_INDEX(versionColumn,'.',1) AS mainVersion ..... ORDER BY LENGTH(mainVersion),mainVersion)

AlLternatively just load all into MySQL, split it there and order the array before printing it to the page.

 

[vbkris]

DRJ:
in this case isnt an ordinary order by enough?

Known is handfull, Unknown is worldfull

 

[DRJ478]

vbkris

If you order just by characters and do not take the length of the string into consideration you will get:

1.2
11.2
14.0b
2.1

assuming there are two digit numbers for versions.
The length is used to ascertain the relative size of the total value, as version 1 is length 1 and 11 is length 2.

 

[vbkris]

thanks...

really appreciated...

Known is handfull, Unknown is worldfull