Member Avatar for 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.

  • 2 Contributors
  • 2 Replies
  • 108 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by omoayan

Dani AI

Generated

A compact, production-safe plan for a year->month->article archive that matches 's desired UX (click a year to reveal months, then pick a month for its issues).

Store each publication's timestamp in a DATE or DATETIME column and add an index. On read, produce a three-level structure (years -> months -> articles). Build that structure server-side so links work without JavaScript, and use JS only to add a client-side expand/collapse (this is the show/hide idea from , implemented as progressive enhancement).

Performance and correctness tips:

  • Avoid scanning the whole table on every request. Either run one aggregated query that returns year/month/article rows and assemble a nested array in PHP, or maintain a small summary table (year, month, count) that is updated on insert/delete.
  • Be careful: applying functions directly to the date column in WHERE/GROUP BY can prevent use of the index. If queries need to be fast, add persisted/generated integer columns for year and month (or maintain them in code) so they can be indexed.
  • Use server-side formatting for month names (respecting site locale and timezone). Keep the URLs bookmarkable like /archive/2012 or /archive/2012/06 for SEO and shareability.
  • Cache aggregated results (short TTL or rebuild nightly for static archives) to avoid repeated heavy aggregation on large datasets.

Small server-side build example (assemble rows into the tree):

$tree = [];
foreach ($rows as $r) {
  $tree[$r['year']][$r['month']][] = [
    'id' => $r['id'],
    'title' => $r['title'],
    'url' => $r['url']
  ];
}

Final notes: always use prepared statements, escape output for HTML, and order years/months descending to show the most recent content first. This approach gives the single-click year view asked for while keeping performance and accessibility in mind, and it complements 's grouping/show-hide suggestion.

Recommended Answers

All 2 Replies

Member Avatar for Member #120589

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.

Edited by Member #120589
Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.