Simple Venue Booking With PHP MySQL (Free Download)

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

 

 

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) 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 &amp; 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.

 

 

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”.

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