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