sorting numbers entered as string data

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-

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

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

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

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

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';
?>

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.

That was it!

Thanks a million!

Have a great weekend!

--Jeff