Records from multiple sections into one viewer

16 posts by 6 authors in: Forums > CMS Builder
Last Post: July 13, 2011   (RSS)

By aev - August 12, 2009

Hi!

Can we join/merge records from 3 or more different sections into one viewer?

We would like to display this in two different ways.
1. Display the 9 newest records regardless of what section it came from.
2. Display the 9 newest records in i similar fashion, but force it to be created from the 3 newest records from each of the 3 sections.

Any suggestions on how we can do this?

-aev-

Re: [aev] Records from multiple sections into one viewer

By Chris - August 12, 2009 - edited: August 13, 2009

Hi aev,

This is certainly possible, but requires no small amount of PHP. Here's how to display your 9 newest records, regardless of which sections each record came from:

Firstly, generate "list page" viewer code for every section and copy the getRecords() calls from each:

list($productsRecords, $productsMetaData) = getRecords(array(
'tableName' => 'products',
));

To help improve efficiency, you can add a limit (and orderBy) to each request, since you won't need more than 9 records from a single section:

list($productsRecords, $productsMetaData) = getRecords(array(
'tableName' => 'products',
'limit' => '9',
'orderBy' => 'createdDate DESC',
));

To get the 9 newest records from all the sections, you'd put all the records in one list, sort them by date, and discard everything after the 9th record.

<?php

require_once "C:/wamp/www/cmsbuilder_1_31/cmsAdmin/lib/viewer_functions.php";

------------------------------8<------------------------------
*** INSERT ALL YOUR getRecord() CALLS HERE, REPLACING MINE ***

list($productsRecords, $productsMetaData) = getRecords(array(
'tableName' => 'products',
'limit' => '9',
'orderBy' => 'createdDate DESC',
));
list($categoryRecords, $categoryMetaData) = getRecords(array(
'tableName' => 'category',
'limit' => '9',
'orderBy' => 'createdDate DESC',
));
--------------------------------------------------------------

// put all the records in one list
$allRecords = array_merge(
----------------------------8<-----------------------------
*** LIST ALL YOUR RECORD VARIABLES HERE, REPLACING MINE ***

$productsRecords,
$categoryRecords,
-----------------------------------------------------------
);

// sort them by date
function createdDateCompareFunction($a, $b) { return -strcmp($a['createdDate'], $b['createdDate']); }
usort($allRecords, 'createdDateCompareFunction');

// discard everything after the 9th record
$newestNineRecords = array_slice($allRecords, 0, 9);
?>

Now you can loop over them with a foreach, but beware: since your records can come from any section, they don't necessarily have the same fields!

<?php foreach ($newestNineRecords as $record): ?>
Created: <?php echo $record['createdDate'] ?><br/>
_link : <a href="<?php echo $record['_link'] ?>"><?php echo $record['_link'] ?></a><br/>
<?php endforeach; ?>

Let us know when you've got that working and we can show you how to get the 3 newest records from 3 sections.

If you have any trouble, please post your Viewer code and any error messages you're getting.

EDIT: updated usort code to work with older versions of PHP
All the best,
Chris

Re: [chris] Records from multiple sections into one viewer

By aev - August 12, 2009

Hi Chris,

your code for this looks great, but I'm having some trouble with this line:
// sort them by date
usort($allRecords, function ($a, $b) { return -strcmp($a['createdDate'], $b['createdDate']); });


It gives me this error:
Parse error: syntax error, unexpected T_FUNCTION in ...

Any suggestions?

Re: [aev] Records from multiple sections into one viewer

By Chris - August 12, 2009 - edited: August 12, 2009

Hi aev,

Oops, my bad. That syntax only works for newer PHPs. Please try replacing the usort line with this:

// sort them by date
function createdDateCompareFunction($a, $b) { return -strcmp($a['createdDate'], $b['createdDate']); }
usort($allRecords, 'createdDateCompareFunction');
All the best,
Chris

Re: [chris] Records from multiple sections into one viewer

By aev - August 12, 2009

Thanks! Now everything works perfect!

-aev-

Re: [aev] Records from multiple sections into one viewer

By Chris - August 13, 2009 - edited: August 13, 2009

Good to hear! :D


2. Display the 9 newest records in i similar fashion, but force it to be created from the 3 newest records from each of the 3 sections.


One question: is this going to be displayed on the same page? If so, we can reuse those getRecord() calls with some array_slice()ing, otherwise we can make new getRecord() calls.
All the best,
Chris

Re: [chris] Records from multiple sections into one viewer

By aev - August 13, 2009

One question: is this going to be displayed on the same page?


No it is not going to be on the same page.

-aev-

Re: [aev] Records from multiple sections into one viewer

By Chris - August 13, 2009

Hi aev,

To get a merged (i.e. globally sorted) list of the newest 3 records from 3 sections, everything will be the same as above, but with two exceptions. Copy the page you made and make the following changes:

1. In each of your three getRecord() calls, request a limit of 3 records instead of 9. Replace this

'limit' => '9',

with this

'limit' => '3',

2. Since you'll have 9 records after merging, you can remove the redundant array_slice(). Delete the following code:

// discard everything after the 9th record
$newestNineRecords = array_slice($allRecords, 0, 9);

Finally, you may want to change the name of your variable from $allRecords to $newRecordSample or whatever makes sense.

Your final code should look something like this:

<?php

require_once "C:/wamp/www/cmsbuilder_1_31/cmsAdmin/lib/viewer_functions.php";

list($aRecords, $aMetaData) = getRecords(array(
'tableName' => 'a',
'limit' => '3',
'orderBy' => 'createdDate DESC',
));
list($bRecords, $bMetaData) = getRecords(array(
'tableName' => 'b',
'limit' => '3',
'orderBy' => 'createdDate DESC',
));
list($cRecords, $cMetaData) = getRecords(array(
'tableName' => 'c',
'limit' => '3',
'orderBy' => 'createdDate DESC',
));

// put all the records in one list
$newRecordSample = array_merge(
$aRecords,
$bRecords,
$cRecords,
);

// sort them by date
function createdDateCompareFunction($a, $b) { return -strcmp($a['createdDate'], $b['createdDate']); }
usort($newRecordSample, 'createdDateCompareFunction');
?>

...

<?php foreach ($newRecordSample as $record): ?>
Created: <?php echo $record['createdDate'] ?><br/>
_link : <a href="<?php echo $record['_link'] ?>"><?php echo $record['_link'] ?></a><br/>
<?php endforeach; ?>

Please let us know if you have any problems or have any more questions! :D
All the best,
Chris

Re: [chris] Records from multiple sections into one viewer

By aev - August 13, 2009

Hi Chris!

A small and easy change but nevertheless brilliant!
Thanks providing this code[:)]

-aev-