Display related table fields for each listing on a LIST page

6 posts by 2 authors in: Forums > CMS Builder
Last Post: June 28   (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 - June 27

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 - June 27

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 - June 27

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 - June 28

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