How do blog systems do this?

Question

I have a social network so a blog is only you could say a widget of the site for a user, so every user can have a blog, I would like to make this blog as nice as possible. WHen you go to a users blog page it shows all blogs for the current month.

My question, how can I show a column on the side like this July 2009 June 2009 May 2009 April 2009 March 2009 February 2009

The catch is it should only list months since the user joined the site until current, so a user that joined2 years ago from now would have 24 of these links and a user just 1 month or newer would only see 1?

The system uses php/mysql

Answer 1

This depends on how your database is structured. Generally, a blog post has a date associated with it. You can usually query your database to get a list of unique months for each user. Are you using an OSS system like Wordpress, or something custom? Can you give any insight on your schema?

Answer 2

Hi,

Assuming you :

• only want to take into account months when there is a blog-post from the user
• your DB is MySQL

You could try something like this :

select concat(YEAR(date), '-', MONTH(date)) as month, count(*) as num 
from ab_post 
where user_id = 1 
group by YEAR(date), MONTH(date) 
order by YEAR(date) desc, MONTH(date) desc;

It will :

• list the years/month when there has been a post from user "1" (this has to be dynamic, of course)
• for each month, give how many posts there are

Which means you'll get something like this :

+--------+-----+
| month  | num |
+--------+-----+
| 2008-6 |   1 |
| 2008-5 |   1 |
| 2008-4 |   3 |
| 2008-3 |   1 |
+--------+-----+

Thing to notice :

• grouping by year/month
• ordering by date desc

As a side note, this will give month as 1 digit for january to september : up to you to find the right format in MySQL's help ;-)

Have fun !