Create a jQuery calendar with AJAX, PHP, and MySQL

Our Goal

This tutorial demonstrates how to build an interactive calendar that displays event data fetched from a JSON API. First, we will setup a MySQL database to store event data. Next, we will build a small PHP application with the Slim Framework to provide event data with a JSON API. Finally, we will create the front-end calendar with HTML and Javascript using the popular FullCalendar jQuery plugin.

The Database

We need a place to store event data. We can store data in a relational database like MySQL or PostgreSQL, in a document database like MongoDB, in a flat XML file, or anywhere else accessible to the PHP programming language. We will use a MySQL database for this tutorial.

Create the Database

Create a new database named calendar. You can do this with a web application like PHPMyAdmin. If you use the command line, log into the mysql server as a user with CREATE privileges and run this command:

CREATE DATABASE calendar;

Load the Schema

We need to define the schema for our calendar database. For the sake of time, I've included the complete schema here. You can execute this SQL with a web application like PHPMyAdmin, or pipe this SQL into the mysql command line program.

CREATE TABLE `calendar`.`events` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255),
    `description` TEXT,
    `location` TEXT,
    `contact` TEXT,
    `url` VARCHAR(255),
    `start` DATETIME,
    `end` DATETIME,
    PRIMARY KEY (`id`),
    INDEX idx_start (`start`),
    INDEX idx_end (`end`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8 COLLATE=UTF8_UNICODE_CI;

Load Sample Data

We want sample data to play with. Load this SQL into your database.

INSERT INTO `calendar`.`events` (
    `title`,
    `description`,
    `location`,
    `contact`,
    `url`,
    `start`,
    `end`
) VALUES (
    'Test Event 1',
    'This is the first test event',
    'Acme Hall, Room 101',
    'John Smith',
    'http://www.example.com',
    CONCAT(DATE_FORMAT(NOW(),'%Y-%m'), '-03 13:00:00'),
    CONCAT(DATE_FORMAT(NOW(),'%Y-%m'), '-03 14:00:00')
), (
    'Test Event 2',
    'This is the second test event',
    'Venable Hall, Room 101',
    'Jane Smith',
    'http://www.example.com',
    CONCAT(DATE_FORMAT(NOW(),'%Y-%m'), '-08 09:00:00'),
    CONCAT(DATE_FORMAT(NOW(),'%Y-%m'), '-08 10:45:00')
), (
    'Test Event 3',
    'This is the third test event',
    'Sitterson Hall, Room 200',
    'Jane Smith',
    'http://www.example.com',
    CONCAT(DATE_FORMAT(NOW(),'%Y-%m'), '-17 15:00:00'),
    CONCAT(DATE_FORMAT(NOW(),'%Y-%m'), '-17 16:30:00') 
); 

The API

Now that we have our MySQL database, schema, and test data, let's build a PHP application with the Slim Framework. Our application will provide event data with a JSON API.

Setup the Slim Framework Project

First, let's create a new Slim Framework project. We'll use Composer to install the Slim Framework library, so make sure you have Composer installed on your machine. Create a new directory to contain our calendar API application; I'll refer to this as the project root directory. This directory should contain the following subdirectories and files:

composer.json
templates/
public/
    .htaccess
    index.php

What do these files mean? The composer.json file is a JSON manifest file used by Composer that lists the PHP components and dependencies used to build our application. The templates/ directory will contain our HTML template. The public/.htaccess file is used by the Apache web server to rewrite all HTTP requests to the index.php file. The index.php file will contain the Slim Framework application code.

I will leave the web server configuration up to you. Just be sure your virtual host's document root points to the project's public/ directory. You'll also want to use URL rewriting to send all HTTP requests to the public/index.php file. If you are using Apache, make sure the public/.htaccess file looks like this:

RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-f
RewriteRule ^ index.php [QSA,L]

If you are using nginx, update your server configuration's location block with this code:

try_files $uri $uri/ /index.php$is_args$args;

Install Composer Dependencies

First, let's install our project dependencies with Composer. For this tutorial, we only need the Slim Framework. Open composer.json in your text editor and ensure its content is:

{
    "require": {
        "slim/slim": "2.*"
    }
}

Next, run this command from your project root directory:

composer install

You'll see some output as Composer downloads the Slim Framework library into a new vendors/ subdirectory beneath your project root directory.

Create the Slim Framework Application

Now that our project dependencies are installed, let's build the Slim Framework application. Open public/index.php in your text editor and add this PHP code:

<?php
require '../vendor/autoload.php';

$app = new \Slim\Slim(array(
    'templates.path' => '../templates'
));

$app->get('/api', function () use ($app) {
    // Fetch and display events as JSON
}); 

$app->run();

What does this code do? Let's walk through the code line by line.

require '../vendor/autoload.php';

This code instructs Composer to autoload our project dependencies. Remember the dependencies we listed above in our composer.json file? Those dependencies will be autoloaded by Composer on-demand as we use them in our application.

$app = new \Slim\Slim(array(
    'templates.path' => '../templates'
));

This code instantiates a new Slim Framework application. It also defines where our application templates are stored relative to the public/index.php file.

$app->get('/api', function () use ($app) {
     // Fetch and display events
});

This code defines a new application route and an associated callback to be invoked when the application receives a GET /api HTTP/1.1 request. We'll build out this route in the next section.

$app->run();

Finally, this code runs the Slim Framework application and routes the current HTTP request to the appropriate application route.

Query for Events

Now that we have our initial Slim Framework application, we need to build out the /api route so that it fetches the correct calendar events and returns them as JSON.

First, let's step back and think about how our front-end calendar will work. Our front-end calendar will use the FullCalendar jQuery plugin to display an interactive calendar that paginates by month. On each pagination (when the calendar month changes), the front-end calendar will send an AJAX request to our Slim Framework application with two GET query parameters: start and end. The query parameters' values will be UNIX timestamps that define the range of events to return (send me all events that start on or after start and end before end). With this in mind, let's update our Slim Framework application's /api route with this code:

$app->get('/api', function () use ($app) {
    // Get the start and end timestamps from request query parameters
    $startTimestamp = $app->request->get('start');
    $endTimestamp = $app->request->get('end');

    try {
        // Open database connection
        $conn = new \PDO('mysql:host=127.0.0.1;dbname=calendar', 'YOUR_USERNAME', 'YOUR_PASSWORD');

        // Query database for events in range
        $stmt = $conn->prepare('SELECT * FROM events WHERE start >= FROM_UNIXTIME(:start) AND end < FROM_UNIXTIME(:end) ORDER BY start ASC');
        $stmt->bindParam(':start', $startTimestamp, \PDO::PARAM_INT);
        $stmt->bindParam(':end', $endTimestamp, \PDO::PARAM_INT);
        $stmt->execute();

        // Fetch query results 
        $results = $stmt->fetchAll(\PDO::FETCH_ASSOC);

        // Return query results as JSON
        echo json_encode($results);
    } catch (\PDOException $e) {
        $app->halt(500, $e->getMessage());
   }
});

Be sure you change the database username and password shown above with your own username and password when instantiating the PDO database connection.

The Calendar

Now we'll build out the front-end calendar and bring together everything we've built so far. First, let's add a new route to our Slim Framework application. Add this code to public/index.php immediately before the last $app->run(); line of code.

$app->get('/', function () use ($app) {
    $app->render('calendar.html');
});

This code defines a new route that will draw the calendar HTML page when the Slim Framework application receives a GET / HTTP/1.1 request. Notice that we render a template named "calendar.html" in the route callback. Create this template at templates/calendar.html with this HTML content:

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="utf-8"/>
        <title>My Calendar</title>
        <link rel="stylesheet" href="//cdnjs.cloudflare.com/ajax/libs/fullcalendar/1.6.4/fullcalendar.css"/>
    </head>
    <body>
        <!-- We will attach the calendar to this element -->
        <div id="calendar"></div>

        <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
        <script src="//cdnjs.cloudflare.com/ajax/libs/fullcalendar/1.6.4/fullcalendar.min.js"></script>
        <script>
            (function ($) { 
                $('#calendar').fullCalendar({
                    events: '/api',
                    eventDataTransform: function (rawEventData) {
                        return {
                            id: rawEventData.id,
                            title: rawEventData.title,
                            start: rawEventData.start,
                            end: rawEventData.end,
                            url: rawEventData.url
                        };
                    }
                });
            })(jQuery);
        </script>
    </body>
</html>

In the last script element, we apply the jQuery FullCalendar plugin to the div#calendar element. The fullCalendar() method accepts an object argument with an events property and an eventDataTransform property (and many other properties). The events property defines the absolute URI path (without query parameters) that will return JSON event data (this is the API we built earlier). The eventDataTransform property transforms each raw event returned from the API into the proper event schema expected by FullCalendar. The eventDataTransform property is unnecessary in this tutorial because our MySQL database schema's column names match the FullCalendar event schema. Should your database schema NOT match FullCalendar's event schema, the eventDataTransform property is how you can transform your raw event data into the FullCalendar format. For more information, read the jQuery FullCalendar documentation.

That's it. View your application in a web browser and you should have an interactive calendar with event data fetched from your Slim Framework API.