create "monthly archives"

[Enc0der]

Hello!

 

I'm currently making a website with some news/articles on a mysql database.

Every item is stored with a 'date' column, in unix timestamp format.

I have no problem, of course, to get the time in the desired format, like this: $time = date('F j, Y, G:i', $time);

 

The problem is I want to make a monthly-archive.

What I need is a two things:

 

1. a function that will get the months of the items, and create the archive-links - with something like "?month=XX" (or maybe "?month=XX&year=YY" ?).

 

Something that will look like this:

November 2005

October 2005

September 2005

etc... and every link will show the items of that specific month:

 

2. a function (I believe it's just a sql-query, actually) that will get all the items from a specific month only. Remember that's every item have a 'date' as a unix timestamp..

 

 

Do you have any idea on how to make that in an efficient way ?

 

Thanks!

[sgiandhu]

[!--quoteo(post=314046:date=Nov 2 2005, 09:25 AM:name=Enc0der)--][div class=\'quotetop\']QUOTE(Enc0der @ Nov 2 2005, 09:25 AM) 314046[/snapback][/div][div class=\'quotemain\'][!--quotec--]

2. a function (I believe it's just a sql-query, actually) that will get all the items from a specific month only. Remember that's every item have a 'date' as a unix timestamp..

Do you have any idea on how to make that in an efficient way ?

 

I can supply this as far as the query to the DB structure goes, but I think someone else might have to help you with the rest.....

 

SELECT * FROM tableNews WHERE YEAR(Date) = YEAR(CURDATE()) AND MONTH(Date) = 8 ORDER BY Date ASC

 

Depending on how your DB is set up, the above should give you all the listings from August for the current year - adjust the MONTH(Date) = X accordingly.

 

Hope this helps

 

J