Simple Seat Reservation With PHP MySQL (Free Download)

Welcome to a tutorial on how to create a simple seat reservation system with PHP and MySQL. Need to offer seat reservations for a vehicle, room, or auditorium? Well, here is a simple one that I have made – Read on!

 

 

TABLE OF CONTENTS

 

 

 

 

PART 1) THE DATABASE

1A) SEATS

-- (A) SEATS
CREATE TABLE `seats` (
  `seat_id` varchar(16) NOT NULL,
  `room_id` varchar(16) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `seats`
  ADD PRIMARY KEY (`seat_id`,`room_id`);

• seat_id Primary key.
• room_id Primary key.

This should be self-explanatory, the available seats that each room has. Just change room_id to vehicle_id if it is for passenger service.

 

1B) SESSIONS

-- (B) SESSIONS
CREATE TABLE `sessions` (
  `session_id` bigint(20) NOT NULL,
  `room_id` varchar(16) NOT NULL,
  `session_date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `sessions`
  ADD PRIMARY KEY (`session_id`),
  ADD KEY `room_id` (`room_id`),
  ADD KEY `session_date` (`session_date`);
 
ALTER TABLE `sessions`
  MODIFY `session_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

• session_id Primary key, auto-increment.
• room_id Foreign key, the session will be held in this room.
• session_date Date when the session is happening.

Next, we have a “sessions” table to hold what event is happening in which room and date. You can rename this to “trips” for passenger services, or maybe even “lessons” for education services.

 

 

1C) RESERVATIONS

-- (C) RESERVATIONS
CREATE TABLE `reservations` (
  `session_id` bigint(20) NOT NULL,
  `seat_id` varchar(16) NOT NULL,
  `user_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `reservations`
  ADD PRIMARY KEY (`session_id`,`seat_id`,`user_id`);

• session_id Primary and foreign key.
• seat_id Primary and foreign key.
• user_id Primary and foreign key.

Lastly, a table to record the reservations – Which user made a reservation for which session and seat(s).

 

1D) DUMMY DATA

-- (D) DUMMY DATA
-- (D1) DUMMY SEATS
INSERT INTO `seats` (`seat_id`, `room_id`) VALUES
('A1', 'ROOM-A'),
('A2', 'ROOM-A'),
('A3', 'ROOM-A'),
('A4', 'ROOM-A'),
('B1', 'ROOM-A'),
('B2', 'ROOM-A'),
('B3', 'ROOM-A'),
('B4', 'ROOM-A'),
('C1', 'ROOM-A'),
('C2', 'ROOM-A'),
('C3', 'ROOM-A'),
('C4', 'ROOM-A');
 
-- (D2) DUMMY SESSION
INSERT INTO `sessions` (`session_id`, `room_id`, `session_date`) VALUES
(1, 'ROOM-A', '2077-06-05 08:00:00');
 
-- (D3) DUMMY RESERVATION
INSERT INTO `reservations` (`session_id`, `seat_id`, `user_id`) VALUES
('1', 'B2', '555'),
('1', 'A4', '888');

To help you better understand, here are some dummy data and examples

• (D1) Here, we have a single ROOM-A with 12 seats.
• (D2) Then, the room is open for booking in CyberPunk 2077.
• (D3) Dummy reservations – User 555 booked seat B2, and user 888 booked seat A4.

 

 

PART 2) PHP RESERVATIONS LIBRARY

<?php
class Reserve {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = null;
  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 FUNCTION - RUN SQL QUERY
  function query ($sql, $data=null) : void {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }
 
  // (D) GET SEATS FOR GIVEN SESSION
  function get ($sessid) {
    $this->query(
      "SELECT sa.`seat_id`, r.`user_id` FROM `seats` sa
      LEFT JOIN `sessions` se USING (`room_id`)
      LEFT JOIN `reservations` r USING(`seat_id`)
      WHERE se.`session_id`=?
      ORDER BY sa.`seat_id`", [$sessid]
    );
    $sess = $this->stmt->fetchAll();
    return $sess;
  }
 
  // (E) SAVE RESERVATION
  function save ($sessid, $userid, $seats) {
    $sql = "INSERT INTO `reservations` (`session_id`, `seat_id`, `user_id`) VALUES ";
    $data = [];
    foreach ($seats as $seat) {
      $sql .= "(?,?,?),";
      array_push($data, $sessid, $seat, $userid);
    }
    $sql = substr($sql, 0, -1);
    $this->query($sql, $data);
    return true;
  }
}
 
// (F) 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", "");
 
// (G) NEW CATEGORY OBJECT
$_RSV = new Reserve();

This may seem confusing at first, but keep calm and look closely.

• (A, B, G) The constructor automatically connects to the database when $_RSV = new Reserve() is created. The destructor closes the connection.
• (C) function query() A helper function to run an SQL query.
• (D & E) There are only 2 functions for this project!
  • function get() Get the seats for the given session.
  • function save() Save the given reservation request. Doh.
• (F) Remember to change the database settings to your own.

 

 

PART 3) RESERVATION PAGE

3A) THE HTML

<?php
// (A) FIXED FOR THIS DEMO
$sessid = 1;
$userid = 999;
 
// (B) GET SESSION SEATS
require "2-reserve-lib.php";
$seats = $_RSV->get($sessid);
?>
 
<!-- (C) DRAW SEATS LAYOUT -->
<div id="layout"><?php
foreach ($seats as $s) {
  $taken = is_numeric($s["user_id"]);
  printf("<div class='seat%s'%s>%s</div>",
    $taken ? " taken" : "",
    $taken ? "" : " onclick='reserve.toggle(this)'",
    $s["seat_id"]
  );
}
?></div>
 
<!-- (D) LEGEND -->
<div id="legend">
  <div class="seat"></div> <div class="txt">Open</div>
  <div class="seat taken"></div> <div class="txt">Taken</div>
  <div class="seat selected"></div> <div class="txt">Your Selected Seats</div>
</div>
 
<!-- (E) SAVE SELECTION -->
<form id="ninja" method="post" action="4-save.php">
  <input type="hidden" name="sessid" value="<?=$sessid?>">
  <input type="hidden" name="userid" value="<?=$userid?>">
</form>
<button id="go" onclick="reserve.save()">Reserve Seats</button>

This HTML page should be pretty self-explanatory once you read through section-by-section.

1. The session ID and user ID are fixed for this demo – Tie these into your own system.
2. Use the PHP library, to get the seats for the given session.
3. <div id="layout"> Choose a seat.
4. <div id="legend"> “Color code” for the seats.
5. <form id="ninja"> A hidden form to submit the chosen seats that the user has chosen.

 

3B) THE JAVASCRIPT

var reserve = {
  // (A) CHOOSE THIS SEAT
  toggle : seat => seat.classList.toggle("selected"),
 
  // (B) SAVE RESERVATION
  save : () => {
    // (B1) GET SELECTED SEATS
    var selected = document.querySelectorAll("#layout .selected");
 
    // (B2) ERROR!
    if (selected.length == 0) { alert("No seats selected."); }
 
    // (B3) NINJA FORM SUBMISSION
    else {
      var ninja = document.getElementById("ninja");
      for (let seat of selected) {
        let input = document.createElement("input");
        input.type = "hidden";
        input.name = "seats[]";
        input.value = seat.innerHTML;
        ninja.appendChild(input);
      }
      ninja.submit();
    }
  }
};

Just some simple Javascript to toggle the seat selection, and submit the hidden form to confirm the reservation. Take note, I assumed that the user can book multiple seats here – If that is not the case, change the Javascript on your own.

 

 

3C) CONFIRMATION PAGE

<?php
// (A) LOAD LIBRARY
require "2-reserve-lib.php";
 
// (B) SAVE
$_RSV->save($_POST["sessid"], $_POST["userid"], $_POST["seats"]);
echo "SAVED";

Lastly, this is as simple as it gets – Save the reservation request when the hidden form is submitted.

 

 

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.

 

 

BAREBONES EXAMPLE ONLY

Of course, this is only a barebones working example. Everyone has their own rules and existing systems, so here are a couple of “to-do” items on your plate:

• Tie in with the user and login system.
• No admin backend and reports – Do your own.
• Reservation rules –
  • Only allow registered users to reserve?
  • Allow users to reserve multiple seats?
  • Allow users to change or cancel reservations afterward?
  • Close reservations N hours before the session starts?
  • Confirm reservation only after payment/deposit?
• Double-check the reservation (4-save.php) before saving – Someone else may have booked the seat while the user is still filling in the reservation form.
• Send a confirmation email to the user?

 

LINKS & REFERENCES

• Login System With PHP MySQL – Code Boxx
• Pure HTML CSS Admin Panel – Code Boxx
• PHP Send Email With HTML Template – Code Boxx