Nesting switch statement in query string 1

[blues77]

Hello,

I'm trying to sometime like the following

$query = "Select COUNT(*) as CLTOTAL, ProviderName as PNAME, b.ProviderCode as PCODE " .
				"from checklist a, providerdetail b " .
				"where (a.ProviderCode=b.ProviderCode)" . 
					switch ($userTypeID)
					{
						case 1:
							" AND (a.Clerk = $clerkID)" .
							break;
						case 2:
							" AND (a.Clerk = $clerkID) OR (a.BranchID = $branch)" . 
							break;
						case 3:
							" AND ((a.Clerk = $clerkID) OR " . in_array($branch, $branchList); . " " .
							break;
						case 9:
							" AND (b.Active = 1)" .
							break;
						default:
							" AND (a.Clerk = $clerkID) " .			
					}
				
				" GROUP BY PCODE, PNAME " .
				" ORDER BY PNAME";

But I'm not sure if this is proper syntax. Can anyone let me know how to accomplish this. Assuming of course that this method won't work.

All help is appreciated
Thanks

 

[sleipnir214]

No, that's not going to work at all.

First, switch statements don't return values, so they can't be used inside a concatenation.

Second, in_array() returns a boolean and PHP doesn't have a default type juggle for that. You're going to end up with some strangeness in the middle of the query you're trying to generate.

The switch-in-the-middle-of-a-concatenation problem can be fixed by not trying to do everything in a single statement:

$query =
	"Select COUNT(*) as CLTOTAL, ProviderName as PNAME, b.ProviderCode as PCODE " .
    "from checklist a, providerdetail b " .
    "where (a.ProviderCode=b.ProviderCode)";
                
switch ($userTypeID)
{
	case 1:
		$query .= " AND (a.Clerk = $clerkID)";
	    break;
	case 2:
		$query .= " AND (a.Clerk = $clerkID) OR (a.BranchID = $branch)";
	    break;
	case 3:
		$query .= " AND ((a.Clerk = $clerkID) OR " . in_array($branch, $branchList) . " ";
	    break;
	case 9:
		$query .= " AND (b.Active = 1)" .
	    break;
	default:
		$query .= " AND (a.Clerk = $clerkID) " .            
}
                
$query .= " GROUP BY PCODE, PNAME ORDER BY PNAME";

(CAUTION. I haven't tried to run this code. I will likely barf.)

Then there is your invocation of in_array() in the middle of the switch. What are you trying to do there?


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[blues77]

Thanks for the suggestion sleipnir. For the in_array() it should read

$query .= " AND ((a.Clerk = $clerkID) OR (a.BranchID can be found in the array $brachList))

Of course that last bit is pseudo code but that is what I'm trying to accomplish with the in_array. Do you have any suggestions on how to tackle this?

Thanks

 

[sleipnir214]

I don't understand what you are trying to accomplish.

You're creating a query string that will be passed to a database server for parsing. It will not know about the array $brachList.

Are you intending to concatenate more to the query if and only if a particular value is to be found in that array?


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[blues77]

What I'm trying to do in this situation is if `case 3:` statement is met then I want to add onto the query string the statement `AND ((a.Clerk = $clerkID) OR (a.BranchID can be found in the array $brachList))` where the a.BranchID is looked for in an array say $branchList. I was trying to break out of the query string, then do the in_array() test and concatenate that result onto the string. So, for example, if the a.BranchID = 1 then the query string would end, the in_array function would look for 1 in the $branchList array and the result from that would be then concatenated back onto the query string. Anyways, I'm probably trying to do too much in one step again. I should be able to do this in multiple steps.

 

[sleipnir214]

Yes, you should do this in multiple steps.

As I posted earlier, in_array() returns a boolean (link to PHP online manual page for in_array()). Boolean values cannot be concatenated.

Your script needs to do something like:

$query =
	"Select COUNT(*) as CLTOTAL, ProviderName as PNAME, b.ProviderCode as PCODE " .
    "from checklist a, providerdetail b " .
    "where (a.ProviderCode=b.ProviderCode)";
                
switch ($userTypeID)
{
	case 1:
		$query .= " AND (a.Clerk = $clerkID)";
	    break;
	case 2:
		$query .= " AND (a.Clerk = $clerkID) OR (a.BranchID = $branch)";
	    break;
	case 3:
		$query .= " AND ((a.Clerk = $clerkID) OR ";
		if (in_array($branch, $branchList))
		{
			//concatenate something to the end 
			//of $query that will be understood
			//by the database server and meaningful
			//to the purposes of this script.
		}
	    break;
	case 9:
		$query .= " AND (b.Active = 1)" .
	    break;
	default:
		$query .= " AND (a.Clerk = $clerkID) " .            
}
                
$query .= " GROUP BY PCODE, PNAME ORDER BY PNAME";

Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[blues77]

Hi That's very close to what I want however for the test
if (in_array($branch, $branchList))
{ ...

the $branch has to be assigned the value from a.BranchID. In other words $branch equals the branch ID of the current row from the result set and then this is checked to see if it is in the array $branchList.

Is there a way to do this since I need to be able to access the value from the query in PHP in order to assign it to $branch.

 

[sleipnir214]

Current row of what result set? We're constructing the SELECT query here. Until we've passed it to the database server there can be no result set.


Want the best answers? Ask the best questions!

TANSTAAFL!!