Loading...

jQuery Fullcalender with PHP and MySQL

David Carr

10 min read - 14th Apr, 2020

I'm a big fan of fullcalendar.io the latest version is quite a bit different from earlier versions, in this tutorial I'll cover how to integrate fullcalendar into a database with MySQL and load the events from PHP.

Demo: https://demos.dcblog.dev/Jquery-fullcalendar-with-php-and-mysql/ 

Download: https://github.com/dcblogdev/fullcalendar

I've downloaded files from https://fullcalendar.io/docs/getting-started also I've added the following:

Bootstrap v4.4.1
jQuery v3.2.1
jQuery UI v1.10.3
Datepicker v1.6.3
Colorpicker

I've placed all the downloaded packages and the ones listed above in a folder called packages, Also I've got a folder called api where all the database files will live.

I'm going to use a PDO Wrapper for the database interactions to install it create a file called composer.json and add:

{
    "require": {
        "dcblogdev/pdo-wrapper": "^1.1"
    }
}

Now run composer install in a terminal to install the wrapper. A vendor folder will be created.

Now create a config.php file and add:

<?php
require('vendor/autoload.php');

use Dcblogdev\PdoWrapper\Database;

$host = "localhost";
$database = "calendar";
$username = "root";
$password = "";

$db = Database::get($username, $password, $database, $host);
$dir = "./";

First, we include the autoloader so any files from composer can be loaded.

Make an instance of the Database wrapper and set the database credentials.

Finally set the folder path $dir = "./" points to the current folder, you can make this absolute if you prefer.

Create a table called events with the following structure:

CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text,
  `start_event` datetime NOT NULL,
  `end_event` datetime NOT NULL,
  `color` varchar(191) DEFAULT NULL,
  `text_color` varchar(191) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

You can import the db.sql file provided on my GitHub repo or run this in your MySQL program of choice.

Create a file called index.php this is where the calender will be used.

Include the config and the packages:

<?php require('config.php');?>
<!DOCTYPE html>
<html>
<head>
    <title>Calandar</title>

    <link href='<?=$dir;?>packages/core/main.css' rel='stylesheet' />
    <link href='<?=$dir;?>packages/daygrid/main.css' rel='stylesheet' />
    <link href='<?=$dir;?>packages/timegrid/main.css' rel='stylesheet' />
    <link href='<?=$dir;?>packages/list/main.css' rel='stylesheet' />
    <link href='<?=$dir;?>packages/bootstrap/css/bootstrap.css' rel='stylesheet' />
    <link href="<?=$dir;?>packages/jqueryui/custom-theme/jquery-ui-1.10.4.custom.min.css" rel="stylesheet">
    <link href='<?=$dir;?>packages/datepicker/datepicker.css' rel='stylesheet' />
    <link href='<?=$dir;?>packages/colorpicker/bootstrap-colorpicker.min.css' rel='stylesheet' />
    <link href='<?=$dir;?>style.css' rel='stylesheet' />

    <script src='<?=$dir;?>packages/core/main.js'></script>
    <script src='<?=$dir;?>packages/daygrid/main.js'></script>
    <script src='<?=$dir;?>packages/timegrid/main.js'></script>
    <script src='<?=$dir;?>packages/list/main.js'></script>
    <script src='<?=$dir;?>packages/interaction/main.js'></script>
    <script src='<?=$dir;?>packages/jquery/jquery.js'></script>
    <script src='<?=$dir;?>packages/jqueryui/jqueryui.min.js'></script>
    <script src='<?=$dir;?>packages/bootstrap/js/bootstrap.js'></script>
    <script src='<?=$dir;?>packages/datepicker/datepicker.js'></script>
    <script src='<?=$dir;?>packages/colorpicker/bootstrap-colorpicker.min.js'></script>
    <script src='<?=$dir;?>calendar.js'></script>
</head>
<body>

The calendar.js is where all the calendar functions will go.

Now add the calender markup:

<div class="container">

    <button type="button" class="btn btn-primary" data-toggle="modal" data-target="#addeventmodal">
      Add Event
    </button>

    <div id="calendar"></div>
</div>

</body>
</html>

By default, the calendar will be attached to an element by its id.

Also since we're using bootstrap we can make use of its modals to load popup windows for adding and editing events.

the markup for these:

<div class="modal fade" id="addeventmodal" tabindex="-1" role="dialog">
    <div class="modal-dialog">
        <div class="modal-content">

            <div class="modal-header">
                <h5 class="modal-title">Add Event</h5>
                <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                    <span aria-hidden="true">&amp;times;</span>
                </button>
            </div>

            <div class="modal-body">

                <div class="container-fluid">

                    <form id="createEvent" class="form-horizontal">

                    <div class="row">

                        <div class="col-md-6">

                            <div id="title-group" class="form-group">
                                <label class="control-label" for="title">Title</label>
                                <input type="text" class="form-control" name="title">
                                <!-- errors will go here -->
                            </div>

                            <div id="startdate-group" class="form-group">
                                <label class="control-label" for="startDate">Start Date</label>
                                <input type="text" class="form-control datetimepicker" id="startDate" name="startDate">
                                <!-- errors will go here -->
                            </div>

                            <div id="enddate-group" class="form-group">
                                <label class="control-label" for="endDate">End Date</label>
                                <input type="text" class="form-control datetimepicker" id="endDate" name="endDate">
                                <!-- errors will go here -->
                            </div>

                        </div>

                        <div class="col-md-6">

                            <div id="color-group" class="form-group">
                                <label class="control-label" for="color">Colour</label>
                                <input type="text" class="form-control colorpicker" name="color" value="#6453e9">
                                <!-- errors will go here -->
                            </div>

                            <div id="textcolor-group" class="form-group">
                                <label class="control-label" for="textcolor">Text Colour</label>
                                <input type="text" class="form-control colorpicker" name="text_color" value="#ffffff">
                                <!-- errors will go here -->
                            </div>

                        </div>

                    </div>



                </div>

            </div>

            <div class="modal-footer">
              <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
              <button type="submit" class="btn btn-primary">Save changes</button>
            </div>

            </form>

        </div><!-- /.modal-content -->
    </div><!-- /.modal-dialog -->
</div><!-- /.modal -->

<div class="modal fade" id="editeventmodal" tabindex="-1" role="dialog">
    <div class="modal-dialog">
        <div class="modal-content">

            <div class="modal-header">
                <h5 class="modal-title">Update Event</h5>
                <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                    <span aria-hidden="true">&amp;times;</span>
                </button>
            </div>

            <div class="modal-body">

                <div class="container-fluid">

                    <form id="editEvent" class="form-horizontal">
                    <input type="hidden" id="editEventId" name="editEventId" value="">

                    <div class="row">

                        <div class="col-md-6">

                            <div id="edit-title-group" class="form-group">
                                <label class="control-label" for="editEventTitle">Title</label>
                                <input type="text" class="form-control" id="editEventTitle" name="editEventTitle">
                                <!-- errors will go here -->
                            </div>

                            <div id="edit-startdate-group" class="form-group">
                                <label class="control-label" for="editStartDate">Start Date</label>
                                <input type="text" class="form-control datetimepicker" id="editStartDate" name="editStartDate">
                                <!-- errors will go here -->
                            </div>

                            <div id="edit-enddate-group" class="form-group">
                                <label class="control-label" for="editEndDate">End Date</label>
                                <input type="text" class="form-control datetimepicker" id="editEndDate" name="editEndDate">
                                <!-- errors will go here -->
                            </div>

                        </div>

                        <div class="col-md-6">

                            <div id="edit-color-group" class="form-group">
                                <label class="control-label" for="editColor">Colour</label>
                                <input type="text" class="form-control colorpicker" id="editColor" name="editColor" value="#6453e9">
                                <!-- errors will go here -->
                            </div>

                            <div id="edit-textcolor-group" class="form-group">
                                <label class="control-label" for="editTextColor">Text Colour</label>
                                <input type="text" class="form-control colorpicker" id="editTextColor" name="editTextColor" value="#ffffff">
                                <!-- errors will go here -->
                            </div>

                        </div>

                    </div>

                </div>

            </div>

            <div class="modal-footer">
              <button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
              <button type="submit" class="btn btn-primary">Save changes</button>
              <button type="button" class="btn btn-danger" id="deleteEvent" data-id>Delete</button>
            </div>

            </form>

        </div><!-- /.modal-content -->
    </div><!-- /.modal-dialog -->
</div><!-- /.modal -->

These both have forms to add and edit the event.

Now lets move on to the JS create a file called calendar.js

add an event listener so the code will only run once the page is fully loaded:

document.addEventListener('DOMContentLoaded', function() {

    var url ='/';

Now we will setup a class for the datepicker to attach to

$('body').on('click', '.datetimepicker', function() {
    $(this).not('.hasDateTimePicker').datetimepicker({
        controlType: 'select',
        changeMonth: true,
        changeYear: true,
        dateFormat: "dd-mm-yy",
        timeFormat: 'HH:mm:ss',
        yearRange: "1900:+10",
        showOn:'focus',
        firstDay: 1
    }).focus();
});

And the color picker

$(".colorpicker").colorpicker();

Now setup a variable for the calendar to attach to a div with an id of calendar

var calendarEl = document.getElementById('calendar');

Now for the FullCalendar 

var calendar = new FullCalendar.Calendar(calendarEl, {

Inside FullCalendar place the calendar options:

plugins: ['interaction', 'dayGrid', 'timeGrid', 'list'],
header: {
    left: 'prev,next today',
    center: 'title',
    right: 'dayGridMonth,timeGridWeek,timeGridDay,listMonth'
},
navLinks: true, // can click day/week names to navigate views
businessHours: true, // display business hours
editable: true,

to load the events we need to call events:

events: url+'api/load.php',

This sends a request to load.php which will return a JSON array of all the events, I'll cover the JS functions first then go over the PHP code.

Now can call 3 additional options, eventDrop, eventResize and eventClick

eventDrop is triggered when an event is dragged and dropped to another date or time.

We collect the start and end dates and then send an ajax request to update.php and pass the id of the event and the start and end time.

eventDrop: function(arg) {
    var start = arg.event.start.toDateString()+' '+arg.event.start.getHours()+':'+arg.event.start.getMinutes()+':'+arg.event.start.getSeconds();
    if (arg.event.end == null) {
        end = start;
    } else {
        var end = arg.event.end.toDateString()+' '+arg.event.end.getHours()+':'+arg.event.end.getMinutes()+':'+arg.event.end.getSeconds();
    }

    $.ajax({
      url:url+"api/update.php",
      type:"POST",
      data:{id:arg.event.id, start:start, end:end},
    });
}

eventReize is very similar, this is triggered when an event is manually resized from a week or day view.

eventResize: function(arg) {
    var start = arg.event.start.toDateString()+' '+arg.event.start.getHours()+':'+arg.event.start.getMinutes()+':'+arg.event.start.getSeconds();
    var end = arg.event.end.toDateString()+' '+arg.event.end.getHours()+':'+arg.event.end.getMinutes()+':'+arg.event.end.getSeconds();

    $.ajax({
      url:url+"api/update.php",
      type:"POST",
      data:{id:arg.event.id, start:start, end:end},
    });
}

eventClick is triggered when an event is clicked on, at this point we want to collect the id of the event and then do an ajax call to get all the event details, add them to a model and show the modal. Also check if a delete button has been pressed on the modal and if so send an ajax event to delete the entry.

eventClick: function(arg) {
    var id = arg.event.id;

    $('#editEventId').val(id);
    $('#deleteEvent').attr('data-id', id); 

    $.ajax({
      url:url+"api/getevent.php",
      type:"POST",
      dataType: 'json',
      data:{id:id},
      success: function(data) {
            $('#editEventTitle').val(data.title);
            $('#editStartDate').val(data.start);
            $('#editEndDate').val(data.end);
            $('#editColor').val(data.color);
            $('#editTextColor').val(data.textColor);
            $('#editeventmodal').modal();
        }
    });

    $('body').on('click', '#deleteEvent', function() {
        if(confirm("Are you sure you want to remove it?")) {
            $.ajax({
                url:url+"api/delete.php",
                type:"POST",
                data:{id:arg.event.id},
            }); 

            //close model
            $('#editeventmodal').modal('hide');

            //refresh calendar
            calendar.refetchEvents();         
        }
    });

    calendar.refetchEvents();
}

On the model for adding an event we need to send the form data to PHP and process the response, we do this by sending an ajax call:

$('#createEvent').submit(function(event) {

    // stop the form refreshing the page
    event.preventDefault();

    $('.form-group').removeClass('has-error'); // remove the error class
    $('.help-block').remove(); // remove the error text

    // process the form
    $.ajax({
        type        : "POST",
        url         : url+'api/insert.php',
        data        : $(this).serialize(),
        dataType    : 'json',
        encode      : true
    }).done(function(data) {

        // insert worked
        if (data.success) {

            //remove any form data
            $('#createEvent').trigger("reset");

            //close model
            $('#addeventmodal').modal('hide');

            //refresh calendar
            calendar.refetchEvents();

        } else {

            //if error exists update html
            if (data.errors.date) {
                $('#date-group').addClass('has-error');
                $('#date-group').append('<div class="help-block">' + data.errors.date + '</div>');
            }

            if (data.errors.title) {
                $('#title-group').addClass('has-error');
                $('#title-group').append('<div class="help-block">' + data.errors.title + '</div>');
            }

        }

    });
});

The process for editing an event is similar:

$('#editEvent').submit(function(event) {

    // stop the form refreshing the page
    event.preventDefault();

    $('.form-group').removeClass('has-error'); // remove the error class
    $('.help-block').remove(); // remove the error text

    //form data
    var id = $('#editEventId').val();
    var title = $('#editEventTitle').val();
    var start = $('#editStartDate').val();
    var end = $('#editEndDate').val();
    var color = $('#editColor').val();
    var textColor = $('#editTextColor').val();

    // process the form
    $.ajax({
        type        : "POST",
        url         : url+'api/update.php',
        data        : {
            id:id, 
            title:title, 
            start:start,
            end:end,
            color:color,
            text_color:textColor
        },
        dataType    : 'json',
        encode      : true
    }).done(function(data) {

        // insert worked
        if (data.success) {

            //remove any form data
            $('#editEvent').trigger("reset");

            //close model
            $('#editeventmodal').modal('hide');

            //refresh calendar
            calendar.refetchEvents();

        } else {

            //if error exists update html
            if (data.errors.date) {
                $('#date-group').addClass('has-error');
                $('#date-group').append('<div class="help-block">' + data.errors.date + '</div>');
            }

            if (data.errors.title) {
                $('#title-group').addClass('has-error');
                $('#title-group').append('<div class="help-block">' + data.errors.title + '</div>');
            }

        }

    });
});

At this point we have the calender and all the ajax calls next we need PHP to process the requests.

Create a folder called api and the following files:

delete.php
getevent.php
insert.php
load.php
update.php

delete.php

Check if an id exists in the POST request and if do then attempt to delete the event where the id posted matched an id from the events table.

include("../config.php");

if (isset($_POST["id"])) {
    $db->delete('events', ['id' => $_POST['id']]);
}

getevent

Check if an id exists in the POST request then load the event that matched the id and then create a data array matching the structure the calendar needs and finally return the array as JSON.

<?php
include("../config.php");

if (isset($_POST['id'])) {
    $row = $db->find("* FROM events where id=?", [$_POST['id']]);
    $data = [
        'id'        => $row->id,
        'title'     => $row->title,
        'start'     => date('d-m-Y H:i:s', strtotime($row->start_event)),
        'end'       => date('d-m-Y H:i:s', strtotime($row->end_event)),
        'color'     => $row->color,
        'textColor' => $row->text_color
    ];

    echo json_encode($data);
}

insert.php

here we validate the data meets our requirements by ensuring the title, start and end are not empty then format the data before inserting the data. We always return JSON data to the user can be show validation errors or to close the model on success.

<?php
include("../config.php");

if (isset($_POST['title'])) {

    //collect data
    $error      = null;
    $title      = $_POST['title'];
    $start      = $_POST['startDate'];
    $end        = $_POST['startDate'];
    $color      = $_POST['color'];
    $text_color = $_POST['text_color'];

    //validation
    if ($title == '') {
        $error['title'] = 'Title is required';
    }

    if ($start == '') {
        $error['start'] = 'Start date is required';
    }

    if ($end == '') {
        $error['end'] = 'End date is required';
    }

    //if there are no errors, carry on
    if (! isset($error)) {

        //format date
        $start = date('Y-m-d H:i:s', strtotime($start));
        $end = date('Y-m-d H:i:s', strtotime($end));

        $data['success'] = true;
        $data['message'] = 'Success!';

        //store
        $insert = [
            'title'       => $title,
            'start_event' => $start,
            'end_event'   => $end,
            'color'       => $color,
            'text_color'  => $text_color
        ];
        $db->insert('events', $insert);

    } else {

        $data['success'] = false;
        $data['errors'] = $error;
    }

    echo json_encode($data);
}

load.php

this file loads all the events for the calendar to read

<?php
include("../config.php");
$data = [];

$result = $db->select("* FROM events ORDER BY id");
foreach($result as $row) {
    $data[] = [
        'id'              => $row->id,
        'title'           => $row->title,
        'start'           => $row->start_event,
        'end'             => $row->end_event,
        'backgroundColor' => $row->color,
        'textColor'       => $row->text_color
    ];
}

echo json_encode($data);

update.php

this again will validate all required fields have been entered and then perform an update 

<?php
include("../config.php");

if (isset($_POST['id'])) {

    //collect data
    $error      = null;
    $id         = $_POST['id'];
    $start      = $_POST['start'];
    $end        = $_POST['end'];

    //optional fields
    $title      = isset($_POST['title']) ? $_POST['title']: '';
    $color      = isset($_POST['color']) ? $_POST['color']: '';
    $text_color = isset($_POST['text_color']) ? $_POST['text_color']: '';

    //validation
    if ($start == '') {
        $error['start'] = 'Start date is required';
    }

    if ($end == '') {
        $error['end'] = 'End date is required';
    }

    //if there are no errors, carry on
    if (! isset($error)) {

        //reformat date
        $start = date('Y-m-d H:i:s', strtotime($start));
        $end = date('Y-m-d H:i:s', strtotime($end));

        $data['success'] = true;
        $data['message'] = 'Success!';

        //set core update array
        $update = [
            'start_event' => date('Y-m-d H:i:s', strtotime($_POST['start'])),
            'end_event' => date('Y-m-d H:i:s', strtotime($_POST['end']))
        ];

        //check for additional fields, and add to $update array if they exist
        if ($title !='') {
            $update['title'] = $title;
        }

        if ($color !='') {
            $update['color'] = $color;
        }

        if ($text_color !='') {
            $update['text_color'] = $text_color;
        }

        //set the where condition ie where id = 2
        $where = ['id' => $_POST['id']];

        //update database
        $db->update('events', $update, $where);

    } else {

        $data['success'] = false;
        $data['errors'] = $error;
    }

    echo json_encode($data);
}

At this point, events can be added, edit, delete and so dragged to other days. 

I'd recommend looking at the complete source code available on:

Demo: https://demos.dcblog.dev/Jquery-fullcalendar-with-php-and-mysql/

Download: https://github.com/dcblogdev/fullcalendar

1 comments
Add a comment