Display related table fields for each listing on a LIST page
6 posts by 2 authors in: Forums > CMS Builder
Last Post: June 28, 2024 (RSS)
By Deborah - June 24, 2024
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, 2024
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, 2024
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.
interactivetools.com
By Deborah - June 27, 2024
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 - June 27, 2024
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!
interactivetools.com
By Deborah - June 28, 2024
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