Related Pages using leftJoin?

3 posts by 2 authors in: Forums > CMS Builder
Last Post: June 22, 2020   (RSS)

By Toledoh - June 18, 2020

Hi All.

I've got a section "Pages", and within that I've got a mulit-select (pillbox) called "related_pages".

I was thinking that I could use something like this to list all the "related pages" that a certain page has.

  // load record from 'pages'  list($pagesRecords, $pagesMetaData) = getRecords(array(
'tableName'   => 'pages',
'where'       => '`num` =' .$selectedCategory['num'],
'loadUploads' => true,    'allowSearch' => false,
'limit'       => '1',
 ));

$pagesRecord = @$pagesRecords[0]; // get first record
if (!$pagesRecord) { dieWith404("Record not found!"); } // show error message if no record found

foreach($pagesRecords as $key => $related){
$relatedNums = implode(", ", $related['related_pages:values']);
if($relatedNums){     $pagesRecords[$key]['related_pages:records'] = mysql_select('pages', "`num` IN ($relatedNums)");
}
}

Would that work?  And how would I utilise the upload files in those relatedRecords?

I'm trying to produce a loop like;

FOR EACH

<div class="card mb-2">
<img src="#image_url#" class="img-fluid" alt="placeholder">
<div class="card-body">
<h5 class="card-title">#related_title#</h5>
<p class="card-text">#related_intro#</p>
<a href="#related_link#" class="btn btn-primary">Go</a>
</div>
</div>

END

Cheers,

Tim (toledoh.com.au)

By gregThomas - June 22, 2020

Hey Tim,

Your method would work great, the only change I would make is to check the related_pages values exist before imploding them, or you might get an error:

<?php

  // load record from 'pages'
  list($pagesRecords, $pagesMetaData) = getRecords(array(
  'tableName'   => 'pages',
  'where'       => '`num` =' .$selectedCategory['num'],
  'loadUploads' => true,    'allowSearch' => false,
  'limit'       => '1',
  ));

  $pagesRecord = @$pagesRecords[0]; // get first record
  if (!$pagesRecord) { dieWith404("Record not found!"); } // show error message if no record found

  if (!empty($related['related_pages:values'])) {
    $relatedNums = implode(", ", $related['related_pages:values']);
    if($relatedNums) { $pagesRecord['related_pages:records'] = mysql_select('pages', "`num` IN ($relatedNums)"); }
  }

I also noticed that the HTML contains an image URL, so you might need to swap out the mysql_select for a getRecords so that the upload records are included.

Cheers,

Greg

Greg Thomas







PHP Programmer - interactivetools.com

By Toledoh - June 22, 2020

Thanks Greg - Great!

Cheers,

Tim (toledoh.com.au)