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 

By Jenna - January 27, 2021

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.

Jenna Cooke - PHP Programmer
interactivetools.com