Welcome to a tutorial on how to create a simple venue booking system with PHP and MySQL. I am pretty sure there are plenty of such “booking” or “reservation” systems out there already. So, here’s one that is slightly different – A barebones version that you can build upon, without any third-party frameworks. Read on!
TABLE OF CONTENTS
PHP MYSQL VENUE BOOKING
All right, let us now get into the details of building a venue booking system with PHP and MySQL.
PART 1) THE DATABASE
1A) VENUE TABLE
-- (A) VENUE
CREATE TABLE `venue` (
`venue_id` bigint(20) NOT NULL,
`venue_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `venue`
ADD PRIMARY KEY (`venue_id`),
ADD KEY `venue_name` (`venue_name`);
ALTER TABLE `venue`
MODIFY `venue_id` bigint(20) NOT NULL AUTO_INCREMENT;
First, let us start with a simple table to hold all the available venues.
venue_id
Primary key and auto-increment.venue_name
Name of the venue.
Feel free to add more fields as required – Opening days and hours, number of rooms, capacity, etc…
1B) BOOKING TABLE
-- (B) BOOKING
CREATE TABLE `booking` (
`book_id` bigint(20) NOT NULL,
`venue_id` bigint(20) NOT NULL,
`book_date` date DEFAULT NULL,
`book_slot` varchar(32) DEFAULT NULL,
`book_name` varchar(255) NOT NULL,
`book_email` varchar(255) NOT NULL,
`book_tel` varchar(60) NOT NULL,
`book_notes` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `booking`
ADD PRIMARY KEY (`book_id`),
ADD KEY `venue_id` (`venue_id`),
ADD KEY `book_date` (`book_date`),
ADD KEY `book_slot` (`book_slot`),
ADD KEY `book_name` (`book_name`),
ADD KEY `book_email` (`book_email`),
ADD KEY `book_tel` (`book_tel`);
ALTER TABLE `booking`
MODIFY `book_id` int(11) NOT NULL AUTO_INCREMENT;
Next, a table to store all the booking entries.
book_id
Primary key and auto-increment.venue_id
Foreign key. The venue being booked.book_date
Date booked for.book_slot
Time slot booked for. This is an open text field, you can pretty much define any time slots you want.book_name book_email book_tel
Name, email, and telephone of the person who booked the venue.book_notes
Optional notes for the booking.
Once again, feel free to add or remove the fields that you want to capture.
1C) DUMMY DATA
-- (C) DUMMY ENTRIES
INSERT INTO `venue` (`venue_id`, `venue_name`) VALUES (NULL, 'Room A'), (NULL, 'Room B'), (NULL, 'Room C');
Finally, just 3 dummy rooms to kickstart the demo.
PART 2) PHP BOOKING LIBRARY
<?php
class Booking {
// (A) CONSTRUCTOR - CONNECT TO DATABASE
private $pdo;
private $stmt;
public $error;
function __construct () {
$this->pdo = new PDO(
"mysql:host=". DB_HOST .";dbname=". DB_NAME .";charset=". DB_CHARSET,
DB_USER, DB_PASSWORD, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_NAMED
]);
}
// (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
function __destruct () {
$this->pdo = null;
$this->stmt = null;
}
// (C) HELPER FUNCTION - RUN SQL QUERY
function query ($sql, $data=null) {
$this->stmt = $this->pdo->prepare($sql);
$this->stmt->execute($data);
}
// (D) GET ALL VENUES
function getVenue () {
$this->query("SELECT * FROM `venue`");
$venue = [];
while ($r = $this->stmt->fetch()) { $venue[$r["venue_id"]] = $r["venue_name"]; }
return $venue;
}
// (E) GET BOOKING FOR THE DAY
function getBooking ($id, $day="") {
// (E1) DEFAULT TO TODAY
if ($day=="") { $day = date("Y-m-d"); }
// (E2) GET ENTRIES
$booking = [];
$this->query(
"SELECT * FROM `booking` WHERE `venue_id`=? AND `book_date`=?",
[$id, $day]
);
while ($r = $this->stmt->fetch()) { $booking[$r["book_slot"]] = $r; }
return $booking;
}
// (F) SAVE BOOKING
function save ($id, $date, $slot, $name, $email, $tel, $notes="") {
// (F1) CHECKS & RESTRICTIONS
// @TODO - ADD YOUR OWN RULES & REGULATIONS HERE
// MUST BE REGISTERED USER TO BOOK?
// MAX # OF RESERVATIONS ALLOWED?
// USER CAN ONLY BOOK X DAYS IN ADVANCE?
// USER CAN ONLY BOOK A MAX OF X SLOTS WITHIN Y DAYS?
// (F2) DATABASE ENTRY
$this->query(
"INSERT INTO `booking` (`venue_id`, `book_date`, `book_slot`, `book_name`, `book_email`, `book_tel`, `book_notes`) VALUES (?,?,?,?,?,?,?)",
[$id, $date, $slot, $name, $email, $tel, $notes]
);
// (F3) CONFIRMATION
// @TODO - UP TO YOU TO COMPLETE. THIS WILL JUST SEND AN EMAIL TO THE USER.
$subject = "Booking Received";
$message = "Thank you, we have received your request and will process it shortly.";
@mail($email, $subject, $message);
return true;
}
}
// (G) DATABASE SETTINGS - CHANGE THESE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");
// (H) NEW BOOKING OBJECT
$_BOOK = new Booking();
Now that we have established the database, we will create a PHP library to work with it. This looks massive at first, but keep calm and look carefully.
- (A & B) When is
$_BOOK = new Booking()
created, the constructor will automatically connect to the database. The destructor will close the database connection. - (C) A helper function to run an SQL query.
- (D to F) The “actual” booking functions.
getVenue()
Get all available venues.getBooking()
Get all bookings for the selected venue and date.save()
Save a booking entry. Complete this function on your own – Do your own checks, and the “post-process”.
- (G & H) Self-explanatory. Remember to change the database settings to your own.
PART 3) PHP BOOKING AJAX HANDLER
<?php
if (isset($_POST["req"])) {
require "2-lib-booking.php";
switch ($_POST["req"]) {
// (A) CHECK BOOKING FOR GIVEN VENUE/DAY
case "check":
echo json_encode($_BOOK->getBooking($_POST["id"], $_POST["date"]));
break;
// (B) SAVE
case "save":
echo $_BOOK->save(
$_POST["venue"], $_POST["date"], $_POST["slot"],
$_POST["name"], $_POST["email"], $_POST["tel"], $_POST["notes"])
? "OK" : $_BOOK->error;
break;
}}
Before we proceed with the HTML booking page, let us create an AJAX endpoint. How this page work is very simple, just send:
$_POST["req"]
to specify a request.- Followed by the required parameters.
For example, to check for the booking for a specified venue and date, just send $_POST["req"] = "check"
, $_POST["id"] = N
, and $_POST["date"] = "YYYY-MM-DD"
.
PART 4) HTML BOOKING PAGE
4A) THE HTML
<?php
// (A) "PHP STUFF"
require "2-lib-booking.php"; // load library
$venue = $_BOOK->getVenue(); // get venues
$mindate = date("Y-m-d"); // min selectable date
// $mindate = date("Y-m-d", strtotime("+2 days"));
?>
<!-- (B) BOOKING FORM -->
<form id="bookForm" method="post" onsubmit="return book.save()">
<!-- (B1) VENUE + TIME -->
<fieldset>
<legend>Venue & Time</legend>
<label>Room</label>
<select id="book_venue" name="venue" onchange="book.check()"><?php
foreach ($venue as $id=>$name) { echo "<option value='$id'>$name</option>"; }
?></select>
<div class="flex">
<div class="flex-grow">
<label>Date</label>
<input type="date" required id="book_date" name="date" onchange="book.check()"
min="<?=$mindate?>" value="<?=$mindate?>">
</div>
<div>
<label>Slot</label>
<select id="book_slot" name="slot">
<option value="AM">AM</option>
<option value="PM">PM</option>
</select>
</div>
</div>
<label>Notes (if any)</label>
<input type="text" name="notes" value="Testing">
</fieldset>
<!-- (B2) CONTACT PERSON -->
<fieldset>
<legend>Contact Person</legend>
<label>Name</label>
<input type="text" name="name" required value="Jon Doe">
<div class="flex">
<div class="flex-grow">
<label>Email</label>
<input type="email" name="email" required value="jon@doe.com">
</div>
<div class="flex-grow">
<label>Telephone</label>
<input type="text" name="tel" required value="123456789">
</div>
</div>
</fieldset>
<!-- (B3) SUBMIT -->
<input id="book_go" type="submit" value="Submit" disabled>
</form>
Well, this “massive HTML form” is just… an HTML form. Feel free to add/remove the fields that you need.
4B) JAVASCRIPT – CHECK SLOT AVAILABILITY
var book = {
// (A) CHECK SLOT AVAILABILTY
check : () => {
// (A1) GET FORM ELEMENTS
let venue = document.getElementById("book_venue"),
date = document.getElementById("book_date"),
slot = document.getElementById("book_slot"),
go = document.getElementById("book_go");
// (A2) DISABLE SLOTS & SUBMIT
slot.disabled = true;
go.disabled = true;
// (A3) CHECK SLOT AVAILABILTY WITH SERVER
let data = new FormData();
data.append("req", "check");
data.append("id", venue.value);
data.append("date", date.value);
// (A4) FETCH
fetch("3-ajax-booking.php", { method: "POST", body: data })
.then(res => res.json())
.then(booked => {
// (A4-1) ALL SLOTS BOOKED FOR THE DAY
if (Object.keys(booked).length>=2) {
slot.innerHTML = "<option>NA</option>";
}
// (A4-2) SLOTS AVAILABLE
else {
slot.innerHTML = "";
for (let s of ["AM", "PM"]) { if (booked[s]==undefined) {
let o = document.createElement("option");
o.innerHTML = s;
o.value = s;
slot.appendChild(o);
}}
slot.disabled = false;
go.disabled = false;
}
});
},
// ...
};
window.addEventListener("load", book.check);
Remember the AJAX handler? Whenever the user changes the venue and date, we will have to fire a fetch
request to the AJAX handler to check for slot availability and update the HTML interface accordingly.
4C) JAVASCRIPT – SUBMIT FORM
// (B) SUBMIT BOOKING
save : () => {
// (B1) FORM DATA
let data = new FormData(document.getElementById("bookForm"));
data.append("req", "save");
// (B2) FETCH SUBMIT FORM
document.getElementById("book_go").disabled = true;
fetch("3-ajax-booking.php", { method: "POST", body: data })
.then(res => res.text())
.then(txt => {
if (txt == "OK") {
alert("@TODO - REDIRECT TO 'THANK YOU' OR DO SOMETHING ELSE");
location.reload();
} else { alert(txt); }
})
.finally(() => {
document.getElementById("book_go").disabled = false;
});
// (B3) PREVENT FORM SUBMIT
return false;
}
The last step of the process, submit the form to the server… Do whatever you need to after that – Redirect the user to a “thank you” page, process payment, or whatever.
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
SORRY FOR THE ADS...
But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.
Buy Me A Coffee Code Boxx eBooks
EXAMPLE CODE DOWNLOAD
Click here for the source code on GitHub gist, just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.
EXTRA BITS & LINKS
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
EXTRA) CSV BOOKING REPORT
<?php
// (A) LOAD LIBRARY
require "2-lib-booking.php";
// (B) OUTPUT AS CSV
header("Content-Type: text/csv");
header("Content-Disposition: attachment;filename=booking.csv");
// (C) OUTPUT ALL BOOKING FOR THE DAY
$today = date("Y-m-d");
$fout = fopen("php://output", "w");
foreach ($_BOOK->getVenue() as $vid=>$v) {
$book = $_BOOK->getBooking($vid, $today);
if (count($book)>0) { foreach ($book as $slot=>$b)
fputcsv($fout, [$v, $b["book_date"], $slot, $b["book_name"], $b["book_email"], $b["book_tel"], $b["book_notes"]]);
}
}
fclose($fout);
How do we use the library to create reports? Here is a small example that will fetch all the reservations for the day, and generate a CSV report with it.
COMPLETE YOUR OWN SYSTEM
A fair reminder that this is only a tutorial. Everyone has a different starting point, everyone has different requirements. To answer some of the common questions:
- Admin, user, and login system – See links below.
- Date range and time slots –
- If you are offering hourly slots, just change AM/PM to slots such as “1000-1100” and “1100-1200”.
- If you are offering a range of dates, it will make sense to change the database table to “start date” and “end date”.
- For both date range and time slot, it will be “start date”, “start slot”, “end date”, and “end slot”.
- Venue capacity and rooms –
- As above, add a “capacity” field to the
venue
table. - Change the
getBooking()
function to count the available capacity. - Change the HTML form
4a-book.php
to select rooms (if any) – Check out the “seat reservation” link below, just use it as “room reservation”.
- As above, add a “capacity” field to the
Yes, it is impossible to create a “one size fits all” system, I cannot offer free consultations to everyone too – The rest is up to you.
COMPATIBILITY CHECKS
- Arrow Functions – CanIUse
- Fetch – CanIUse
Works on all modern “Grade A” browsers.
LINKS & REFERENCES
- PHP Login System Tutorials on Code Boxx – Simple | No Database | Using JWT
- Admin Page Template – Code Boxx
- JS Seat Reservation – Code Boxx
THE END
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!