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 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 ross - January 28, 2014

Hi Deborah

Glad to hear Chris' code worked! 

With your next question, I just want to confirm you are asking how to limit the number of records that are displayed out of the new array Chris' code creates for you. If that's the case, try adding the following into your foreach loop:

 <?php if (@$counter++ == 7) { break; } ?>

You can change the "7" to what ever you like. 

Let me know how this works for you. Thanks!

-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com

Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/

By Deborah - January 28, 2014

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

Thanks so much for your help once again.

~ Deborah