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

 

 

 

 

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.

 

 

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.

 

 

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