Sunday, October 29 2017
After long time we are publishing a new article about calendar events and multiple recurrence with mysql. So many free open source scripts are available for integrating calendar events. In this post explains how to integrate iCalendar event calendar script in PHP with MYSQL. Lets see database design things are
We have split two different ways of db structure one is a usual method and another one is a iCalendar method.
Old Method:
Id | event | start_time | end_time | is_all_day | repeat_type | interval |
2 | Home | 2017-10-01 8:00:00 | 2017-10-30 9:30:00 | 0 | 1 | 1:30 |
1 | Home | 2017-10-01 10:00:00 | 2017-11-30 12:00:00 | 0 | 2 | 2 |
Notes:
is_all_day – all day event (0-FALSE, 1-TRUE)
repeat_type – 1-daily, 2-weekly, 3-monthly, 4-yearly
interval – event duration.
Cons:
Instead of this method to use iCalendar event scheduler.
iCalendar Method:
Define rrule:
rrule is nothing but recurring rule. It always start FREQ keyword. We have to define the values for it and read more structure from here.
For example it is a weekly event this should start every monday to friday means that
FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR For daily 10 times FREQ=DAILY;COUNT=10 For monthly 1st monday to start FREQ=MONTHLY;BYMONTHDAY=1
Now we create a database and below these tables.
Step 1: calendar table
CREATE TABLE `calendars` ( `id` INT NOT NULL , `title` VARCHAR(255) NOT NULL , `color` SMALLINT NOT NULL DEFAULT 0 , `hidden` TINYINT(1) NOT NULL DEFAULT 0 , PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=44 DEFAULT CHARSET=latin1;
Step 2: events table
CREATE TABLE `events` ( `id` INT NOT NULL AUTO_INCREMENT , `title` VARCHAR(255) NOT NULL , `start` DATETIME NOT NULL , `end` DATETIME NOT NULL , `all_day` TINYINT(1) NULL DEFAULT 0 , `calendar_id` INT NULL , `app_id` VARCHAR(20) NOT NULL , `location` VARCHAR(255) NULL , `notes` TEXT NULL , `url` VARCHAR(255) NULL , `reminder` VARCHAR(255) NULL , `rrule` VARCHAR(999) NULL, `duration` INT NULL, PRIMARY KEY (`id`) , INDEX `calendar_id_idx` (`calendar_id` ASC) , CONSTRAINT `calendar_id` FOREIGN KEY (`calendar_id` ) REFERENCES `calendars` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=MyISAM AUTO_INCREMENT=44 DEFAULT CHARSET=latin1;
Download iCalendar event full script from our github page click here.
Right now we use iCalendar script and will add more related post to iCalendar functional wise in future.
Thanks for reading this post and let me know your feedback its very helpful to all readers.