PHP and SQL | Specific thing in query

Question

In DB I have a field that contains user's birthday. Example: 01.09.1994 I have a PHP function that calculates of user's birth. Now the thing that I want to do: I want to search all the users with age 18 for example, but I want to do this in the sql query. How can this happen?

"SELECT * FROM `users` WHERE `birth` = '18'"

How with sql to automatically turn the birth(01.09.1994) into ages, without getting the results and then turn the birthday in ages with php?

What is the data type of date value ? Show some sample data. — Abhik Chakraborty, Apr 10 '15 at 19:09
I already said. Data is saved this way for every user d.m.y(01.06.1993) — Galabin Vasilev, Apr 10 '15 at 19:11
I know you are, but we're not going to write your query for you :D I'll give you a hint - look up MySQL's date functions, like `DATE_SUB()` — Jay Blanchard, Apr 10 '15 at 19:15
`01.09.1994` is a string, not a date. it would be easier if you formatted your data to use a proper date column/type https://dev.mysql.com/doc/refman/5.1/en/datetime.html then you could use standard date/time functions to do what you want https://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html — Sean, Apr 10 '15 at 19:17
possible duplicate of [MySQL Query to get age from date of birth](http://stackoverflow.com/questions/17734739/mysql-query-to-get-age-from-date-of-birth) — Tab Alleman, Apr 10 '15 at 19:57

score 1 · Accepted Answer · answered Apr 10 '15 at 19:16

The date you are saving is not a real date and it needs to be converted to real date using str_to_date function and then using the timestampdiff you can get the age as

mysql> select timestampdiff(year,str_to_date('01.06.1993','%d.%m.%Y'),curdate()) as age ;
+------+
| age  |
+------+
|   21 |
+------+

So if the column name is dob in the table and you want to find all the users having age = 18 you can do as

select * from users
where 
timestampdiff(year,str_to_date(dob,'%d.%m.%Y'),curdate()) = 18

Thank you so much! Your answer fit perfectly in my query and it works find for now! — Galabin Vasilev, Apr 10 '15 at 22:12

PHP and SQL | Specific thing in query

1 Answers1