SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Blog archive by date and month.

    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. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    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. #3
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    .: 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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,218
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Codiah View Post
    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.
    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 --
    Code:
    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...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •