Comparing multi-selections from two different records - looking for matches

11 posts by 3 authors in: Forums > CMS Builder
Last Post: October 22, 2019   (RSS)

By Mikey - October 8, 2019

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

By daniel - October 9, 2019

Hi Zicky,

There are a few bits of info that would help me troubleshoot this issue:

  1. The value of $home_pageRecord['dept_personnel:values'] when you run this script. This can be output to the page with the following:
    showme($home_pageRecord['dept_personnel:values']);​
  2. The full SQL statement being run by getRecords(). You can find this by adding the "debugSql" parameter to the getRecords options, like this:
      list($directory_staffRecords, $directory_staffMetaData) = getRecords(array(
        'tableName'   => 'directory_staff',
        //'perPage'     => '10',
        'where'       => $searchString,
        'loadUploads' => true,
        'allowSearch' => false,
        'orderBy'     => 'last_name ASC',
        'debugSql'    => true,
      ));​

Could you find this info, and copy it here? Both of these will display to the page, so I would recommend using a test page.

Thanks!

Daniel
Technical Lead
interactivetools.com

By Mikey - October 11, 2019

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

By daniel - October 15, 2019

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
Technical Lead
interactivetools.com

By Mikey - October 15, 2019

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?

By Mikey - October 16, 2019

Hey Daniel,

I gave that a shot, but still no joy. I'm getting the same results as before.

By daniel - October 17, 2019 - edited: October 17, 2019

Hi Zicky,

That's curious. I have one more thing for you to try:

if(!empty($home_pageRecord['dept_personnel:values']) && 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 ";
      }
    }
  }
 
if (empty( $searchString )){
  $searchString = '0';
}

If that still doesn't work, feel free to escalate this to a 2nd-level support request (https://www.interactivetools.com/support/request/) and I can take a look at it directly.

Thanks!

Daniel
Technical Lead
interactivetools.com

By Mikey - October 17, 2019

Hey Daniel,

I gave that a shot as well, still no joy.

I just submitted the 2nd-level support ticket. If you need to talk to me, Damon should have my phone number... it may be a bit easier for me to explain a few things over the phone.

Thanks, Zicky

By daniel - October 22, 2019

Hi All,

I just wanted to wrap this up with an update for anyone else who might be having a similar issue: the correct solution was to use "FALSE" instead of "0", like this:

if (empty( $searchString )){
  $searchString = 'FALSE';
}

Some additional technical details: In a MySQL query "0" and "FALSE" are functionally equivalent in most cases (https://dev.mysql.com/doc/refman/8.0/en/boolean-literals.html). However, in PHP the string "0" is considered falsy (https://www.php.net/manual/en/language.types.boolean.php#language.types.boolean.casting) and as such is being discarded by CMSB's getRecords() call, whereas the string "FALSE" is not.

Cheers,

Daniel
Technical Lead
interactivetools.com