PHP Tennis Court Reservation (HTML5/JavaScript Frontend, MySQL Database)

Features

• Multiple tennis courts, grouped by location (indoor, outdoor)

• Public interface for visitors: see available time slots, book a time slot

• Admin interface for staff: see and manage all reservations

• Showing different prices depending on time of day

• Prevents creating reservations in the past

• PHP source code

• MySQL database

License

Licensed for testing and evaluation purposes. Please see the license agreement included in the sample project. You can use the source code of the tutorial if you are a licensed user of DayPilot Pro for JavaScript.

JavaScript Scheduler Introduction

This project uses DayPilot JavaScript Scheduler component for the reservation UI. For introduction to using the scheduler component please see HTML5 Scheduler Tutorial - it shows how to set up the scheduler, load events and resources, enable drag and drop, configure scrolling and time headers.

Hours Available for Reservation

The Scheduler is able to display non-continuous timeline. We will use this feature and limit the Scheduler view to the available hours (from 6 am to 11 pm). 

We will hide the non business hours using showNonBusiness property. By default, all weekends are defined as non-business hours so we need to override this setting manually. In this configuration, the Scheduler will skip the non-business hours and display a special separator (a black vertical line) to indicate a break in the timeline. You can modify the appearance of the time break using CSS if needed (it is marked using scheduler_default_matrix_vertical_break CSS class for the default theme).

dp.businessBeginsHour = 6;
dp.businessEndsHour = 23;
dp.businessWeekends = true;
dp.showNonBusiness = false;

Tennis Court Hourly Rates

Our web application uses different prices for the time slots, depending on the time of day - peak times are more expensive. The slot prices are defined in JavaScript:

const slotPrices = {
  "06:00": 12,
  "07:00": 15,
  "08:00": 15,
  "09:00": 15,
  "10:00": 15,
  "11:00": 12,
  "12:00": 10,
  "13:00": 10,
  "14:00": 12,
  "15:00": 12,
  "16:00": 15,
  "17:00": 15,
  "18:00": 15,
  "19:00": 15,
  "20:00": 15,
  "21:00": 12,
  "22:00": 10,
};

We will customize the grid cells using onBeforeCellRender event handler. It will display the price directly in the time slot. The color of the free slots is set to green, lighter colors are used for cheaper slots. Time slots in the past are left with the default appearance (white).

dp.onBeforeCellRender = args => {

  if (args.cell.isParent) {
    return;
  }

  if (args.cell.start < new DayPilot.Date()) {  // past
    return;
  }

  var color = "green";

  var slotId = args.cell.start.toString("HH:mm");
  var price = slotPrices[slotId];

  var min = 5;
  var max = 15;
  var opacity = (price - min) / max;
  var text = "$" + price;
  args.cell.areas = [
    {
      left: 0,
      right: 0,
      top: 0,
      bottom: 0,
      backColor: color,
      fontColor: "white",
      html: text,
      style: "display: flex; justify-content: center; align-items: center; opacity: " + opacity
    }
  ]
};

Loading Tennis Courts

Our application displays the reservation schedule for multiple tennis courts. We will display the courts on the Y axis on the left side of the Scheduler. The tennis courts will be arranged in a tree hierarchy. There will be two parent nodes (Indoor, Outdoor) that will display the courts grouped by location.

dp.treeEnabled = true;

function loadResources() {
  dp.rows.load("backend_resources.php");
}

PHP

<?php
require_once '_db.php';

$scheduler_groups = $db->query('SELECT * FROM groups ORDER BY name');

class Group {}
class Resource {}

$groups = array();

foreach($scheduler_groups as $group) {
  $g = new Group();
  $g->id = "group_".$group['id'];
  $g->name = $group['name'];
  $g->expanded = true;
  $g->children = array();
  $groups[] = $g;

  $stmt = $db->prepare('SELECT * FROM resources WHERE group_id = :group ORDER BY name');
  $stmt->bindParam(':group', $group['id']);
  $stmt->execute();
  $scheduler_resources = $stmt->fetchAll();

  foreach($scheduler_resources as $resource) {
    $r = new Resource();
    $r->id = $resource['id'];
    $r->name = $resource['name'];
    $g->children[] = $r;
  }
}

header('Content-Type: application/json');
echo json_encode($groups);

Sample JSON Response

[
  {"id":"group_1","name":"Indoor","expanded":true,"children":[
    {"id":"1","name":"Court 1"},
    {"id":"2","name":"Court 2"},
    {"id":"3","name":"Court 3"},
    {"id":"4","name":"Court 4"}]},
  {"id":"group_2","name":"Outdoor","expanded":true,"children":[
    {"id":"11","name":"Court 5"},
    {"id":"12","name":"Court 6"},
    {"id":"13","name":"Court 7"},
    {"id":"14","name":"Court 8"}]}
]

Prevent Concurrent Reservations

We don't want the users to create multiple reservations for the same time slot so we will disable overlapping events in the Scheduler. This setting will be applied when creating new reservations and also when moving and resizing the reservation (this is only possible in the admin interface).

dp.allowEventOverlap = false;

Slots Unavailable for Reservations

We will load data for existing reservations and display them as blocks of unavailable time. The event/reservation details won't be visible to users. Only the administrator will be able to see the reservation name and other details.

Loading the availability data:

function loadEvents() {
  dp.events.load("backend_events_busy.php");  // GET  request with "start" and "end" query string parameters
}

backend_events_busy.php

<?php
require_once '_db.php';

$json = file_get_contents('php://input');
$params = json_decode($json);

$stmt = $db->prepare('SELECT * FROM events WHERE NOT ((end <= :start) OR (start >= :end))');
$stmt->bindParam(':start', $_GET['start']);
$stmt->bindParam(':end', $_GET['end']);
$stmt->execute();
$result = $stmt->fetchAll();

class Event {}
$events = array();

foreach($result as $row) {
  $e = new Event();
  $e->id = $row['id'];
  $e->text = "";
  $e->start = $row['start'];
  $e->end = $row['end'];
  $e->resource = $row['resource_id'];
  $e->moveDisabled = true;
  $e->resizeDisabled = true;
  $e->clickDisabled = true;
  $e->backColor = "#E69138";   // lighter #F6B26B
  $e->bubbleHtml = "Not Available";

  $events[] = $e;
}

header('Content-Type: application/json');
echo json_encode($events);

The PHP endpoint returns the reservation data. It is a JSON array of event data objects. Each event object includes the required fields:

• start (reservations start date/time)

• end (reservation end date/time)

• id (reservation id)

• text (reservation text - the public interface uses an empty string)

• resource (the court id)

We also add a few optional parameters to modify the default behavior:

• moveDisabled (disables drag and drop reservation moving)

• resizeDisabled (disables drag and drop reservation resizing)

• clickDisabled (disables the default reservation click action)

• backColor (the reservation background color is set to light orange)

• bubbleHtml (sets the HTML that displays on hover)

Reservation Restrictions

We will implement two custom rules that will apply when creating a new reservation:

• It's not possible to create reservations in the past.

• It's not possible to create reservations longer than 4 hours.

Creating Reservations in the Past

We will use onTimeRangeSelecting event which is fired in real time whenever the selected date range changes. It will let us provide immediate feedback to the users about the selected date range.

In this case, we will forbid the selection if it starts in the past. The selection color will change to red (if we mark it as not allowed). We will also display a custom message that will show details about the rule.

dp.onTimeRangeSelecting = args => {
  if (args.start < new DayPilot.Date()) {
      args.right.enabled = true;
      args.right.html = "Can't book in the past";
      args.allowed = false;                        
  }
  // ...
};

Maximum Reservation Duration

We will add one more rule that will prevent users from creating reservations longer than 4 hours. We will check the duration property of the args object and display a message describing the problem.

dp.onTimeRangeSelecting = args => {

  // ...

  if (args.duration.totalHours() > 4) {
      args.right.enabled = true;
      args.right.html = "You can only book up to 4 hours";
      args.allowed = false;
  }
}; 

Reservation Scheduler Time Headers

We will display time header with three levels:

• month, year

• day

• hour

We will use a custom format string to customize the date appearance. The Scheduler will use the format string in connection with the current locale to display localized values. By default it is set to "en-us".

You can also customize the time header HTML if you need more control over the content.

dp.timeHeaders = [
  { groupBy: "Month", format: "MMMM yyyy" },
  { groupBy: "Day", format: "dddd, MMMM d"},
  { groupBy: "Hour", format: "h tt"}
];

Scrolling to the Selected Date

We will add a Navigator control to allow easy switching of the visible date:

<div style="display: flex">
  <div style="">
    <div id="nav"></div>
  </div>
  <div style="flex-grow: 1; margin-left: 10px;">
    <div id="dp"></div>
  </div>
</div>          

<script>
    const nav = new DayPilot.Navigator("navigator");
    nav.onTimeRangeSelected =  args => {
        const day = args.day;
        
        const start = day.firstDayOfMonth();
        const days = day.daysInMonth();
        dp.startDate = start;
        dp.days = days;
        dp.update();
        loadEvents();
    };
    nav.init();
       
    const dp = new DayPilot.Scheduler("scheduler");
    // ...
    dp.init();
 </script>

The Navigator fires onTimeRangeSelected event whenever the user selects a new date. This event handler switches the Scheduler to display the selected month and loads the reservations.

As the next step, we will modify the event handler to scroll to the selected date:

const nav = new DayPilot.Navigator("navigator");
nav.onTimeRangeSelected = args => {
  const day = args.day;
  
  if (dp.visibleStart() <= day && day < dp.visibleEnd()) {
      dp.scrollTo(day, "fast");
  }
  else {
      const start = day.firstDayOfMonth();
      const days = day.daysInMonth();
      dp.startDate = start;
      dp.days = days;
      dp.update();
      dp.scrollTo(day, "fast");
      loadEvents();
  }
};
nav.init();

Administration Interface

The admin interface (admin.php) is very similar to the public one. However, it provides full access to the reservations:

• It displays full reservation details, including the name.

• All drag and drop operations are allowed. It is possible to move and resize reservations.

• It's possible to edit the reservations (clicking the reservation opens a modal dialog with reservation details).

The reservations are loaded using backend_events.php which shows the full reservation text and doesn't disable drag and drop:

<?php
require_once '_db.php';
    
$json = file_get_contents('php://input');
$params = json_decode($json);

$stmt = $db->prepare('SELECT * FROM [events] WHERE NOT ((end <= :start) OR (start >= :end))');
$stmt->bindParam(':start', $params->start);
$stmt->bindParam(':end', $params->end);
$stmt->execute();
$result = $stmt->fetchAll();

class Event {}
$events = array();

foreach($result as $row) {
  $e = new Event();
  $e->id = $row['id'];
  $e->text = $row['name'];
  $e->start = $row['start'];
  $e->end = $row['end'];
  $e->resource = $row['resource_id'];
  $e->bubbleHtml = "Event details: <br/>".$e->text;
  $events[] = $e;
}

header('Content-Type: application/json');
echo json_encode($events);

?> 

Reservation moving is enabled using onEventMoved event handler:

dp.onEventMoved = async args => {

  const data = {
    id: args.e.id(),
    newStart: args.newStart,
    newEnd: args.newEnd,
    newResource: args.newResource
  };
  await DayPilot.Http.post("backend_move.php", data);
  dp.message("Moved.");

}; 

Event click is mapped to editing the reservation using a modal dialog:

dp.onEventClicked = async  args => {

  const courts = []
    .concat(dp.resources[0].children)
    .concat(dp.resources[1].children);

  const form = [
    {name: "Name", id: "name"},
    {name: "Start", id: "start", dateFormat: "MMMM d, yyyy h:mm tt"},
    {name: "End", id: "end", dateFormat: "MMMM d, yyyy h:mm tt"},
    {name: "Court", id: "resource", options: courts},
  ];

  const data = {
    id: args.e.data.id,
    name: args.e.data.text,
    start: args.e.data.start,
    end: args.e.data.end,
    resource: args.e.data.resource
  };

  const modal = await DayPilot.Modal.form(form, data);
  dp.clearSelection();
  if (modal.canceled) {
    return;
  }
  await DayPilot.Http.post("backend_update.php", modal.result);

  const e = {
    start: modal.result.start,
    end: modal.result.end,
    id: modal.result.id,
    text: modal.result.name,
    resource: modal.result.resource
  };

  dp.events.update(e);

};

MySQL and SQLite Database

By default, the project uses SQLite database to minimize the setup requirements. You can switch to MySQL by editing the _db.php:

<?php

// use sqlite
// require_once '_db_sqlite.php';

// use MySQL
require_once '_db_mysql.php';

You can configure the MySQL database connection properties (server address, user name, password, database name) in _db_mysql.php:

<?php
$host = "127.0.0.1";
$port = 3306;
$username = "username";
$password = "password";
$database = "tennis-court-reservation";

// ...

The database will be created and initialized automatically if it doesn't exist.

Database Schema (MySQL)

This web application uses a MySQL database with the following schema:

CREATE TABLE `events` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` TEXT NULL,
	`start` DATETIME NULL DEFAULT NULL,
	`end` DATETIME NULL DEFAULT NULL,
	`resource_id` VARCHAR(30) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `groups` (
	`id` INT(11) NOT NULL,
	`name` VARCHAR(200) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `resources` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(200) NULL DEFAULT NULL,
	`group_id` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
);

History

• June 8, 2021: DayPilot Pro 2021.2.5000; ES6 syntax, async/await for promises; using system fonts

• November 21, 2020: DayPilot Pro 2020.4.4766; jQuery removed; bug fixes

• July 7, 2020: DayPilot Pro 2020.3.4558

• Jul 12, 2018: MySQL support added, DayPilot Pro 2018.2.3324

• Jun 28, 2017: DayPilot Pro updated

• Dec 31, 2015: Initial release