Ordering the Dodgy Digits!
9 posts by 3 authors in: Forums > CMS Builder
Last Post: May 15, 2008 (RSS)
By Perchpole - March 31, 2008
Imagine a list of products - with reference codes numbered 1 through to 200. I would like to list these products/codes in CMS in numerical order. It should look something like this...
1
2
3
4
..etc.
Trouble is, it's not that simple because the numbers are in the wrong (digit) format. If I were to list them, it would look something like this...
1
10
11
12
...etc.
In order to list the number correctly, it would be necessary to renumber the products, thus...
001
002
003
004
...etc.
Unfortunately, I cannot renumber the reference codes (because the client won't countenance such a thing). So is there any ingenious way I can trick CMS builder into ordering the list correctly despite the problem with the format?
[crazy]
AJ
Re: [Perchpole] Ordering the Dodgy Digits!
By Dave - March 31, 2008
Hope that helps! Let me know if that works! :)
interactivetools.com
Re: [Dave] Ordering the Dodgy Digits!
By Perchpole - March 31, 2008
There is, surely, no limit to your talents!
It works - sort of - in some instances. The trouble is, I forgot to mention that some of the ref codes have letters before the digits. So, for example, one list would be...
G1
G10
G11
G12
...etc.
It doesn't work in this instance - but thanks all the same.
[:)]
AJ
Re: [Perchpole] Ordering the Dodgy Digits!
By Dave - March 31, 2008
interactivetools.com
Re: [Dave] Ordering the Dodgy Digits!
By Perchpole - March 31, 2008
Either, both and all of the above. Whichever method works best!
[:)]
AJ
Re: [Perchpole] Ordering the Dodgy Digits!
By Dave - April 1, 2008
The kind of sorting you want is called "natural sort order", it's how humans expect things to be sorted when you have letters an numbers together. I did some research on google (link) and there is a lot of people talking about it but not many solutions that would work for this case.
MySQL has a list of function you can use (link) but nothing was exactly what we needed.
One idea I came up with was to remove all the characters. If all you had was "R" you could do that with an order by like this and have it sort on the number only:
REPLACE(refnum, 'R', '')+0
Except if you had more letter than just R you'd just to nest it like this (for S as well):
REPLACE(REPLACE(refnum, 'R', ''), 'S', '')+0
And so on. It's workable, but gets pretty complicated pretty quickly when you have lots of possible letters
Another totally different idea would be to had a second field just for sorting where you enter just the number part of the reference num.
Hope that helps. If I think of anything else I'll post.
interactivetools.com
Re: [Dave] Ordering the Dodgy Digits!
By Perchpole - April 1, 2008
Crikey. You never do things by halves, do you? That's a fabulous answer - and it works too!
The only thing I've noticed is that when you use the code on a list page you need to use double quotes around the data in parentheses...
REPLACE(refnum, "[/#ff0000]R"[/#ff0000], ""[/#ff0000])+0You can also use the code (with double or single quotes) in the 'Order by' box of the Section Editor screens.
This will be very useful.
Thanks again!
[:)]
AJ
Re: [Perchpole] Ordering the Dodgy Digits!
By Dave - April 2, 2008
interactivetools.com
Re: [Dave] Ordering the Dodgy Digits!
By webdude - May 15, 2008
1
10
2
3
4
5
6
7
8
9
... instead of the expected ...
1
2
3
4
5
6
7
8
9
10
Then I have a trick for you to use in your viewer code. Use the MySQL CAST function in your ORDER BY expression... like so:
SELECT * FROM `faq` WHERE `num` > 0 ORDER BY CAST(`myorder` AS UNSIGNED) ASC
I find this much easier than trying to pad numbers with zeros. Unless you have gigantic result sets the CPU hit should be very minimal.