Build Hotel Reservation System with PHP & MySQL

In our previous tutorial you have learned how to develop Content Management System with PHP and MySQL. In this tutorial you will learn how to develop Online Hotel Reservation System with PHP and MySQL.

The Online Hotel Reservation System is an automated system that allows hotel owners to manage rooms, accommodations and reservationists. It allows the public users to search rooms availability and book rooms. The system can easily be accessed anywhere to manage rooms and reservations.

So if you’re are PHP developer and looking for a solution to develop your own hotel reservation system, then you’re here at the right place. In this tutorial we will develop a live example of hotel reservation system to manage both admin section and frontend. You can use this system to develop with more feature as per your requirement.

Also, read:

Here we will develop a live example of Online Hotel Reservation System and cover following.

The Administrator will do the following:

Manage Rooms.
Manage Accommodations.
Manage Reservations.
Manage Users.

The users will do the following:

Check Room Availability.
View Rooms and Rates.
Book Rooms.

So let’s implement Online Hotel Reservation System. The file structure are:

hotel-reservation-system
- admin
  - config
    - database.php
  - class
    - User.php
    - Accomodation.php
    - Rooms.php
  - index.php
  - accomodation.php
  - rooms.php
  - reservation.php
  - users.php
- index.php

Step1: MySQL Database Tables

First we will create MySQL database tables to develop this system. We we will create hotel_user table to store users login information.

CREATE TABLE `hotel_user` (
  `id` int(11) UNSIGNED NOT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `gender` enum('Male','Female') NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(64) NOT NULL,
  `mobile` varchar(12) NOT NULL,
  `address` text NOT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `role` enum('admin','user') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `hotel_user`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `hotel_user`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

here is sample dump data:

INSERT INTO `hotel_user` (`id`, `first_name`, `last_name`, `gender`, `email`, `password`, `mobile`, `address`, `created`, `role`) VALUES
(1, 'Jhon', 'Lennon', 'Male', 'admin@phpzag.com', '202cb962ac59075b964b07152d234b70', '1234567890', '', '2020-11-28 22:45:58', 'admin'),
(2, 'Ryan', 'Smith', 'Male', 'user1@phpzag.com', '202cb962ac59075b964b07152d234b70', '123456789', '', '2020-11-28 22:45:58', 'user'),
(3, 'Jhon', 'Eyan', 'Male', 'user2@phpzag.com', '202cb962ac59075b964b07152d234b70', '123456789', '', '2020-11-28 22:45:58', 'user'),
(4, 'Dunkun', 'damian', 'Male', 'user3@phpzag.com', '202cb962ac59075b964b07152d234b70', '123456789', '', '2020-11-28 22:45:58', 'user');

we will create hotel_accomodation table to store accomodations details.

CREATE TABLE `hotel_accomodation` (
  `id` int(11) NOT NULL,
  `accomodation` varchar(30) NOT NULL,
  `description` varchar(90) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `hotel_accomodation`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `hotel_accomodation`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

Here is sample dump data:

INSERT INTO `hotel_accomodation` (`id`, `accomodation`, `description`) VALUES
(12, 'Standard Room', 'max 22hrs.'),
(13, 'Travelers Time', 'max of 12hrs.'),
(15, 'Bayanihan Room', 'max 22hrs.');

we will create hotel_room table to store rooms details.

CREATE TABLE `hotel_room` (
  `id` int(11) NOT NULL,
  `room_number` int(11) NOT NULL,
  `accomodation_id` int(11) NOT NULL,
  `room` varchar(30) NOT NULL,
  `description` varchar(255) NOT NULL,
  `number_person` int(11) NOT NULL,
  `price` double NOT NULL,
  `picture` varchar(255) NOT NULL,
  `room_type` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `hotel_room`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `hotel_room`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

Here is the sample data:

INSERT INTO `hotel_room` (`id`, `room_number`, `accomodation_id`, `room`, `description`, `number_person`, `price`, `picture`, `room_type`) VALUES
(11, 10, 12, 'Deluxe A', 'without TV', 1, 10, '1.jpg', 0),
(12, 12, 12, 'Deluxe B', 'Without TV', 2, 15, '2.jpg', 0),
(13, 1, 13, 'Deluxe C', 'Without TV', 1, 445, '3.jpg', 0),
(14, 2, 13, 'Deluxe  A', 'Without TV', 2, 495, '4.jpg', 0),
(15, 1, 15, 'Deluxe  A+', 'Without TV', 5, 1250, '5.jpg', 0),
(16, 3, 12, 'Deluxe  B and Ground Floor', 'With TV', 1, 725, '6.jpg', 0);

we will create hotel_reservation table to store rooms reservations details.

CREATE TABLE `hotel_reservation` (
  `id` int(11) NOT NULL,
  `confirmation_code` varchar(50) NOT NULL,
  `transaction_date` date NOT NULL,
  `room_id` int(11) NOT NULL,
  `arrival` datetime NOT NULL,
  `departure` datetime NOT NULL,
  `room_price` double NOT NULL,
  `purpose` varchar(30) NOT NULL,
  `status` varchar(11) NOT NULL,
  `book_date` datetime NOT NULL,
  `remark` text NOT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `hotel_reservation`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `hotel_reservation`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

and we will create hotel_payment table to store room reservation payment details.

CREATE TABLE `hotel_payment` (
  `id` int(11) NOT NULL,
  `transaction_date` datetime NOT NULL,
  `confirmation_code` varchar(30) NOT NULL,
  `quantity` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `price` double NOT NULL,
  `message` tinyint(1) NOT NULL,
  `status` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `hotel_payment`
  ADD PRIMARY KEY (`id`);
  
ALTER TABLE `hotel_payment`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

Step2: Manage Accomodatoins

We will implement functionality to manage accomodations. We will create HTML in accomodation.php to design page to implement functionality to list accomodation, add, edit and delete.

<div> 	
	<div class="panel-heading">
		<div class="row">
			<div class="col-md-10">
				<h3 class="panel-title"></h3>
			</div>
			<div class="col-md-2" align="right">
				<button type="button" id="addAccomodation" 
class="btn btn-info" 
title="Add Accomodation"><span 
class="glyphicon glyphicon-plus"></span></button>
			</div>
		</div>
	</div>
	<table id="accomodationListing"
 class="table table-bordered table-striped">
		<thead>
			<tr>						
				<th>Id</th>					
				<th>Accomodation</th>					
				<th>Description</th>					
				<th></th>
				<th></th>					
			</tr>
		</thead>
	</table>
</div>

<div id="accomodationModal" class="modal fade">
	<div class="modal-dialog">
		<form method="post" id="accomodationForm">
			<div class="modal-content">
				<div class="modal-header">
					<button 
type="button" class="close" 
data-dismiss="modal">×</button>
					<h4 class="modal-title"><i 
class="fa fa-plus"></i> Edit Category</h4>
				</div>
				<div class="modal-body">						
					<div class="form-group">
						<div class="row">
							<label 
class="col-md-4 text-right">Accomodation Name <span 
class="text-danger">*</span></label>
							<div class="col-md-8">
								<input type="text" 
name="accomodationName" id="accomodationName" 
autocomplete="off" class="form-control" required />
							</div>
						</div>
					</div>	
					
					<div class="form-group">
						<div class="row">
							<label 
class="col-md-4 text-right">Description <span 
class="text-danger"></span></label>
							<div class="col-md-8">
								<textarea class="form-control" 
rows="5" name="description" 
id="description"></textarea>
							</div>
						</div>
					</div>							
				</div>
				<div class="modal-footer">
					<input type="hidden" name="id" id="id" />						
					<input type="hidden" name="action" id="action" value="" />
					<input type="submit" name="save" id="save" 
class="btn btn-info" value="Save" />
					<button type="button" 
class="btn btn-default" data-dismiss="modal">Close</button>
				</div>
			</div>
		</form>
	</div>
</div>

We will initialize datatables in accomodation.js and make ajax request to accomodation_action.php with action listAccomodation to display accomodation list.

var accomodationRecords = $('#accomodationListing').DataTable({
	"lengthChange": false,
	"processing":true,
	"serverSide":true,		
	"bFilter": true,
	'serverMethod': 'post',		
	"order":[],
	"ajax":{
		url:"accomodation_action.php",
		type:"POST",
		data:{action:'listAccomodation'},
		dataType:"json"
	},
	"columnDefs":[
		{
			"targets":[0, 3, 4],
			"orderable":false,
		},
	],
	"pageLength": 10
});

we will check action listAccomodation an call method listAccomodation() from class Accomodation.php to list them.

if(!empty($_POST['action']) && $_POST['action'] == 'listAccomodation') {
	$accomodation->listAccomodation();
}

we will implement method listAccomodation() in class Accomodation.php to get listing.

public function listAccomodation(){			
		
	$sqlQuery = "
		SELECT id, accomodation, description
		FROM ".$this->accomodationTable." ";
					
	if(!empty($_POST["order"])){
		$sqlQuery .= ' ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
	} else {
		$sqlQuery .= ' ORDER BY id ASC ';
	}
	
	if($_POST["length"] != -1){
		$sqlQuery .= ' LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
	}
	
	$stmt = $this->conn->prepare($sqlQuery);
	$stmt->execute();
	$result = $stmt->get_result();	
	
	$stmtTotal = $this->conn->prepare($sqlQuery);
	$stmtTotal->execute();
	$allResult = $stmtTotal->get_result();
	$allRecords = $allResult->num_rows;
	
	$displayRecords = $result->num_rows;
	$records = array();	

	while ($accomodation = $result->fetch_assoc()) { 				
		$rows = array();			
		$rows[] = $accomodation['id'];			
		$rows[] = $accomodation['accomodation'];
		$rows[] = $accomodation['description'];
		$rows[] = '<button type="button" 
name="update" id="'.$accomodation["id"].'" 
class="btn btn-warning btn-xs update"><span 
class="glyphicon glyphicon-edit" title="Edit"></span></button>';			
		$rows[] = '<button type="button" 
name="delete" id="'.$accomodation["id"].'" 
class="btn btn-danger btn-xs delete" ><span 
class="glyphicon glyphicon-remove" title="Delete"></span></button>';
		$records[] = $rows;
	}
	
	$output = array(
		"draw"	=>	intval($_POST["draw"]),			
		"iTotalRecords"	=> 	$displayRecords,
		"iTotalDisplayRecords"	=>  $allRecords,
		"data"	=> 	$records
	);
	
	echo json_encode($output);
}

Step3: Manage Rooms

We will create HTML in rooms.php to design page to list rooms. We will also manage add, edit and delete rooms.

<div> 	
	<div class="panel-heading">
		<div class="row">
			<div class="col-md-10">
				<h3 class="panel-title"></h3>
			</div>
			<div class="col-md-2" align="right">
				<button type="button" id="addRoom" 
				class="btn btn-info" title="Add Room"><span 
				class="glyphicon glyphicon-plus"></span></button>
			</div>
		</div>
	</div>
	<table id="roomsListing" class="table table-bordered table-striped">
		<thead>
			<tr>						
				<th>Id</th>					
				<th>Room</th>					
				<th>Picture</th>
				<th>Accomodation</th>
				<th>Person</th>
				<th>Price</th>					
				<th></th>
				<th></th>					
			</tr>
		</thead>
	</table>
</div>

In rooms.js, we will make ajax request to rooms_action.php wtih action listRooms to list rooms.

var roomsRecords = $('#roomsListing').DataTable({
	"lengthChange": false,
	"processing":true,
	"serverSide":true,		
	"bFilter": true,
	'serverMethod': 'post',		
	"order":[],
	"ajax":{
		url:"rooms_action.php",
		type:"POST",
		data:{action:'listRooms'},
		dataType:"json"
	},
	"columnDefs":[
		{
			"targets":[0, 6, 7],
			"orderable":false,
		},
	],
	"pageLength": 10
});

we will check for action listRooms in rooms_action.php and call method listRooms() from class Rooms.php to list rooms.

if(!empty($_POST['action']) && $_POST['action'] == 'listRooms') {
	$room->listRooms();
}

we will implement method listRooms() in class Rooms.php to list them.

public function listRooms(){			
		
	$sqlQuery = "
		SELECT rooms.id, rooms.room_number, rooms.room, 
rooms.number_person, rooms.price, rooms.picture, 
accomodation.accomodation
		FROM  ".$this->roomTable." rooms
		LEFT JOIN ".$this->accomodationTable." 
accomodation ON rooms.accomodation_id = accomodation.id ";
					
	if(!empty($_POST["order"])){
		$sqlQuery .= ' ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
	} else {
		$sqlQuery .= ' ORDER BY rooms.id ASC ';
	}
			
	if($_POST["length"] != -1){
		$sqlQuery .= ' LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
	}
	
	$stmt = $this->conn->prepare($sqlQuery);
	$stmt->execute();
	$result = $stmt->get_result();	
	
	$stmtTotal = $this->conn->prepare($sqlQuery);
	$stmtTotal->execute();
	$allResult = $stmtTotal->get_result();
	$allRecords = $allResult->num_rows;
	
	$displayRecords = $result->num_rows;
	$records = array();	

	while ($room = $result->fetch_assoc()) { 				
		$rows = array();			
		$rows[] = $room['id'];			
		$rows[] = $room['room'];
		$rows[] = "<img src='../images/".$room['picture']."' width='60' height='60'>";
		$rows[] = $room['accomodation'];
		$rows[] = $room['number_person'];
		$rows[] = "$".$room['price'];
		$rows[] = '<button type="button" 
name="update" id="'.$room["id"].'" 
class="btn btn-warning btn-xs update"><span 
class="glyphicon glyphicon-edit" title="Edit"></span></button>';			
		$rows[] = '<button type="button" 
name="delete" id="'.$room["id"].'" 
class="btn btn-danger btn-xs delete" ><span 
class="glyphicon glyphicon-remove" title="Delete"></span></button>';
		$records[] = $rows;
	}
	
	$output = array(
		"draw"	=>	intval($_POST["draw"]),			
		"iTotalRecords"	=> 	$displayRecords,
		"iTotalDisplayRecords"	=>  $allRecords,
		"data"	=> 	$records
	);
	
	echo json_encode($output);
}

13 thoughts on “Build Hotel Reservation System with PHP & MySQL”

nidnoi says:

6 January, 2021 at 2:05 pm

the front-end button and the download button, both of them do not work.
1. PHPZAG Team says:
  
  16 January, 2021 at 4:16 pm
  
  its fixed now, thanks!
Hamood alazri says:

8 January, 2021 at 11:53 pm

download link is not downloaded anything it just return back to this page
Thank you for your effors
1. PHPZAG Team says:
  
  16 January, 2021 at 4:16 pm
  
  its fixed now, thanks!
Adeniyi Toyyib says:

21 January, 2021 at 9:29 am

thanks alot i really appreciate, this really helps.
Oladiti abiodun says:

1 July, 2021 at 7:54 am

After booking, why can’t I be able to make payment for the room?
1. PHPZAG Team says:
  
  7 August, 2021 at 5:02 pm
  
  have you implemented payment gateways for payment, you need to implement this as per your requirement. Thanks!
MAXMAS says:

6 July, 2021 at 9:36 am

I suggest offer system requirements.
And can you offer sample mysql data?

And I get an error: Fatal error: Call to a member function bind_param() on boolean in ..
1. PHPZAG Team says:
  
  14 August, 2021 at 3:08 pm
  
  Sample data updated in tutorial, you can use that. thanks!
Shahen Gasparyan says:

8 August, 2021 at 6:32 am

Hi, I tried to use this project for training purposes. The biggest problem is that you have not provided examples filled in the database, just an empty one. There’s not user or admin registered so it is not letting to login. The front page just looks empty, only the two colors lines and nothing else, while in the demo everything is in their places. Would you be so kind to provide a working model, with registered admin, rooms options, etc., so it will be more comfortable for learning. Thanks.
1. PHPZAG Team says:
  
  14 August, 2021 at 1:07 pm
  
  I have updated tutorial with table structures and sample data. you can use this. thanks!
Bethel says:

14 July, 2022 at 7:41 am

There is no SQL file
1. PHPZAG Team says:
  
  23 July, 2022 at 7:46 am
  
  complete database table with structure and data provided in tutorial. thanks!

Comments are closed.

Step1: MySQL Database Tables

Step2: Manage Accomodatoins

Step3: Manage Rooms

13 thoughts on “Build Hotel Reservation System with PHP & MySQL”

Latest Tutorials

Popular Tags