Archiving Journals

[omoayan]

Hi,

I am trying to build an archive for a website that will be publishing journals. However, I am having challenges building the archive where past journals will be stored and retrieved for future references and reading. My major challenge is how to retrieve the datetime from the DB and display it by year first and when a particular year is clicked it takes one to the months and when a month is clicked, the publications for the month are displayed for the interested reader to pick from. A sample of this is http://academicjournals.org/JABSD/Archive.htm. I will need assistance on this.

AN easy way to do this is to either store dates as unix date format or unix timestamp.
If the former:

Assume the date field is called 'dated':

SELECT YEAR(`dated`) AS yr, MONTH(`dated`) AS mn, COUNT(`dated`) AS tot FROM `articles` GROUP BY yr, mn ORDER BY yr DESC,mn DESC 

That will give you a basic list of published articles by year and the month (mst recent first):

yr      mn  tot
2012    10  5
2012    9   6
2012    8   3

You can use MONTHNAME(dated) to get the name of the month if you'd rather, BUT, you need to check the locale of the server - ensure that it is in the language expected. Or you can use the date() function or an array of month names, (for certain minority languages) for this.

The 'blogger-style' show/hide will require javascript and a different sql statement.

[omoayan]

Thanks diafol. I have tried to do this but what I actually desire to do is to have just one year description like 2012 and when it is clicked we see the months and we can then pick with months the publications for each month. Thanks and I am still open for further suggestions.