Setting controls in mysql_select
5 posts by 3 authors in: Forums > CMS Builder
Last Post: January 2, 2014 (RSS)
By nmsinc - December 20, 2013
I'm have some problems using a "OR" statement to set controls below with no luck - any help would be appreciated.
$adjusterRecords = mysql_select( ("accounts", "adjusts_all_insurance_company_claims = '1' AND disabled = 'No' AND user_type IN('$user_type', '$user_type2') ORDER BY fullname") OR ("accounts", "assigned_to = $table1Record['num'] AND disabled = 'No' AND user_type IN('$user_type', '$user_type2') ORDER BY fullname") );
Thanks - nmsinc
Hi NMSINC,
Here's a fairly complex where statement that might help understand the syntax required.
There was a an accounts table member record number value passed from a search form, the accounts records could have a status field with the values 1 (active) or 2 (inactive), and accounts records had membership_levels of 1 through 6 where level 2 required a separate set of criteria screening
<?php $member = $_REQUEST['member']; ?>
'where' => " createdByUserNum IN (SELECT num FROM cms_accounts WHERE (account_status LIKE '%1%' AND membership_level LIKE '%2%') ) AND `createdByUserNum` = '$member' AND updatedDate > '$listing_age' AND `status` = 'Available' OR (createdByUserNum IN (SELECT num FROM cms_accounts WHERE (account_status LIKE '%1%' AND (membership_level LIKE '%1%' OR membership_level LIKE '%3%' OR membership_level LIKE '%4%' OR membership_level LIKE '%5%' OR membership_level LIKE '%6%')) ) AND `createdByUserNum` = '$member' AND `status` = 'Available') ",
Best,
Jerry Kornbluth
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Hi nmsinc,
I think I've reordered your mysql select statement so that it will work:
$adjusterRecords = mysql_select("accounts", "(adjusts_all_insurance_company_claims = '1' OR assigned_to = '{$table1Record['num']}' ) AND disabled = '0' AND user_type IN('$user_type', '$user_type2') ORDER BY fullname");
Here are some of the issues I think were stopping it working:
- There were double brackets on the mysql_select variables, so a singe boolean was being passed into the function instead of the table name and a where statement.
- The order by variables were inside of the where statement instead of at the end of it.
- You needed to have curly brackets around the $table1Record['num'] item, otherwise PHP won't treat it like a string.
- The accounts table stores checkbox values as 1 for checked, and 0 for unchecked. So I've changed the disabled value to 0.
Let me know if this works, or if you get any errors.
Cheers!
Greg
PHP Programmer - interactivetools.com