Jump to content

Archived

This topic is now archived and is closed to further replies.

latemodern

Alias Not working in WHERE clause

Recommended Posts

Hi There,

 

I am trying to construct a MYSQL query to filter out people who are too old or too young based on their date of birth and the users $maxage and $minage specifications

 

I created an equation that takes that date if birth (from the users.dob category) and calculates their age and aliases it as 'age'. In my WHERE clause I am trying to get it to only include users whose age is higher than the $minage variable and lower than the $maxge variable. However, mysql returns an error saying that 'age' is an unknown category. Any ideas? Here is the code:

 

//cut out people who are too young

$filter = $filter. ' age >=' .$own_minage. ' AND ';

 

//cut out people who are too old

 

$filter = $filter. ' age <=' .$own_maxage. ' AND ';

 

 

$user_record_query = "SELECT users.user_id, CONCAT(first_name, ' ', last_name) AS name,

(YEAR(CURDATE())-YEAR(dob)) - (RIGHT(CURDATE(),5)<RIGHT(dob,5)) AS age, gender.gender, minbeds, maxbeds, minrent, maxrent, minage, maxage, email, phone, personal_description, gendermix.gendermix, file_name, smoker.smoker, studentmix.studentmix, smoking_mix.smokingmix, student.student

FROM users

JOIN gender ON users.gender = gender.gender_id

JOIN uploads ON uploads.user_id=

users.user_id

JOIN smoker ON users.smoker=smoker_id

JOIN studentmix ON users.studentmix_id=studentmix.studentmix_id

JOIN smoking_mix ON users.smokingmix_id=smoking_mix.smokingmix_id

JOIN users_area_assoc ON users_area_assoc.user_id = users.user_id

JOIN area ON area.area_id=users_area_assoc.area_id

JOIN student ON users.occupation=student.student_id

JOIN gendermix ON users.gendermix_id=gendermix.gendermix_id

WHERE $filter"

 

Thanks

 

Chris

 

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.