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 Zicky - 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
PHP Programmer
interactivetools.com

By Zicky - 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
PHP Programmer
interactivetools.com

By daniel - October 16, 2019

Hi Zicky,

I just realized the issue is that the pillbox is returning an empty array - not "nothing" - so is_array() will still return true. This is likely what changed with the selector. You can try adding a check using empty() as well:

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 ";
      }
    }
  } else {
    $searchString = '0';
  }

Let me know if that does the trick!

Thanks,

Daniel
PHP Programmer
interactivetools.com

By Zicky - 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
PHP Programmer
interactivetools.com

By Zicky - 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
PHP Programmer
interactivetools.com