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!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

Download & Notes Database PHP
HTML CSS Useful Bits & Links The End

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

  • Download and unzip into your HTTP folder.
  • Create a test database and import 1-database.sql.
  • Open 2-reserve-lib.php and change the database settings (F) to your own.
  • Access 3a-reservation.php in the browser.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

PART 1) THE DATABASE

All right, let us now get into the first part of the project, creating a database for the seat reservation.

 

SEATS

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

Well, this should be self-explanatory. The available seats. Just change room_id to vehicle_id if it is for passenger service.

 

SESSIONS

1-database.sql
-- (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=latin1;
 
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;

Next, we have a “sessions” table – These can also be “trips” for passenger services, or maybe “lessons” for education services.

 

RESERVATIONS

1-database.sql
-- (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=latin1;
 
ALTER TABLE `reservations`
  ADD PRIMARY KEY (`session_id`,`seat_id`,`user_id`);

A table to record the reservations made by the users – For which session, and which seat(s) they have reserved.

 

 

DUMMY DATA

1-database.sql
-- (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');

Lastly, some dummy data and examples to help you better understand.

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

 

PART 2) PHP LIBRARY

With that, let us move on to the next piece of the foundation – A PHP library to handle the reservations.

 

RESERVATIONS PHP LIBRARY

2-reserve-lib.php
<?php
class Reserve {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = null;
  function __construct () {
    try {
      $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
        ]);
    } catch (Exception $ex) { exit($ex->getMessage()); }
  }
 
  // (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) {
    $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 .= "(?,?,?),";
      $data[] = $sessid;
      $data[] = $seat;
      $data[] = $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", "utf8");
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) 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 & G) Don’t think these need explanation…

 

 

PART 3) HTML RESERVATION PAGES

With the foundations established, let us finally get into the reservation pages themselves.

 

RESERVATION PAGE

3a-reservation.php
<?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>Green seats - Open</div>
<div>Grey seats - Taken</div>
<div>Blue seats - Your chosen seats</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 onclick="reserve.save()">Reserve Seats</button>

This is just an HTML page to get the seats from the database, and show them in a “nice format”. Not going to explain line-by-line, but just take note that user ID and session ID are fixed for this demo – You will want to change these to your own (tie in with your user system).

 

THE JAVASCRIPT

3b-reservation.js
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.

 

 

CONFIRMATION PAGE

4-save.php
<?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 submitted reservation request.

 

USEFUL BITS & LINKS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

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 –
    • Allow only registered users to reserve?
    • Allow users to reserve multiple seats?
    • Allow users to change 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

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published.