How to develop Projects(HBS) Using PHP and Mysql
Part 10
5️⃣ Hotel Booking System
🛏️ Room Management Module
Module 2: Add Rooms and Check Availability
Purpose: Allows admins to manage hotel rooms and users to check room availability.
✅ Functionalities:
- ✅ Add room to a hotel
- ✅ Edit room details
- ✅ Delete room
- ✅ List all rooms in a hotel
- ✅ Check room availability by date
🗂 Project Structure:
│── /rooms/
│ ├── add_room.php # Add room to a hotel
│ ├── edit_room.php # Edit room details
│ ├── delete_room.php # Delete a room
│ ├── room_list.php # List all rooms per hotel
│ ├── check_availability.php # Check room availability by date
🗃 Suggested Database Tables:
🛏️ 1. Create rooms_tbl
CREATE TABLE rooms_tbl (
id INT AUTO_INCREMENT PRIMARY KEY,
hotel_id INT NOT NULL,
room_number VARCHAR(50) NOT NULL,
room_type VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
status VARCHAR(20) DEFAULT 'Available', -- Available, Booked, Maintenance
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (hotel_id) REFERENCES hotels_tbl(id) ON DELETE CASCADE
);
📅 2. Create bookings_tbl (for checking room availability)
CREATE TABLE bookings_tbl (
id INT AUTO_INCREMENT PRIMARY KEY,
room_id INT NOT NULL,
user_id INT NOT NULL,
check_in_date DATE NOT NULL,
check_out_date DATE NOT NULL,
status VARCHAR(20) DEFAULT 'Confirmed', -- Confirmed, Cancelled, Pending
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (room_id) REFERENCES rooms_tbl(id) ON DELETE CASCADE
-- Optionally: FOREIGN KEY (user_id) REFERENCES users_tbl(id)
);
🔍 3. Query to check room availability:
Check available rooms for a hotel between two dates (e.g., '2025-05-01' to '2025-05-05'):
SELECT * FROM rooms_tbl
WHERE hotel_id = 1
AND status = 'Available'
AND id NOT IN (
SELECT room_id FROM bookings_tbl
WHERE status = 'Confirmed'
AND (
(check_in_date <= '2025-05-05' AND check_out_date >= '2025-05-01')
)
);
✅ rooms/add_room.php
<?php
include '../db.php';
if ($_SERVER["REQUEST_METHOD"] == "POST")
{
$hotel_id = $_POST['hotel_id'];
$room_number
= $_POST['room_number'];
$room_type =
$_POST['room_type'];
$price = $_POST['price'];
$sql = "INSERT
INTO rooms_tbl (hotel_id, room_number, room_type, price)
VALUES ('$hotel_id', '$room_number', '$room_type', '$price')";
if (mysqli_query($conn,
$sql)) {
echo "Room
added successfully!";
} else {
echo "Error:
" . mysqli_error($conn);
}
}
?>
✅ rooms/edit_room.php
<?php
include '../db.php';
if ($_SERVER["REQUEST_METHOD"] == "POST")
{
$room_id = $_POST['room_id'];
$room_number
= $_POST['room_number'];
$room_type =
$_POST['room_type'];
$price = $_POST['price'];
$status = $_POST['status'];
$sql = "UPDATE
rooms_tbl
SET
room_number='$room_number', room_type='$room_type', price='$price', status='$status'
WHERE id='$room_id'";
if (mysqli_query($conn,
$sql)) {
echo "Room
updated successfully!";
} else {
echo "Error:
" . mysqli_error($conn);
}
}
?>
✅ rooms/delete_room.php
<?php
include '../db.php';
if (isset($_GET['id'])) {
$room_id = $_GET['id'];
$sql = "DELETE
FROM rooms_tbl WHERE id='$room_id'";
if (mysqli_query($conn,
$sql)) {
echo "Room
deleted successfully!";
} else {
echo "Error:
" . mysqli_error($conn);
}
}
?>
✅ rooms/room_list.php
<?php
include '../db.php';
if (isset($_GET['hotel_id'])) {
$hotel_id = $_GET['hotel_id'];
$sql = "SELECT
* FROM rooms_tbl WHERE hotel_id='$hotel_id'";
$result = mysqli_query($conn,
$sql);
while ($row
= mysqli_fetch_assoc($result)) {
echo "Room:
{$row['room_number']} | Type: {$row['room_type']} | Price: {$row['price']} |
Status: {$row['status']}<br>";
}
}
?>
✅ rooms/check_availability.php
<?php
include '../db.php';
if ($_SERVER["REQUEST_METHOD"] == "POST")
{
$check_in = $_POST['check_in_date'];
$check_out =
$_POST['check_out_date'];
$sql = "SELECT
* FROM rooms_tbl
WHERE id NOT IN (
SELECT room_id FROM bookings_tbl
WHERE ('$check_in' < check_out_date AND '$check_out' >
check_in_date)
)
AND status = 'Available'";
$result = mysqli_query($conn,
$sql);
echo "<h3>Available
Rooms:</h3>";
while ($row
= mysqli_fetch_assoc($result)) {
echo "Room:
{$row['room_number']} | Type: {$row['room_type']} | Price: {$row['price']}<br>";
}
}
?>
✅ db.php
(Database Connection File)
<?php
$host = "localhost";
$user = "root";
$password = "";
$dbname = "hotel_db";
$conn = mysqli_connect($host, $user, $password, $dbname);
if (!$conn) {
die("Connection
failed: " . mysqli_connect_error());
}
?>