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

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