Group multiple by month

[doddsey_65]

It seems so simple but i have spent the best part of 2 hours trying to do this. I want to select all the topics from the database and all of the users, then i want to display the dates of the last 7 days and show how many users registered and how many topics were created on that day like so:

 

4th Aug

2 topics

1 user

 

3rd Aug

5 topics

3 Users

 

and so on.

 

Anyone have any tips or can anyone help?

 

[Psycho]

What field type are you using for the date?

[doddsey_65]

UNIX timestamp

[Psycho]

UNIX timestamp

 

MySQL does not have a unix timestamp type field. It has "DATETIME", "DATE", and "TIMESTAMP" (which is not a unix timestamp). If you are simply storing a unix timestamp value from PHP into a numeric type field in MySQL you cannot group by dates without more work - if at all. It *may* be possible using MySQL's FROM_UNIXTIME, but I've never tried it and don't know if you can use MySQL's other data functions on top of that one.

 

So, please be specific in the exact field type you are using to store the date value in MySQL.

[fenway]

It's just being stored as an INT (hopefully UNSIGNED).

[kickstart]

Hi

 

Something like this should do it (not tested, so probably some typos)

 

SELECT Last7Days.aDay, COUNT(topics.id), COUNT(users.registerdate)
FROM (SELECT DATE_ADD(NOW(), INTERVAL (-1*i) DAY) AS aDay FROM integers WHERE i BETWEEN 0 AND 6) Last7Days
LEFT OUTER JOIN topics ON Last7Days.aDay = DATE(FROM_UNIXTIME(topics.topicDate))
LEFT OUTER JOIN users ON Last7Days.aDay = DATE(FROM_UNIXTIME(users.registerdate))
GROUP BY Last7Days.aDay

 

Relies on a table of integers from 0 to 9.

 

All the best

 

Keith