mbishop1980
Posts: 31
|
| Posted: 04/01/2008, 11:52 AM |
|
I'm a creating a CCS Calendar with the Calendar Builder. What I'm wanting to do is give the user the option to repeat the event either weekly, monthly, yearly. Things of that nature.
Does anyone have any ideas on this? I have look on the forum with no success.
_________________
Thanks,
Eric |
 |
 |
ckroon
Posts: 869
|
| Posted: 05/25/2008, 12:11 AM |
|
I am interested in this functionality as well. Anyone have any pointers?
I need to allow the user to repeat an event.. same day/hours every week...
_________________
Walter Kempees...you are dearly missed. |
|
|
|
mentecky
Posts: 321
|
| Posted: 05/25/2008, 5:00 PM |
|
I did this kind of... I'll share how I got repeating events to work on my site http://www.meetwny.com. It's probably not as full featured as you need but should be a good start.
I created a table, chat_rooms, with the following fields:
CREATE TABLE `chat_rooms` (
`chat_room_id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(128) NOT NULL,
`description` text NOT NULL,
`chat_type` int(11) NOT NULL,
`chat_start_date` date NOT NULL DEFAULT '2000-01-01',
`chat_start` time NOT NULL,
`chat_end_date` date NOT NULL DEFAULT '2030-12-31',
`chat_end` time NOT NULL,
PRIMARY KEY (`chat_room_id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
The fields are self explanatory except chat_type. I used these values and stored them in a table named chat_types.
1 - Sundays
2 - Mondays
3 - Tuesdays
4 - Wednesdays
5 - Thursdays
6 - Fridays
7 - Saturdays
8 - Weekdays
9 - Weekends
10 - Every Day
11 - One Time Event
Now the tricky part... the SQL query to display the events:
SELECT chat_rooms.*, chat_types.chat_type AS chat_types_chat_type
FROM chat_rooms INNER JOIN chat_types ON
chat_rooms.chat_type = chat_types.chat_type_id
WHERE ( chat_rooms.chat_start_date <= CURDATE() )
AND ( chat_rooms.chat_end_date >= CURDATE() )
AND ((( chat_types.chat_type_id = 11
OR chat_types.chat_type_id = 10 ))
OR ( chat_types.chat_type_id = 9
AND ( ( DAYOFWEEK(CURDATE()) = 1 )
OR ( DAYOFWEEK(CURDATE()) = 7 ) ))
OR ( chat_types.chat_type_id = 8
AND ( DAYOFWEEK(CURDATE()) > 1 )
AND ( DAYOFWEEK(CURDATE()) < 7 ) )
OR ( chat_types.chat_type_id = DAYOFWEEK(CURDATE()) ) )
ORDER BY chat_start
Wow that still gives me a headache to look at. OK, what the above does is display all chat rooms open for the current date based on its type, start and end dates. This only handles daily and weekly events but you could add more layers to that large SQL statement to handle monthly and annual I am sure. My site did not require any other types.
The fun part is in the last section of the SQL WHERE clause that contains a bunch of ORs. You'll see where it's going if you match chat_type_id to the table above. Pay attention to the parenthesis.
You can see it in action in the middle of the home page of the site I mentioned above.
I either helped or totally confused you. I hope it was more on the helped side.
Rick
_________________
http://www.ccselite.com |
|
|
|
ckroon
Posts: 869
|
| Posted: 05/25/2008, 6:03 PM |
|
Thanks! That will help!
_________________
Walter Kempees...you are dearly missed. |
|
|
|
mamboBROWN
Posts: 1713
|
| Posted: 05/25/2008, 8:29 PM |
|
mbishop1980
If this solves your problems please add [RESOLVED] to your title. Thanks.
|
|
|
|
mbishop1980
Posts: 31
|
| Posted: 05/26/2008, 1:49 PM |
|
Thank you very much mentecky. I will try this to see how it turns out.
_________________
Thanks,
Eric |
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/26/2008, 1:59 PM |
|
@Rick,
So what you are doing here is entering one event in the calendar..
Give it a start date and an enddate in the future (expiration)
as well as a repetition marker, right?
You are therefor not actually creating a record for each event = each day it occurs.
Which I think is a nifty solution.
I'd like to hear from the others though if this is what they meant, or if they meant actually copying records.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
mentecky
Posts: 321
|
| Posted: 05/26/2008, 2:14 PM |
|
Quote wkempees:
@Rick,
So what you are doing here is entering one event in the calendar..
Give it a start date and an enddate in the future (expiration)
as well as a repetition marker, right?
You are therefor not actually creating a record for each event = each day it occurs.
Which I think is a nifty solution.
I'd like to hear from the others though if this is what they meant, or if they meant actually copying records.
Walter
Yes. I have 1 record for a chatroom that for example is set for every Tuesday between two dates. Then Every Tuesday it opens between the start time and end time. It works great because I can change the times for the room and one edit changes all future events without a lot of DB code. I can also temporarily disable a room by setting it's end date to yesterday... then when I want it back just set the date to a future date.
It's probably not the perfect solution for everyone, but it works great for what I needed.
Rick
_________________
http://www.ccselite.com |
|
|
|
mbishop1980
Posts: 31
|
| Posted: 05/27/2008, 6:50 AM |
|
That is the perfect solution for me. Even though I have to figure how to incorporate repeating every year or month into that.
I originally wanted to create a individual record for each event but I can definitely see where Rick's solution would be easier to edit. Thanks for your help Rick.
_________________
Thanks,
Eric |
|
|
|
mentecky
Posts: 321
|
| Posted: 05/27/2008, 8:02 AM |
|
Quote mbishop1980:
That is the perfect solution for me. Even though I have to figure how to incorporate repeating every year or month into that.
I originally wanted to create a individual record for each event but I can definitely see where Rick's solution would be easier to edit. Thanks for your help Rick.
Glad I could help. I can help with that massive or statement if you need but it sounds like you got a handle on it.
Rick
_________________
http://www.ccselite.com |
|
|
|
mbishop1980
Posts: 31
|
| Posted: 05/27/2008, 8:14 AM |
|
I'm going to try and work on it some this evening. I may need your help, the whole every month, every year thing may get me. But I'm going to try.
_________________
Thanks,
Eric |
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/27/2008, 8:29 AM |
|
Quote mbishop1980:
I'm going to try and work on it some this evening. I may need your help, the whole every month, every year thing may get me. But I'm going to try.
Kudos to Rick, it is what's needed, actualy I like it myself.
While you are doing the month/year, how about working days/ weekends/quarter.....
Walter.
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
mentecky
Posts: 321
|
| Posted: 05/27/2008, 9:12 AM |
|
Quote wkempees:
Quote mbishop1980:
I'm going to try and work on it some this evening. I may need your help, the whole every month, every year thing may get me. But I'm going to try.
Kudos to Rick, it is what's needed, actualy I like it myself.
While you are doing the month/year, how about working days/ weekends/quarter.....
Walter.
That whole mess of an OR statement actually does weekdays and weekends. I stopped there though. I didn't need much else.
That's for the compliments !
Rick
_________________
http://www.ccselite.com |
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/27/2008, 11:24 AM |
|
@Rick,
I have taken a look at your SQL, built the sample and simplified it for you, as a gesture.
Please let me know if you agree with:
SELECT chat_rooms.*, chat_types.chat_type AS chat_types_chat_type
FROM chat_rooms INNER JOIN chat_types ON
chat_rooms.chat_type = chat_types.chat_type_id
WHERE ( chat_rooms.chat_start_date <= CURDATE() AND chat_rooms.chat_end_date >= CURDATE() )
AND (
( chat_types.chat_type_id = 11 OR chat_types.chat_type_id = 10) OR
( chat_types.chat_type_id = DAYOFWEEK( CURDATE() ) ) OR
( chat_types.chat_type_id = 9 AND ( DAYOFWEEK( CURDATE() ) = 1 OR DAYOFWEEK( CURDATE() ) = 7 ) ) OR
( chat_types.chat_type_id = 8 AND ( DAYOFWEEK( CURDATE() ) > 1 AND DAYOFWEEK( CURDATE() ) < 7 ) )
)
ORDER BY chat_start
See if you agree, and I did not miss anything
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/27/2008, 11:40 AM |
|
SELECT chat_rooms.*, chat_types.chat_type AS chat_types_chat_type
FROM chat_rooms INNER JOIN chat_types ON
chat_rooms.chat_type = chat_types.chat_type_id
WHERE ( chat_rooms.chat_start_date <= CURDATE() AND chat_rooms.chat_end_date >= CURDATE() )
AND (
( chat_types.chat_type_id = 11 OR chat_types.chat_type_id = 10) OR
( chat_types.chat_type_id = DAYOFWEEK( CURDATE() ) ) OR
( chat_types.chat_type_id = 9 AND ( DAYOFWEEK( CURDATE() ) = 1 OR DAYOFWEEK( CURDATE() ) = 7 ) ) OR
( chat_types.chat_type_id = 8 AND ( DAYOFWEEK( CURDATE() ) > 1 AND DAYOFWEEK( CURDATE() ) < 7 ) ) OR
( chat_types.chat_type_id = 12 AND ( DAYOFMONTH(chat_rooms.chat_start_date) = DAYOFMONTH( CURDATE() ) ) )
)
ORDER BY chat_start
Caters for Monthly recurrance, add value 12 - Monthly, to the chat_types table.
Taken from the start_date day value, so one can set '2008-04-28' to 2008-12-31' monthly occurance will be on 28th of each month upto and including december.
Walter
(kind of enjoying this one  )
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
mentecky
Posts: 321
|
| Posted: 05/27/2008, 11:56 AM |
|
looks right to me Walter. Thanks!
Quote :(kind of enjoying this one )
Where were you when I was pulling my hair out writing that SQL nightmare!?!?!?!
_________________
http://www.ccselite.com |
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/27/2008, 11:59 AM |
|
lol, doing someone elses maybe?
just skim the forum and you will see some.
As well as maintining my (major) application, in which I built a report generator in MySQL that reads a MySQL table holding the report's SELECT in a BLOB as well as pointers to the next row holding the next .... and so on.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
mbishop1980
Posts: 31
|
| Posted: 06/02/2008, 6:03 AM |
|
@ Rick & Walter
You guys are the bomb, it worked and I would like to give you guys a great big compliment.
Thank you for all of your input, couldn't have done it without you.
_________________
Thanks,
Eric |
|
|
|
wkempees
Posts: 1679
|
| Posted: 06/02/2008, 6:08 AM |
|
@Eric,
Glad you enjoyed it.
We are still at it though, see us here http://forums.codecharge.com/posts.php?post_id=97355
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
mentecky
Posts: 321
|
| Posted: 06/02/2008, 7:04 PM |
|
Quote mbishop1980:
@ Rick & Walter
You guys are the bomb, it worked and I would like to give you guys a great big compliment.
Thank you for all of your input, couldn't have done it without you.
Thanks! If you liked this solution check out Recurring Nightmares Part II at http://forums.codecharge.com/posts.php?post_id=97419
Rick
_________________
http://www.ccselite.com |
|
|
|
|
