how to read out timestamp in database

for automatic archive function

[chemoul]

Hi,

I have a mysql database table with hundreds of articles. Each article contains a timestamp field (yyyy-mm-dd hh:mm:ss). I would like to create a function that automatically creates an archive, with the articles grouped by month and year (a page with links to month/year and the articles of this month/year displayed on the following page). To do this I need to know which months/years exist in the database and then I need to create a query for each existing month/year combination so that the articles of every month/year get included in the archive pages.

What would be the best way to do this?

Thank you for your comments.
Stefan

[jatar_k]

>> create a function that automatically creates an archive

is this to be an on demand thing or is going to only run, for example, once a month to archive the last created?

If it is going to be on demand, I think that is a bad idea. I would run it on cron either daily, weekly or monthly.

The first run will be a little intense but after that is just an update of the new articles and shouldn't be a big deal. For the first run I would pull all the Distinct months and years and then build from there, selecting content as it goes working off of an array of dates already selected and sorted.

[chemoul]

thank you for your reply.
I can run it as a cron job in intervals, that's not a problem. My issue is that I will have no way of knowing what months/years will be in the database, so I don't know how to do the query in the first place. I don't really want to fill an array with, say from 1996 to 2050 and query the database for the existance of every month between those years. Is there another way to select all the timestamps (and the attached articles), then determine which months/years exist and then group and link them accordingly on a webpage?

Thank you for your help.

Stefan

[jatar_k]

>> I have a mysql database table with hundreds of articles

that isn't much and can be processed in a flash

>> Is there another way to select all the timestamps (and the attached articles), then determine which months/years exist and then group and link them accordingly on a webpage?

you have the answer right there, though I wouldn't select the attached articles necessarily.

As far as an archive goes, I assume you are just looking to have something like

2003
2004
2005

then click on those and have months or just list months under each year, then list the article link under that.

I would create a script on cron as we said that runs once a month to build static archives but that's just me. If you want to keep it dynamic then you would need to have it tiered so that any given page isn't doing too much work.

I would take a look at Date and Time Functions [dev.mysql.com] to see what you can do with date columns and how to select certain parts.