how to run between query for alisas column using mysql php

Question

I'm working on a search form were user select age from to. then result displays accroding to search.

My table as these (name,dob,emailid,mobileno) columns

Here is my query

SELECT name,dob, YEAR(CURDATE()) - YEAR(dob) AS age AND age (BETWEEN '25' AND '30') FROM users

Here i'm able to get age based on dob(DateofBirth) but unable to run between query...

How to do so? Is i'm missing up something ?

Kindly help me.

Thanks in Advance

try this. `SELECT YEAR(CURDATE()) - YEAR(dob) AS age FROM users HAVING (age >= '25' AND age <= '30')` — Jitendra, Jul 29 '17 at 10:39

Peter van der Wal · Accepted Answer · 2017-07-29T10:43:39.977

Beside some syntactically errors in your query, the main problem is: You can't use the aliases in the SELECT columns-part of your query within the WHERE-part. This has to do with the fact that SQL performs the WHERE before the SELECT columns. Just include your formula also in the WHERE-part:

SELECT 
    name, 
    dob, 
    YEAR(CURDATE()) - YEAR(dob) AS age 
FROM users
WHERE YEAR(CURDATE()) - YEAR(dob) BETWEEN '25' AND '30'

Side note: have a look at the question How to calculate age (in years) based on Date of Birth and getDate() since your formula isn't always giving the correct result (e.g. for date of birth 'Dec 1, 2000' and current date 'Jul 29, 2017' it is returning 17, where it should be 16)

how to run between query for alisas column using mysql php

1 Answers1