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