Display related table fields for each listing on a LIST page

6 posts by 2 authors in: Forums > CMS Builder
Last Post: Friday at 10:02am   (RSS)

By Deborah - June 24

Hello, All.

I'm attempting to display data on a LIST page from a related table using a "List Options from Database" radio field.

No errors appear, but neither does the related 'website_url' field or other related fields.

Setup for the "gallery_showing" radio field in the 'paintings' table:
 Get Options from database (advanced)
 Section Tablename = galleries
 Option values = num
 Option labels = gallery_name

// Get list of paintings
  list($paintingsRecords, $paintingsMetaData) = getRecords(array(
    'tableName'   => 'paintings',
    'where' => ' category LIKE "%available%" ', 
    'loadUploads' => true,
    'allowSearch' => false,
    'loadCreatedBy' => false,
  ));
  
  // load records from 'galleries'
  list($galleriesRecords, $galleriesMetaData) = getRecords(array(
    'tableName'   => 'galleries',
    'loadUploads' => true,
    'allowSearch' => false,
    'loadCreatedBy' => false,
  ));

-------------

<?php foreach ($paintingsRecords as $record): ?>

 
 <?php // load info from 'paintings' table
       foreach ($record['painting'] as $index => $upload): ?>
         <img src="<?php echo htmlencode($upload['thumbUrlPath2']) ?>">
      <?php endforeach ?> 
     
     
  <?php // load record from 'galleries' table
      // that matches this painting's selection for "gallery_name"   
           list($galleriesRecords, $galleriesMetaData) = getRecords(array(
          'tableName'   => 'galleries',
          'where'       => "`num` = ".$record['num'],
          'allowSearch' => false,
          'limit'       => '1',
     ));
    ?> 
    
  <?php // display all 'galleries' table related fields
           if (!empty($galleriesRecord['website_url'])): ?>
           Website: <?php echo htmlencode($galleriesRecord['website_url']) ?>
           <?php else: ?>
           (No website)
           <?php endif ?>
    
    [... do same for other related fields ...]
    

<?php endforeach ?>

The code below is mostly based on code from another project, but I can't determine the differences. Hope I'm at least headed in the right direction?

Thanks in advance for any insights!
~ Deborah

By Deborah - Thursday at 2:24pm

Hey, All.

At the risk of being overly insistent...

Someone out there in CMSB Land must have this solution in their toolkit? Any insights would be greatly appreciated!

Thanks!
~ Deborah

By Dave - Thursday at 3:28pm

Hi Deborah, 

If you have under a hundred galleries, you could just load them all into memory and look them up by num like this: 

// load records from 'galleries'
[$galleriesRecords, $galleriesMetaData] = getRecords([
    'tableName'     => 'paintings',
    'loadUploads'   => true,
    'allowSearch'   => false,
    'loadCreatedBy' => false,
]);
$numToGallery = array_column($galleriesRecords, null, 'num');

Then you can load and display them like this:

<?php foreach ($paintingsRecords as $record): ?>
  <?php $gallery = $numToGallery[$record['gallery_showing']] ?? []; ?>

    <?php if (!empty($gallery['website_url'])): ?>
        Website: <?php echo htmlencode($gallery['website_url']) ?>
    <?php else: ?>
        (No website)
    <?php endif ?>

Here's what's happening: 

  • We're assuming that paintings.gallery_showing contains the record number of the galley, e.g., gallery.num
  • We create an array of galleries indexed by their record num so we can look them up by gallery num
  • As we loop over the painting records, we try and set $galley to the gallery record from our $numToGallery array
  • If there's no gallery defined, or it's not found, the (PHP8) ?? code means "if the previous value wasn't set use the next one, and [] is an empty array
  • So $gallery is an array of gallery values or an empty array.
  • Then we just check if each value you want is defined and show it.

Hope that helps!  Let me know if that works for you.

Dave Edis - Senior Developer
interactivetools.com

By Deborah - Thursday at 4:06pm

Dave, thank you for your code suggestion.

I didn't have success with my first implementation attempt, but will have a  fresh look in the AM (East Coast time here).

Your help is much appreciated, as always!
~ Deborah

By Deborah - Friday at 10:02am

Dave, success!

Turns out I provided the incorrect field name for the radio field in the 'paintings' table. It should have been "galleries_num", not "gallery_showing".

<?php $gallery = $numToGallery[$record['galleries_num']] ?? []; ?>

Many thanks for the code and the explanation! I'll likely be using this method quite often in the future.

~ Deborah