Clever Geek Handbook

From a list of birthday dates to an array of a general user from each individual age

I would like to understand the most efficient way to achieve this:

I have a MySQL table with my user information including birthday (YYYY / MM / DD). My goal is to get an array (php) with the total number of users for each distinct age from 10 to 60. I can just query all of my users, pass the birthday to the age birthday php function I wrote, and then populate the array with the totals. But I'm wondering if there is a way to build a sql query that does the job for me?

+3


source to share


3 answers


You should be able to group rows:

SELECT
    FLOOR(DATEDIFF(NOW(), `birthday`)/365) AS 'Age',
    COUNT(*)
FROM `users`
WHERE FLOOR(DATEDIFF(NOW(), `birthday`)/365) BETWEEN 10 AND 60
AND `id` IN (1, 3, 5, 12, 29)
GROUP BY FLOOR(DATEDIFF(NOW(), `birthday`)/365)
ORDER BY FLOOR(DATEDIFF(NOW(), `birthday`)/365) ASC


The result will not contain ages that have no users, but I'm not sure why you need this.

Update  - Added filter id - Fixed date calculation (oops!) - Named new column

+2


source


You will need to use MySql DATEDIFF () .



SELECT USER, DATEOFBIRTH FROM USERTABLE WHERE DATEDIFF(DATEOFBIRTH,NOW()) < 60;
0


source 


SELECT * FROM tableName 
         WHERE dateOfBirth >= (CURRENT_DATE - INTERVAL 60 YEAR)   
         AND dateOfBirth <= ( CURRENT_DATE - INTERVAL 10 YEAR )

Hope I'm not offended by the database experts on this ...

0


source






More articles:


All Articles