specifying day in where clause

[phpjaco]

hello

need some help in my syntax here. i want to take only days that are on a sunday or that are not on a sunday. im not sure i think have asked a similiar question, in case so appologize for the double posting. anyway heres what i got:

 $sql = 'SELECT DATE_FORMAT(date,"%a") AS cur_day, date,day,download,heading,id,listen,listen_link,text,timestamp FROM webcasts ORDER BY date DESC';

what i really want to be doing is this, use cur_day and make sure its not a sunday

 $sql = 'SELECT DATE_FORMAT(date,"%a") AS cur_day, date,day,download,heading,id,listen,listen_link,text,timestamp WHERE  DATE_FORMAT(date,"%a") AS cur_day2 != "Sun", FROM webcasts ORDER BY date DESC';

what is the correct syntax here? why cant i use the variable that is defined as cur_day - WHERE cur_day != 'Sun' -> unknown column 'cur_day'

any help would be appreciated.

[stolzyboy]

straight from mysql.com

Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined. For example, the following query is illegal:

SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;

so, just don't use the alias in the where clause, use the DATE_FORMAT(blah) IN the WHERE clause

[phpjaco]

i have tried this but keep getting an error

SELECT DATE_FORMAT(date,"%a") AS cur_day, date,day,download,heading,id,listen,listen_link,text,timestamp FROM webcasts WHERE DATE_FORMAT(date,"%a") as day = "Sun" ORDER BY date DESC 

what am i doing wrong here, do i need to put the date in brackets?

[stolzyboy]

SELECT DATE_FORMAT(date,"%a") AS cur_day, date,day,download,heading,id,listen,listen_link,text,timestamp FROM webcasts WHERE DATE_FORMAT(date,"%a") = "Sun" ORDER BY date DESC

[phpjaco]

yeah that gets me what i want. thanks for all the help! 🙂