Include Multi-value choice in "where" clause

Hi nmsinc,

So in your accounts section, you have a field called experience, lets say it has the values earth,fire,wind and water. 

Then you have another field in another section that retrieves the accounts using the code in the previous post, but you only want to retrieve the accounts with experience set to wind?

Then you'd need to add the following to the end of each of the where statements:

And `experience` = 'wind' 

So the first select statement on the previous posts code would change to be:

SELECT num, COALESCE(  CONCAT(fullname, " - ", ROUND(<?php echo $distanceExpr; ?>, 2), " mi"), fullname ) as fullname
FROM `<?php echo $TABLE_PREFIX ?>accounts`
WHERE (`member_company_accounts` = '<?php echo $ESCAPED_FILTER_VALUE ?>' AND disabled = 'No' AND user_type IN('Adjuster', 'Dispatcher/Adjuster', 'Processor/Adjuster', 'Dispatcher/Processor/Adjuster') AND company_type = 'Independent' AND `latitude` AND `longitude`) AND `experience` = 'wind'
ORDER BY <?php echo $distanceExpr ?>

Have I understood correctly?

Thanks!

Greg

Hi Greg,

I tried your suggestion and the value(s) do not pass through - I even tried setting the user so that only one value was present and that did not work either - any other help would be appreciated - please!

SELECT num, COALESCE(  CONCAT(fullname, " - ", ROUND(<?php echo $distanceExpr; ?>, 2), " mi"), fullname ) as fullname
FROM `<?php echo $TABLE_PREFIX ?>accounts`
WHERE (`member_company_accounts` = '<?php echo $ESCAPED_FILTER_VALUE ?>' AND 'disabled' = 'No' AND user_type IN('Adjuster', 'Dispatcher/Adjuster', 'Dispatcher/Processor/Adjuster') AND 'adjusts_third_party_claims' != '1') AND `experience` = 'Hail and Wind Damage'
ORDER BY <?php echo $distanceExpr ?>


Thanks - nmsinc

Hi nmsinc,

Multi-value list fields store their values as a tab-separated list of values, with extra tabs on the beginning and end of the field. So, for example, if a record has "WIND" and "FIRE" selected in its experience field, it will look like this:

\tWIND\tFIRE\t

(Note that \t is a tab character.)

To check if a record has at least "WIND" selected, you can use a LIKE clause which checks for the substring "\tWIND\t":

`experience` LIKE '%\tWIND\t%'

SELECT num, COALESCE(  CONCAT(fullname, " - ", ROUND(<?php echo $distanceExpr; ?>, 2), " mi"), fullname ) as fullname
FROM `<?php echo $TABLE_PREFIX ?>accounts`WHERE (`member_company_accounts` = '<?php echo $ESCAPED_FILTER_VALUE ?>' AND 'disabled' = 'No' AND user_type IN('Adjuster', 'Dispatcher/Adjuster', 'Dispatcher/Processor/Adjuster') AND 'adjusts_third_party_claims' != '1')
  AND `experience` LIKE '%\tHail and Wind Damage\t%'
ORDER BY <?php echo $distanceExpr ?>

Does that help?

Hi Chris,

Your suggestion worked perfectly - now, how would I code that into a php page outside the CMS?

Thanks - nmsinc

Something like this?

$where = "`member_company_accounts` = $ESCAPED_FILTER_VALUE";
$where .= " AND `disabled` = 'No'";
$where .= " AND `user_type` IN('Adjuster', 'Dispatcher/Adjuster', 'Dispatcher/Processor/Adjuster')";
$where .= " AND `adjusts_third_party_claims` != '1')";
$where .= " AND `experience` LIKE '%\tHail and Wind Damage\t%'";

// load records from 'accounts'
list($accountsRecords, $accountsMetaData) = getRecords(array(
  'tableName'   => 'accounts',
  'where'       => $where,
));


Does that help?

Hi Chris,

I should have been more specific - here is the code I need to adjust:

<?php if ($table4Record['num']): ?>
      <option value = "<?php echo $table4Record['num'] ?>"><?php echo $table4Record['fullname'] ?></option>
      <?php else: ?>
      <option value="">Please Select An Adjuster</option>
      <?php endif; ?>
      <?php foreach ($independentAdjusterDispatcherDistanceToRecords as $distance => $users): ?>
        <?php foreach ($users as $user): ?>
        <?php $company = mysql_get('member_companies', $user['member_company_accounts']); ?>
          <?php if ($company['accepts_all_indepedents'] == "Yes"): ?>
          <?php if (($user['user_type'] == "Adjuster" OR $user['user_type'] == "Dispatcher/Adjuster" OR $user['user_type'] == "Processor/Adjuster" OR $user['user_type'] == "Dispatcher/Processor/Adjuster") AND !$user['disabled']): ?>
           <option value = "<?php echo $user['num'];?>"><?php echo $user['fullname'];?> - <?php echo $distance != "Distance NA" ? $distance." mi" : 'Distance NA'; ?></option>
          <?php endif; ?>
          <?php endif; ?>
        <?php endforeach ?>
      <?php endforeach ?>


Hi nmsinc,

Do you mean that you want to apply the same logic to your innermost IF condition? If so, replacing that line with this may do the trick:

<?php
  if (
    (
      $user['user_type'] == "Adjuster"
      OR $user['user_type'] == "Dispatcher/Adjuster"
      OR $user['user_type'] == "Processor/Adjuster"
      OR $user['user_type'] == "Dispatcher/Processor/Adjuster"
    )
    AND !$user['disabled']
    AND strpos($user['experience'], "\tHail and Wind Damage\t") !== FALSE
  ):
?>

strpos() is a PHP function which searches one string for another and returns the character position of the first occurrence, or FALSE if it wasn't found. In that way, it can be used like Mysql's LIKE.

Does that help?

Hi nmsinc,

You'll need to add dots between your variable and your literal strings to join (aka concatenate) them into one string:

"\t"$record['insured_claim_type']"\t"

...should be...

"\t" . $record['insured_claim_type'] . "\t"

So that line should read:

AND strpos($user['experience'], "\t" . $record['insured_claim_type'] . "\t") !== FALSE

Note that !== FALSE does not mean the same thing as == !FALSE. It's important to consider the case where strpos() returns 0 (zero) as the position of the first match, which is different than when it returns FALSE for no matches at all. 0 !== FALSE is a true statement, but 0 == !FALSE is a false statement. The red warning section on this page has some helpful links if you want to read more: http://www.php.net/manual/en/function.strpos.php

Does that help?