Jump to content


Photo

create "monthly archives"


  • Please log in to reply
1 reply to this topic

#1 Enc0der

Enc0der
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 02 November 2005 - 05:25 PM

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!

#2 sgiandhu

sgiandhu
  • Validating
  • PipPip
  • Member
  • 13 posts
  • LocationVancouver, B.C. Canada

Posted 20 November 2005 - 07:50 PM

[!--quoteo(post=314046:date=Nov 2 2005, 09:25 AM:name=Enc0der)--][div class=\'quotetop\']QUOTE(Enc0der @ Nov 2 2005, 09:25 AM) View Post[/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 ?
[/quote]

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






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users