[ciapug] Building a Where Clause from an Array

[Chris Hettinger]

>> 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.

See... It wasn't clicking with me today... I knew that, but was still 
putting in the "where" ... oops. lol

-ch

David Champion wrote:

> 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
> 
> _______________________________________________
> ciapug mailing list
> ciapug@cialug.org
> http://cialug.org/mailman/listinfo/ciapug
>