Combining array_merge with perPage

5 posts by 2 authors in: Forums > CMS Builder
Last Post: May 17, 2016   (RSS)

By JeffC - May 12, 2016 - edited: May 12, 2016

Hi 

I am outputting entries from 3 tables into 1 list page using array_merge. Currently the code below shows everything.

// load list records from 'review'
list($reviewRecords, $listMetaData) = getRecords(array(
'tableName'   => 'review',
'loadUploads' => true,
'allowSearch' => true,
));
  
  
// load list records from 'interview'
list($interviewRecords, $listMetaData) = getRecords(array(
'tableName'   => 'interview',
'loadUploads' => true,
'allowSearch' => true,
));
  
  
// load list records from 'profile'
list($friendsRecords, $listMetaData) = getRecords(array(
'tableName'   => 'profile',
'loadUploads' => true,
'allowSearch' => true,
));

 
$listRecords = array_merge($reviewRecords, $interviewRecords, $profileRecords);

I would like to show 12 items per page, but importantly it must be the 12 most recent entries from ALL the tables that have been merged.

As noted by Jason in the forum back in 2011 the perPage option will only limit the number of record at the time the query takes place and can't be applied afterwards. Jason also linked to this post but the link is broken now: http://www.interactivetools.com/forum/gforum.cgi?post=88664#88664.

Any help gratefully received

Thanks

Jeff

By ross - May 12, 2016

Hi Tim

Thanks for posting.  What you'll need here is "usort()". 

You can read up on "usort()" in the PHP docs here: http://php.net/manual/en/function.usort.php

Also, I based most of this post on code I found in this thread on StackOverflow:

http://stackoverflow.com/questions/2910611/php-sort-a-multidimensional-array-by-element-containing-date

Let's go over an example.

First, load the records from our tables:

// load records from 'list 1'
  list($list1Records) = getRecords(array(
    'tableName'   => 'sample_multi_record',
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => 12,
    'orderBy'     => "createdDate DESC",
  ));
  
   // load records from 'list 2'
  list($list2Records) = getRecords(array(
    'tableName'   => 'another_multi_section',
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => 12,
    'orderBy'     => "createdDate DESC",
  ));
  
    // load records from 'list 3'
  list($list3Records) = getRecords(array(
    'tableName'   => 'yet_another_multi_section',
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => 12,
    'orderBy'     => "createdDate DESC",
  ));

Notice how I am sorted by "createdDate DESC" and using "limit 12" in all three.  This way we only ever get a max of 12 records from each table and they are already kind of sorted.

Next, let's merge all the arrays together using array_merge() like this:

// merge arrays
$mergedList1AndList2AndList3 = array_merge($list1Records,$list2Records,$list3Records);

Next, time to sort the merged array

// sort merged arrays
usort($mergedList1AndList2AndList3, 'date_compare');
  
## compare two dates
## if returned value is positibe $a was the larger date
## if returned value is negative $b was the larger date
function date_compare($a, $b)
{
  $t1 = strtotime($a['createdDate']);
  $t2 = strtotime($b['createdDate']);
  return $t2 - $t1;
}

Notice how usort() uses a callback function to do the actual sorting. 

The last thing will be to reduce the merged and sorted list to the first 12 records using array_slice().

// only need first 12 elements from merged and sorted list
$first12Records = array_slice($mergedList1AndList2AndList3, 0, 12);

Now you have an array called "first12Records" that contains 12 records taken from three different sections and sorted by "createdDate".

Does that all make sense?  

Give it a shot and let me know how you make out.

-----------------------------------------------------------
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 JeffC - May 13, 2016

Hi Ross

Thanks for your help.

A great solution, that's got me on the right track, but it isn't quite what I was after.

Your solution works perfectly to show the most recent 12 entries from all lists - but it only displays the first 12. I would like to show all of the entries, but 12 per page. i.e.

Page 1 = 1-12

Page 2 = 13-24

Page 3 = 25-36 etc

Jeff

By ross - May 17, 2016

Hi Jeff

I see what you mean and there isn't really a simple solution and I'd need to build something custom for you through consulting.  

You can send me an email to consulting@interactivetools.com if you'd like to have that conversation.

Let me know what you think.

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/