5 posts by 2 authors in: Forums > CMS Builder
Last Post: 2 hours ago   (RSS)

1. I have a category section editor called “directory_staff_categories” where I can create simple categories to be used as options to select from in other areas of the site as multi-select options.

2. I have a personnel directory that allows me to select multiple categories “associated_departments” to associate a person with. This pillbox multi-select “associated_departments” pulls it’s available options from a category section as defined in #1 above.

3. I have other areas of my website “like the home page, about us, etc.” set up to allow me to select from “dept_personnel” options using the pillbox multi-select. The options available for the “dept_personnel” multi-select come from the “directory_staff_categories” as defined in #1.

4. This string below worked great for filtering through and finding personnel that had the same selections as defined with the home page. But since I upgraded my website to a newer version of CMS Builder, all personnel show on my home page if there are no options selected for the home page or the personnel.

5. I believe something has changed with the new pillbox multi-selection feature, which requires my code below to be updated, but I’m not sure where to start looking, so I thought I’d ask and see if anyone has some suggestions on what could be wrong with the code below when comparing multi-selections from two different records and looking for matches.

$searchString = '';
  //Create the where statement to be used to filter personnel
  
  //if departement values have been selected....
  if(is_array(@$home_pageRecord['dept_personnel:values'])){
    //Count the total number if items in the array.....
    $arrayCounter = count($home_pageRecord['dept_personnel:values']);
    //Loop through the array....
    foreach($home_pageRecord['dept_personnel:values'] as $key => $filterItem){
      //create the search string that searches the associated_departements for the associated value....
      $searchString .= "associated_departments LIKE '%\t$filterItem\t%'";
      //If this is not the last item to filter by, add or to the statement....
      if(($key+1) != $arrayCounter){
        $searchString .= " OR ";
      }
    }
  }
  
///////////////// directory_staff /////////////////

  // load records from 'directory_staff'
  list($directory_staffRecords, $directory_staffMetaData) = getRecords(array(
    'tableName'   => 'directory_staff',
    //'perPage'     => '10',
    'where'       => $searchString,
    'loadUploads' => true,
    'allowSearch' => false,
    'orderBy'     => 'last_name ASC',
  ));

Thanks Zicky

Hey Daniel,

Just to clarify... the trouble I am having is that "IF" there is/are no selection(s) made from the site page's "dept_personnel" pillbox multi-select options, then all personnel appear on the site page. So what I'm trying to do is show NO personnel if no "dept_personnel" pillbox multi-select options are chosen, and IF a "dept_personnel" selection is made - only show those personnel who have a matching category num. 

I hope that makes sense.

Below is the output you asked for.

#1 Output when two categories are selected from the "dept_personnel" pillbox:

Array
(
    [0] => 52
    [1] => 56
)

#2 Output when two categories are selected from the "dept_personnel" pillbox:

SELECT SQL_CALC_FOUND_ROWS `directory_staff`.*
FROM `cms_directory_staff` as `directory_staff`
 WHERE (associated_departments LIKE '%	52	%' OR associated_departments LIKE '%	56	%') AND `directory_staff`.hidden = 0 
 ORDER BY last_name ASC

SELECT * FROM `cms_uploads` WHERE tableName = 'directory_staff' AND
fieldName IN ('flex_banner','personnel_photo','vcard_qr_code','side_photo_docs') AND
recordNum IN (35,32,36)
 ORDER BY `order`, num

SELECT SQL_CALC_FOUND_ROWS `accounts`.*
FROM `cms_accounts` as `accounts`
 WHERE (`num` IN (2)) 
 ORDER BY fullname, username

SELECT SQL_CALC_FOUND_ROWS `members`.*
FROM `cms_members` as `members`
 WHERE (`num` IN (2)) AND `members`.hidden = 0 
 ORDER BY fullname, username

Below is the output when NO categories are selected from the "dept_personnel" pillbox:

#1 Output

Array
(
)

#2 Output

SELECT SQL_CALC_FOUND_ROWS `directory_staff`.*
FROM `cms_directory_staff` as `directory_staff`
 WHERE `directory_staff`.hidden = 0 
 ORDER BY last_name ASC

SELECT * FROM `cms_uploads` WHERE tableName = 'directory_staff' AND
fieldName IN ('flex_banner','personnel_photo','vcard_qr_code','side_photo_docs') AND
recordNum IN (31,33,35,32,34,36)
 ORDER BY `order`, num

SELECT SQL_CALC_FOUND_ROWS `accounts`.*
FROM `cms_accounts` as `accounts`
 WHERE (`num` IN (2)) 
 ORDER BY fullname, username

SELECT SQL_CALC_FOUND_ROWS `members`.*
FROM `cms_members` as `members`
 WHERE (`num` IN (2)) AND `members`.hidden = 0 
 ORDER BY fullname, username

Thanks, Zicky

Hi Zicky,

It's unclear to me what change could have caused this to start happening, however, if you simply want the query to return nothing from "directory_staff" if "dept_personel" is empty, that should be easy to accomplish. There are a few ways to go about this, one of which is simply setting $searchString to a value that will return nothing if "dept_personel:values" is empty, something like this:

  if(is_array(@$home_pageRecord['dept_personnel:values'])){
    //Count the total number if items in the array.....
    $arrayCounter = count($home_pageRecord['dept_personnel:values']);
    //Loop through the array....
    foreach($home_pageRecord['dept_personnel:values'] as $key => $filterItem){
      //create the search string that searches the associated_departements for the associated value....
      $searchString .= "associated_departments LIKE '%\t$filterItem\t%'";
      //If this is not the last item to filter by, add or to the statement....
      if(($key+1) != $arrayCounter){
        $searchString .= " OR ";
      }
    }
  } else {
    $searchString = '0';
  }

Let me know if that helps with your issue, or if you have any other questions!

Thanks,

Daniel
PHP Programmer
interactivetools.com

Hey Daniel,

Actually, what you proposed with the else was once of the first things I tried with no success.

Do you think that somehow the pillbox selector is introducing "spaces" and somehow this query is picking up the spaces and therefore causing the issue?