Filter category list to display records associated with a multi-select options from a drop down list.

14 posts by 3 authors in: Forums > CMS Builder
Last Post: October 29, 2018   (RSS)

By Mikey - May 6, 2018 - edited: May 6, 2018

I'm working on a catering menu and I need to filter and display records from a "category" menu which have drop down list multi-selection from a field called "catering_menu_type". There are two options listed in the drop down multi-selector as seen as below:

1|Main Catering
2|Box Catering

Im using a multi-selection drop down list, so a record can have both options selected, but I need to retrieve only the records matching the "catering_menu_type" selected.

Here's what I'm working with, and I've set the filter to bold to show what I'm trying to achieve.

<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php

  // load viewer library
  $libraryPath = 'cmsbuilder/lib/viewer_functions.php';
  $dirsToCheck = array('/home/server/public_html/','','../','../../','../../../');
  foreach ($dirsToCheck as $dir) { if (@include_once("$dir$libraryPath")) { break; }}
  if (!function_exists('getRecords')) { die("Couldn't load viewer library, check filepath in sourcecode."); }
  if (!@$GLOBALS['GEOCODER_PLUGIN'])         { die("You must activate the Geocoder plugin before you can access this page."); } 
  if (!@$GLOBALS['GEOCODER_GOOGLE_API_KEY']) { die("You must have a Google Maps API key to display a map, see the readme file for instructions on getting one."); exit; } 
  if (!@$GLOBALS['WEBSITE_MEMBERSHIP_PLUGIN']) { die("You must activate the Website Membership plugin before you can access this page."); }
  
        // load menu
  list($catering_menuRecords, $selectedCategory) = getCategories(array(  
    'tableName'           => 'menu',  
    'where' => 'catering_menu_type = 1',
    'selectedCategoryNum' => '',         // defaults to getNumberFromEndOfUrl()  
    'categoryFormat'      => 'showall',  // showall, onelevel, twolevel  
  ));
  
        // load menu
  list($catering_menuAnchorsRecords, $selectedCategory) = getCategories(array(  
    'tableName'           => 'menu',  
    'where' => 'catering_menu_type = 1',
    'selectedCategoryNum' => '',         // defaults to getNumberFromEndOfUrl()  
    'categoryFormat'      => 'showall',  // showall, onelevel, twolevel  
  ));
?>

<!doctype html>
<html>
<head>
<meta charset="UTF-8">

</head>
<body onload="initialize()">

<ul>
<?php foreach ($catering_menuAnchorsRecords as $index => $categoryAnchorsRecord): ?>
  <?php if ($categoryAnchorsRecord['depth'] == 0): ?>
      <li><a href="#scrollToAnchor<?php echo $categoryAnchorsRecord['num'];?>" title="<?php echo $categoryAnchorsRecord['name'];?>"><?php echo $categoryAnchorsRecord['name'] ?></a></li>
  <?php endif ?>
<?php endforeach ?>
</ul>

<?php foreach ($catering_menuRecords as $index => $categoryRecord): ?>
<?php if ($categoryRecord['depth'] == 0): ?>

<div id="scrollToAnchor<?php echo $categoryRecord['num'] ?>"></div>
<h3><?php echo $categoryRecord['name'] ?></h3>
<?php $parent=$categoryRecord['num'];?> 

<ul>                        
  <?php foreach ($catering_menuRecords as $index => $categoryRecord): ?>
    <?php if ($categoryRecord['parentNum'] == $parent): ?>
      <li>
        <h4><?php echo $categoryRecord['name'];?></h4>
        <?php echo $categoryRecord['content'] ?>
      </li>
    <?php endif ?>
  <?php endforeach; ?> 
</ul>  

<?php endif ?>
<?php endforeach; ?> 

</body>
</html>

I've tried every method I could think of to filter these, but nothing has worked. Any assistance would be greatly appreciated.

Thanks, Zicky

By Mikey - May 8, 2018 - edited: May 8, 2018

I've tried so many things and nothing seems to work. Searched the entire forum looking for some tip to help me get the desired results with no luck.

The 'where' below throws errors.

        // load menu
  list($catering_menuRecords, $selectedCategory) = getCategories(array(  
    'tableName'           => 'menu',  
    'where' => 'catering_menu_type = 1',
    'selectedCategoryNum' => '',         // defaults to getNumberFromEndOfUrl()  
    'categoryFormat'      => 'showall',  // showall, onelevel, twolevel  
  ));
  
        // load menu
  list($catering_menuAnchorsRecords, $selectedCategory) = getCategories(array(  
    'tableName'           => 'menu',  
    'where' => 'catering_menu_type = 1',
    'selectedCategoryNum' => '',         // defaults to getNumberFromEndOfUrl()  
    'categoryFormat'      => 'showall',  // showall, onelevel, twolevel  
  ));

So I decided to try if statements of all sorts.

The code below will get the first option 1|Main Catering from the multi-selection drop down when it's set to ['catering_menu_type'] == 1, but It will not load anything beyond the first option. For example: I changed ['catering_menu_type'] == 1 to ['catering_menu_type'] == 2 and nothing loads for 2|Box Catering.

My head hurts!

<?php foreach ($catering_menuAnchorsRecords as $index => $categoryAnchorsRecord): ?>
  <?php if ($categoryAnchorsRecord['depth'] == 0): ?>
          <?php if ($categoryAnchorsRecord['catering_menu_type'] == 2): ?>
      <li><a href="#scrollToAnchor<?php echo $categoryAnchorsRecord['num'];?>" title="<?php echo $categoryAnchorsRecord['name'];?>"><?php echo $categoryAnchorsRecord['name'] ?></a></li>
          <?php endif ?>
  <?php endif ?>
<?php endforeach ?>
</ul>

<?php foreach ($catering_menuRecords as $index => $categoryRecord): ?>
<?php if ($categoryRecord['depth'] == 0): ?>
          <?php if ($categoryRecord['catering_menu_type'] == 2): ?>

<div id="scrollToAnchor<?php echo $categoryRecord['num'] ?>"></div>
<h3><?php echo $categoryRecord['name'] ?></h3>
<?php $parent=$categoryRecord['num'];?> 

<ul>                        
  <?php foreach ($catering_menuRecords as $index => $categoryRecord): ?>
    <?php if ($categoryRecord['parentNum'] == $parent): ?>
          <?php if ($categoryRecord['catering_menu_type'] == 2): ?>
      <li>
        <h4><?php echo $categoryRecord['name'];?></h4>
        <?php echo $categoryRecord['content'] ?>
      </li>
          <?php endif ?>
    <?php endif ?>
  <?php endforeach; ?> 
</ul>  

  <?php endif ?>
<?php endif ?>
<?php endforeach; ?>

Zicky

By leo - May 8, 2018 - edited: May 8, 2018

Hi Zicky,

The multiselect value is actually stored as a tab-separated string in the database, so you can't get them by "catering_menu_type = 1". Try change where statement to: catering_menu_type LIKE "%\t1\t%".

Leo - PHP Programmer (in training)
interactivetools.com

By Mikey - May 8, 2018

Hey Leo,

Thanks for the response and suggestion. I did try that prior to the original post... no joy. It produced the following error message:

Category Viewer (catering_menu) errors
Unknown option 'where' specified
Valid option names are: (tableName, useSeoUrls, debugSql, selectedCategoryNum, categoryFormat, loadUploads, defaultCategory, rootCategoryNum, ulAttributes, ulAttributesCallback, liAttributesCallback, loadCreatedBy, ignoreHidden)

Zicky

By leo - May 8, 2018

Oh didn't realize it's getCategories(). You can use getRecords() to get the record and if you need additional category info then use getCategories()

Leo - PHP Programmer (in training)
interactivetools.com

By Mikey - May 8, 2018

Yea, I tried get getRecords() in prior attempts to get this working, but it threw the following errors as well:

getRecords(catering_menu) errors
Unknown option 'selectedCategoryNum' specified. Valid option names are: (tableName, where, orWhere, orderBy, limit, offset, perPage, loadUploads, allowSearch, requireSearchMatch, loadCreatedBy, useSeoUrls, loadListDetails, joinTable, debugSql, leftJoin, useCache, pageNum, ignoreHidden, ignorePublishDate, ignoreRemoveDate, requireSearchSuffix, includeDisabledAccounts, addSelectExpr, groupBy, having, loadPseudoFields)
Unknown option 'categoryFormat' specified. Valid option names are: (tableName, where, orWhere, orderBy, limit, offset, perPage, loadUploads, allowSearch, requireSearchMatch, loadCreatedBy, useSeoUrls, loadListDetails, joinTable, debugSql, leftJoin, useCache, pageNum, ignoreHidden, ignorePublishDate, ignoreRemoveDate, requireSearchSuffix, includeDisabledAccounts, addSelectExpr, groupBy, having, loadPseudoFields)

I'm not sure what to do at this point. I've tried countless scenarios with no success.

Zicky

By leo - May 8, 2018 - edited: May 8, 2018

Hi Zicky,

With getRecords(), you can't set "selectedCategoryNum" and "categoryFormat".

Leo - PHP Programmer (in training)
interactivetools.com

By Mikey - May 8, 2018

Hey Leo,

any suugestions on how I could filter out results using an if statement to look for match values?

Example: <?php if ($categoryRecord['catering_menu_type'] == 2): ?>

zicky

By leo - May 8, 2018 - edited: May 8, 2018

Hi Zicky,

Again, depending on the field type. If it's a multiselect field then you may need to use preg_match(): http://php.net/manual/en/function.preg-match.php.

Or explode('\t', trim($records)); to get an array of records then check if the value is in this array: if(in_array(2, $categoryRecord['catering_menu_type']))

Leo - PHP Programmer (in training)
interactivetools.com

By Mikey - May 8, 2018

Thanks for the help Leo!

I got this working using the if statements below.

<?php if ( (preg_match("/1/", $categoryRecord['catering_menu_type'])) ): ?>
<?php endif; ?>

<?php if ( (preg_match("/2/", $categoryRecord['catering_menu_type'])) ): ?>
<?php endif; ?>

Zick