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: [chris] Records from multiple sections into one viewer

By aev - August 12, 2009


If I comment out the "usort" line giving me trouble, everything works fine except it is not globally sorted.

Could please post some instructions on how to force merged records to be from all sections, then sorted by date after merge.. [:)]

Thanks for excellent help on this!

-aev-

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: [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-