Ordering the Dodgy Digits!

9 posts by 3 authors in: Forums > CMS Builder
Last Post: May 15, 2008   (RSS)

By Perchpole - March 31, 2008

This isn't really a CMS issue. I've encountered it several times over the years - most recently whilst working on a CMS project. For that reason (and because this place is overflowing with some very clever people) I hope someone might be able to give me some advice!

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

Yes, there is actually! :) It's because it's not a numeric field. You can trick MySQL into treating as a numeric field by adding "+0" after the fieldname in the "Order By". So it might look like this: refnum+0 (or in reverse) refnum+0 DESC

Hope that helps! Let me know if that works! :)
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Ordering the Dodgy Digits!

By Perchpole - March 31, 2008

Dave -

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: [Dave] Ordering the Dodgy Digits!

By Perchpole - March 31, 2008

Dave -



Either, both and all of the above. Whichever method works best!

[:)]

AJ

Re: [Perchpole] Ordering the Dodgy Digits!

By Dave - April 1, 2008

Hmm, I've been playing around with this for a bit with not much luck. The 'Order By' is actually just a MySQL "ORDER BY" clause - part of a MySQL query. So you can use any MySQL functions or tricks in there.

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.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Ordering the Dodgy Digits!

By Perchpole - April 1, 2008

Dave -



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])+0



You 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

Great! Glad to hear that did the job. :)
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Ordering the Dodgy Digits!

By webdude - May 15, 2008

Just wanted to follow up on this for other users. If you are having problems with sorting of integers, such as:

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.