Jump to content

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

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.