display associated record field for each record on list page
9 posts by 4 authors in: Forums > CMS Builder
Last Post: January 27 (RSS)
By Deborah - December 4
Hi, Everyone. I've searched the forum extensively, but haven't found anything for this scenario and hope someone can guide me to a solution.
----------------------------------
TABLES:
// list of moorings
list($mooringsRecords, $mooringsMetaData) = getRecords(array(
'tableName' => 'moorings',
'allowSearch' => false,
));
// list of inspections records
list($inspectionsRecords, $inspectionsMetaData) = getRecords(array(
'tableName' => 'inspections',
'allowSearch' => false,
));
----------------------------------
DESIRED HTML OUTPUT on the Moorings List Viewer page:
<?php foreach ($mooringsRecords as $record): ?>
(the usual records for $mooringsRecords)
+ most recent ['inspection_date'] field from $inspectionsRecords that matches this moorings record number
<?php endforeach ?>
----------------------------------
The $inspections ["mooring_number"] field editor setup:
Field Options - Display as pulldown
Get options from database (advanced)
Section Tablename = moorings
Field for option values = num
Field for option labels = num
Each mooring can have multiple ["inspection_date"]s, but we only want to display the most recent date for each mooring.
----------------------------------
Hope I explained that well enough. Thanks for any help you can give!
~ Deborah
By Toledoh - December 4
Hi Deborah.
Someone else will be able to give more expert / elegant guidance, but maybe something along the lines of this inserted within the loop?
<?php
// load record from 'inspections'
list($inspectionsRecords, $inspectionsMetaData) = getRecords(array(
'tableName' => 'inspections',
'where' => "`num` = ".$record['num'],
'loadUploads' => true,
'allowSearch' => false,
'limit' => '1',
));
$inspectionsRecord = @$inspectionsRecords[0]; // get first record
if (!$inspectionsRecord) { dieWith404("Record not found!"); } // show error message if no record found
echo htmlencode($inspectionsRecord['title']);
?>
Tim (toledoh.com.au)
By Deborah - December 5
Hi, Tim.
Your suggestion was brilliant! I never knew it was possible to add a records call within a loop.
I removed the "dieWith404" because that caused a "headers already sent" error for any moorings that did not contain a maintenance record.
RESULTING CODE:
***** HEAD *****
// list of moorings
list($mooringsRecords, $mooringsMetaData) = getRecords(array(
'tableName' => 'moorings',
'allowSearch' => false,
));
***** HTML (LIST VIEWER PG) *****
<?php foreach ($mooringsRecords as $record): ?>
(mooring records...)
<?php // LAST INSPECTION DATE
// load record from 'maintenance' table
list($maintenanceRecords, $maintenanceMetaData) = getRecords(array(
'tableName' => 'maintenance',
'where' => "`num` = ".$record['num'], // match record num in 'moorings'
'orderBy' => "maintenance_date DESC", // get most recent
'allowSearch' => false,
'limit' => '1',
));
$maintenanceRecord = @$maintenanceRecords[0]; // get first record
// if (!$maintenanceRecord) { dieWith404("Record not found!"); } // removed to avoid headers error
?>
Last Inspection Date:
<?php if ($inspectionsRecord['inspection_date']): ?>
<?php echo date("m/d/Y", strtotime($inspectionsRecord['inspection_date'])) ?>
<?php endif ?>
<?php // end mooring records
endforeach ?>
Thank so much - I truly appreciate your help!
~ Deborah
By Toledoh - December 5
Great.
My only concern is that if you have a lot of moorings, and you do a getRocords for each, it might ge quite a load. Maybe theres a leftJoin or a mysqlselect kind of version that would be better?
Tim (toledoh.com.au)
By Deborah - December 6
Tim, that thought occurred to me, too.
I'm wondering if there is a solution such as you suggested. Maybe someone will have that answer.
~ Deborah
By gkornbluth - December 6
Hi Deborah,
Don't know if it will help, but take a look at this post.
https://www.interactivetools.com/forum/forum-posts.php?postNum=2232753
Jerry Kornbluth
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
By Deborah - December 7
Hi, Jerry.
That was an interesting post - thank you!
With my limited PHP skills, I wasn't able to adjust the code to work for my example that does not need to reference uploads. Good to know uploads can be included, though.
Thanks again.
Deborah
By gkornbluth - December 7
Good morning Deborah,
You're welcome.
I wish I could help more, but my skills in this area are limited as well.
Jerry Kornbluth
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
By Jenna - January 27
Hi all,
Just taking a look at this old post and noticed that there could still be unanswered questions. Do you still require assistance?
We'd be happy to help, please let us know.
interactivetools.com