sorting numbers entered as string data

12 posts by 4 authors in: Forums > CMS Builder
Last Post: July 28, 2008   (RSS)

By rcrofoot - March 31, 2008

Hi Dave-

I have a field called "Price" which is limited to accepting numbers only...However, this is still string data...Is there a way to convert to numeric data so that sorting will be accurate???

In my "Load Record List" code in a PHP page, I use this to sort:

$options['orderBy'] = 'price DESC';

However, because it's string data, sorting from highest price to lowest is not accurate, and I can't quite figure out how to enter the price data as numeric to begin with...or if that's even possible...I.E. to store the numeric string data in a table field defined as numeric...

Right now I'm only dealing with a range of 0 to 9,999,999...anything less than 1,000,000 has to be input with a leading zero in order to sort properly...Hopefully you catch my drift...

Thanks for the help...

Rick

Re: [rcrofoot] sorting numbers entered as string data

By Dave - March 31, 2008

Hi Rick,

Try setting the order by to this:

$options['orderBy'] = 'price+0 DESC';

The +0 tricks it into treating it like a number. Then, in the field editor for the field set "Allowed Content" to "Only allow characters" and then enter 1234567890. in the character field. That will prevent anyone from entering non-numeric values in the fields.

Hope that helps! Let me know if you need anything else.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] sorting numbers entered as string data

By rcrofoot - March 31, 2008

Dave-

I was doing this in PHP (multiplying the numeric string by 1 to convert to number), but just didn't think of using that trick in the line $options['orderBy'] = 'price DESC';

$options['orderBy'] = 'price+0 DESC'; works great!

Many thanks...again...Rick

Re: [rcrofoot] sorting numbers entered as string data

By HDLLC - July 23, 2008

This is the exact sort of thing I need to do - show the list page based on a "price" field...

I'm not that keen with php - so wondering -

What is the whole block of code - or where do I use the above tip?

Thanks!

--Jeff

Re: [HDLLC] sorting numbers entered as string data

By rcrofoot - July 24, 2008

Hello-

Sorry it took so long to get back to you...It seems your question has been answered, however here's the code I used to solve the problem I was having in displaying records based on sorting by 'price':

<?php

require_once "/usr/www/users/decaro/kellyassociates/decaro/cmsAdmin/lib/viewer_functions.php";
$options = array(); // NOTE: see online documentation for more details on these options
$options['tableName'] = 'featured_homes_ka'; // (REQUIRED) MySQL tablename to list record from. Example: 'article';
$options['titleField'] = 'title'; // (optional) MySQL fieldname used in viewer url for search engines. Example: 'title' would display: viewer.php/article_title_here-123
$options['viewerUrl'] = 'featuredhomesdetails_ka.php'; // (optional) URL of viewer page. Example: '/articles/view.php';
$options['perPage'] = '300'; // (optional) The number of records to display per page. Example: '5'; Defaults to 10.
$options['orderBy'] = 'price+0 DESC';[/#ff0000] // (optional) Fieldnames to sort by. Example: 'field1, field2 DESC, field3';
$options['pageNum'] = ''; // (optional) Page number of results to display. Example: '1'; Defaults to ?page=# value, or 1 if undefined
$options['where'] = ''; // (ADVANCED) Additional MySQL WHERE conditions. Example: 'fieldname = "value"'
$options['useSeoUrls'] = ''; // (ADVANCED) Set this to '1' for search engine friendly urls: view.php/123 instead of view.php?123 (not supported on all web servers)
list($listRows, $listDetails) = getListRows($options);
?>

The red highlight above forces string data (in this case numbers entered as string data) to get converted to numeric data...You could also use 'price*1 DESC';[/#ff0000]

Hope that helps somewhat...Rick

Re: [rcrofoot] sorting numbers entered as string data

By HDLLC - July 24, 2008

Thanks for posting - both of you!

Question - it's not working on my list page - or maybe I'm not using this correctly... Here's the top code in my page:

<?php

require_once "/webserverPath/cmsAdmin/lib/viewer_functions.php";

list($propertiesRecords, $propertiesMetaData) = getRecords(array(
'tableName' => 'properties',
'perPage' => '10',
$options['orderBy'] = 'price+0 DESC',
));

?>

Adding that "orderby" line didn't seem to make any difference...

Any thoughts?

Thanks!

--Jeff

Re: [HDLLC] sorting numbers entered as string data

By rcrofoot - July 24, 2008

The first thing I would try is to add a semicolon at the end of each statement...see red highlights below:

<?php require_once "/usr/www/users/decaro/kellyassociates/decaro/cmsAdmin/lib/viewer_functions.php";
$options['perPage'] = '300';[/#ff0000] // (optional) The number of records to display per page. Example: '5'; Defaults to 10.
$options['orderBy'] = 'price+0 DESC';[/#ff0000] // (optional) Fieldnames to sort by. Example: 'field1, field2 DESC, field3';
?>

Re: [rcrofoot] sorting numbers entered as string data

By Kenny - July 24, 2008 - edited: July 24, 2008

Replace

$options['orderBy'] = 'price+0 DESC',

with

'orderBy' => 'price+0 DESC',


This is for the most up to date version of CMSB. The code that rcrofoot is using is for an older version of CMS Builder.

Re: [sagentic] sorting numbers entered as string data

By HDLLC - July 24, 2008

That was it!

Thanks a million!

Have a great weekend!

--Jeff