display associated record field for each record on list page

9 posts by 4 authors in: Forums > CMS Builder
Last Post: January 27, 2021   (RSS)

By Deborah - December 4, 2020

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, 2020

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']);

?>
Cheers,

Tim (toledoh.com.au)

By Deborah - December 5, 2020

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, 2020

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?

Cheers,

Tim (toledoh.com.au)

By Deborah - December 6, 2020

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

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

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By Deborah - December 7, 2020

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 

Good morning Deborah,

You're welcome.

I wish I could help more, but my skills in this area are limited as well.

Jerry Kornbluth

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php