How to build an events agenda with SQL

In my opinion, one of the coolest things of the site I’ve built to Paulo gazela is the event agenda on the navigation bar.

Of course there are many calendar programs around to add to your site and it’s also possible to integrate google calendar or a similar tool, but all Paulo needed was a way to show the events he was going to be in for the folowing, say, 15 days, and an easy interface to add them.

The solution is very simple. We’ve built a database with the date, time and details of the events and we created a SQL query to get all the events 15 days from the current date.

A single SQL query like the one below can handle everything

SELECT *
FROM event, place
WHERE event.place=place.id
  AND CURDATE() < = event.thedate
  AND DATE_ADD(CURDATE(),INTERVAL 15 DAY) >= event.thedate
ORDER BY
  event.thedate ASC,
  event.thetime ASC;

If you want to understand better what this is, to build the database tables and get some more detail, read on.

I will assume you have the basic knowledge about connecting to the database system in your server, but I’ll try go through all steps that are common for every system.

This is an ad hoc tutorial and I won’t explain a lot o SQL syntax, but if you want to really learn SQL in a generic sense, I encourage you to start learning here

First of all, you need to create the database and the tables. The SQL command to create the event table is similar to this

CREATE TABLE event (
  id INT UNIQUE NOT NULL AUTO_INCREMENT,
  thedate DATE,
  thetime TIME,
  place INT,
  name VARCHAR(40)
);

The reason why eventplace is an INT field is because the place in itself has a lot of information and good practice on databases (normal forms) require that places have to be stored on its own table.

CREATE TABLE place (
  id INT UNIQUE NOT NULL AUTO_INCREMENT,www.
  name VARCHAR(40),
  address VARCHAR(100),
  website VARCHAR(50),
  phone VARCHAR(20)
);

Now we have the database tables set up!

First thing to do is to create a place where one or more events will happen. The SQL to do that is something like:

INSERT INTO place (name, address, website, phone)
VALUES ('A cool place', 'Cool St. 42','www.coolplace.im','555-1234');

The id field is going to fill automatically with a unique number and the value will be set up to the next free number. As this is our first insertion, “Cool Place” register will have id 1, but we are not suppose to know this by heart, stick with me and I’ll show you how to do it. For now, let’s just assume we know that.

So, let’s insert an event to happen on a couple of days on Cool Place, which has id=1.

INSERT INTO event (thedate, thetime, place, name)
VALUES ('2007-04-05', '14:30:00', 1 ,'A Cool Event');

And this event will happen again next month.

INSERT INTO event (thedate, thetime, place, name)
VALUES ('2007-05-27', '14:30:00', 1 ,'A Cool Event');

Note that the third parameter, place, does not come into quotes, because is a numerical value rather than a string.

Ok, we’re set and done with the database, by repeating this procedures, we can insert as many places as we want, and events associated with these places. What we want to do now, is to list the events that will happen in the next 15 days.

Before we do that, let’s just see how to retrieve the information. We should issue a command to select the entries we want in the database.

The simplest query is something similar to

SELECT *
FROM event;

This means, select all fields on the table ‘events’, the asterisk stands for ‘all fields’. A somehow more complex query would be like this

But now, we want to know the name and address of the place where the event will occur, instead of the event id. So we want something like this:

SELECT
  thedate as date,
  thetime as time,
  event.name as e_name,
  place.name as p_name,
  address as addr
FROM event, place
WHERE event.place=place.id;

To show the events that will happen within 15 days from now, we must select all entries where the date is greater than today and lesser than today + 15 days.

SELECT
  event.thedate as d,
  event.thetime as t,
  event.name as e_name,
  place.name as p_name,
  place.address as addr
FROM event, place
WHERE event.place=place.id
  AND CURDATE() < = event.thedate
  AND DATE_ADD(CURDATE(),INTERVAL 15 DAY) >= event.thedate;

And how about to order entries by date and time? First order the entries by the date, adn then, events that have the same date will be ordered by the time.

SELECT
  event.thedate as d,
  event.thetime as t,
  event.name as e_name,
  place.name as p_name,
  place.address as addr
FROM event, place
WHERE event.place=place.id
  AND CURDATE() < = event.thedate
  AND DATE_ADD(CURDATE(),INTERVAL 15 DAY) >= event.thedate
ORDER BY
  event.thedate ASC,
  event.thetime ASC;

Ok, that’s it! This is the SQL we need.

A PHP implementation of the query would be something like this

<?php
$con = mysql_connect("mysql.myserver.com","user","password");
if (!$con)
{
  die('Could not connect: ' . mysql_error());
}

$query = "
    SELECT
      event.thedate as d,
      event.thetime as t,
      event.name as e_name,
      place.name as p_name,
      place.address as addr
    FROM event, place
    WHERE event.place=place.id
      AND CURDATE() < = event.thedate
      AND DATE_ADD(CURDATE(),INTERVAL 15 DAY) >= event.thedate
    ORDER BY
      event.thedate ASC,
      event.thetime ASC";

echo "<table<";
$result = mysql_query($query);
while($row = mysql_fetch_array($result))
{
    echo "<tr>";
    echo "<td>" . $row['date'] . "</td>";
    echo "<td>" . $row['time'] . "</td>";
    echo "<td>" . $row['name'] . "</td>";
    echo "<td>" . $row['place'] . "</td>";
    echo "<td>" . $row['addr'] . "</td>";
    echo "<td>" . $row['website'] . "</td>";
    echo "<td>" . $row['phone'] . "</td>";
    echo "<td>" . $row['descr'] . "</td>";
    echo "</tr>";
}
echo "</table<";
?>