Blog

Where we discuss our work, thoughts, and process

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

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

Comments

's avatar
Lyubov Berezina

Thank you for this awesome tutorial! This is exactly what I need for one of my projects.

However I need to ask a question as I have not completely understood something.

Do I need to change anything if I'm using MySQL instead of SQLite?
Looks like I need to change the way the PDO connects to the DB, f.e.:
$db = new PDO('mysql:host=localhost;dbname=testdb');

is it ok?

Do I have to change anything in the scripts themselves to keep the functionality?

Thanks once more for this project!

(Please excuse my poor English)

's avatar
Josh Lockhart NMC team member

@Lyubov Berezina Technically, you only need to change the DSN in the PDO object like you said. However, you may also want to change the database schema so that the title and slug fields are VARCHAR(255) instead of TEXT. Keep in mind that the time column is an INTEGER type in the schema above... if you change this to DATETIME in MySQL, you will have to update the queries to reflect this change.

's avatar
Lyubov Berezina

Josh, thank you very much! I've managed to make it work on my personal machine with XAMPP installed. Works like charm :)

I've got two issues though:

1. I've got an authorization error and had to create an 'ODBC'@'localhost' user with no password in order to allow PDO connection. It doesn't look like quite a secure setup being implemented on the production machine thus I'll have to eplore it further. I guess this is somehow a question of the correct PDO connection.

2. My day events results look like this:
* :24 - Hockey Game
* :05 - Lacrosse Game
(The "Hour" part is cropped). Any ideas why it is so? I would appreciate any help.

Thank you again :)

's avatar
Lyubov Berezina

So the correct MySQL PDO connect should be smth like this:

$db = new PDO('mysql:host=localhost;dbname=events', 'user', 'password');

thought it might be useful for anyone :)

's avatar
Oleg Blaschuk

Is there any chance to assign a special class to the link of the day when there're events? I wish I could style those links with another background image so the visitors could see when there's an event (right now I need to specify somewhere near the calendar on which days they should click).
Thanks a lot!

's avatar
Josh Lockhart NMC team member

@Oleg You can update calendar.php on line 52 to add a class attribute to each calendar day hyperlink.

's avatar
Oleg Blaschuk

@Josh, then it will apply this class to all the day links from 1st to 31st, right? And what I mean is a class to apply only to those days on which there are any events (like on http://keepthebellringing.org/) So if there're any events on , say, 15th, then this day link will have a special class to style this day with f.e. a background image or smth like this.

's avatar
Shane

Is there a way that i can get it to pull the current days events if there are any onload. Id like show events for the current day when a user hits the calendar page rather than waiting for them to select a day?

Thanks for any input you can provide.

's avatar
Jacob

Hi,

Thanks for the great tutorial! I completed it from scratch, but found when I clicked on the date, nothing happened. So I used the provided files, thinking I must have done something wrong, but the same thing happens. I have changed the URL in the calendar JS file.

Do I need to do anything extra on my end to set up SQLite?

Thanks, Jacob.

's avatar
Wietse

I have the seem problem as Jacob.

's avatar
Chris

Fantastic tutorial!
It it possible to add left and right navigation to cycle through each month?

's avatar
sean

can i get a little help? im a newbie and i dont understand the time integer at all. how do you get august 16, 2009 out of 1250463600? Thanks

's avatar
Danny

Is there a way to switch from PDO to just plan MySQL? as this isnt work for me.

's avatar
Will

Hiya I still can't get my events to appear, like Jacob???

Any ideas????

Thanks

's avatar
Will

Opps sorry forgot to ask, is it hard to get the date with an event to change color in the css somehow??

I saw on the Salvation Army website that the days with events go red - any direction???

Thanks a million

's avatar
Noli

Thanks for this post.

It gives me an idea how to use sqlite and spatialite as server for mobile gis i.e. openlayers mobile.

's avatar
Jez
I like this a lot, a few people requesting how to show events visually on the calendar so thought i'd share my solution: modify the calendar.php script to query the db for all timestamps and then convert these to the equivalent timestamp at 00:00 on that date inputting the results into an array:

$db = new PDO('mysql:host=localhost;dbname=calendar','root','');

$stmt = $db->prepare('SELECT time FROM events');
$stmt->execute();
$rawTimeStamps = $stmt->fetchAll(PDO::FETCH_ASSOC);
$cleanDateArray = array();

foreach ($rawTimeStamps as $t) {
$rawDate = $t['time'];
$rawDate = getdate($rawDate);
$cleanDate = mktime(0,0,0,$rawDate['mon'],$rawDate['mday'],$rawDate['year']);
$cleanDataArray[] = $cleanDate;
}

These timestamps will now match the unique ids given to each element in the calendar so you can add an additional id to these elements by checking them against the array you created and adding an id attribute if a match is found:

Modify line 67-68:

if (in_array($timestamp, $cleanDataArray)) {
$calendar.= ''.$list_day.'';
} else {
$calendar.= ''.$list_day.'';
}

then just modify the css in frontend.php to style the days with events. something like #hasEvent {background: red;} should do the trick.
's avatar
rothAttack
for MySQL users, here's my working code:

<?php<br /> $db = new PDO('mysql:host=HOST;dbname=DATABASE NAME','USERNAME','PASSWORD');

if (!is_object($db)) trigger_error("Failed to connect to 'database' "
." | Error = $db", E_USER_ERROR);

//Set date from GET timestamp parameter
if( !isset($_GET['timestamp']) )
die('You must provide a timestamp');
else
$date = getdate($_GET['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']);

//Fetch events from database as associative array
$stmt = $db->prepare('SELECT * 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);

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

?>

the downloadable backend.php has an error in the strftime("%l: %M %p", $event['time']); %l was typed instead of %I so the hour didn't show.
's avatar
Alice
@rothAttack Its now showing any events. I'm getting error on this line. SELECT * from events WHERE time BETWEEN ? AND ? ORDER BY time ASC. And I'm using your mysql version. thanks
's avatar
Tomasz
The live demo doesn't work: the jquery-calendar/demo/frontend.php returns Content-Type: application/octet-stream and shows PHP code (is not processed by PHP).
's avatar
Scotty
I thought the whole idea of using Javascript/AJAX with PHP on the server side (with a database of events) was to eliminate the annoying screen refresh everytime you change months or such. Wouldn't it be much better to simply do popups to show events, rather than have to "GOTO" a calendar page to view them? I think this could be much better. Those are the problems I'm trying to solve, yours falls short.
's avatar
Marcus
How do I edit the script to show monday first?
's avatar
Jiri
Hi, I have the same trouble as Marcus. We are both little noobs, but somebody could help us :) Thanks a lot...
's avatar
akplebani
For people who uses mysql and wants the calendar to display the events title inside the date cell, just replace the calendar.php to this:

function draw_calendar($month,$year){



/* draw table */
$calendar = '';

/* table headings */
$headings = array('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday');
$calendar.= ''.implode('',$headings).'';

/* days and weeks vars now ... */
$running_day = date('w',mktime(0,0,0,$month,1,$year));
$days_in_month = date('t',mktime(0,0,0,$month,1,$year));
$days_in_this_week = 1;
$day_counter = 0;
$dates_array = array();

/* row for week one */
$calendar.= '';

/* print "blank" days until the first of the current week */
for($x = 0; $x < $running_day; $x ):
$calendar.= ' ';
$days_in_this_week ;
endfor;

$db = new PDO('mysql:host=localhost;dbname=calendar','root','');

$stmt = $db->prepare('SELECT time, title FROM events');
$stmt->execute();

$rawTimeStamps = $stmt->fetchAll(PDO::FETCH_ASSOC);
$cleanDateArray = array();

foreach ($rawTimeStamps as $t) {
$rawDate = $t['time'];
$rawDate = getdate($rawDate);
$cleanDate = mktime(0,0,0,$rawDate['mon'],$rawDate['mday'],$rawDate['year']);
$cleanDataArray[] = $cleanDate;
}

/* keep going with days.... */
for($list_day = 1; $list_day <= $days_in_month; $list_day ):<br />
$calendar.= '';

$timestamp = mktime(0,0,0,$month,$list_day,$year);

if (in_array($timestamp, $cleanDataArray)) {

/* embromation */
$date = getdate($timestamp);
$time_start = mktime(0,0,0,$date['mon'],$date['mday'],$date['year']);
$time_end = mktime(23,59,59,$date['mon'],$date['mday'],$date['year']);
$stmt = $db->prepare('SELECT title FROM events WHERE time BETWEEN ? AND ?');
$stmt->bindParam(1,$time_start,PDO::PARAM_INT);
$stmt->bindParam(2,$time_end,PDO::PARAM_INT);
$stmt->execute();
$events = $stmt->fetch(PDO::FETCH_ASSOC);

$calendar.= ''.$list_day.''.$events["title"].'';
} else {
$calendar.= ''.$list_day.'';
}

$calendar.= '';
if($running_day == 6):
$calendar.= '';
if(($day_counter 1) != $days_in_month):
$calendar.= '';
endif;
$running_day = -1;
$days_in_this_week = 0;
endif;
$days_in_this_week ; $running_day ; $day_counter ;
endfor;

/* finish the rest of the days in the week */
if($days_in_this_week < 8):
for($x = 1; $x <= (8 - $days_in_this_week); $x ):<br /> $calendar.= ' ';
endfor;
endif;

/* final row */
$calendar.= '';

/* end the table */
$calendar.= '';

/* all done, return result */
return $calendar;
}
?>
's avatar
Salan
How Can I add new events... I don't understand how can I connect to mySql database. Could you with this script?
's avatar
Pete
Thanks for the great tutorial, best I found, I'm going to work on adding a editable calendar for people to use possibly. Is this open liscense to use on my site?
's avatar
aditi
Please help me. I want jquery plugin calendar to use in my php project.

Leave a comment

Real Time Web Analytics