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

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!

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,

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?

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,

Hey Daniel,

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

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!

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

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,