Simple Hotel Booking Management PHP MYSQL (Free Download)

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

 

 

EXAMPLE CODE DOWNLOAD

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.

 

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

 

 

 

 

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.

1. Some date range calculations. To restrict the min/max selectable dates.
2. “Step 1” – Select a date.
3. “Step 2” – Choose a room.
4. “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 to 3-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” into rsv.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.

 

 

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