Reminder Database

[defcon2000]

Hello Everyone,

 

I have the following 2 tables as follows:

 

Reminder Table

`reminder_id` int(10) unsigned NOT NULL auto_increment,

`username` varchar(50) default NULL,

`reminderdate` date default NULL,

`reminder` varchar(255) default NULL,

`autoclose` varchar(1) default NULL,

`recur` varchar(1) default NULL,

`occurence` int(3) default NULL,

`emailme` varchar(3) default NULL,

`emailtoperson1` varchar(100) default NULL,

`emailtoperson2` varchar(100) default NULL,

`emailtoperson3` varchar(100) default NULL,

`reminded` varchar(1) default NULL,

`status` varchar(10) default NULL,

PRIMARY KEY (`reminder_id`)

 

Occurence

`occurence_id` int(11) NOT NULL auto_increment,

`occurence` varchar(50) collate latin1_general_ci default NULL,

`periodtype` varchar(11) collate latin1_general_ci default NULL,

`period` int(2) default NULL,

PRIMARY KEY (`occurence_id`)

 

occurence.occurence_id relates to reminder.occurence

 

The occurence table stores the type of occurences available for example:

Occurence_ID Occurence Period Period Type

1 1 Week 7 Week

2 Quarterly 3 Month

3 Annual 1 Year

 

The idea of the occurence table is so that dates can be change by day, month or year and not having to go deep into mathematical calculations.

 

A sample data for reminder is as follows:

`reminder_id` 10

`username` defcon2000

`reminderdate` 2005-12-03

`reminder` "hello this is a test"

`autoclose` Y

`recur` Y

`occurence` 2 (meaning it will recur every quarter)

`emailme` Y

`emailtoperson1` "defcon2000@hammer.com"

`emailtoperson2` ""

`emailtoperson3` ""

`reminded` N

`status` "Open"

 

--------------------------------------------------------------------------------

 

The logic I am look for in an SQL format is:

 

SELECT * From reminder

WHERE "status" = "open" and "reminded" ="N"

 

DO THE FOLLOWING FOR EACH ROW ROUND

 

IF "reminderdate" = "today's date"

THEN "reminded" = "Y"

 

IF "reminderdate" > "7 days from today's date" and "reminded" = "Y" and "autoclose" = "Y" and “recur” = "N"

THEN "status" = "Closed"

ELSE

"status" = "Closed"

COPY RECORD into reminder

CHANGE “reminderdate” meet “occurrence” requirement,

SET “reminded” = “N”

SET “recur” = “Y”,

SET “autoclose” = “Y”

SET "status" = "Open"

 

--------------------------------------------------------------------------------

 

I hope someone can help me as I am quite new to MySQL and esp SQL.

 

Thanks in advance.

 

Rgds,

 

 

[fenway]

You're going to have to break up each of those pieces of logic into SQL syntax, and try them in sequence. Post back when you've got that far, and we can walk you through the tricky parts.