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
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
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.
interactivetools.com
Re: [Dave] sorting numbers entered as string data
By rcrofoot - March 31, 2008
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
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 Kenny - July 23, 2008
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
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
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
<?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
$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
Thanks a million!
Have a great weekend!
--Jeff