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
PHP MYSQL SEAT RESERVATION
All right, let us now get into the details of creating a seat reservation system with PHP and MYSQL.
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.
- The session ID and user ID are fixed for this demo – Tie these into your own system.
- Use the PHP library, to get the seats for the given session.
<div id="layout">
Choose a seat.<div id="legend">
“Color code” for the seats.<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.
DOWNLOAD & NOTES
Here is the download link to the example code, so you don’t have to copy-paste everything.
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 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 –
- 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
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!