Jump to content


Photo

Alias Not working in WHERE clause


  • Please log in to reply
No replies to this topic

#1 latemodern

latemodern
  • Members
  • Pip
  • Newbie
  • 2 posts

Posted 04 November 2005 - 10:46 AM

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





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users