Jump to content


Photo

Reminder Database


  • Please log in to reply
1 reply to this topic

#1 defcon2000

defcon2000
  • Members
  • Pip
  • Newbie
  • 5 posts

Posted 13 December 2005 - 12:52 AM

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,



#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 24 December 2005 - 01:13 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users