Event Calendar in php

[venkat0904]

Hi all,

Since its been a while now that i am learning php I undertook a project to develop event calendar in php and mysql. There will be a public calendar published on site which every1 can view. Then as a user logs in he will have his own personal calendar in which he can publish events and share with either a specific group or make it public.

any suggestions to start up with??
especially m pretty confused for DB structure that will help me accomplish this...

[network18]

Hi all,

Since its been a while now that i am learning php I undertook a project to develop event calendar in php and mysql. There will be a public calendar published on site which every1 can view. Then as a user logs in he will have his own personal calendar in which he can publish events and share with either a specific group or make it public.

any suggestions to start up with??
especially m pretty confused for DB structure that will help me accomplish this...

You can have below table structure -

event_id int(2) not null,
event varchar(30) not null,
event_date date ,
entry_date datetime,
user_id int(2) referes to the usermaster table.
and while displaying the calendar check this table for any event available (for the particular user ,this to be done if the user sees only his events only) for that particular date.

[venkat0904]

yeah that should help me take off... bt as i stated earlier a user can view his own events as well as of others in same group who have shared an event...
I can think of an addition ie with user id we can save group id of an event, in case the user wishes to share it with the group and while displaying we can check if user's own group id matches with event's group id..
what do u say??

You can have below table structure -

event_id int(2) not null,
event varchar(30) not null,
event_date date ,
entry_date datetime,
user_id int(2) referes to the usermaster table.
and while displaying the calendar check this table for any event available (for the particular user ,this to be done if the user sees only his events only) for that particular date.

[network18]

yeah that should help me take off... bt as i stated earlier a user can view his own events as well as of others in same group who have shared an event...
I can think of an addition ie with user id we can save group id of an event, in case the user wishes to share it with the group and while displaying we can check if user's own group id matches with event's group id..
what do u say??

its good. see you just needed a little push and now you can fly yourself :)

[venkat0904]

lolzzz m barely crawling n u talk abt flying... Thanks for the help to start though...
newayz d job isnt over yet. I ll need help time to time..
meanwhile if any1 has got some other suggestions, u r most welcome...

its good. see you just needed a little push and now you can fly yourself :)

I'd suggest setting some sort of "repeatable event". Does an event repeat regularly (e.g. every day, every Monday and Thursday, every first Monday of each calendar month). There's nothing more frustrating than having to enter repeat data.

If doing this, sometimes a repeat event may be cancelled / amended for a particular date - there should be a way to override the default message/info, or "unlink" the event from the date/time.

That may be a pain, but a cool project to try and get your head around!

[venkat0904]

Thats exactly the next feature I was thinking about...ie recurring events... i want my calendar to enable user to enter recurring events till a specific date or if they want, to end never..
i m not sure how to achieve that in DB coz previously i thought i ll have a separate table called dates which will have event id and corresponding dates but if a user chooses event to repeat infinitely then this fails... any suggestions??

I'd suggest setting some sort of "repeatable event". Does an event repeat regularly (e.g. every day, every Monday and Thursday, every first Monday of each calendar month). There's nothing more frustrating than having to enter repeat data.

If doing this, sometimes a repeat event may be cancelled / amended for a particular date - there should be a way to override the default message/info, or "unlink" the event from the date/time.

That may be a pain, but a cool project to try and get your head around!

I don't know if you'd need a separate table, but a repeating field set to some sort of value could be: DAY, with a another field holding comma separated values (or a serialized list) showing the details, e.g. 1,4 (for Monday and Thursday); MONTHLY_FIRST_WEEK and 3 (for first Wednesday of each calendar month). This could all go to your events table, with the date set to the first occurrence. You'd also need an enddate field.

You could then have an OVERRIDES field (set to the id of the repeating event) for all the cancelled or 'unlinked' events - events based on the original, but with slightly different details.

Haven't thought this through, but it's probably something like this.

There are a number of free calendar scripts out there with repeating events. Perhaps you could install a few to see how they arrange their data.

[venkat0904]

hey nice advice... actually m a novice in DB design so was not sure about how to go around it.
But there is a shortcoming with this design.. suppose i need to change the description of a recurring event just for one instance then i guess this structure wont let me do that..
any suggestions??

I don't know if you'd need a separate table, but a repeating field set to some sort of value could be: DAY, with a another field holding comma separated values (or a serialized list) showing the details, e.g. 1,4 (for Monday and Thursday); MONTHLY_FIRST_WEEK and 3 (for first Wednesday of each calendar month). This could all go to your events table, with the date set to the first occurrence. You'd also need an enddate field.

You could then have an OVERRIDES field (set to the id of the repeating event) for all the cancelled or 'unlinked' events - events based on the original, but with slightly different details.

Haven't thought this through, but it's probably something like this.

There are a number of free calendar scripts out there with repeating events. Perhaps you could install a few to see how they arrange their data.

hey nice advice... actually m a novice in DB design so was not sure about how to go around it.
But there is a shortcoming with this design.. suppose i need to change the description of a recurring event just for one instance then i guess this structure wont let me do that..
any suggestions??

That's exactly the content of my post that you quoted! That's the purpose of the OVERRIDE field.

If the override field have the repeating event 'id' value in it, your code will pick that up as 'don't put in the default info' for the repeating event this date/time, 'instead replace it with this info'.

[venkat0904]

ohhkk now i get ur point... awesome dude!!!
I didnt understand what u mean by override initially but its crystal clear now..
Makes perfect sense to me..
I can think of another feature to add up. I want my application to create html code for a user which he can paste in his own webpage and his public events can be displayed over there..
m totally blank regarding this rite nw..any suggestions ??

That's exactly the content of my post that you quoted! That's the purpose of the OVERRIDE field.

If the override field have the repeating event 'id' value in it, your code will pick that up as 'don't put in the default info' for the repeating event this date/time, 'instead replace it with this info'.

[network18]

ohhkk now i get ur point... awesome dude!!!
I didnt understand what u mean by override initially but its crystal clear now..
Makes perfect sense to me..
I can think of another feature to add up. I want my application to create html code for a user which he can paste in his own webpage and his public events can be displayed over there..
m totally blank regarding this rite nw..any suggestions ??

To create the html for the user, depending upon his few click , like positions of certain divs or table/border color he wants etc, you can use the DOM's parent-child nodes structure with javascript.

[venkat0904]

I think i didnt make myself very clear... let me define what i m trying to do.
The user can view his personal calendar by logging on to my site. He will have public as well as private events. Now i want to generate a HTML for that specific user which when pasted on his personal website, will display the same calendar with only public events visible..
B t dub i have a little idea abt DOM n javascript.. :(

To create the html for the user, depending upon his few click , like positions of certain divs or table/border color he wants etc, you can use the DOM's parent-child nodes structure with javascript.

You need to create an api
A js file that users cann link to with an auth key maybe

[network18]

you can have a look at this for reference.
It will involve creating the API which will link to your database also depending upon the user key(but i think no need to customize it on user basis, because all users will see the public events only in the embeded calendar) to fetch the public events apart from the html it will provide to render the calendar on the webpage.

[venkat0904]

ohhkk thats something i dont wanna go for rite nw...
actually enough of planning, now i ll start with implementation...
but once m done with all other features i ll definitely go for an api.
Thank you guys.
n btw do suggest me if u can think of some additional features...

[venkat0904]

all right m back with another issue. What i am trying to do is when a user adds a new event, he is provided with a list of resources from resource master table. now when i save the event to my event table, how am i supposed to save the resources selected by user because user can choose more than one of them...
suggestions pls...

You need a 'link table' with just the event_id and resource_id (may be more complicated for repeating events?). You then get the info from an INNER JOIN query.

[venkat0904]

I considered this option but it will create problems with override. Suppose for event_id 2 which is a recurring event user selects resource A and then later overrides it as resource B for one instance. now since the event_id in override table is a foreign key from events_tbl, the override changes in resource wont get reflected..
correct me if i m wrong...

You need a 'link table' with just the event_id and resource_id (may be more complicated for repeating events?). You then get the info from an INNER JOIN query.

[venkat0904]

still looking for a solution but no luck yet...
anyone got an idea??

Sorry, didn't get back earlier.
No my solution should work because the override is also a unique event and has its own event_id.

[venkat0904]

hey ardav... glad u r back..
let me just recap what i am trying to do..
there exists a separate table called override which contains the event_id as a foreign key from the event table for which a recurring event needs an override... now , when an event is to be displayed, i check in event table if the override field is set to true... if yes, i search with same event_id in override table and print the corresponding values.
Now the problem is, i dont have a resource field in either event table or override table coz 1 event can use multiple resources..
so, the problem arises when an event is overriden, because the overriden event and the original recurring event both share the same event_id, i cant override the resources..

Sorry, didn't get back earlier.
No my solution should work because the override is also a unique event and has its own event_id.

no, override table does not have a unique event_id coz its maintained in a separate table..
Sorry if i sound confusing... Do lemme know if u ve got any ideas..

The rationale I proposed was to include override as a field in the events table so that it would have a unique event_id, thereby having all the attributes of a 'normal' event. If you've gone and created a separate overrides table, fair enough, but your trouble is that you may have conflicting 'id'values if you need to UNION/INTERSECT your queries(id in event and id in override). I would suggest a re-design, but that's up to you.

My suggestion:

events
id (PK, int)
event_start_time (time)
event_end_time (time)
date_from (date)
date_until (date)
recurring_type (FK, int)
recurring_properties (varchar) - depending on the type
override (int) - this is the id of the event being overriden
author_id (FK, int) - creator
date_created (datetime)
date_updated (datetime)
privacy_level (tinyint, 2) - 0 = public, 1 = group, 2 = personal etc.

recur
id (PK, int)
label (varchar) - e.g. 'DAILY', 'WEEKLY', 'C_MONTHLY', 'FORTNIGHTLY' etc etc

NOTE
recurring_type/recurring_properties and override can't each have values at the same time.

[venkat0904]

Thanx for ur prompt reply ardav...
I am open to any redesign coz m still in the designing phase...

The rationale I proposed was to include override as a field in the events table so that it would have a unique event_id, thereby having all the attributes of a 'normal' event. If you've gone and created a separate overrides table, fair enough, but your trouble is that you may have conflicting 'id'values if you need to UNION/INTERSECT your queries(id in event and id in override). I would suggest a re-design, but that's up to you.

My suggestion:

events
id (PK, int)
event_start_time (time)
event_end_time (time)
date_from (date)
date_until (date)
recurring_type (FK, int)
recurring_properties (varchar) - depending on the type
override (int) - this is the id of the event being overriden
author_id (FK, int) - creator
date_created (datetime)
date_updated (datetime)
privacy_level (tinyint, 2) - 0 = public, 1 = group, 2 = personal etc.

recur
id (PK, int)
label (varchar) - e.g. 'DAILY', 'WEEKLY', 'C_MONTHLY', 'FORTNIGHTLY' etc etc

The above suggested structure looks good... As far as i understand it, the overriden event will make a separate entry in the event table itself with its own event_id... That should work fine unless u want to override a recurring event more than once... Eg suppose i created a weekly recurring event but i want to change event title for next week and the following one.. How am i supposed to deal with that?

NOTE
recurring_type/recurring_properties and override can't each have values at the same time.

What are you trying to tell me here?? are you saying that we cant override a recurring event? That looks fairly possible to me with this structure..

What are you trying to tell me here?? are you saying that we cant override a recurring event? That looks fairly possible to me with this structure..

No what I mean is that an event can either be:

*a straightforward event (DON'T fill recurring or override fields)
*a recurring event (fill both fields BUT NOT override field)
*an override event (fill override BUT NOT recurring fields)

The "override" record as I see it can:

*be similar to the repeating record (in which certain repeating field data can be cloned/copied to this new record)
*blank (as in cancelled) - perhaps you need a cancelled field (?) for this
*totally different - just a regular entry

The above suggested structure looks good... As far as i understand it, the overriden event will make a separate entry in the event table itself with its own event_id... That should work fine unless u want to override a recurring event more than once... Eg suppose i created a weekly recurring event but i want to change event title for next week and the following one.. How am i supposed to deal with that?

There is no reason why you can't override the repeating record more than once. This is like the option mentioned above:

*be similar to the repeating record (in which certain repeating field data can be cloned/copied to this new record)

[venkat0904]

okk i see.. so u r suggesting there would be 3 types of events present in my events table.. as stated below--

No what I mean is that an event can either be:

*a straightforward event (DON'T fill recurring or override fields)
*a recurring event (fill both fields BUT NOT override field)
*an override event (fill override BUT NOT recurring fields)

but that means i need to make repeated entries for a recurring event for each instance which i certainly want to avoid coz if an event repeats infinitely then...

NOOOOOOOO!
The recurring events and override fields are just 'flags' in a way. Each type of event is just that - an event.
If the event is just a one-off and is not overriding any repeating event, the recurring and override fields are blank.
If the event is recurring - the recurring fields are filled but not the override.
If the event is an override - the recurring fields are blank and the override field(s) is/are filled.

The recurring event is only specified ONCE. Your query will pick it up if it is infinite or the 'period' satisfies the calendar dates that need to be displayed (let's call this the "calendar period", e.g. weekly view).
It will then check for overrides in that "calendar period", so that certain repeats can be overwritten. To these we add normal once-only events.
Your events should now be in some sort of array (or object, if using OOP).
You then go to it to display these events in the calendar table via loop.

[venkat0904]

ohhkk that made things pretty clear...
but dont u think it would be an additional burden, each time when displaying anything in calendar, we need to check all the recurring events if they fall into current required period even if their start date is ages old... but then i cant see an alternative too... this is the way it has to be done to deal with infinitely recurring events...
Thanks for your patience and suggestions... U R D MAN !!

NOOOOOOOO!
The recurring events and override fields are just 'flags' in a way. Each type of event is just that - an event.
If the event is just a one-off and is not overriding any repeating event, the recurring and override fields are blank.
If the event is recurring - the recurring fields are filled but not the override.
The recurring event is only specified ONCE. Your query will pick it up if it is infinite or the 'period' satisfies the calendar dates that need to be displayed (let's call this the "calendar period", e.g. weekly view).
It will then check for overrides in that "calendar period", so that certain repeats can be overwritten. To these we add normal once-only events.
Your events should now be in some sort of array (or object, if using OOP).
You then go to it to display these events in the calendar table via loop.

but dont u think it would be an additional burden, each time when displaying anything in calendar, we need to check all the recurring events if they fall into current required period even if their start date is ages old..

To be honest, the alternative would be to create events for every week (or whenever the recurring event was to take place). In adition, what would happen if you wanted to change something on the recurring event, e.g. title or resources. You would then have to change every event manually (unless you wrote a script to do this - pain).

Mark it solved if we've finished.

[venkat0904]

well i cant see creating individual events as an option for me coz it will fail for never ending recurring events.

am i done?.. Nooo its just the database design we ve dealt with... that too not completely coz m thinking of an additional feature to add up.
as i said earlier a user will belong to a certain group and only his group's events are visible to him, i want users to first approve that he belongs to some specific group...
suggestions are welcome...

but dont u think it would be an additional burden, each time when displaying anything in calendar, we need to check all the recurring events if they fall into current required period even if their start date is ages old..

To be honest, the alternative would be to create events for every week (or whenever the recurring event was to take place). In adition, what would happen if you wanted to change something on the recurring event, e.g. title or resources. You would then have to change every event manually (unless you wrote a script to do this - pain).

Mark it solved if we've finished.