unknown column when use aliases

[phprock]

Hi,
I'm having problem with following query.

$sql="SELECT user_name,(YEAR(CURRENT_DATE)-YEAR(user_dob)) as dob 
FROM ".$prefix."_matrimonial_user
WHERE (dob BETWEEN $age[0] AND $age[1]) 
AND user_sex='$sex' AND user_ethnicity=$ethnicity";

Error is dob is unknown column

SELECT user_name,(YEAR(CURRENT_DATE)-YEAR(user_dob)) as dob
FROM nuke_matrimonial_user WHERE (dob BETWEEN 31 AND 40) AND user_sex='Female'
AND user_ethnicity=6
Error Unknown column 'dob' in 'where clause'

Any help.

Regards

[toplay]

MySQL thinks you're trying to use a column named current_date. It's a function, so you forgot the parenthesis. Example:

$sql="SELECT user_name, YEAR(CURRENT_DATE()) - YEAR(user_dob) as dob FROM {$prefix}_matrimonial_user WHERE (dob BETWEEN {$age[0]} AND {$age[1]}) AND user_sex = '$sex' AND user_ethnicity = '$ethnicity'";

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html#id3011796

🙂

[phprock]

thank you , but i still get the same error "uknown column dob".

[Lars_Berg]

You can't use an alias name in the where clause.

SELECT user_name,dob
 from (select user_name,
   YEAR(CURRENT_DATE) - YEAR(user_dob) as dob
  FROM nuke_matrimonial_user 
 where user_sex='Female'
   AND user_ethnicity=6 )dt
  WHERE dob BETWEEN 31 AND 40

This requires 4.1 of Mysql. If you don't have that version you need to repeat the expression for dob in the where clause.

SELECT user_name,YEAR(CURRENT_DATE)-YEAR(user_dob) as dob
FROM nuke_matrimonial_user 
WHERE YEAR(CURRENT_DATE)-YEAR(user_dob) BETWEEN 31 AND 40
 AND user_sex='Female'
 AND user_ethnicity=6 

MySQL thinks you're trying to use a column named current_date. It's a function, so you forgot the parenthesis.

Although current_date is a function the () are not necessary.

[phprock]

Thank you. It's work perfect.

Regards,