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