JavaScript Resource Calendar Tutorial (PHP/MySQL) | DayPilot Code

Features

• JavaScript resource calendar web application that displays calendar for multiple resources side by side (as columns).

• Drag and drop support (creating, moving and resizing events).

• Loading resources (columns) and events using a REST API.

• The REST backend is implemented in PHP/MySQL.

• The project was built using Resource Calendar UI Builder online configurator.

• Includes a trial version of DayPilot Pro for JavaScript (see also License below)

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. Buy a license.

Resource Calendar: Basic Configuration

The basic resource calendar configuration is easy - just create and instance of the JavaScript calendar component and set the view type to resources.

You can also use the resource calendar UI builder to customize the configuration and generate the JavaScript project including the boilerplate code and dependencies.

JavaScript (index.html)

<script src="js/daypilot/daypilot-all.min.js"></script>

<div id="dp"></div>

<script>
  var dp = new DayPilot.Calendar("dp");
  dp.viewType = "Resources";
  dp.columns.list = [
    {id: 1, name: "Person 1"},
    {id: 2, name: "Person 2"},
    {id: 3, name: "Person 3"}
  ];
  dp.init();
</script>

This code snippet initializes the calendar and binds it to the placeholder <div> specified using id attribute ("dp").

It changes the viewType to "Resources". This mode will display the specified resources as columns.

The columns are specified using columns.list property. This is a simple array of column objects, each with id and name property. This configuration uses the current date for the columns. You can also specify a custom date for each of the columns using date property:

dp.columns.list = [
  {id: 1, name: "Person 1", date: "2021-05-01"},
  {id: 2, name: "Person 2", date: "2021-05-01"},
  {id: 3, name: "Person 3", date: "2021-05-01"}
];

If the date property is not specified, the value of startDate property will be used.

Resource Calendar: Column Hierarchy

When there are more than just a few columns it may be helpful to arrange the columns in groups. The resources calendar supports a column tree that can be as deep as you need. The number of column tree levels is specified using headerLevels property. This example specifies a single group called "People" with three child columns ("Person 1",  "Person 2" and "Person 3").

JavaScript (index.html)

dp.viewType = "Resources";
dp.headerLevels = 2;
dp.columns.list = [
    { name: "People", children: [
      {id: 1, name: "Person 1"},
      {id: 2, name: "Person 2"},
      {id: 3, name: "Person 3"}
    ]}
];

Loading Columns from MySQL Database

The static declaration of columns only work for very simple scenarios. In most cases, you will want to load the resource calendar columns from a database. The calendar component offers a convenience method that loads the columns using an AJAX call from a remote REST API:

dp.columns.load("backend_resources.php");

It loads the columns using a GET HTTP request and automatically updates the resource calendar as soon as the response is received. The specified URL must return a JSON array with the structure exactly matching the columns array structure described in the previous steps.

Here is a sample PHP source code that loads the columns from MySQL database and returns the JSON string in the required format.

PHP (backend_resources.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->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);

The JSON response looks like this:

[
  {"id":"group_1","name":"People","children":[
    {"id":"1","name":"Person 1"},
    {"id":"2","name":"Person 2"},
    {"id":"3","name":"Person 3"}
  ]},
  {"id":"group_2","name":"Tools","children":[
    {"id":"4","name":"Tool 1"},
    {"id":"5","name":"Tool 2"},
    {"id":"6","name":"Tool 3"}
  ]}
]

Loading Event Data from MySQL Database

Now when we have initialized the resource calendar and loaded the columns from the server we can load the calendar event data.

There is also a convenience method for this and the logic is very similar:

dp.events.load("backend_events.php");

This call invokes a GET HTTP request to the specified URL. Note that the URL will be automatically extended with start and end query string parameters. The parameters are set according to the current view and you can use them on the server side to limit the SQL SELECT only to the date range that is currently visible in the calendar.

PHP (backend_events.php)

<?php
require_once '_db.php';

$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 = $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);

Resource Calendar: Drag and Drop Event Moving

Adding drag and drop functionality is even easier. Common drag and drop operations (including event moving and resizing) are enabled by default.

The only thing we need to add is a custom event handler (onEventMoved in case of event moving) that will notify the server about the operation. We will use it to call a REST endpoint that will save the change in our MySQL database.

JavaScript

<script>
  var dp = new DayPilot.Calendar("dp", {
    // ...
    onEventMoved: function (args) {
        var params = {
            id: args.e.id(),
            start: args.newStart,
            end: args.newEnd,
            resource: args.newResource
        };
        DayPilot.Http.ajax({
            url: "backend_move.php",
            method: "POST",
            data: params,
            success: function(ajaxArgs) {
                var result = ajaxArgs.data;
                dp.message(result.message);
            }
        });
    }
  });

  dp.init();
</script>

<?php
require_once '_db.php';

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

$stmt = $db->prepare("UPDATE events SET start = :start, end = :end, resource_id = :resource WHERE id = :id");
$stmt->bindParam(':start', $params->start);
$stmt->bindParam(':end', $params->end);
$stmt->bindParam(':id', $params->id);
$stmt->bindParam(':resource', $params->resource);
$stmt->execute();

class Result {}
$response = new Result();
$response->result = 'OK';
$response->message = 'Update successful';

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

Event Creation using Drag and Drop

You can add new events to the resource calendar using drag and drop. This feature is enabled by default (timeRangeSelectedHandling is set to "Enabled"). All you need to do is to add onTimeRangeSelected event handler.

Our event handler opens a modal dialog and asks for event text. When a user confirms the event text, we call "backend_event.php" script to create a new record in the database. The final step is to add the new event to the resource calendar using events.add() method:

onTimeRangeSelected: function (args) {
  DayPilot.Modal.prompt("Create a new event:", "Event 1").then(function(modal) {
    var dp = args.control;
    dp.clearSelection();
    if (!modal.result) { return; }

    var params = {
        start: args.start,
        end: args.end,
        text: modal.result,
        resource: args.resource
    };

    DayPilot.Http.ajax({
        url: "backend_create.php",
        method: "POST",
        data: params,
        success: function(ajaxArgs) {
            var result = ajaxArgs.data;

            params.id = result.id;
            dp.events.add(params);
            dp.message(result.message);
        }
    });
  });
},

Database Backend (SQLite and MySQL)

By default, the sample project uses SQLite database that is initialized automatically (daypilot.sqlite file is created automatically in the PHP project root - just make sure that the application has write permissions for the project folder). The SQLite database requires no configuration and is good for the initial testing.

In order to switch the backend storage to MySQL you need to edit _db.php file which looks like this:

<?php

// use sqlite
require_once '_db_sqlite.php';

// use MySQL
//require_once '_db_mysql.php';

In order to switch to MySQL you need to comment out the _db_sqlite.php include and enable _db_mysql.php.

Don't forget to update the MySQL connection details at the top of _db_mysql.php:

<?php
$host = "127.0.0.1";
$port = 3306;
$username = "username";
$password = "password";
$database = "resource-calendar";

// ...

The database will be created and initialized automatically if it doesn't exist. Just make sure that the specified user has the permission to create a new database.

MySQL Database Schema

CREATE TABLE IF NOT EXISTS `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text,
  `start` datetime DEFAULT NULL,
  `end` datetime DEFAULT NULL,
  `resource_id` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `groups` (
  `id` int(11) NOT NULL,
  `name` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `resources` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) DEFAULT NULL,
  `group_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

History

• November 21, 2020: Upgraded to DayPilot Pro for JavaScript 2020.4.4766

• April 19, 2018: Initial release