Building a blog section.. How do I create a menu with all the dates so you can sort between time periods?

Question

I'm currently working on creating my own blog section.. After brainstorming a little while I realized I'm a little stumped. I'm working with php and mysql (with codeigniter framework).

The front blog page would show the top 5 post; this I can easily do.

How would I go about creating a navigation that allows you to see the years that blogs were posted, and under those years the months. Then you can click that month you want to see blog posts for. I was thinking I would first need to figured out all the years that I have posts. Then from there figure out which months have posts, and then run a query for each one of those months.

I know the easy answer is just to use some sort of prepackaged deal, but I realy want to learn mysql queries better as well as php logic.

Anyway, any pointers would be greatly appreciated!

ps. table structure is super simple: id,title,content,author,date (is an actual date field)

Answer 1

You can always poke around the Wordpress plugin repository to see how people do it for that engine. (Smart Archives is one.) That way, you can get some hints, but would still need to develop your own since it's for a different blogging engine.

Answer 2

Try the following SQL:

SELECT 
    *, MONTH(date) AS month, YEAR(date) AS year 
FROM 
    table
WHERE
    year = '2009'
    AND month = '6'
GROUP BY
    year, month
ORDER BY
    date DESC

Your PHP just needs to pass in the appropriate year & month, or leave out the entire WHERE clause to get all.