display associated record field for each record on list page

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

?>

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

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

Good morning Deborah,

You're welcome.

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

Jerry Kornbluth

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.