Forum Moderators: open

Message Too Old, No Replies

Sorting text+numbers

         

Sandd

1:26 am on Sep 7, 2007 (gmt 0)

10+ Year Member



I'm trying to sort data (soccer team age groups) with the 'agegroup' field containing data like:

bu8
bu9
gu11
gu8
bu12
gu15

I want to output to PHP with it sorted as:

bu8
bu9
bu12
gu8
gu11
gu15

I can't figure it out. My query(s) attempts refuse to work.

Any ideas?

Sandd

Lord Majestic

1:45 am on Sep 7, 2007 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



If you get something like this:

bu12
bu8
bu9
gu11
gu15
gu8

Then the issue is that numbers at the end of strings are treated as strings, which means bu12 will come before bu8. To fix this problem you can split those fields into two different columns one of which would be numeric - this is really a better database design, but alternatively you can use substrings to "pad" numbers in string with trailing zeros. Say ages can't be greater than 100, so you need to pad all single digit numbers to get the following data:

bu08
bu09
gu11
gu08
bu12
gu15

Then string based sorting will result in the order that you expect. It is better to use 2 columns though.

Sandd

2:40 am on Sep 7, 2007 (gmt 0)

10+ Year Member



After searching for WAY too long, that's what I did. Thank you!