Welcome to a tutorial on how to create a hotel booking management system with PHP and MySQL. Looking to open some rooms up for booking on the Internet? Need a simple project or example to get started? Here is a quick sharing of my own, without using any third-party frameworks – Read on!
TABLE OF CONTENTS
HOTEL BOOKING WITH PHP MYSQL
All right, let us now get into more details on how to create a hotel booking system with PHP and MySQL.
PART 1) THE DATABASE
1A) HOTEL ROOMS TABLE
-- (A) ROOMS
CREATE TABLE `rooms` (
`room_id` varchar(255) NOT NULL,
`room_type` varchar(1) NOT NULL,
`room_price` decimal(12,2) DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `rooms`
ADD PRIMARY KEY (`room_id`),
ADD KEY `room_type` (`room_type`);
INSERT INTO `rooms` (`room_id`, `room_type`, `room_price`) VALUES
('#01-A', 'S', '10.00'),
('#01-B', 'S', '10.00'),
('#02-A', 'D', '20.00'),
('#02-B', 'T', '20.00'),
('#03-A', 'B', '30.00'),
('#04-A', 'P', '40.00');
First, let us start with the obvious – We need a database to store the rooms and reservations. This is a simple one to keep track of the available rooms:
room_id
The room number, primary key.room_type
For this example, we will use:- Single.
- Twin.
- Double.
- Business.
- Presidential.
room_price
Price per day, per hour, or per slot. For this example, we are going to assume “per day”.
Feel free to add more fields as required.
1B) RESERVATIONS TABLE
-- (B) RESERVATIONS
CREATE TABLE `reservations` (
`reservation_id` bigint(20) NOT NULL,
`room_id` varchar(255) NOT NULL,
`reservation_start` date NOT NULL,
`reservation_end` date NOT NULL,
`reservation_name` varchar(255) NOT NULL,
`reservation_email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `reservations`
ADD PRIMARY KEY (`reservation_id`),
ADD KEY `reservation_name` (`reservation_name`),
ADD KEY `reservation_email` (`reservation_email`),
ADD KEY `room_id` (`room_id`);
ALTER TABLE `reservations`
MODIFY `reservation_id` bigint(20) NOT NULL AUTO_INCREMENT;
Next, this table will store all the reservations.
reservation_id
Primary key, auto-increment.room_id
Reserved room.reservation_start
reservation_end
Start and end date.reservation_name
Customer’s name.reservation_email
Customer’s email.
Once again, feel free to add more fields as required.
PART 2) PHP LIBRARY
<?php
class Hotel {
// (A) CONSTRUCTOR - CONNECT TO THE DATABASE
private $pdo = null;
private $stmt = null;
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_ASSOC
]);
}
// (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
function __destruct () {
if ($this->stmt !== null) { $this->stmt = null; }
if ($this->pdo !== null) { $this->pdo = null; }
}
// (C) HELPER - RUN SQL QUERY
function query ($sql, $data=null) : void {
$this->stmt = $this->pdo->prepare($sql);
$this->stmt->execute($data);
}
// (D) SAVE ROOM
function save ($id, $type, $price, $oid=null) {
// (D1) SQL & DATA
$sql = $oid==null
? "INSERT INTO `rooms` (`room_id`, `room_type`, `room_price`) VALUES (?,?,?)"
: "UPDATE `rooms` SET `room_id`=?, `room_type`=?, `room_price`=? WHERE `room_id`=?" ;
$data = [$id, $type, $price];
if ($oid!=null) { $data[] = $oid; }
// (D2) RUN SQL
$this->query($sql, $data);
return true;
}
// (E) GET ROOMS FOR SELECTED DATE RANGE
function get ($from=null, $to=null) {
// (E1) GET ALL ROOMS
$this->query("SELECT * FROM `rooms`");
$rooms = [];
while ($r = $this->stmt->fetch()) {
$rooms[$r["room_id"]] = [
"t" => ROOM_TYPE[$r["room_type"]],
"p" => $r["room_price"]
];
}
// (E2) INCLUDE RESERVATIONS
if ($from && $to) {
$this->query(
"SELECT * FROM `reservations`
WHERE (`reservation_start` BETWEEN ? AND ?)
OR (`reservation_end` BETWEEN ? AND ?)",
[$from, $to, $from, $to]
);
while ($r = $this->stmt->fetch()) { if (isset($rooms[$r["room_id"]])) {
// (E2-1) ASSUMPTION - MORNING CHECKOUT + AFTERNOON CHECKIN
// ALLOW "SAME DAY RESERVATION" IF END DATE IS SAME
if ($r["reservation_end"] == $from) { continue; }
// (E2-2) MARK AS "BOOKED"
if (!isset($rooms[$r["room_id"]]["b"])) { $rooms[$r["room_id"]]["b"] = []; }
$rooms[$r["room_id"]]["b"][] = [
"s" => $r["reservation_start"],
"e" => $r["reservation_end"]
];
}}
}
// (E3) RETURN RESULTS
return $rooms;
}
// (F) SAVE RESERVATION
function reserve ($id, $start, $end, $name, $email) {
$this->query(
"INSERT INTO `reservations` (`room_id`, `reservation_start`, `reservation_end`, `reservation_name`, `reservation_email`)
VALUES (?,?,?,?,?)", [$id, $start, $end, $name, $email]
);
return true;
}
}
// (G) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");
// (H) ROOM TYPES & RULES
define("ROOM_TYPE", [
"S" => "Single", "D" => "Double", "T" => "Twin",
"B" => "Business", "P" => "Presidential"
]);
define("MIN_BOOK", 1); // min next day
define("MAX_BOOK", 30); // max next month
define("MIN_STAY", 1); // min 1 day stay
define("MAX_STAY", 7); // max 7 days stay
// (I) START
$_HOTEL = new hotel();
This PHP library is pretty much the “engine”. It looks complicated at first, but keep calm and look carefully.
- (A, B, I) When
$_HOTEL = new hotel()
is created, the constructor connects to the database. The destructor closes the connection. - (C)
query()
A helper function to run an SQL query. - (D to F) The actual “hotel functions”.
save()
Add or update an existing room.get()
Get hotel rooms, and include their reservation status for the given date range.reserve()
Save a reservation.
- (G) Database settings, remember to change to your own.
- (H) The room types, and a couple of reservation rules.
PART 3) AJAX HANDLER
<?php
if (isset($_POST["req"])) {
require "2-lib-hotel.php";
switch ($_POST["req"]) {
// (A) GET AVAILABLE ROOMS
case "get":
echo json_encode($_HOTEL->get($_POST["from"], $_POST["to"]));
break;
// (B) RESERVE ROOM
case "reserve":
echo $_HOTEL->reserve($_POST["id"], $_POST["start"], $_POST["end"], $_POST["name"], $_POST["email"])
? "OK" : $_HOTEL->error ;
break;
}
}
With the library in place, we can now create an AJAX endpoint to handle requests. This script pretty much “maps” $_POST
to library functions.
- Just send the request
$_POST["req"]
to this script, along with the required parameters. - E.G.
$_POST["req"]="get"
,$_POST["from"]
, and$_POST["to"]
to get available rooms for the given period.
PART 4) RESERVATION PAGE
4A) THE HTML
<?php
// (A) SOME DATE CALCULATIONS
require "2-lib-hotel.php";
$min = date("Y-m-d", strtotime("+".MIN_BOOK."days"));
$max = date("Y-m-d", strtotime("+".MAX_BOOK."days"));
?>
<!-- (B) SELECT DATE -->
<form id="sDate" onsubmit="return rsv.get()">
<div class="step">STEP 1 OF 3</div>
<h1 class="head">SELECT DATE</h1>
<label>Check In</label>
<input type="date" id="sDateFrom"
min="<?=$min?>" max="<?=$max?>" value="<?=$min?>">
<label>Staying For (Days)</label>
<input type="number" id="sDateTo"
min="<?=MIN_STAY?>" max="<?=MAX_STAY?>" value="<?=MIN_STAY?>">
<input type="submit" value="Next" class="button">
</form>
<!-- (C) SELECT ROOM -->
<div id="sRoom" class="hide"></div>
<!-- (D) ENTER CONTACT INFO -->
<form id="sContact" class="hide" onsubmit="return rsv.reserve()">
<div class="step">STEP 3 OF 3</div>
<h1 class="head">CONTACT INFO</h1>
<label>Name</label>
<input type="text" name="name" required>
<label>Email</label>
<input type="email" name="email" required>
<input type="submit" value="Submit" class="button">
<input type="button" value="Back" class="button" onclick="rsv.switch(1)">
</form>
This is the single page to accept reservations.
- Some date range calculations. To restrict the min/max selectable dates.
- “Step 1” – Select a date.
- “Step 2” – Choose a room.
- “Step 3” – Enter particulars. Feel free to capture more fields as required.
4B) JAVASCRIPT – AJAX HELPER
var rsv = {
// (A) HELPER - AJAX FETCH
fetch : (data, after) => {
// (A1) FORM DATA
let form;
if (data instanceof FormData) { form = data; }
else {
form = new FormData();
for (let [k, v] of Object.entries(data)) { form.append(k, v); }
}
// (A2) FETCH
fetch("3-ajax-hotel.php", { method : "post", body : form })
.then(res => res.text())
.then(txt => after(txt))
.catch(err => console.error(err));
},
};
var rsv
is an object that contains all the Javascript to drive the HTML interface.- The very first
fetch()
is a helper function to do an AJAX call to3-ajax-hotel.php
.
4C) JAVASCRIPT – INITIALIZE & HTML INTERFACE
// (B) PROPERTIES
hSec : null, // html sections
date : null, // currently selected date
room : null, // currently selected room
// (C) INITIALIZE - GET HTML SECTIONS
init : () => rsv.hSec = [
document.getElementById("sDate"),
document.getElementById("sRoom"),
document.getElementById("sContact")
],
// (D) SWITCH HTML SECTIONS
switch : i => { for (let j in rsv.hSec) {
if (i==j) { rsv.hSec[j].classList.remove("hide"); }
else { rsv.hSec[j].classList.add("hide"); }
}},
window.onload = rsv.init;
- (B & C) On page load,
rsv.init()
will run. All this does is to get the “3 HTML steps” intorsv.hSec
as an array. - (D)
rsv.switch()
Another helper function to switch between the “3 HTML steps”.
4D) JAVASCRIPT – GET ROOMS
// (E) GET ROOMS FOR SELECTED DATE PERIOD
get : () => {
// (E1) GET DATE
rsv.date = {
days : parseInt(document.getElementById("sDateTo").value),
from : document.getElementById("sDateFrom").value
}
rsv.date.to = new Date(rsv.date.from);
rsv.date.to = new Date(
rsv.date.to.setDate(rsv.date.to.getDate() + rsv.date.days)
).toISOString().substring(0, 10);
// (E2) FETCH ROOMS
rsv.fetch({ req : "get", ...rsv.date }, res => {
// (E2-1) DRAW SELECTED DATE
rsv.hSec[1].innerHTML = "";
let row = document.createElement("div");
row.className = "rHead";
row.innerHTML = `<div class="step">STEP 2 OF 3</div>
<h1 class="head">SELECT A ROOM</h1>
<div class="step">FROM ${rsv.date.from} TO ${rsv.date.to} (${rsv.date.days} DAYS)</div>`;
rsv.hSec[1].appendChild(row);
// (E2-2) DRAW ROOMS
for (let [i,r] of Object.entries(JSON.parse(res))) {
row = document.createElement("div");
row.className = "rRow";
row.innerHTML = `<div class="rType">${r.t}</div>`;
if (r.b) {
row.classList.add("rBooked");
let s = '<div class="rStat">This room is booked.<ul>';
for (let b of r.b) { s += `<li>${b.s} (PM) to ${b.e} (AM)</li>`; }
s += '</ul></div>';
row.innerHTML += s;
} else {
row.innerHTML += `<div class="rStat">
Reserve this room for $${(rsv.date.days * r.p).toFixed(2)}.
</div>`;
row.onclick = () => rsv.set(i);
}
rsv.hSec[1].appendChild(row);
}
// (E2-3) BACK BUTTON
row = document.createElement("input");
row.type = "button";
row.className = "button";
row.value = "Back";
row.onclick = () => rsv.switch(0);
rsv.hSec[1].appendChild(row);
// (E2-4) DONE - SHOW PAGE
rsv.switch(1);
});
return false;
},
This is “step 2” of the HTML interface, after the user chooses the date. rsv.get()
will fetch the available rooms from 3-ajax-hotel.php
, and draw them in an HTML list.
4E) JAVASCRIPT – SUBMIT RESERVATION FORM
// (F) SELECT ROOM
set : id => {
rsv.room = id;
rsv.switch(2);
},
// (G) SUBMIT RESERVATION
reserve : () => {
// (G1) FORM DATA
let data = new FormData(rsv.hSec[2]);
data.append("req", "reserve");
data.append("id", rsv.room);
data.append("start", rsv.date.from);
data.append("end", rsv.date.to);
// (G2) AJAX FETCH
rsv.fetch(data, res => {
if (res=="OK") { location.href = "5-thank-you.html"; }
else { alert(res); }
});
return false;
}
This is “step 3” of the HTML. When the user chooses a room, enters the particulars, and submits the reservation request.
PART 5) THANK YOU PAGE
<h1>Thank You</h1>
<p>Reservation received.</p>
Lastly, this is nothing more than a dummy “thank you” page.
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
SUPPORT
600+ free tutorials & projects on Code Boxx and still growing. I insist on not turning Code Boxx into a "paid scripts and courses" business, so every little bit of support helps.
Buy Me A Meal 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.
IMPROVEMENT IDEAS
This is ultimately a simple tutorial and barebones example. A lot more can be done, and should be done:
- The current assumption is “afternoon check-in, morning checkout”. Some of you guys may be thinking “book by AM/PM slot” or “book by the hourly slots”. For that, do your own changes to the database, library, and HTML/Javascript.
- Complete your own checkout process.
- If you only allow registered users to book, or want to automatically create accounts for them – Check out the links below.
- Link up with payment processors – Paypal, Stripe, Apple Pay, Google Pay, etc…
- Send confirmation email/sms/call.
- Nice “thank you” page.
- Create your own “admin panel” to better manage things. Links below.
- Create your own reports, and add them to the PHP library. Can be a simple
SELECT * FROM `reservations` WHERE (start BETWEEN ? AND ?) OR (end BETWEEN ? AND ?)
.
The possibilities are endless, it is impossible to cover everything. Nor can I give free consultations – It’s up to you now to complete your own system.
COMPATIBILITY CHECKS
- Arrow Functions – CanIUse
- Fetch – CanIUse
- Template Literals – CanIUse
This example should work on most modern browsers.
LINKS & REFERENCES
- Simple User Login System With PHP MySQL – Code Boxx
- How To Send Email In PHP (Very Simple Examples) – Code Boxx
- Simple User Role Management System PHP MySQL – Code Boxx
- Simple PHP MySQL Admin Panel – 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!