PHP MYSQL menu in the archive for months and months

Question

I am looking for an effective way to match all blog posts in a menu of the following format:

2012

• August (6)
• September (4)
• October (2)

The month representing the month (obviously), and the value inside the brackets representing the number of posts in that month. After clicking, a search will be done for all messages this month, that year.

I need it to be dynamic, automatically collect November, when the post was created this month and continued in December, in 2013, etc. etc.

All I have is a UNIX timestamp for each message. I would really like to avoid using separate functions to collect infinite comlex arrays, etc.

Any help is greatly appreciated.

Answer 1

From your question, I understand that you are trying to find a query to group several items by month and year. The following should do the trick:

SELECT YEAR(dateField) AS YEAR, MONTH(dateField) AS MONTH, COUNT(*) AS TOTAL FROM table GROUP BY YEAR, MONTH 

Obviously, “DateField” is the name of the datetime / timestamp column and “table” is the name of your table.

Additional information on GROUP BY clauses and aggregate functions (for example, the COUNT (*) function used above) is here .