CMS Populate Using Advanced Filter

3 posts by 2 authors in: Forums > CMS Builder
Last Post: November 30, 2013   (RSS)

By nmsinc - November 23, 2013

I have and advanced filter I'm using for a dropdown. All filters work great with the exception of the last one listed here : ".$table1Record['offers_third_party_claims']." ='1'

I have listed the entire statement below - any help would be appreciated - thanks NMSINC

<?php $table1Record = mysql_get('member_companies', $CURRENT_USER['member_company_accounts']); ?>

SELECT num, fullname FROM `<?php echo $TABLE_PREFIX ?>accounts`

WHERE `member_company_accounts` = '<?php echo $ESCAPED_FILTER_VALUE ?>' AND user_type IN('Adjuster', 'Dispatcher/Adjuster') AND disabled = 'No' OR user_type IN('Adjuster', 'Dispatcher/Adjuster') AND adjusts_third_party_claims = '1' AND disabled = 'No' AND ".$table1Record['offers_third_party_claims']." ='1'     

nmsinc

By Dave - November 28, 2013

Hi nmsinc, 

Sorry for the delay in responding to this.  

The first thing I'd do is create a test pulldown with "Get Options from MySQL query (advanced)" set to this:

<?php $table1Record = mysql_get('member_companies', $CURRENT_USER['member_company_accounts']); ?>

SELECT " '<?php echo mysql_escape($CURRENT_USER['member_company_accounts']); ?>'  - '<?php echo mysql_escape($table1Record['offers_third_party_claims']); ?>' "

That should output the two values that are being inserted into the query.  

Also, I'd add some brackets, I always use lots of brackets so I don't need to think/worry about operator precedence (http://dev.mysql.com/doc/refman/5.0/en/operator-precedence.html).

Currently I believe you have this: WHERE

(`member_company_accounts` = '<?php echo $ESCAPED_FILTER_VALUE ?>' AND user_type IN('Adjuster', 'Dispatcher/Adjuster') AND disabled = 'No')
OR (user_type IN('Adjuster', 'Dispatcher/Adjuster') AND adjusts_third_party_claims = '1' AND disabled = 'No' AND ".$table1Record['offers_third_party_claims']." ='1')

And I'm guessing maybe you want something more like this? 

(`member_company_accounts` = '<?php echo $ESCAPED_FILTER_VALUE ?>' AND disabled = 'No' AND user_type IN('Adjuster', 'Dispatcher/Adjuster') 
AND (adjusts_third_party_claims != '1'  OR ( adjusts_third_party_claims = '1' AND ".$table1Record['offers_third_party_claims']." ='1' ) )

Try those, or if they don't work, let me know what you're trying to accomplish and what a plain text version of the query might look like.

Hope that helps!

Dave Edis - Senior Developer
interactivetools.com