Posted by Steve on 11/08/07 15:34

"Captain Paralytic" <paul_lautman@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.

[Back to original message]