Main
Index 		Search
Posts 		Who's
Online 		Log
In

Home: Products: CMS Builder:
Sorting on numeric column

 
 


pothompson
User

Mar 23, 2009, 10:31 AM

Post #1 of 3 (880 views)
Shortcut
Sorting on numeric column Can't Post
I have a column in a section that I've created which holds prices. If I use the orderBy attribute when getting records in the viewer code, it will order based on the string, not the number. So, 10.39 is less than 5.90 for example.

Is there a way around this, I can't see a way to define the column type as numeric, unless I just have to do that in the MySQL database directly?

Thanks,

Paul.


Dave
Staff / Moderator


Mar 23, 2009, 12:15 PM

Post #2 of 3 (875 views)
Shortcut
Re: [pothompson] Sorting on numeric column [In reply to] Can't Post
Hi Paul,

As of v1.25 (we're currently at 1.27) you can specify a MySQL column type in the field editor. It's at the very bottom under advanced options.

Additionally, you can force string fields to sort numerically in MySQL by adding +0 such as:
ORDER BY `price`+0

So either change the column type or convert the type on the fly with a math operation.

Hope that helps!

Dave Edis - Senior Developer
interactivetools.com
 


pothompson
User

Mar 23, 2009, 12:25 PM

Post #3 of 3 (874 views)
Shortcut
Re: [Dave] Sorting on numeric column [In reply to] Can't Post
That's great, the MySQL column type is just what I need in future, but as I've already got a table full of data the Price+0 method will work for the current set-up, so thanks!