Blog archive by date and month

Databases
1

OK, so I’ve been learning PHP for about a month and a half now and I’m really stumped on this one, and after hours of searching i haven’t found a solution by myself so I’m turning to the wonderful people here at SitePoint.

Basically I’m building a blog from scratch out of PHP as a learning project and a usable finished product. Where I’m stuck is with my idea for an archive. What i want is a simple list that reads like the following example.

  • January 2010 (1)
  • February 2010 (3)
  • May 2010 (2)

I want to display the month and year, and then how many blog post are in that time period, skipping the ones that are empty so i can link back to my previous posts. I’ve been racking my brain for a few hours now trying to work this out in my head and I’m not getting anywhere fast. The trouble is I don’t know how to make it sort by both month and year and only display one link if I have more than one post in the same month. Any help would be overly appreciated.

thanks.

2 
SELECT
  YEAR(post_date),
  MONTH(post_date),
  COUNT(*)
FROM
  posts
GROUP BY
  YEAR(post_date),
  MONTH(post_date)
ORDER BY
  YEAR(post_date),
  MONTH(post_date)

This will give you back what you asked (one row per month/year with the count of how many rows have a date in that month/year).

The GROUP BY means you’ll only get one row for each combination of month/year.

The ORDER BY means it’ll be returned in order of the year, and where the year is equal, in order of the month.

3

.: Thanks Dan, that’s exactly what I was looking for, I posted this on a few sites hoping for a quick response and you’re the only one that came through with a sensible answer. So I really appreciate that allot.

And not to mention this shows me how to do a few things in a query that I was un-aware of like grouping, so every new piece of information is like a whole new doorway of simplification for me.

Needless to say I will be sticking around Sitepoint for any future needs of this sort, perhaps I’ll be able top lend a hand as well someday soon.

4

OMG, how lame could those other sites be

your problem, and the solution, is at the novice level, there’s nothing particularly difficult about it

by the way, you wanted month names, right?

you’ll need to use this –

SELECT MONTHNAME(post_date) -- display this
     , YEAR(post_date)
     , COUNT(*)
     , MONTH(post_date)  -- and ignore this in the display
  FROM posts
GROUP 
    BY YEAR(post_date)
     , MONTH(post_date)
     , MONTHNAME(post_date)
ORDER 
    BY YEAR(post_date)
     , MONTH(post_date)

notice that in order to include MONTHNAME in the SELECT clause, you need to include it in the GROUP BY clause

actually, you don’t need both MONTHNAME and MONTH in the GROUP BY clause in mysql, but ignore this non-standard anomaly for now

you do, however, need to include MONTH in the ORDER BY clause (so as to get the proper sequencing), and therefore you also need to include it in the SELECT clause

maybe a bit beyond novice level after all…

:slight_smile: