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)

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 Dave - Thursday at 4:11pm

Hi Deborah,

Sure, and feel free to send in a 2nd level support request if you don't get it right away and I'll take a look:
https://www.interactivetools.com/support/request/

When we can see the code it just takes a minute.

Good luck!

Dave Edis - Senior Developer
interactivetools.com

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