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

 

 

 

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.

 

 

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) 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