I'm having a little problem with trying to sort the contents of a table programs by the column prog_id which holds the id of each program in the following format:
1.0.1, 1.0.2, 1.0.3, ..., 1.0.10, 1.0.11, ..., 1.1.0, 1.1.1 etc
When I sort by prog_id i get
1.0.1, 1.0.10, 1.0.11, 1.0.2, 1.0.3 ...
which is correct as far as MySQL goes but not correct for the order in which the data should display.
I tried using another column, orderby in which I could save an index and order by that but I would have to enter the values manually and there are a few thousand rows in my table which would take quite a long time to do.
Any tricks I could use to get my data to display in the "proper" order? BTW, I'm using PHP & MySQL.
Not optimal solution -
...ORDER BY substring_index(prog_id, '.', 1), substring_index(substring_index(prog_id, '.', 2), '.', -1), substring_index(prog_id, '.', -1)
Odd solution, but try it -
...ORDER BY INET_ATON(prog_id)