Create a jQuery calendar with AJAX, PHP, and a remote data source

Update! We have a newer version of this post that has a much better setup here: Create a jQuery calendar with AJAX, PHP, and MySQL

This tutorial demonstrates how to create a jQuery calendar with PHP and Javascript using a remote data source. This is an extension of my previous article about the jQuery calendar I developed for the Salvation Army of Wake County website.

The final calendar in this tutorial contains a hyperlink for each day of the month. When a hyperlink is clicked, a list of events for that day is requested from a remote script, cached client-side, and displayed on the page.

Download project ZIP file

Tutorial Requirements

You should be comfortable with HTML, jQuery, and PHP. However, I explain the code step-by-step to show how all of the pieces fit together. In this tutorial I use PHP and a SQLite database; however, you may use any database for which there is a PHP PDO database driver.

Tutorial Overview

There are several pieces that work together in this tutorial.

• /frontend.php

  This is the epicenter of the tutorial. You will view this file in a web browser. This file provides the calendar's HTML markup, includes the jQuery calendar script, and displays the calendar and its events.

• /backend.php

  This connects frontend.php and the database. This PHP script is called remotely from frontend.php and accepts a GET parameter called "timestamp". This script queries the database for all events that occur on the same day as the timestamp parameter. The events returned from the database are output in an HTML unordered list.

• /scripts/calendar.js

  This Javascript file is included into frontend.php and uses jQuery to attach onclick event handlers to each hyperlink in the calendar table. Each event handler will send an HTTP request to backend.php along with a unique "timestamp" GET parameter. The HTTP response from each HTTP request is cached client-side to avoid duplicate HTTP requests. This script also inserts the HTML returned from backend.php into the document object model of frontend.php.

• /includes/calendar.php

  This PHP script contains a function written by David Walsh that creates the calendar's initial HTML markup. I made several edits to David's original function for this tutorial. You can read more about my edits in this file's header. This file is included into and called from frontend.php.

• /db/calendar.db

  This is the SQLite database file that contains a tuple for each calendar event. This file is referenced from backend.php with a PDO database driver.

Step 1: Setup the database

First, create and prepare the database using the schema shown below. You need to create the database and load it with the sample data before moving on with this tutorial. If you use SQLite, create a database file called calendar.db in the "db" directory within the project directory. If you use the ZIP download for this project, the SQLite database has already been created for you.

CREATE TABLE events (
	id INTEGER PRIMARY KEY,
	title TEXT,
	slug TEXT,
	time INTEGER
);
INSERT INTO events (title,slug,time) VALUES ('Soccer Game','soccer-game',1250256000);
INSERT INTO events (title,slug,time) VALUES ('Basketball Game','basketball-game',1250361000);
INSERT INTO events (title,slug,time) VALUES ('Hockey Game','hockey-game',1250367840);
INSERT INTO events (title,slug,time) VALUES ('Lacrosse Game','lacrosse-game',1250438700);
INSERT INTO events (title,slug,time) VALUES ('Ping Pong Game','ping-pong-game',1250463600);

`id` is an auto-incrementing integer primary key
`slug` is a URL-friendly text representation of the event title
`time` is an integer UNIX timestamp

Next, I establish a PHP PDO database connection in the backend.php file. Open backend.php in a plain-text editor and edit line 10 to establish a connection to the database. If you use the ZIP download for this project, the database connection has already been created for you. Visit the PHP PDO website to learn more about creating PDO database connections.

$db = new PDO('sqlite:db/calendar.db');

Step 2: Create the calendar markup

Next, I create the calendar's HTML markup with PHP. I see no point in re-inventing the wheel, so I use a great PHP calendar generation script by David Walsh. I make several changes to David's original function. You can read about my changes in the header of /includes/calendar.php.

The calendar is displayed in frontend.php. First, I include the calendar function into frontend.php on line 1.

<?php include('includes/calendar.php'); ?>

Next, I call the draw_calendar() method in frontend.php on line 16. This outputs the entire calendar HTML markup.

<h1>August 2009</h1>
<?php echo draw_calendar(8,2009); ?>
<div id="calendar-events"></div>

Step 3: Setup the backend PHP script

The backend.php script connects frontend.php to the database. The backend.php script receives an XMLHttpRequest from frontend.php that looks like this:

backend.php?timestamp=1249966800

Next, the backend.php script asks the database for all events that occur on the same day as the timestamp. Let's walk through the code step by step.

$db = new PDO('sqlite:db/calendar.db');

First, I establish a PDO connection to the SQLite database. If you do not use the database provided in the ZIP download for this project, edit line 10 to establish a PDO connection to your own database.

//Set date from GET timestamp parameter
if( !isset($_GET['timestamp']) )
	die('You must provide a timestamp');
else
	$date = getdate($_GET['timestamp']);

Next, I verify that a timestamp is specified. If a timestamp is not specified, I return an error message to frontend.php. Else, I extract an array of date and time information based on the provided timestamp.

//Define start and end timestamps for the requested day
$time_start = mktime(0,0,0,$date['mon'],$date['mday'],$date['year']);
$time_end = mktime(23,59,59,$date['mon'],$date['mday'],$date['year']);

Using the timestamp provided in the previous step, I extrapolate two additional timestamps: one for the beginning of the same day and one for the end of the same day.

//Fetch events from database as associative array
$stmt = $db->prepare('SELECT id, title, slug, time FROM events WHERE time BETWEEN ? AND ? ORDER BY time ASC');
$stmt->bindParam(1,$time_start,PDO::PARAM_INT);
$stmt->bindParam(2,$time_end,PDO::PARAM_INT);
$stmt->execute();
$events = $stmt->fetchAll(PDO::FETCH_ASSOC);	

Next, I create a PDO statement and execute a SQL query against the database. This SQL query returns all events that start on the same day as the specified timestamp. Notice how I use the two extrapolated timestamps as boundaries for the BETWEEN clause. The returned events are collected into an associative array referenced by the $events variable.

//Send output
if( !count($events) )
	exit('<p>No events were found</p>');
$output = '<ul>';
foreach( $events as $event )
	$output .= '<li>'.strftime("%l:%M %p",$event['time']).' - '.$event['title'].'</li>';
$output .= '</ul>';
exit($output);

Finally, I output an HTML unordered list containing all of the events returned by the SQL query.

Step 4: Add XMLHttpRequest behavior with jQuery

In this last step of the tutorial, I add behavior to frontend.php with jQuery. The Javascript attaches onclick event handlers to each hyperlink in the calendar table. When a hyperlink is clicked, an XMLHttpRequest is sent to backend.php with a unique timestamp parameter. When a response is received from backend.php, the HTML returned by backend.php is inserted into the document object model (DOM) to reveal the events for the clicked day.

You must provide the absolute or relative path to the backend.php on line 4 of /scripts/calendar.js. This path will be different than the path shown below.

jQuery(document).ready(function($){

	//CHANGE ME!!! Define the relative or absolute path to your backend script
	var remoteUrl = '/~joshlockhart/calendar/backend.php';

	//Initialize the HTTP cache
	var remoteCache = new Array();

	//For each link in the calendar...
	$('table.calendar a').each(function(i,item){

		//Unique ID for the link
		var linkId = item.id;

		//Unique URL for the link
		var linkUrl = remoteUrl+'?timestamp='+linkId;

		//Attach onclick event handler
		$(this).click(function(){			
			var calendarEvents = $('#calendar-events');			
			calendarEvents.slideUp('fast',function(){			
				if( remoteCache[linkId] != undefined ) { 
					calendarEvents.html(remoteCache[linkId]);
					calendarEvents.slideDown('fast');
				} else {
					calendarEvents.load(linkUrl,function(){
						remoteCache[linkId] = calendarEvents.html();
						calendarEvents.slideDown('fast');
					});
				}
			});
			return false;
		});

	});

});	

The jQuery script caches all responses received from backend.php. If a calendar link is clicked more than once, the list of events for the given day is retrieved from cache if available instead of sending a duplicate HTTP request. This saves bandwidth and significantly increases the responsiveness of the jQuery calendar.

This script is saved in the external Javascript file /scripts/calendar.js. I include this external Javascript on line 12 of frontend.php.

Step 5: View the finished jQuery calendar

View frontend.php in a web browser and click on the 14th, 15th, or 16th. You should see a list of events appear for each day. Feel free to add more events to your database and watch them appear on the calendar. Post questions or comments using the form below. Enjoy!

Download project ZIP file