Sorting on numeric column

3 posts by 2 authors in: Forums > CMS Builder
Last Post: March 23, 2009   (RSS)

By pothompson - March 23, 2009

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.

Re: [pothompson] Sorting on numeric column

By Dave - March 23, 2009

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

Re: [Dave] Sorting on numeric column

By pothompson - March 23, 2009

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!