[SOLVED] How to use derived field

[marukochan]

Hi! This may be easy but I don't know how it works.

 

I have a table (project) with fields (project_title, date_award, etc.)

 

I want to select only projects with specific year_month.

 

My code goes

...
...
$query_1 = mysql_query("SELECT EXTRACT( YEAR_MONTH FROM date_award ) AS new_date FROM project ");
$query_2 = mysql_query("SELECT * FROM project WHERE new_date='200607'") or die(mysql_error());
...
...

 

First I extract year & month into newly created field during $query_1 which is 'new_date'. However, $query_2 gives an error

Unknown column 'new_date' in 'where clause'

 

The question is how do I use the derived data in new_date to select the records?

 

TQ

[artacus]

Well it fails because after you run the query your results go away.

 

You don't really need to do all of this. Just write a better where clause:

SELECT * FROM project

WHERE  YEAR(date_award) = 2007 AND  MONTH(date_award) = 1

[marukochan]

Thanks artacus. Yours is much better and simple.