[ciapug] Building a Where Clause from an Array

[David Champion]

Chris Hettinger wrote:

> Okay ... I'm sure I can do this but it's just not clicking today. Please 
> HELP! :)
> 
> I have these 4 variables that I want to use in a where clause in my SQL 
> query. The thing is, it's a situation where it a 1 or more of the fields 
> will have a value... so here is my psydocode:
> 
> // populated from a form
> $lname
> $fname
> $patientid
> $caseid
> 
> /*
> * for this example assume
> * $lname = ''
> * $fname = 'chris'
> * $patientid = '150'
> * $caseid = ''
> *
> */
> 
> // put these into a array
> $Params = array{
>         "lname" => $lname,
>         "fname" => $fname,
>         "patientid" => $patientid,
>         "caseid" => $caseid
>         );
> 
> // I count the number of values in the array
> // so I know I have 2 values.
> 
> $sql = "SELECT * FROM myTable";
> 
> // Here I want to loop through the 'Params' array where
> // a 'key' has a 'value' and then build a where clause
> 
> // if there is only 1 value
> $sql .= " WHERE key = 'value'";
> 
> // if there is more than 1 value, use AND in the clause
> $sql .= " AND WHERE key = 'value'";
> 
> $sql .= " ORDER BY lname ASC";
> 
> ... Thanks
> -ch

It's "pseudocode", not "psydocode", although you could describe my code 
as "psychocode". :/

Depending on how many fields you have, it may be easier to skip the 
array, and just say:

(warning - I haven't done PHP for so long I barely remember it)

$sql = "select * from stuff where ";
$where = "";
if ($lname) {
	$where .= "lname = '" . $lname ."' ";
}
if ($fname) {
	// see if we need the "and"...
	if ($where) {
		$where .= "and ";
	}
	$where .= "fname = '" . $fname . "' ";
}
...

$sql = $sql . $where;

When I have a ton of fields, I've created a function so I can pass the 
field name and value, and it will add it to the "where" clause.

Oh, you'd probably figure this out, but it's not correct to say "where x 
= y and where a = b" ... you only need the keyword "where" once.

-dc