Interactivetools.com Forum: Products: CMS Builder: sorting numbers entered as string data

Home: Products: CMS Builder:
sorting numbers entered as string data

rcrofoot
User

Mar 31, 2008, 4:14 PM

Post #1 of 12 (2635 views)
Shortcut

sorting numbers entered as string data	Can't Post

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

Dave
Staff / Moderator

Mar 31, 2008, 5:07 PM

Post #2 of 12 (2634 views)
Shortcut

Re: [rcrofoot] sorting numbers entered as string data [In reply to]	Can't Post

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

rcrofoot
User

Mar 31, 2008, 5:35 PM

Post #3 of 12 (2632 views)
Shortcut

Re: [Dave] sorting numbers entered as string data [In reply to]	Can't Post

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

HDLLC
User

Jul 23, 2008, 2:24 PM

Post #4 of 12 (2488 views)
Shortcut

Re: [rcrofoot] sorting numbers entered as string data [In reply to]	Can't Post

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

sagentic
User

Jul 23, 2008, 3:48 PM

Post #5 of 12 (2480 views)
Shortcut

Re: [HDLLC] sorting numbers entered as string data [In reply to]	Can't Post

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
/* STEP 1: LOAD RECORDS - Copy this PHP code block to the TOP of your page BEFORE anything else. */
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

rcrofoot
User

Jul 24, 2008, 6:19 AM

Post #6 of 12 (2443 views)
Shortcut

Re: [HDLLC] sorting numbers entered as string data [In reply to]	Can't Post

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'; // (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';

Hope that helps somewhat...Rick

HDLLC
User

Jul 24, 2008, 6:42 AM

Post #7 of 12 (2440 views)
Shortcut

Re: [rcrofoot] sorting numbers entered as string data [In reply to]	Can't Post

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:

Code

<?php 
  /* STEP 1: LOAD RECORDS - Copy this PHP code block to the TOP of your page BEFORE anything else. */ 
  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

rcrofoot
User

Jul 24, 2008, 6:58 AM

Post #8 of 12 (2439 views)
Shortcut

Re: [HDLLC] sorting numbers entered as string data [In reply to]	Can't Post

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'; // (optional) The number of records to display per page. Example: '5'; Defaults to 10.
$options['orderBy'] = 'price+0 DESC'; // (optional) Fieldnames to sort by. Example: 'field1, field2 DESC, field3';
?>

sagentic
User

Jul 24, 2008, 9:44 AM

Post #9 of 12 (2424 views)
Shortcut

Re: [rcrofoot] sorting numbers entered as string data [In reply to]	Can't Post

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.

(This post was edited by sagentic on Jul 24, 2008, 9:47 AM)

HDLLC
User

Jul 24, 2008, 12:51 PM

Post #10 of 12 (2406 views)
Shortcut

Re: [sagentic] sorting numbers entered as string data [In reply to]	Can't Post

That was it!

Thanks a million!

Have a great weekend!

--Jeff

rcrofoot
User

Jul 27, 2008, 7:43 AM

Post #11 of 12 (2369 views)
Shortcut

Re: [Dave] sorting numbers entered as string data [In reply to]	Can't Post

Hi Dave-

I've run into a snag...not with CMS...but with a 3rd party company currently used by real estate companies, and I was hoping to get your opinion...

It's simply this: If I am supplied a .txt notepad file containing 2 lines of tab delimited fieldnames, followed by many lines of tab delimited listing data, is it possible to create a MySql table based on the fieldnames given, but not being supplied data types or varchar/char field lengths??? I don't think there is and have experimented with variations of CREATE TABLE but with no success...

This has been extremely frustration. This company supplies a technical pdf, but nowhere is there mentioned data type/fieldlength values...but they do mention that 3 tables have to be built an their relation to each other...Am I not as proficient in SQL as I thought, or is being supplied field names only and data not enough to create a table and use LOAD DATA INFILE to populate it???

Just a "you're not as good as you thought" or "they're not giving you enough data" type of response would be of great help to me...

By the way...CMS is working flawlessly on 2 ouf our sites!...

Thanks, Rick

Dave
Staff / Moderator

Jul 28, 2008, 9:39 AM

Post #12 of 12 (2267 views)
Shortcut

Re: [rcrofoot] sorting numbers entered as string data [In reply to]	Can't Post

Hi Rick,

The default field types we use for CMS Builder are quite large and will likely accommodate your data.

One way to manipulate various import files is to import them into excel and then export them as something easier to work with (sometimes that's CSV).

If you need to merge 3 tables, though, you'll likely need some custom programming for that. We do lots of custom import/export scripts for CMS Builder and I'd be happy to take a look at your sample import data and let you know what would be involved.

If you want to talk about that feel free to drop me an email at dave@interactivetools.com.

Dave Edis - Senior Developer
interactivetools.com

Search for (options)