0

I'm accessing a database created by another web company to retrieve event information for my current client. My client enters info for events and notes whether the date is recurring or not. I'm trying to display all the recurring dates. So far I have been able to get everything to display, regular dates as well as recurring.

The tables are laid out as follows:

  • Events
  • Events_Recurring

Here is part of the Events table BIGGER PICTURE

events table

This is what the Events_Recurring table looks like

events recurring table

When the client checks it as recurring, the events_recurring table creates a row with the Event ID and other information like what day of the week or month the event is recurring on.

I'm just not sure how to display multiples of that certain ID that is recurring. I have a start date, and end date I can access, as well as what day of the week it is recurring on.

So for example: If this event reoccured every thursday. and I knew it started on Jan 1st and ended Jan 31st, can I run through that and spit out 4 different events all with the date of every Thursday in January?

Here is the full code I am working with, it's a little messy while trying to figure this out. I'm checking for the recurrence towards the bottom

// Access external database
$events_db = new wpdb(TOP SECRET CREDENTIALS HERE);
$events_db->show_errors();

if ($events_db) :
    // Query Events Database
    $events = $events_db->get_results(
        "
        SELECT ID, RequestDateStart, RequestDateEnd, Ministry, RequestTimeStart, EventName, CoordinatorName, EventDescription, Location
        FROM gc_events
        WHERE PrivateEvent = 0
        AND Ministry = 15
        AND date(RequestDateStart)>=date(NOW())
        ORDER BY RequestDateStart
        "
    );

    // Create the event data that will be displayed
    foreach ($events as $event) :

        // Store Event ID in a variable
        $masterID = $event->ID;

        echo '<div class="col-12">';

        echo '<strong>ID:</strong> ' . $event->ID . '<br /><strong>Event Name:</strong> ' . $event->EventName . '<br /><strong>Leader:</strong> ' . $event->CoordinatorName . '<br /><strong>Date:</strong> ' . date('l, F j',strtotime($event->RequestDateStart)) . '<br /><strong>Start Time:</strong> ' . date('g:i a',strtotime($event->RequestTimeStart));

        // CHECK IF RECURRING

        $recurring_events = $events_db->get_results(
            "
            SELECT gc_event_id, period, day
            FROM gc_event_recurring
            WHERE gc_event_id = '$masterID'
            "
        );

        foreach ($recurring_events as $recurring_event) :
        if ($recurring_event->period === 'week') {

        echo '<div class="col-12"><strong>&uarr; WEEKLY</strong><br />';

        echo $recurring_event->day;

        echo '</div>';

        }
        endforeach;

        echo '</div>';

    endforeach;
endif;

The result I am getting right now (with recurring events) is

Event: Weekly Prayer
Date: Feb 1, 2013

The result I would like is

Event: Weekly Prayer
Date: Feb 1, 2013

Event: Weekly Prayer
Date: Feb 8, 2013

Event: Weekly Prayer
Date: Feb 15, 2013

Event: Weekly Prayer
Date: Feb 22, 2013

This would be if the start date was Feb 1st and end date was Feb 28th.

Improve this question
20
  • your question is confuding can you please show the code and yes please try to define you question in a good way dnt panic just be calm and write Commented Feb 2, 2013 at 7:12
  • Trying not to panic :) added a bit more info, as well as the code I'm working with Commented Feb 2, 2013 at 7:19
  • so, recurring event is displayed multiple times? Commented Feb 2, 2013 at 7:25
  • ($recurring_event->period === 'week') do not use === use == Commented Feb 2, 2013 at 7:29
  • 1
    please post table structure and temp data; also the sample result you would like to obtain Commented Feb 2, 2013 at 7:37

2 Answers 2

Reset to default
12

A word of advice.

Although designing a database to store the 'description' of the repetition pattern is a very clean approach from a design point-of-view, you may get a lot of problems down the way.

I've done a project with a similar approach a while ago (I will look up the database design and add that to my answer) and, although I was able to reproduce the exact date/times of the recurring events, you will run into problems in the following situations; most originate from this:

the recurring events describe the repetition pattern, so the actual (individual) events are no physical records in your database

  1. If the customer decides to add a new event, how will you check if it overlaps with any existing event? You'll have to calculate all 'events', based on the repetition pattern.
  2. If the customer decides that the scheduled time for an event needs to be changed, how will you have this change apply to all future events and not for events that are in the past (you'll have to duplicate the original event, modify its end-date, and set the duplicated event with a new start-date)
  3. If the customer decides he wants to remove a single day from the repetition pattern (e.g. a single event has ben canceled), you will also have to split the original event into two separate repetitions, or have a 'canceled/blocked' dates/times table
  4. If people need to 'book' for specific events, you won't be able to attach them to a 'real' event-record, because the individual events because they are not physically present in the database. e.g. to check if a single event can be re-scheduled or canceled, you'll need to do this from code as the database cannot make use of foreign-key constraints to automatically update related reservations
  5. Regarding performance; because individual events are not physically stored, they will have to be calculated every time you want to show them. Consider having 1000 recurring events in the database and try to show a 'calendar' of week 23 two years from now. You'll have to analyze all recurring-events patterns and calculate all events that they produce!

All depends of course on the actual usage of your system, but I wanted to warn you for problems we've run into.

Here's the schema for the 'schedules' table (contains recurring events pattern);

CREATE TABLE IF NOT EXISTS `schedules` (
  `id`              int(11)     NOT NULL auto_increment,
  `date_start`      date        NOT NULL,
  `time_start`      time        NOT NULL,
  `time_end`        time        NOT NULL,
  `recur_until`     date        default NULL COMMENT 'end date when recurrence stops',
  `recur_freq`      varchar(30) default NULL COMMENT 'null, "secondly", "minutely", "hourly", "daily", "weekly", "monthly", "yearly"',
  `recur_interval`  smallint(5) unsigned default NULL COMMENT 'e.g. 1 for each day/week, 2 for every other day/week',
  `recur_byday`     smallint(5) unsigned default NULL COMMENT 'BITWISE; monday = 1, sunday = 64',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

How to circumvent the problems described

Fully describing a solution to these problems won't probably be suitable here, but here's some things to consider;

Storing a recurring event as described on itself is not bad practice. It perfectly describes when, and how often, an event should take place. However, the lack of physical records for the actual events is what causes the problem.

  1. When creating or modifying a recurring event, calculate all resulting events and store them as physical records. These records can be queried, 'reservations' can be attached to them and you'll be able to make use of database features, like foreign-key-constraints to handle them properly.
  2. When storing the individual events as described in 1., make sure you're keeping a reference to the 'schedule' that they belong to. If (for example) the customer wants to change the time of a recurring event, you'll be able to update all related (individual) events.
  3. Keep in mind that in situation 2, you'll probably only want to update future events, so the 'recurring event' will still need to be 'split' in two to achieve that. In which case 'future' events need to be attached to the new 'recurring event', old events stay attached to the existing 'recurring event'

invest time in your database/software design, properly investigate if the design will 'work' for the thing you're trying to achieve. Test it, try things and if they don't work, don't hesitate to 'throw it away', often it's easier to start from scratch than try to 'fix' things. A proper design will take time and may take several 'redesigns' to get it right, but it will save you time and money in the end.

Hope this helps, good luck!

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Wow, thank you for that information! I actually just started messing around with databases this week. I didn't build the current database I am working with, just trying to pull information from it. You did make some solid points though that I will keep in mind. Thanks!
@ftntravis You're welcome. I'll add some additional information to my post that may circumvent the problems described
I love it. Thanks! This is a great response, coming from a graphic design background I can definitely see the art of building a nice, readable database.
1 
        foreach ($events as $event) :

                // Store Event ID in a variable
                $masterID = $event->ID;

                echo '<div class="col-12">';

                echo '<strong>ID:</strong> ' . $event->ID . '<br /><strong>Event Name:</strong> ' . $event->EventName . '<br /><strong>Leader:</strong> ' . $event->CoordinatorName . '<br /><strong>Date:</strong> ' . date('l, F j',strtotime($event->RequestDateStart)) . '<br /><strong>Start Time:</strong> ' . date('g:i a',strtotime($event->RequestTimeStart));

                // CHECK IF RECURRING

                $recurring_events = $events_db->get_results(
                    "
                    SELECT gc_event_id, period, day
                    FROM gc_event_recurring
                    WHERE gc_event_id = '$masterID'
                    "
                );

                foreach ($recurring_events as $recurring_event) :
                if ($recurring_event->period == 'week') {
                $StartDate = strtotime($event->RequestDateStart);
                $EndDate = strtotime($event->RequestDateEnd);
$TotalDays = round(($EndDate-$StartDate)/(60*60*24*7));
                for($i = 0 ;$i<($TotalDays-1);$i++)
                {
                $StartDate += (60*60*24*7);
                echo '<div class="col-12">';

                echo '<strong>ID:</strong> ' . $event->ID . '<br /><strong>Event Name:</strong> ' . $event->EventName . '<br /><strong>Leader:</strong> ' . $event->CoordinatorName . '<br /><strong>Date:</strong> ' . date('l, F j',$StartDate) . '<br /><strong>Start Time:</strong> ' . date('g:i a',strtotime($event->RequestTimeStart));


                }


                }
                endforeach;

                echo '</div>';

            endforeach;

try this and tell me if it works

Improve this answer

22 Comments

$recurring_event->day is day of the week I believe.
but in the table its a count i think.see in the question.he can use that thing as count what ever it is
ahh almost! I thought it was working, but in checking them, some are going past the date they are supposed to end. The client enters in a start date and end date which I can use. I'm guessing these could fix the problem of going past?
whats the problem you facing and can you show me the out put you getting now?
you can see here: beta.revival.tv/test events that are supposed to end are still being populated with more dates.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.