Setting controls in mysql_select

5 posts by 3 authors in: Forums > CMS Builder
Last Post: January 2, 2014   (RSS)

By gkornbluth - December 25, 2013

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

The first CMS Builder reference book is now available on-line!







Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php

By nmsinc - December 26, 2013

HI Jerry,

Thanks, but not quite what I'm looking for!

nmsinc

By gregThomas - January 2, 2014

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

Greg Thomas







PHP Programmer - interactivetools.com

By nmsinc - January 2, 2014

Hi Greg,

Thanks for your help on this - worked great!

nmsinc