Server, Database, Application and Laravel Backups - Get fully protected with SnapShooter
How to Build a PHP Booking Calendar with MySQL
n this tutorial, we will build a nice booking calendar using PHP and MySQL. Booking calendars are very common applications, you will learn how to write PHP code that separates business logic from presentation through this tutorial.
After this tutorial, you should be able to build a working booking calendar as shown below:
Prepare the database
All the booking dates will be stored inside the MySQL database. Let's design a simple database table.
Run the SQL statement below from your database console to create a table: bookings.
CREATE TABLE bookings (
id int auto_increment,
booking_date DATE,
constraint pk_example primary key (id)
);
All bookings will be stored inside the table above, and each booked date is saved inside booking_date column.
Build a Booking class
In this section, we will build a PHP class that interacts with the database. It is capable of listing all the bookings, inserting a booking as well as deleting a booking.
- Create a php file Booking.php and place the code below:
<?php
class Booking
{
private $dbh;
private $bookingsTableName = 'bookings';
/**
* Booking constructor.
* @param string $database
* @param string $host
* @param string $databaseUsername
* @param string $databaseUserPassword
*/
public function __construct($database, $host, $databaseUsername, $databaseUserPassword)
{
try {
$this->dbh =
new PDO(sprintf('mysql:host=%s;dbname=%s', $host, $database),
$databaseUsername,
$databaseUserPassword
);
} catch (PDOException $e) {
die($e->getMessage());
}
}
The code above will connect to a database when it is instantiated using PDO. When working with a database in PHP, we should always use PDO as it provides great security and friendly API.
- Create a function index() inside the Booking class:
public function index()
{
$statement = $this->dbh->query('SELECT * FROM ' . $this->bookingsTableName);
return $statement->fetchAll(PDO::FETCH_ASSOC);
}
This function will list out all the booking records and return them as an associated array.
- Create a function add() inside the Booking class:
public function add(DateTimeImmutable $bookingDate)
{
$statement = $this->dbh->prepare(
'INSERT INTO ' . $this->bookingsTableName . ' (booking_date) VALUES (:bookingDate)'
);
if (false === $statement) {
throw new Exception('Invalid prepare statement');
}
if (false === $statement->execute([
':bookingDate' => $bookingDate->format('Y-m-d'),
])) {
throw new Exception(implode(' ', $statement->errorInfo()));
}
}
This function inserts a booking into the bookings table. We are using PDO's prepare statements to do the insertion, which provides auto escaping.
- Create a function delete() inside the Booking class:
public function delete($id)
{
$statement = $this->dbh->prepare(
'DELETE from ' . $this->bookingsTableName . ' WHERE id = :id'
);
if (false === $statement) {
throw new Exception('Invalid prepare statement');
}
if (false === $statement->execute([':id' => $id])) {
throw new Exception(implode(' ', $statement->errorInfo()));
}
}
This function deletes a booking from the bookings table by taking the primary key.
That is all for Booking class, and the complete Booking.php is shown as below:
<?php
class Booking
{
private $dbh;
private $bookingsTableName = 'bookings';
/**
* Booking constructor.
* @param string $database
* @param string $host
* @param string $databaseUsername
* @param string $databaseUserPassword
*/
public function __construct($database, $host, $databaseUsername, $databaseUserPassword)
{
try {
$this->dbh =
new PDO(sprintf('mysql:host=%s;dbname=%s', $host, $database),
$databaseUsername,
$databaseUserPassword
);
} catch (PDOException $e) {
die($e->getMessage());
}
}
public function index()
{
$statement = $this->dbh->query('SELECT * FROM ' . $this->bookingsTableName);
return $statement->fetchAll(PDO::FETCH_ASSOC);
}
public function add(DateTimeImmutable $bookingDate)
{
$statement = $this->dbh->prepare(
'INSERT INTO ' . $this->bookingsTableName . ' (booking_date) VALUES (:bookingDate)'
);
if (false === $statement) {
throw new Exception('Invalid prepare statement');
}
if (false === $statement->execute([
':bookingDate' => $bookingDate->format('Y-m-d'),
])) {
throw new Exception(implode(' ', $statement->errorInfo()));
}
}
public function delete($id)
{
$statement = $this->dbh->prepare(
'DELETE from ' . $this->bookingsTableName . ' WHERE id = :id'
);
if (false === $statement) {
throw new Exception('Invalid prepare statement');
}
if (false === $statement->execute([':id' => $id])) {
throw new Exception(implode(' ', $statement->errorInfo()));
}
}
}
Build a Calendar class
In one of our previous tutorials, we have built a powerful calendar class that comes with hooks. In this tutorial, we will utilize it to accomplish our goal.
- Create a file Calendar.php, copy and paste the following code:
<?php
class Calendar
{
/**
* Constructor
*/
public function __construct()
{
$this->naviHref = htmlentities($_SERVER['PHP_SELF']);
}
/********************* PROPERTY ********************/
public $cellContent = '';
protected $observers = array();
private $dayLabels = array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun");
private $currentYear = 0;
private $currentMonth = 0;
private $currentDay = 0;
private $currentDate = null;
private $daysInMonth = 0;
private $sundayFirst = true;
private $naviHref = null;
/********************* PUBLIC **********************/
/* @return void
* @access public
*/
public function attachObserver($type, $observer)
{
$this->observers[$type][] = $observer;
}
/*
*
* @return void
* @access public
*/
public function notifyObserver($type)
{
if (isset($this->observers[$type])) {
foreach ($this->observers[$type] as $observer) {
$observer->update($this);
}
}
}
public function getCurrentDate()
{
return $this->currentDate;
}
/**
* Set week labels' order.
* When it is set to false,
* monday will be listed as the first day.
*
* @param boolean
* @return void
* @author The-Di-Lab <thedilab@gmail.com>
* @access public
*/
public function setSundayFirst($bool = true)
{
$this->sundayFirst = $bool;
}
/**
* print out the calendar
*
* @param string
* @param string
* @param array
* @return string
* @author The-Di-Lab <thedilab@gmail.com>
* @access public
*/
public function show($month = null, $year = null, $attributes = false)
{
if (null == $year && isset($_GET['year'])) {
$year = $_GET['year'];
} else if (null == $year) {
$year = date("Y", time());
}
if (null == $month && isset($_GET['month'])) {
$month = $_GET['month'];
} else if (null == $month) {
$month = date("m", time());
}
$this->currentYear = $year;
$this->currentMonth = $month;
$this->daysInMonth = $this->_daysInMonth($month, $year);
$content = '<div id="calendar">' .
'<div class="box">' .
$this->_createNavi() .
'</div>' .
'<div class="box-content">' .
'<ul class="label">' . $this->_createLabels() . '</ul>';
$content .= '<div class="clear"></div>';
$content .= '<ul class="dates">';
for ($i = 0; $i < $this->_weeksInMonth($month, $year); $i++) {
for ($j = 1; $j <= 7; $j++) {
$content .= $this->_showDay($i * 7 + $j, $attributes);
}
}
$content .= '</ul>';
$content .= '<div class="clear"></div>';
$content .= '</div>';
$content .= '</div>';
return $content;
}
/********************* PRIVATE **********************/
/**
* create the li element for ul
*
* @param string
* @param array
* @return string
* @author The-Di-Lab <thedilab@gmail.com>
* @access private
*/
private function _showDay($cellNumber, $attributes = false)
{
if ($this->currentDay == 0) {
//1 (for Monday) through 7 (for Sunday)
$firstDayOfTheWeek = date('N', strtotime($this->currentYear . '-' . $this->currentMonth . '-01'));
if ($this->sundayFirst) {
if ($firstDayOfTheWeek == 7) {
$firstDayOfTheWeek = 1;
} else {
$firstDayOfTheWeek++;
}
}
if (intval($cellNumber) == intval($firstDayOfTheWeek)) {
$this->currentDay = 1;
}
}
if (($this->currentDay != 0) && ($this->currentDay <= $this->daysInMonth)) {
$this->currentDate = date('Y-m-d', strtotime($this->currentYear . '-' . $this->currentMonth . '-' . ($this->currentDay)));
$cellContent = $this->_createCellContent($attributes);
$this->currentDay++;
} else {
$this->currentDate = null;
$cellContent = null;
}
return '<li id="li-' . $this->currentDate . '" class="' . ($cellNumber % 7 == 1 ? ' start ' : ($cellNumber % 7 == 0 ? ' end ' : ' ')) .
($cellContent == null ? 'mask' : '') . '">' . $cellContent . '</li>';
}
/**
* create navigation
*
* @return string
* @author The-Di-Lab <thedilab@gmail.com>
* @access private
*/
private function _createNavi()
{
$nextMonth = $this->currentMonth == 12 ? 1 : intval($this->currentMonth) + 1;
$nextYear = $this->currentMonth == 12 ? intval($this->currentYear) + 1 : $this->currentYear;
$preMonth = $this->currentMonth == 1 ? 12 : intval($this->currentMonth) - 1;
$preYear = $this->currentMonth == 1 ? intval($this->currentYear) - 1 : $this->currentYear;
return
'<div class="header">' .
'<a class="prev" href="' . $this->naviHref . '?month=' . sprintf('%02d', $preMonth) . '&year=' . $preYear . '">Prev</a>' .
'<span class="title">' . date('Y M', strtotime($this->currentYear . '-' . $this->currentMonth . '-1')) . '</span>' .
'<a class="next" href="' . $this->naviHref . '?month=' . sprintf("%02d", $nextMonth) . '&year=' . $nextYear . '">Next</a>' .
'</div>';
}
/**
* create calendar week labels
*
* @return string
* @author The-Di-Lab <thedilab@gmail.com>
* @access private
*/
private function _createLabels()
{
if ($this->sundayFirst) {
$temp = $this->dayLabels[0];
for ($i = 1; $i < sizeof($this->dayLabels); $i++) {
$tmp = $this->dayLabels[$i];
$this->dayLabels[$i] = $temp;
$temp = $tmp;
}
$this->dayLabels[0] = $temp;
}
$content = '';
foreach ($this->dayLabels as $index => $label) {
$content .= '<li class="' . ($label == 6 ? 'end title' : 'start title') . ' title">' . $label . '</li>';
}
return $content;
}
/**
* create content for li element
*
* @param array
* @return string
* @author The-Di-Lab <thedilab@gmail.com>
* @access private
*/
private function _createCellContent($setting = false)
{
$this->cellContent = '';
$this->cellContent = $this->currentDay;
//observer
$this->notifyObserver('showCell');
return $this->cellContent;
}
/**
* calculate number of weeks in a particular month
*
* @param number
* @param number
* @return number
* @author The-Di-Lab <thedilab@gmail.com>
* @access private
*/
private function _weeksInMonth($month = null, $year = null)
{
if (null == ($year))
$year = date("Y", time());
if (null == ($month))
$month = date("m", time());
// find number of weeks in this month
$daysInMonths = $this->_daysInMonth($month, $year);
$numOfweeks = ($daysInMonths % 7 == 0 ? 0 : 1) + intval($daysInMonths / 7);
$monthEndingDay = date('N', strtotime($year . '-' . $month . '-' . $daysInMonths));
$monthStartDay = date('N', strtotime($year . '-' . $month . '-01'));
$monthEndingDay == 7 ? $monthEndingDay = 0 : '';
$monthStartDay == 7 ? $monthStartDay = 0 : '';
if ($monthEndingDay < $monthStartDay) {
$numOfweeks++;
}
return $numOfweeks;
}
/**
* calculate number of days in a particular month
*
* @param number
* @param number
* @return number
* @author The-Di-Lab <thedilab@gmail.com>
* @access private
*/
private function _daysInMonth($month = null, $year = null)
{
if (null == ($year))
$year = date("Y", time());
if (null == ($month))
$month = date("m", time());
return date('t', strtotime($year . '-' . $month . '-01'));
}
}
We are not going to explore the details of this class, since we have already covered it at https://www.startutorial.com/articles/view/php-calendar-class-with-hooks, feel free to read more there.
- Create a file BookableCell.php, copy and paste the following code:
<?php
class BookableCell
{
/**
* @var Booking
*/
private $booking;
private $currentURL;
/**
* BookableCell constructor.
* @param $booking
*/
public function __construct(Booking $booking)
{
$this->booking = $booking;
$this->currentURL = htmlentities($_SERVER['REQUEST_URI']);
}
public function update(Calendar $cal)
{
if ($this->isDateBooked($cal->getCurrentDate())) {
return $cal->cellContent =
$this->bookedCell($cal->getCurrentDate());
}
if (!$this->isDateBooked($cal->getCurrentDate())) {
return $cal->cellContent =
$this->openCell($cal->getCurrentDate());
}
}
public function routeActions()
{
if (isset($_POST['delete'])) {
$this->deleteBooking($_POST['id']);
}
if (isset($_POST['add'])) {
$this->addBooking($_POST['date']);
}
}
private function openCell($date)
{
return '<div class="open">' . $this->bookingForm($date) . '</div>';
}
private function bookedCell($date)
{
return '<div class="booked">' . $this->deleteForm($this->bookingId($date)) . '</div>';
}
private function isDateBooked($date)
{
return in_array($date, $this->bookedDates());
}
private function bookedDates()
{
return array_map(function ($record) {
return $record['booking_date'];
}, $this->booking->index());
}
private function bookingId($date)
{
$booking = array_filter($this->booking->index(), function ($record) use ($date) {
return $record['booking_date'] == $date;
});
$result = array_shift($booking);
return $result['id'];
}
private function deleteBooking($id)
{
$this->booking->delete($id);
}
private function addBooking($date)
{
$date = new DateTimeImmutable($date);
$this->booking->add($date);
}
private function bookingForm($date)
{
return
'<form method="post" action="' . $this->currentURL . '">' .
'<input type="hidden" name="add" />' .
'<input type="hidden" name="date" value="' . $date . '" />' .
'<input class="submit" type="submit" value="Book" />' .
'</form>';
}
private function deleteForm($id)
{
return
'<form onsubmit="return confirm(\'Are you sure to cancel?\');" method="post" action="' . $this->currentURL . '">' .
'<input type="hidden" name="delete" />' .
'<input type="hidden" name="id" value="' . $id . '" />' .
'<input class="submit" type="submit" value="Delete" />' .
'</form>';
}
}
Two major APIs of this class we should take a look at:
- Function update(Calendar $cal): this function is called when the Calendar class renders each cell. We render different content for the cell depends on its booking status. If this date is booked, we will render a cell with a cancellation link, otherwise, we render a booking link.
- Function routeActions(): this is a very simple routing function. It checks the $_POST parameter and decides whether to delete a booking or add a booking.
The BookableCell connects to the Calendar class via a hook, we call BookableCell a plugin for Calendar.
Putting it Together
Now it is time to put everything together and present our booking calendar to the world.
- Create a file index.php with the following code:
<html>
<head>
<link href="calendar.css" type="text/css" rel="stylesheet"/>
</head>
<body>
<?php
include 'Calendar.php';
include 'Booking.php';
include 'BookableCell.php';
$booking = new Booking(
'tutorial',
'localhost',
'root',
''
);
$bookableCell = new BookableCell($booking);
$calendar = new Calendar();
$calendar->attachObserver('showCell', $bookableCell);
$bookableCell->routeActions();
echo $calendar->show();
?>
</body>
</html>
Let's go through the code above from top to bottom.
- Instantiate a BookableCell plugin object.
- Instantiate a Calendar object.
- Hook the BookableCell object into Calendar object.
- Call the BookableCell object's routing function.
- Render the calendar.
- Last missing puzzle is the css file, create a file calendar.css, copy and paste the code below:
/*******************************Calendar Top Navigation*********************************/
body {
font-family: "Arial";
}
div#calendar {
margin: 0px auto;
padding: 0px;
width: 602px;
}
div#calendar div.box {
position: relative;
top: 0px;
left: 0px;
width: 100%;
height: 40px;
background-color: #3FA7D6;
}
div#calendar div.header {
line-height: 40px;
vertical-align: middle;
position: absolute;
left: 11px;
top: 0px;
width: 582px;
height: 40px;
text-align: center;
}
div#calendar div.header a.prev, div#calendar div.header a.next {
position: absolute;
top: 0px;
height: 17px;
display: block;
cursor: pointer;
text-decoration: none;
color: #FFF;
}
div#calendar div.header span.title {
color: #FFF;
font-size: 18px;
}
div#calendar div.header a.prev {
left: 0px;
}
div#calendar div.header a.next {
right: 0px;
}
/*******************************Calendar Content Cells*********************************/
div#calendar div.box-content {
border: 1px solid #3FA7D6;
border-top: none;
}
div#calendar ul.label {
float: left;
margin: 0px;
padding: 0px;
margin-top: 5px;
margin-left: 5px;
}
div#calendar ul.label li {
margin: 0px;
padding: 0px;
margin-right: 5px;
float: left;
list-style-type: none;
width: 80px;
height: 40px;
line-height: 40px;
vertical-align: middle;
text-align: center;
color: #000;
font-size: 15px;
background-color: transparent;
}
div#calendar ul.dates {
float: left;
margin: 0px;
padding: 0px;
margin-left: 5px;
margin-bottom: 5px;
}
/** overall width = width+padding-right**/
div#calendar ul.dates li {
margin: 0px;
padding: 0px;
margin-right: 5px;
margin-top: 5px;
line-height: 80px;
vertical-align: middle;
float: left;
list-style-type: none;
width: 80px;
height: 80px;
font-size: 25px;
background-color: #FFF;
color: #000;
text-align: center;
position: relative;
}
:focus {
outline: none;
}
div.clear {
clear: both;
}
li div {
display: flex;
}
li div form {
display: inline;
align-self: center;
margin: 0;
position: absolute;
bottom: 2px;
}
div.open {
background: #59CD90;
width: 100%;
height: 100%;
display: flex;
justify-content: center;
flex-direction: column;
text-align: center;
}
div.booked {
background: #D36135;
width: 100%;
height: 100%;
display: flex;
justify-content: center;
flex-direction: column;
text-align: center;
line-height: inherit;
}
.submit {
box-shadow:inset 0px 1px 0px 0px #ffffff;
background:linear-gradient(to bottom, #ffffff 5%, #f6f6f6 100%);
background-color:#ffffff;
border-radius:3px;
border:1px solid #dcdcdc;
display:inline-block;
cursor:pointer;
color:#666666;
font-size:10px;
font-weight:bold;
padding:3px 12px;
text-decoration:none;
text-shadow:0px 1px 0px #ffffff;
}
.submit:hover {
background:linear-gradient(to bottom, #f6f6f6 5%, #ffffff 100%);
background-color:#f6f6f6;
}
.submit:active {
position:relative;
top:1px;
}
Final and Source Code
If you have followed along correctly. Head over to index.php from your browser. You should see a beautiful PHP booking calendar as shown below:
If you follow along with the tutorial step by step, you will get all the source code in place. However, if you are feeling lazy or need to download the complete source code from us. You can do so by paying us a small fee. Your support will enable us to produce better and more in-depth tutorials.