WordPress Custom Event Calendar – Displaying our events

After the last day, all we had left to do is to get the events that we stored in the database and display them on the calendar. Well, maybe this is easier said than done. In the video tutorial, we will create a dedicated class just to get the events out of the database and prep them to be displayed on the calendar.

Considerations

There really is four types of events that we need think of:

• An event that starts and ends on the same day
• An event that starts and ends on different days but happens in the currently displayed month
• An event that starts in the previous month and ends in the currently displayed month
• An event that starts in the currently displayed month that continues into the next month

These are fourth unique cases of events that we will use for our calendar and these will determine how we get the data and also organize the data.

Our Query

As you will see in the video tutorial, we need to create a complex MySQL query that will get all the rows that meet the criteria above. This will involve a join between “our_events” table and the “posts” table. The WHERE clause takes into consideration the 4 cases from above. Here is the our query:

"SELECT e.start_date, e.end_date, e.start_time, e.end_time, p.ID, p.post_title, p.post_name, p.post_excerpt
      FROM {$wpdb->prefix}our_events AS e
      JOIN {$wpdb->prefix}posts AS p ON e.post_id = p.ID
      WHERE e.event_visible = 1
      AND p.post_status = 'publish'
      AND (e.start_date LIKE '{$year}-{$month}%' OR e.end_date LIKE '{$year}-{$month}%')
      ORDER BY e.start_time, e.start_date"

Looking at this query, the part that ensures we meet all the criteria we set above is this section:

AND (e.start_date LIKE '{$year}-{$month}%' OR e.end_date LIKE '{$year}-{$month}%')

The logic is: if we have any event that starts in this month, obviously we need get that row, but we also need to get any rows for events that end in this month. These are the events that started in the previous month and continue into the present month.

Notes on Organizing the Data

The data that we get could be as little as one or two rows but each of these could appear on the calendar multiple times if each happen over several days. This means we need create an array of $events that represents each day on the calendar which an event happens. The $keys for the $events array will be the day in which the event happens and the $value will be the $row that was found in the database. So if we have an event that starts on the 1st and ends on the 2nd we will need to insert this row two times into our array with the keys 1 and 2.

To make this determination, we need data about the event such as the start date, end date, start month, and end month. Also to help us make this determination, we will need information about the current month we are displaying: what is the previous month, what is the next month, and how many days does this month have. If you look at the code, you will see our four cases with into our calendar_data function and how we use the data about the event and month to insert the event into the $events array.

Notes on the Calendar Content

Now, that we have the data organized we need to put the data in a usable format. The data is currently in a multi dimensional array which we will need to convert to content. To do this we will loop the array on each day and create a un-order list for day. The unordered list will hold list items created from the rows of data we got from the data base.

The files

Here is the plugin for download so far

Whats left?

The calendar might be functioning but sure is not the prettiest thing, next time we will look at styling our calendar for your site.