how to merge 2 fields to sort and account for null

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, 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

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

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.

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

Hi, Jeff.

I'm posting back, because I was mistaken and don't wish to mislead anyone.

The code I indicated in my prior post does not result in a sorted list. The resulting list is ordered by record number and the 'groupName_or_lastName' does not display.

Considering the above and the lack of connecting to the uploads, I'm going to abandon this and take a different approach.

Thanks for your help, all the same!

~ Deborah