Converting a conditional block into a multi-dimensional array - how to?

[cparekh]

Hi,

 

I'm having trouble scaling a script I wrote a while back...

 

background:

The script is like a search-able address book. There are 8 access levels and the user can have permission access to a combination of any/all levels. The results returned are based on the access level combination as a sql query is created based on that access level combination i.e. a user can only see other users at their level.

 

There used to be only 3 levels and so I had a block of conditional statements to generate the required sql query. However with 8 levels there would be too many statements to maintain and errors are very likely.

 

I can only think that the best way to do this is to use a multi-dimensional array (?) but I have no idea how to set it up and then traverse it to get the relevant values out to use (I don't have much experience with multi-dimensional arrays).

 

This is what the current code is:

//Create the sql conditions for each access level

if ($access1 == 'yes')
       {
	$sql1 = "usertype= 'Principal' or usertype = 'Principal Assistant' or usertype = 'Named' or usertype = 'E-mail' or usertype = 'Web' ";
}
if ($access2 == 'yes')
{
	$sql2 = "usertype = 'Principal' or usertype = 'Deputy Principal' or usertype = 'Contact'";
}
if ($access3 == 'yes')
{
	$sql3 = "usertype = 'Principal' or usertype = 'Contact' ";
}

//join them
if (($access1 == 'yes') and ($access2 == 'yes') and ($access3 == 'yes')) //all 3
{ $sqlconditions = $sql1." or ".$sql2." or ".$sql3; }
elseif (($access1 == 'yes') and ($access2 == 'yes') and ($access3 == 'no'))
{ $sqlconditions = $sql1." or ".$sql2; }
elseif (($access1 == 'yes') and ($access2 == 'no') and ($access3 == 'no'))
{ $sqlconditions = $sql1; }
elseif (($access1 == 'no') and ($access2 == 'yes') and ($access3 == 'yes'))
{ $sqlconditions = $sql2." or ".$sql3; }
elseif (($access1 == 'no') and ($access2 == 'yes') and ($access3 == 'no'))
{ $sqlconditions = $sql2; }
elseif (($access1 == 'no') and ($access2 == 'no') and ($access3 == 'yes'))
{ $sqlconditions = $sql3; }
elseif (($access1 == 'yes') and ($access2 == 'no') and ($access3 == 'yes'))
{ $sqlconditions = $sql1." or ".$sql3; }

 

$sqlconditions is passed into the main sql query as a condition. For each access level we have different user types i.e. 'Principal', 'Contact' etc. (btw, none of user access values or types are held in a local db - they are written into the session when a user logs in after the db of a third party crm is interrogated )

 

Any help or direction is greatly appreciated.

 

Thanks,

C.

[jl5501]

My approach would be to simply build the query incrementally adding conditions as necessary

 

so, to start you would have

$ sql = 'select * from mytable ';

$cond = 'where 1=1';

 

then for each check

 

if($access1 = 'yes')

$cond .= 'or usertype="princpal"';

and so on with the other access checks

 

then at the end

if($cond != 'where 1=1')

        $sql .= $cond;

[cparekh]

My worry is that because I have 8 levels I'll end up with a few hundred conditional statements for all the  combinations/permutations possible

 

I've just tried writing a statement for each and although I haven't yet met each combination I have over 60 conditional statements - too many to manage effectively I feel.

 

Is there an easier way to iterate through so many possibilities?

[akitchin]

it's difficult to understand what the data you're working with looks like, but it seems like a worthwhile suggestion is using the IN operator for the given query. in this way, you can simply add the user types of interest to an array, and implode them into one WHERE clause:

 

// go through each of the "accessx" variables
// if any of them is "yes," add the relevant user types for that access level to the array of allowed user types
$usertypes_allowed = array();
for ($i = 1; $i <= 3; ++$i)
{
  $this_access = 'access'.$i;
  if ($$this_access == 'yes')
  {
    switch $i:
      case 1:
        $usertypes_allowed[] = 'Principal';
        $usertypes_allowed[] = 'Principal Assistant';
        break;
      case 2:
        $usertypes_allowed[] = 'Deputy Principal';
        break;
  }
}

// destroy duplicates
$usertypes_allowed = array_unique($usertypes_allowed);

// compound into one handy WHERE clause
$where_clause = 'WHERE usertype IN ('.implode(',', $usertypes_allowed).')';

echo $where_clause;

 

any explanation of where the $accessx variables are coming from, and how the user types are accorded to that access level would be very helpful in clarifying the issue.