merge two lists + sort on date

6 posts by 4 authors in: Forums > CMS Builder
Last Post: January 28, 2014   (RSS)

By Deborah - January 27, 2014

Hi. I have two sets of calendar editors, "Events" and "Workshops" and we need to merge those listings into one merged list for display on the Home Page, but ALSO sort the final list display by 'start_date'.

What's happening with the code below is the lists are merged into one, but the records displayed in that list are split with the top of the list showing all 'events' records listed by start_date, followed by all 'workshops' listed records by start_date.

// list of events   
list($eventsRecords, $eventsMetaData) = getRecords(array(     
'tableName'   => 'events',     
'where'       => '(publish_date <= NOW()) AND (removal_date>= TIMESTAMP(CURDATE(), "00:00:00")) AND featured LIKE "1"',     
'limit'       => '3',     
'orderBy'     => "start_date ASC",             
));      

// list of workshops   
list($workshopsRecords, $workshopsMetaData) = getRecords(array(     
'tableName'   => 'workshops',     
'where'       => '(publish_date <= NOW()) AND (removal_date>= TIMESTAMP(CURDATE(), "00:00:00")) AND featured LIKE "1"',     
'limit'       => '2',     
'orderBy'     => "start_date ASC",   
));  
?>

<?php // merge two tables into one list  
$eventsandworkshopsRecords = array_merge($eventsRecords, $workshopsRecords);  
?>

In my HTML I have:

<?php foreach ($eventsandworkshopsRecords as $record): ?>
<?php echo htmlencode($record['start_date']) ?>
(...etc...)
<?php endforeach ?>


I'm hoping someone has a solution for this as it would really make my day!

Thanks for any help.

~ Deborah

By Chris - January 27, 2014

Hi Deborah,

After merging, you'll want to sort the records by date. I think the simplest fix would be to use array_multisort(). It's a poorly-documented and poorly-understood function which sorts one array using the sort order from sorting another array. Imagine if you had a stack of sealed DVDs and a stack of cards with the release dates of the films and both stacks were in the same order (so the first card corresponds to the first DVD, etc.); if you wanted to sort the DVDs by release date, you could sort the cards, making the same swaps in the DVD stack as you make in the card stack. So the first thing to do is to build the stack of cards...

$startDates = array();
foreach ($eventsandworkshopsRecords as $record) {
  $startDates[] = strtotime($record['start_date']);
}

// sort $eventsandworkshopsRecords using $startDates as a reference
array_multisort(
  $startDates, SORT_ASC,
  $eventsandworkshopsRecords
);

You'll want to add this code after your array_merge() call. Please let me know if you have any questions. Hope this helps!

All the best,
Chris

By Dave - January 27, 2014

I still find array_multisort() confusing, which is why I usually just ask Chris to do it for me. But that is a good description! ;) 

Dave Edis - Senior Developer
interactivetools.com

By Deborah - January 27, 2014

Chris, your code is perfection! If you only knew how many times I wanted to achieve that in recent years, but never asked how!

So now that I have the two tables merged into one date-sorted list, is it possible for that list to display a limited number of records if I remove the limits I previously indicated (such as 'limit'  => '3',) from the individual tables?

Thanks so much for your help.

~ Deborah

By Deborah - January 28, 2014

Ross, Yes, that is exactly what I was looking for!

Thanks so much for your help once again.

~ Deborah