select values in a range

[Anonymous]

Hi guys,

I am trying to create a dating website where i wanna display our registered users age by refering to the date of birth they entered. Besides of that, i also wanna do searching base on the age i calculated from the system

tasks i wan my system to perform
- calculate the age of the user once they entered date of birth or year of birth
- when a visitor wanna search registered users who are in the range of 20 to 25 years old, the system will search from the database and display the users who fall under this category.. by refering to date of birth / year of birth field

* this is what i hope u guys can give me an idea
- how actually i can calculate how old are they when i write my SQL query ??
by assuming this is what the user entered, display all users who are 20 to 25 years old..
i wanna do something like
SELECT username FROM users WHERE age = (age between 20 to 25)...

hope u guys understand what i am trying to ask.. could u guys help me out on this or let me know where to get such reference..

thanks.

 

[Anonymous]

Well, someone's age is just the current date minus the date they were born. Oddly, MySQL doesn't make this subtraction easier for us, but this works:

SELECT FLOOR((TO_DAYS(NOW()) - TO_DAYS(date_of_birth))/365) AS age;

And if you want to select all people whose age (as defined above) is between 20 and 25, you just say so:

SELECT FLOOR((TO_DAYS(NOW()) - TO_DAYS(date_of_birth))/365) AS age FROM table1 WHERE age BETWEEN 20 and 25;

If you don't actually need to return their age, the below is a bit simpler:

SELECT column1, column2 FROM table1 WHERE date_of_birth BETWEEN DATE_SUB(NOW(), INTERVAL 25 YEAR) AND DATE_SUB(NOW(), INTERVAL 20 YEAR);