how to merge 2 fields to sort and account for null

8 posts by 2 authors in: Forums > CMS Builder
Last Post: May 15, 2017   (RSS)

By Deborah - May 4, 2017

Hi. I'm looking for a recipe... the examples I find in the forum relate to merging fields from two tables, but I need to merge two textfields within the same table and account for any blank fields.

The $performers table has textfields for:
'first_name'
'middle_name'
'last_name'
'suffix'
'group_name'

I would like to merge the 'last_name' and 'group_name' fields, then ASC sort that merged list with these rules:

1) If exists 'group_name' AND 'last_name' - use 'group_name'
2) If 'group_name' is null - use 'last_name'
3) If 'last_name' is null - use 'group_name'
4) If both 'last_name' and 'group_name' are null - omit

The sorted web page display will display the name as either an individual (first_name, middle_name, last_name, suffix) OR a group (group_name) - not both.

EXAMPLE RESULTS:
Lee Adams
Bill Jones
Marenka Brothers Band
Amy Smith
Toledo Troubadours

Any code examples would be much appreciated! And if there's a better approach than having the fields I've set up, I welcome those ideas, too.

Thank you.
~ Deborah

By kitsguru - May 6, 2017

Rules 1 & 3 can be combined into one as the last name state is irrelevent

  1. if exists 'group_name' - use 'group_name'
  2. else use Last Name
  3. where last_name is not null and group_name is not null

The easiest way to combine the two fields in this case is to use COALESCE which returns the first non null value.

select from tablename COALESCE (group_name, last_name) as sortorder, *
where  last_name is not null and group_name is not null
order by sortorder

Jeff Shields

By Deborah - May 6, 2017

Jeff, thanks!

I read more about COALESCE online and can imagine that the example you provided would work in a situation where I wanted to create a 'get options from database' for an editor setup in CMS Builder.

What I wish to do at this time is output a list on the public html web page. I'm not PHP-savvy enough to determine how to create the code that would merge and sort the two fields as shown in my example.

Sorry if I wasn't clear, but appreciate the info for future use.

I'm still wondering if there's a better approach than the one I'm taking.

~ Deborah

By kitsguru - May 6, 2017

This really is the easier way to do it using a custom query to replace the auto generated code for getRecords. The following will do what you need and returns the rows pre-sort with a new column called 'sortorder'. Now you can iterate directly over $listRecords just like you normally would. The only difference is you don't have the metadata.

$query = "select from tablename COALESCE (group_name, last_name) as sortorder, *
    where  last_name is not null and group_name is not null
    order by sortorder";
$listRecords = mysql_select_query($query);

Jeff Shields

By Deborah - May 6, 2017

Jeff, here is where I sheepishly have to admit how little I know about PHP. (The only PHP experience I have is with CMS Builder.)

I understand the principal "action" behind your code example and have no doubt it's valid. My issue is that I simply don't know how to incorporate it into a web page.

Could you please tell me exactly how I should replace these two blocks of code?

// list of performers
list($performersRecords, $performersMetaData) = getRecords(array(
'tableName' => 'performers',
'allowSearch' => false,
));
$performersRecord = @$performersRecords[0]; // get first record


<?php foreach ($performersRecords as $record): ?>
(echo here combined and sorted list of 'group_name' and 'last_name' fields)
<?php endforeach ?>

My apologies again for not understanding more. I'm very appreciative of your help!

~ Deborah

By kitsguru - May 7, 2017

Hi Deborah,

I took a look through the view functions and found that there is a getRecordsCustom function that can be use to replace the normal getRecords. Using this you can pass a custom query and retrieve the list of records and metadata.

// this is the mysql query to execute
// it returns list of performer records plus an extra column ( groupName_or_lastName )
// with either the group name or lastname, first not null matches
// It only returns those records that have either a group name or last name i.e. both cannot be null
// it is pre-sorted by the new column

$query = "select *, COALESCE (group_name, last_name) as groupName_or_lastName    
    FROM ". $TABLE_PREFIX . "performers
    WHERE  last_name is not null and group_name is not null    
    ORDER BY groupName_or_lastNam";

// getRecordsCustom() - return data in the same format as getRecords but with custom SQL quuery
// ... this is an easy way to get paging working
//  list($recordList, $listMetadata) = getRecordsCustom(array(
//    'query'   => "SELECT * FROM tablename",
//    'perPage' => 5,
//    'pageNum' => 4, // defualts to $_REQUEST['page'] if not specified
//  ));
list($performersRecords, $performersMetaData) = getRecordsCustom(array(
    'query' => $query,
));

// $performersRecord = @$performersRecords[0]; // get first record -- this is unnecessary as it is not used

<?php foreach ($performersRecords as $record): ?>
<!-- echo combined and sorted list of 'group_name' and 'last_name' fields -->
<?php echo $record['groupName_or_lastNam'];
<?php // add additional echo statements as needed ?>
<?php endforeach ?>

I have not tested this but it should do the trick.

Jeff Shields

By Deborah - May 8, 2017

Hi, Jeff. Success!

The names are merged and sorting correctly on the page and all of the other fields in the 'performers' table display except the uploads.

The default upload code results in these error messages:
  Notice: Undefined index: photo_small_sq in ...
  Warning: Invalid argument supplied for foreach() ...

Current code:

$query = "SELECT *, COALESCE (group_name, last_name) as groupName_or_lastName    
    FROM ". $TABLE_PREFIX . "performers
    WHERE  last_name is not null or group_name is not null    
    ORDER BY groupName_or_lastName";

list($performersRecords, $performersMetaData) = getRecordsCustom(array(
    'query' => $query,
    'perPage' => 500, //not actually using pagination, so used high #
    'pageNum' => 1,
));


<?php foreach ($performersRecords as $record): ?>

<?php echo htmlencode($record['num']) ?>
<?php echo htmlencode($record['full_name_group_name']) ?>

Lines causing errors...
<?php foreach ($record['photo_small_sq'] as $index => $upload): ?>
   <?php if ($upload['hasThumbnail']): ?>
   <img src="<?php echo htmlencode($upload['thumbUrlPath2']) ?>">
   <?php endif ?>
<?php endforeach ?>

<?php endforeach ?>

Having gotten this far, I'm hoping there's a method for displaying the uploads. I've tried some code in other posts, but haven't found a post specific to this example. Would you have any thoughts on the uploads?

Thanks for your help so far... this is very promising!

~ Deborah