Posted by Steve on 11/08/07 19:43

"Darko" <darko.maksimovic@gmail.com> wrote in message
news:1194548559.891114.33150@v23g2000prn.googlegroups.com...
> On Nov 8, 4:34 pm, "Steve" <no....@example.com> wrote:
>> "Captain Paralytic" <paul_laut...@yahoo.com> wrote in message
>>
>> news:1194534736.193031.304830@q5g2000prf.googlegroups.com...
>>
>>
>>
>> > On 8 Nov, 14:13, jc...@lycos.com wrote:
>> >> $query = "SELECT DISTINCT names
>> >> FROM $table
>> >> Order by names";
>> >> $result = mysql_query($query);
>>
>> >> In the code above, 'Order by' works very well when not used in
>> >> conjunction with other query-able items but how about when used in a
>> >> query where other possible choices are included? How do I use 'Order
>> >> by' in the query below where it will only act on 'names' when "All"
>> >> is
>> >> selected and otherwise, not really be active during the query as in
>> >> the code below? I'm using PHP to access MySQL. Thanks
>>
>> >> $query = "SELECT *
>> >> FROM $table
>> >> WHERE 1 = 1 ";
>> >> if($year != "All") $query .= "and year = '".$year."'";
>> >> if($status != "All") $query .= "and status = '".
>> >> $status."'";
>> >> if($names != "All") $query .= "and names = '".$names."'";
>> >> $result = mysql_query($query);
>>
>> > ORDER BY names
>>
>> just a note about legibility here...
>>
>> $sql[] = "
>> SELECT *
>> FROM " . $table . "
>> WHERE 1 = 1
>> ";
>> if ($year != 'ALL')
>> {
>> $sql[] = " AND year = '" . $year . "'";}
>>
>> if ($status != 'ALL')
>> {
>> $sql[] = " AND status = '" . $status. "'";}
>>
>> if ($names != 'ALL')
>> {
>> $sql[] = " AND names = '" . $names. "'";}
>>
>> $sql = implode("\r\n", $sql);
>>
>> that avoide the problem of forgetting to put a space between the if'd
>> conditions/criterion. note, i put the spaces before 'and' simply so that
>> when i go to debug the current sql being run, it echos nice and formatted
>> in
>> the browser.
>
> You and your formatting again :D You won't give up until you educate
> all newbies to
> proper formatting, eh? :)

he, he, he, he!

well, actually the formatting is just a bonus. some db's, even though they
strip out spaces themselves when parsing, will caugh up errors if the
clauses run together...which happens the way the op wrote it...where 1=1and
year='2007'and status=... building $sql as an array and then imploding it
back on itself avoids the problem and also makes for more clearly defined if
() cases.

but, i'm still chuckling. that was good.

[Back to original message]