automated archive on date

Rollo_Tamasi

hi guys, i'm creating a general events guide for a site i'm working on. The administrator adds events to the database and when the date of the event has passed then it is automatically removed from the events_page and stored in an archive. I have fields in a single table for name / email / current_date / event_title / description / event_date / url

what other fields would i need to implement this or would i need to create a new table and run a query when the event_date = now ?

also if anybody has any code snippets that i could use for this system i would appriciate it.

vaaaska

It looks like you have what you need. Just query/sort everything by date.

However, your date format could be important. I often use 2004-12-31 13:14:52 (Y-m-d H:i:s) as my date format - it sorts just fine and works natually with MySQL. Others will use more 'simple' date formats like unix time or whatnot.

Go for it...

Rollo_Tamasi

i'd prefer the europe date system myself of 00-00-0000

do you know what the php query code would look like to remove an event from the events list if the date of the event has past?

leatherback

on top of my head, something like:

delete from table where unix_timestamp(DATECOLUM) < curr_date()

Rollo_Tamasi

thanks, but i don't want to delete the event, i would prefer to move it to an archive, so that's why i was wondering should i have another table specifically for archiving.

mrfritz379

I don't think you really need to add any columns or tables or anything. When showing current events, use a WHERE clause in your query to show anything beyond the current time (WHERE date_field > $now). For your archive page, change the WHERE clause to show anything before now (WHERE date_field < $now).
And of course, you can divide up the archive page however you want (WHERE date_field BETWEEN $thirty_days AND $now).

Installer

i'd prefer...00-00-0000

You'd be better off building and storing dates as "yyyy-mm-dd" as that would be compatible with MySQL DATE and DATETIME formats and would also work with, e.g., PHP strtotime(). Then you'd have a lot less string-conversion PHP code to write (do the conversion if desired only for display), and your db queries would be more straight-forward. You'd be able to more easily use the built-in MySQL Date and Time Functions and cut down on the PHP coding needed. For example:

$query = "DELETE FROM table WHERE date_column < CURDATE()";
// Just an example; you don't have to delete.

$display_format = date('d-m-Y', strtotime($date_from_db));

leatherback

Installer wrote:
You'd be better off building and storing dates as "yyyy-mm-dd"

Actually... Just use a date, or date-time colum in mysql, which will store the date as a unix timestamp in the database, and just format whichever way you prefer when you retrieve it, using dateformat: DATE_FORMAT(DATECOLUM, '%Y %M %d')

And, yes, if you do not want to delete anay, just do the
select from table where unix_timestamp(DATECOLUM) > curr_date()

edit
I think it is actually now() and not curr_date. Have a look at the timefunctions mentioned before in the thread

/edit

mrfritz379

I thought mysql used the 0000-00-00 format for dates... at least, that's how I always format my data for storage... 😕

leatherback

Hey MrFritz,

It depends a little on the format you use.

A timestamp colum will use the "number of seconds since 1970", requires 4 bytes, whereas the time / data colums store the actual date & time, each requireing 3 bytes. A date & time colum will require 8 bytes.

I prefer using the timestamp, because I usually find out that I would like to be able to store both time & date for records.

Installer

Just use a date, or date-time colum in mysql, which will store the date as a unix timestamp in the database

No.

A timestamp colum will use the "number of seconds since 1970"...

The MySQL TIMESTAMP type uses the same format as DATE (date part) and DATETIME (yyyy-mm-dd etc., or variations of). See here.

I think it is actually now() and not curr_date. Have a look at the timefunctions mentioned before in the thread...

I'll repost my link for you. "NOW()" returns the date and time. "CURDATE()" returns the date, which is what Rollo wanted.