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: [HDLLC] sorting numbers entered as string data

By Kenny - July 23, 2008

This code has been moved with later versions of CMS Builder to the top of the page.

A sample of one that we used is:

<?php

require_once "/home/remaxc/public_html/webadmin/lib/viewer_functions.php";

list($commercialRecords, $commercialMetaData) = getRecords(array(
'tableName' => 'commercial',
'orderBy' => 'price+0 DESC',

));
$commercialRecord = @$commercialRecords[0]; // get first record

?>


But also in the field where price is displayed, you can ensure that it actually looks like a "price" by adding a few extra codes:

$<?php echo number_format($commercialRecord['price'] ,2) ?>


The ",2" tells it to give two decimal places

The "number_format" tells it to treat it like a number to include comma separators.

Notice the "$" before the echo statement - that's just embedded into the code so it shows up automatically for every price.

On the backend, we just allow the user to enter in numbers.

So they would enter in 3456 (or 3456.00)

And what would be displayed is $3,456.00

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