I have 3 tables in my database, tbl_events, tbl_exceptions, tbl_archived_events
tbl_events stores a list of events, this contains the following fields
**eventID** => int(4)Key, AutoIncrement
eventREF => VARCHAR (4) ( will remain the same for each version of the event and will be used to check for an exception)
eventName => VARCHAR (30) (Name of the event)
eventType => int(4) will determine the type of event it is
eventLocation => VARCHAR (30) hold the event location data
eventDate => DATETIME hold the date of the event
eventStart => DATETIME hold the start time of the event
eventEnd => DATETIME hold the end time of the event
isReoccuring => int(2) Default to 1 which means it is reoccurring
frequency => VARCHAR (10) will be either Daily/Weekly/Monthly/Yearly
eventLink => VARCHAR (30) will contain a link to the event page if there is one
eventValid => int(2) Will be set to 1 if the event is on and 0 if there is an exception
tbl_exceptions stores a set of events and the dates they are not held. There might be certain times when a reoccurring event might not be held. This table will hold the following field information
**exceptionID** KeyField, AutoIncrement => int(4)
eventREF => VARCHAR (4) Holds the event ref number
exceptionDate => DATETIME , Hold the date of the exception
tbl_archive_events will store the past events that have expired. this table will store the same data as the tbl_events table but for past events only
**eventID** => int(4)Key, AutoIncrement
eventREF => VARCHAR (4) ( will remain the same for each version of the event and will be used to check for an exception)
eventName => VARCHAR (30) (Name of the event)
eventType => int(4) will determine the type of event it is
eventLocation => VARCHAR (30) hold the event location data
eventDate => DATETIME hold the date of the event
eventStart => DATETIME hold the start time of the event
eventEnd => DATETIME hold the end time of the event
isReoccuring => int(2) Default to 1 which means it is reoccurring
frequency => VARCHAR (10) will be either Daily/Weekly/Monthly/Yearly
eventLink => VARCHAR (30) will contain a link to the event page if there is one
eventValid => int(2) Will be set to 1 if the event is on and 0 if there is an exception[/CODE]
So once an event has expired i would like mysql to do the following:
- 1/ if the event is a reoccurring event create a new event and set the eventDate date relative to the length of time specified in the frequently field relative to the existing event.
- 2/ if the new event eventREF is found in the eventException table then set the eventValid to 0
- 3/ once the event has expired copy the event to eventsArchive table
- 4/ delete expired event from tbl_events table
i have this so far
DELIMITER $$
CREATE
EVENT `new_event`
ON SCHEDULE EVERY 1 DAY STARTS '2016-07-24 03:00:00'
DO BEGIN
-- create new event
SELECT eventID, eventREF, eventTitle, eventLocation, eventDate, eventStart, eventEnd, isReoccuring, frequency, eventType, eventLink, eventValid
FROM tbl_events
WHERE eventDate < now()
--- for each event found create a new event
INSERT INTO tbl_events (eventID, eventREF, eventTitle, eventLocation, eventDate, eventStart, eventEnd, isReoccuring, frequency, eventType, eventLink, eventValid)
-- copy expired events to tbl_archived_events
INSERT INTO tbh_archived_events (eventID, eventREF, eventTitle, eventLocation, eventDate, eventStart, eventEnd, isReoccuring, frequency, eventType, eventLink, eventValid)
SELECT eventID, eventREF, eventTitle, eventLocation, eventDate, eventStart, eventEnd, isReoccuring, frequency, eventType, eventLink, eventValid
FROM tbl_events
WHERE eventDate < now();
-- delete expired events from tbl_events
DELETE FROM tbl_events WHERE eventDate < now();
END */$$[/PHP]
obviously the above is not correct and I'm not really sure what i am doing, would appreciate some help please Thanks
Luke
