Fullcalendar with PHP and CodeIgniter - Database Events - Part 2

In this next tutorial, we are going to set up our database to store our Calendar events, then display them using FullCalendar and PHP. This tutorial is a continuation of our FullCalendar with PHP and CodeIgniter series. View previous parts:

FullCalendar with PHP and CodeIgniter
FullCalendar with PHP and CodeIgniter - Database Events - Part 2 (current)
FullCalendar with PHP and CodeIgniter - Adding Events - Part 3
FullCalendar with PHP and CodeIgniter - Modifying Events - Part 4

First, we need to create the datatable table that stores the events:

CREATE TABLE `calendar_events` (
 `ID` int(11) NOT NULL,
 `title` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
 `start` datetime NOT NULL,
 `end` datetime NOT NULL,
 `description` varchar(1000) COLLATE utf8_unicode_ci NOT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Now we need to modify our Calendar_model.php to include the methods to grab the events we need. We'll also add in the methods needed to create, edit and delete events.

Modify Calendar_model.php:

public function get_events($start, $end)
{
    return $this->db->where("start >=", $start)->where("end <=", $end)->get("calendar_events");
}

public function add_event($data)
{
    $this->db->insert("calendar_events", $data);
}

public function get_event($id)
{
    return $this->db->where("ID", $id)->get("calendar_events");
}

public function update_event($id, $data)
{
    $this->db->where("ID", $id)->update("calendar_events", $data);
}

public function delete_event($id)
{
    $this->db->where("ID", $id)->delete("calendar_events");
}

These are just some simple methods and they should be pretty self-explanatory. We'll be using the get_events() method for this part of the tutorial. The $start and $end values are going to be obtained from our FullCalendar plugin that generates them for us.

Since we haven't created adding events yet, let's add in some dummy data using SQL:

INSERT INTO `calendar_events` (`ID`, `title`, `start`, `end`, `description`) VALUES
(1, 'Test Event', '2017-03-16 00:00:00', '2017-03-16 00:00:00', ''),
(2, 'New Event', '2017-03-23 00:00:00', '2017-03-23 00:00:00', '');

Note: Change the dates above so that they correspond to your current month and year. This will allow you to see the events on the first page.

Next up, we need to modify our FullCalendar script to now include an Event Source that grabs events via AJAX.

Modify application/views/calendar/index.php:

 $('#calendar').fullCalendar({
     eventSources: [
         {
             events: function(start, end, timezone, callback) {
                 $.ajax({
                 url: '<?php echo base_url() ?>calendar/get_events',
                 dataType: 'json',
                 data: {
                 // our hypothetical feed requires UNIX timestamps
                 start: start.unix(),
                 end: end.unix()
                 },
                 success: function(msg) {
                     var events = msg.events;
                     callback(events);
                 }
                 });
             }
         },
     ]
 });

If you've ever used jQuery's AJAX method before, this code will look quite similar to you.

We send the start and end dates to our script calendar/get_events/. This allows us to grab the events between those dates (this stops us from grabbing all the events in the calendar at once - saving resources!). The unix() function generates a timestamp based on the current date.

The JavaScript variables start and end are generated by FullCalendar. FullCalendar will generate the dates based on the view of the calendar. We are using the default monthly view, so the start and end dates correspond to the beginning and end of the month.

Our controller function, get_events(), is yet to be created but notice that the dataType it returns is set to JSON. This is how FullCalendar handles it's event data so we need to send back JSON encoded data to FullCalendar.

The success: function() might look a little confusing but the callback(events) part is just our way of sending our event data back to FullCalendar to process the events we give it.

Let's create our get_events() function next so you can see how we get the calendar events data and process it.

Modify application/controllers/Calendar.php:

 public function get_events()
 {
     // Our Start and End Dates
     $start = $this->input->get("start");
     $end = $this->input->get("end");

     $startdt = new DateTime('now'); // setup a local datetime
     $startdt->setTimestamp($start); // Set the date based on timestamp
     $start_format = $startdt->format('Y-m-d H:i:s');

     $enddt = new DateTime('now'); // setup a local datetime
     $enddt->setTimestamp($end); // Set the date based on timestamp
     $end_format = $enddt->format('Y-m-d H:i:s');

     $events = $this->calendar_model->get_events($start_format, $end_format);

     $data_events = array();

     foreach($events->result() as $r) {

         $data_events[] = array(
             "id" => $r->ID,
             "title" => $r->title,
             "description" => $r->description,
             "end" => $r->end,
             "start" => $r->start
         );
     }

     echo json_encode(array("events" => $data_events));
     exit();
 }

The first thing we do is grab the $start and $end dates. We use PHP's Datetime function to convert the timestamps into the dates we need and setting the correct format. The format we are storing our events in is Y-m-d H:i:s. This is how PHP processes dates, you can read more about it here: http://php.net/manual/en/function.date.php

Now with all that new code added in, go back to your Calendar and you should now see the events being displayed to you. The events are coming directly from your database and so all that's left to do is implement adding, editing and deleting events directly from the Calendar.