How to develop Projects(HotelMS) Using PHP and Mysql
Part 9
5️⃣ Hotel Booking System
📌 Folder Structure:
│── /hotels/
│ ├── add_hotel.php # Add hotel
│ ├── edit_hotel.php # Edit hotel details
│ ├── delete_hotel.php # Delete hotel
│ ├── hotel_list.php # List all hotels
📌 Required Database Table (events)
Make sure you have this table in your database:
To store images for a
hotel, you can add a column for storing image file paths or use a BLOB data
type to store the image directly in the database.
Option 1: Storing Image
Paths (Recommended)
This method stores the
image file path or URL in the database while the actual images are saved in a
folder on the server.
CREATE TABLE hotel (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
address TEXT NOT NULL,
city VARCHAR(100) NOT NULL,
state VARCHAR(100) NOT NULL,
country VARCHAR(100) NOT NULL,
postal_code VARCHAR(20) NOT NULL,
phone VARCHAR(20) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
website VARCHAR(255),
rating DECIMAL(2,1) CHECK (rating BETWEEN 0
AND 5),
total_rooms INT NOT NULL CHECK (total_rooms
> 0),
available_rooms INT NOT NULL CHECK
(available_rooms >= 0),
amenities TEXT,
image_url VARCHAR(255), -- Store the path
or URL of the image
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
);
- Pros:
More efficient, better performance, easy to manage.
- Cons:
Requires managing image files separately.
Option 2: Storing Image
as BLOB (Binary Data)
This method stores the
image directly in the database as binary data.
CREATE TABLE hotel (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
address TEXT NOT NULL,
city VARCHAR(100) NOT NULL,
state VARCHAR(100) NOT NULL,
country VARCHAR(100) NOT NULL,
postal_code VARCHAR(20) NOT NULL,
phone VARCHAR(20) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
website VARCHAR(255),
rating DECIMAL(2,1) CHECK (rating BETWEEN 0
AND 5),
total_rooms INT NOT NULL CHECK (total_rooms
> 0),
available_rooms INT NOT NULL CHECK
(available_rooms >= 0),
amenities TEXT,
image BLOB, -- Store the image directly in
the database
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP
);
- Pros:
Everything is stored in the database.
- Cons:
Increases database size, affects performance.
hotels/add_hotel.php
<?php
// Database connection
$conn = mysqli_connect("localhost",
"root", "", "hotel_booking");
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Check if form is
submitted
if ($_SERVER["REQUEST_METHOD"]
== "POST") {
$name = mysqli_real_escape_string($conn, $_POST['name']);
$address = mysqli_real_escape_string($conn,
$_POST['address']);
$city = mysqli_real_escape_string($conn, $_POST['city']);
$state = mysqli_real_escape_string($conn, $_POST['state']);
$country = mysqli_real_escape_string($conn,
$_POST['country']);
$postal_code = mysqli_real_escape_string($conn,
$_POST['postal_code']);
$phone = mysqli_real_escape_string($conn, $_POST['phone']);
$email = mysqli_real_escape_string($conn, $_POST['email']);
$website = mysqli_real_escape_string($conn,
$_POST['website']);
$rating = mysqli_real_escape_string($conn, $_POST['rating']);
$total_rooms = mysqli_real_escape_string($conn,
$_POST['total_rooms']);
$available_rooms = mysqli_real_escape_string($conn,
$_POST['available_rooms']);
$amenities = mysqli_real_escape_string($conn,
$_POST['amenities']);
// Handling Image Upload
$image_path = "";
if (!empty($_FILES["image"]["name"]))
{
$target_dir = "uploads/";
$image_path = $target_dir . basename($_FILES["image"]["name"]);
move_uploaded_file($_FILES["image"]["tmp_name"],
$image_path);
}
// Insert Query
$sql = "INSERT INTO hotel (name,
address, city, state, country, postal_code, phone, email, website, rating,
total_rooms, available_rooms, amenities, image_url)
VALUES ('$name', '$address', '$city',
'$state', '$country', '$postal_code', '$phone', '$email', '$website', '$rating',
'$total_rooms', '$available_rooms', '$amenities', '$image_path')";
if (mysqli_query($conn, $sql)) {
echo "Hotel added
successfully!";
} else {
echo "Error: " . mysqli_error($conn);
}
mysqli_close($conn);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,
initial-scale=1.0">
<title>Add Hotel</title>
</head>
<body>
<h2>Add Hotel</h2>
<form action="" method="post"
enctype="multipart/form-data">
<label>Hotel Name:</label>
<input type="text" name="name"
required><br>
<label>Address:</label>
<textarea name="address"
required></textarea><br>
<label>City:</label>
<input type="text" name="city"
required><br>
<label>State:</label>
<input type="text" name="state"
required><br>
<label>Country:</label>
<input type="text" name="country"
required><br>
<label>Postal Code:</label>
<input type="text" name="postal_code"
required><br>
<label>Phone:</label>
<input type="text" name="phone"
required><br>
<label>Email:</label>
<input type="email" name="email"
required><br>
<label>Website:</label>
<input type="text" name="website"><br>
<label>Rating (0-5):</label>
<input type="number" name="rating"
step="0.1" min="0" max="5"><br>
<label>Total Rooms:</label>
<input type="number" name="total_rooms"
required><br>
<label>Available
Rooms:</label>
<input type="number" name="available_rooms"
required><br>
<label>Amenities:</label>
<textarea name="amenities"></textarea><br>
<label>Hotel Image:</label>
<input type="file" name="image"><br>
<button type="submit">Add
Hotel</button>
</form>
</body>
</html>
Explanation
1.
Database Connection:
Uses mysqli_connect() to establish a connection.
2.
Form Handling:
Checks for POST request and collects input values securely using mysqli_real_escape_string().
3.
Image Upload Handling:
Saves the image in the uploads/ directory and stores the file path in the
database.
4.
Insert Query Execution:
Uses mysqli_query() to insert data into the hotel table.
5.
Basic HTML Form:
Allows user input for hotel details.
hotels/edit_hotel.php
<?php
// Database connection
$conn = mysqli_connect("localhost",
"root", "", "hotel_booking");
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Get hotel ID from URL
if (isset($_GET['id'])) {
$hotel_id = $_GET['id'];
// Fetch hotel details
$query = "SELECT * FROM hotel WHERE id
= $hotel_id";
$result = mysqli_query($conn, $query);
if ($result && mysqli_num_rows($result)
> 0) {
$hotel
= mysqli_fetch_assoc($result);
} else {
echo "Hotel not found!";
exit;
}
}
// Check if form is
submitted
if ($_SERVER["REQUEST_METHOD"]
== "POST") {
$name = mysqli_real_escape_string($conn, $_POST['name']);
$address = mysqli_real_escape_string($conn,
$_POST['address']);
$city = mysqli_real_escape_string($conn, $_POST['city']);
$state = mysqli_real_escape_string($conn, $_POST['state']);
$country = mysqli_real_escape_string($conn,
$_POST['country']);
$postal_code = mysqli_real_escape_string($conn,
$_POST['postal_code']);
$phone = mysqli_real_escape_string($conn, $_POST['phone']);
$email = mysqli_real_escape_string($conn, $_POST['email']);
$website = mysqli_real_escape_string($conn,
$_POST['website']);
$rating = mysqli_real_escape_string($conn, $_POST['rating']);
$total_rooms = mysqli_real_escape_string($conn,
$_POST['total_rooms']);
$available_rooms = mysqli_real_escape_string($conn,
$_POST['available_rooms']);
$amenities = mysqli_real_escape_string($conn,
$_POST['amenities']);
// Handle Image Upload
$image_path = $hotel['image_url']; //
Default to existing image
if (!empty($_FILES["image"]["name"]))
{
$target_dir = "uploads/";
$image_path = $target_dir . basename($_FILES["image"]["name"]);
move_uploaded_file($_FILES["image"]["tmp_name"],
$image_path);
}
// Update Query
$sql = "UPDATE hotel SET
name = '$name',
address = '$address',
city = '$city',
state = '$state',
country = '$country',
postal_code = '$postal_code',
phone = '$phone',
email = '$email',
website = '$website',
rating = '$rating',
total_rooms = '$total_rooms',
available_rooms = '$available_rooms',
amenities = '$amenities',
image_url = '$image_path'
WHERE id = $hotel_id";
if (mysqli_query($conn, $sql)) {
echo "Hotel updated
successfully!";
} else {
echo "Error: " . mysqli_error($conn);
}
mysqli_close($conn);
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,
initial-scale=1.0">
<title>Edit Hotel</title>
</head>
<body>
<h2>Edit Hotel</h2>
<form action="" method="post"
enctype="multipart/form-data">
<label>Hotel Name:</label>
<input type="text" name="name"
value="<?php echo $hotel['name']; ?>" required><br>
<label>Address:</label>
<textarea name="address"
required><?php echo $hotel['address']; ?></textarea><br>
<label>City:</label>
<input type="text" name="city"
value="<?php echo $hotel['city']; ?>" required><br>
<label>State:</label>
<input type="text" name="state"
value="<?php echo $hotel['state']; ?>" required><br>
<label>Country:</label>
<input type="text" name="country"
value="<?php echo $hotel['country']; ?>" required><br>
<label>Postal Code:</label>
<input type="text" name="postal_code"
value="<?php echo $hotel['postal_code']; ?>"
required><br>
<label>Phone:</label>
<input type="text" name="phone"
value="<?php echo $hotel['phone']; ?>" required><br>
<label>Email:</label>
<input type="email" name="email"
value="<?php echo $hotel['email']; ?>" required><br>
<label>Website:</label>
<input type="text" name="website"
value="<?php echo $hotel['website']; ?>"><br>
<label>Rating (0-5):</label>
<input type="number" name="rating"
step="0.1" min="0" max="5" value="<?php
echo $hotel['rating']; ?>"><br>
<label>Total Rooms:</label>
<input type="number" name="total_rooms"
value="<?php echo $hotel['total_rooms']; ?>"
required><br>
<label>Available
Rooms:</label>
<input type="number" name="available_rooms"
value="<?php echo $hotel['available_rooms']; ?>"
required><br>
<label>Amenities:</label>
<textarea name="amenities"><?php
echo $hotel['amenities']; ?></textarea><br>
<label>Current Hotel
Image:</label><br>
<img src="<?php echo $hotel['image_url'];
?>" width="100" alt="Hotel Image"><br>
<label>Upload New
Image:</label>
<input type="file" name="image"><br>
<button type="submit">Update
Hotel</button>
</form>
</body>
</html>
Explanation
1.
Database Connection:
Establishes a connection using mysqli_connect().
2.
Fetch Hotel Details:
Retrieves existing hotel data based on the id passed in the URL ($_GET['id']).
3.
Update Form Handling:
Handles user input, sanitizes data, and updates the database using mysqli_query().
4.
Image Handling:
If the user uploads a new image, it is saved and updated in the database.
Otherwise, the existing image remains unchanged.
5.
HTML Form with Pre-Filled Values:
Displays the hotel details in the form, allowing users to edit and update.
How to Use
1.
Navigate to edit_hotel.php?id=1 (replace 1
with the hotel ID).
2.
The form loads with existing hotel
details.
3.
Edit the details and submit the form.
4.
The hotel details update in the database.
hotels/delete_hotel.php
<?php
// Database connection
$conn = mysqli_connect("localhost",
"root", "", "hotel_booking");
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Check if hotel ID is
set
if (isset($_GET['id'])) {
$hotel_id = $_GET['id'];
// Fetch hotel image path before deletion
$query = "SELECT image_url FROM hotel
WHERE id = $hotel_id";
$result = mysqli_query($conn, $query);
if ($result && mysqli_num_rows($result)
> 0) {
$hotel = mysqli_fetch_assoc($result);
$image_path = $hotel['image_url'];
// Delete hotel record
$delete_query = "DELETE FROM hotel
WHERE id = $hotel_id";
if (mysqli_query($conn, $delete_query))
{
// Delete the image file if it
exists
if (!empty($image_path) && file_exists($image_path))
{
unlink($image_path); // Remove
image file
}
echo "Hotel deleted
successfully!";
} else {
echo "Error deleting hotel:
" . mysqli_error($conn);
}
} else {
echo "Hotel not found!";
}
} else {
echo "Invalid request!";
}
// Close connection
mysqli_close($conn);
?>
How It Works
1.
Database Connection:
Connects to the database using mysqli_connect().
2.
Check for Hotel ID:
Ensures id is provided in the URL ($_GET['id']).
3.
Fetch Image Path:
Retrieves the image_url before deletion to remove the associated file.
4.
Delete Hotel Record:
Executes the DELETE FROM hotel WHERE id = $hotel_id query.
5.
Delete Image File:
Uses unlink() to remove the hotel image from the server.
6.
Error Handling:
Displays appropriate messages if the hotel is not found or the query fails.
How to Use
1.
Navigate to delete_hotel.php?id=1 (replace
1 with the hotel ID).
2.
The script will delete the hotel record
and its image file (if it exists).
3.
You will see a success or error message.
hotels/hotel_list.php
<?php
// Database connection
$conn = mysqli_connect("localhost",
"root", "", "hotel_booking");
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Fetch all hotels
$query = "SELECT *
FROM hotel ORDER BY id DESC";
$result = mysqli_query($conn,
$query);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,
initial-scale=1.0">
<title>Hotel List</title>
<style>
table {
width: 100%;
border-collapse: collapse;
}
table, th, td {
border: 1px solid black;
}
th, td {
padding: 10px;
text-align: left;
}
img {
width: 100px;
height: auto;
}
</style>
</head>
<body>
<h2>Hotel List</h2>
<a href="add_hotel.php"><button>Add
New Hotel</button></a>
<table>
<tr>
<th>ID</th>
<th>Hotel Name</th>
<th>City</th>
<th>Country</th>
<th>Rating</th>
<th>Available
Rooms</th>
<th>Image</th>
<th>Actions</th>
</tr>
<?php
if ($result && mysqli_num_rows($result)
> 0) {
while ($row = mysqli_fetch_assoc($result))
{
echo "<tr>
<td>{$row['id']}</td>
<td>{$row['name']}</td>
<td>{$row['city']}</td>
<td>{$row['country']}</td>
<td>{$row['rating']}</td>
<td>{$row['available_rooms']}</td>
<td><img src='{$row['image_url']}'
alt='Hotel Image'></td>
<td>
<a
href='edit_hotel.php?id={$row['id']}'>Edit</a> |
<a
href='delete_hotel.php?id={$row['id']}' onclick='return confirm(\"Are you
sure you want to delete this hotel?\")'>Delete</a>
</td>
</tr>";
}
} else {
echo "<tr><td
colspan='8'>No hotels found.</td></tr>";
}
?>
</table>
</body>
</html>
<?php
// Close connection
mysqli_close($conn);
?>
Explanation
1.
Database Connection:
Connects to the database using mysqli_connect().
2.
Fetch Hotels:
Executes SELECT * FROM hotel ORDER BY id DESC to get all hotels.
3.
HTML Table:
Displays hotel details (ID, Name, City, Country, Rating, Available Rooms,
Image).
4.
Actions:
Provides Edit and Delete links.
o Edit
redirects to edit_hotel.php?id=HOTEL_ID.
o Delete
calls delete_hotel.php?id=HOTEL_ID with a JavaScript confirmation prompt.
5.
Styling:
Adds basic CSS for table layout.
6.
Handles Empty List:
If no hotels exist, it shows No hotels found. message.
How to Use
1.
Save this file as hotel_list.php.
2.
Open hotel_list.php in the browser.
3.
View all hotels in a table format.
4.
Click Edit to update hotel details.
5.
Click Delete to remove a hotel with
a confirmation prompt.