Get list of months from MySQL

How do I get a lists of months for my weblog (like Wordpress)

[thijsnetwork]

Hello, I'm creating my own weblog script and I'd like to have a list of months to have quick access to the archive. Just like the Wordpress weblog system.

I don't want it to be exactly the same as in Wordpress, I 'd like to have a list of months of the current year, and a list of years below it to view entries older than one year.

Like this:

Archives

April 2005
February 2005
January 2005

2004
2003
etc.

Notice that March 2005 is not included, because there weren't any blog-entries written in March.

My blogs-tables looks like this (simplified):

+-----+------------+
¦ id ¦ date    ¦
+-----+------------+
¦ 137 ¦ 1108402089 ¦
¦ 138 ¦ 1108462489 ¦
¦ 139 ¦ 1140002089 ¦
+-----+------------+

the date column contains a plain unix timestamp.

Does anybody of this forum know how I can create a MySQL query which returns the months?

[LangDesigns]

It might be easier to assign a date to your posts.
So when you post something a month, year, day, time ect is assigned to the post.

So you can check the database.
$sql = "SELECT * FROM posts WHERE month = "March" AND year="2005";

[thijsnetwork]

Well that isn't the problem, I can do the same as in your example without having to create 2 additional columns with something like:

SELECT * FROM `posts` WHERE FROM_UNIXTIME(date, '%M') = March && FROM_UNIXTIME(date, '%Y') = 2005

But that's not the answer to my question, I would like to have MySQL give a list of months wherein posts have submitted.

[coopster]

Have you tried a DISTINCT or perhaps a GROUP BY clause, with an ORDER BY YEAR DESC, MONTH DESC? Of course, you would have to run this over your "posts" table.

[hakre]

to answer your question: yes it's possible. LangDesigns solution would only blow up your database and making no sense at all. coopster is focussing into the right direction, it's DISTINCT and/or GROUP BY which will help you. additionally, FROM_UNIXTIME(date, '%M') was named by yourself.

SELECT FROM_UNIXTIME(date, '%Y-%M') AS dt, COUNT(data) AS cnt FROM blogs GROUP BY dt ORDER BY dt DESC

should do the trick for the current year for each month. for the other years, you have to modify your query a bit:

SELECT FROM_UNIXTIME(date, '%Y') AS dt, COUNT(data) AS cnt FROM blogs GROUP BY dt ORDER BY dt DESC

you can then code the WHERE-clause yourself to minimize the datastock which needs to be queried at all. i optionally put cnt into the query which contains the number of entries in that month / year.

[thijsnetwork]

Thanks, you put me in the right direction! I'll use the last query, and modify it to my needs. The grouping of the years other than 2005 will be done by php.