nearly have it...but need a wee bit of assistance. attempting to single out selected results from a details page in one section, of selected fields from another section

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

By Codee - June 11

Greetings CMSB Fans,

Here's what I am attempting to accomplish: I have table sections called "lessons" and "cases".  Data, graphics, etc. go into both tables, and many records created in each. In the lessons table there is a field titled "case_examples" which connects to the cases table and outputs a checkbox for the content admin can click and choose any relevant cases applying to that lesson. Now the output on the front end is to have the lessonDetails.php display all of its graphics and data, and at the end of the lesson there is a field titled "case_examples" which we need to display the few cases that were selected.

In the top of my lessonDetails.php page after the call to the database I have:

 // load record from 'lessons'
  list($lessonsRecords, $lessonsMetaData) = getRecords(array(
    'tableName'   => 'lessons',
    'where'       => whereRecordNumberInUrl(0),
    'loadUploads' => true,
    'allowSearch' => false,
    'limit'       => '1',
  ));
  $lessonsRecord = @$lessonsRecords[0]; // get first record
  if (!$lessonsRecord) { dieWith404("That record is not currently available. Please click BACK in your browser."); } // show error message if no record found
  
 		// load records from 'cases'
  list($casesRecords, $casesMetaData) = getRecords(array(
    'tableName'   => 'cases',
    'joinTable' => 'lessons',
    'loadUploads' => true,
    'allowSearch' => true,
  ));

Then in the body, also part of where I'm stuck I have:

<!--begin relevant case studies links-->
<?php if ($lessonsRecord['case_examples']): ?>
	<div class="pdfarea"><br><h4>Relevant Case Studies:</h4></div>
	<br>
	<div class="directions">
		<span class="flyertext">
			[clicking on the hypertext opens the case in a new window.]
		</span>
		<br>
	</div>
  <?php foreach ($casesRecords as $listRecord): ?>
	   <?php $isSelected = ($listRecord['num']); ?>
			<div class="inversebox">
		 		<?php if ($isSelected) { print "<b>"; } ?>
					<a href="<?php echo htmlencode($listRecord['_link']) ?>" target="_blank" rel="noopener nofollow ugc">
						<?php echo htmlencode($listRecord['title']) ?>
					</a>
					<br>
				<?php if ($isSelected) { print "</b>"; } ?>
			</div>
	<?php endforeach ?>
<?php endif ?>

<!--these are the num values for relevant related cases-->

In cmsb database editor within the "lessons" table the field case_examples is a list field with the following parameters:

Display as: checkboxes

Section Tablename: cases

Use this field for option values: num

Use this field for option labels: title

*I did attempt different type of field, related record, after choosing type: none, but had no clue what to do after that.

Obviously this code is neither totally correct, nor complete.  The current display front end result is that ALL the cases in the database are displaying for the case_examples field instead of the ones selected from within the database record. And no errors.

By Dave - June 13

Hi Codee, 

Let's try adding a where to your cases query at the top (and removing the 'joinTable'), try this: 

// get cases for this lesson
$caseNums      = $lessonsRecord['case_examples:values'] ?? [];  // array of case nums (default to empty array)
$caseNums      = array_map('intval', $caseNums);                // ensure nums are integers
$caseNumsAsCSV = implode(",", $caseNums);                       // convert to CSV string, e.g., 1,2,3
$caseNumsAsCSV = $caseNumsAsCSV ?: '0';                         // default to '0' if empty
$casesWHERE    = "num IN ($caseNumsAsCSV)";                     // build WHERE clause for SQL query

// load records from 'cases'
list($casesRecords, $casesMetaData) = getRecords(array(
    'tableName'   => 'cases',
    'loadUploads' => true,
    'allowSearch' => true,
    'where'       => $casesWHERE,
));

Let me know if that works for you.

Dave Edis - Senior Developer
interactivetools.com