How to develop Projects(HBS)
Using PHP and Mysql
Part 12
5️⃣ Hotel Booking System
✅ Module
4: Payment & Invoice Management
🎯 Purpose:
This module allows customers to pay for their bookings and generate invoices.
It also includes the functionality for the admin to manage payments and view
payment reports.
✅ Functionalities
to Implement:
For Users (Customers):
1.
Make Payment:
o Customers
should be able to make payments for their confirmed bookings using available
payment methods (e.g., credit card, PayPal, etc.).
2.
View Payment Status:
o After
making the payment, users should be able to view the status of their payment
(Pending, Completed, Failed).
3.
Generate Invoice:
o Once
the payment is successful, users should be able to generate an invoice that
includes booking details, payment amount, and payment method.
For Admin/Hotel Owner:
1.
View All Payments:
o Admins
should be able to view a list of all payments, including the status (Pending,
Completed, Failed), payment method, and amounts.
2.
Approve or Confirm Payments (Optional):
o Admins
can manually confirm the payment if needed, especially if the payment method
requires manual verification.
3.
Generate Payment Reports:
o Admins
should be able to generate reports related to payments, such as total payments,
pending payments, or payments by method.
📁
Suggested File/Folder Structure:
/payments/├── make_payment.php # Form to make a payment├── payment_status.php # View payment status├── generate_invoice.php # Generate payment invoice├── payment_history.php # View user's payment history├── manage_payments.php # Admin view of all payments├── payment_reports.php # Admin report generation🧮
Database Tables:
1. payments
Table:
This table stores
details about the payments made by users.
CREATE TABLE payments ( id INT AUTO_INCREMENT PRIMARY KEY, booking_id INT NOT NULL, payment_method ENUM('Credit Card', 'PayPal', 'Cash', 'Bank Transfer') NOT NULL, payment_status ENUM('Pending', 'Completed', 'Failed') DEFAULT 'Pending', amount DECIMAL(10,2) NOT NULL, payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (booking_id) REFERENCES bookings(id));- Fields:
id: Unique identifier for each payment.booking_id: The ID of the booking associated with the payment.payment_method: The method used for payment (Credit Card, PayPal, etc.).payment_status: The status of the payment (Pending, Completed, Failed).amount: The total amount paid.payment_date: Timestamp of when the payment was made.
2. invoices
Table:
This table stores
invoice details for completed payments.
CREATE TABLE invoices ( id INT AUTO_INCREMENT PRIMARY KEY, payment_id INT NOT NULL, invoice_number VARCHAR(50) UNIQUE NOT NULL, invoice_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, total_amount DECIMAL(10,2), FOREIGN KEY (payment_id) REFERENCES payments(id));- Fields:
id: Unique identifier for the invoice.payment_id: The ID of the payment associated with the invoice.invoice_number: A unique invoice number for each payment.invoice_date: Date and time when the invoice was generated.total_amount: Total amount on the invoice.
💡
Implementation Tips:
1.
Payment Integration:
o Integrate
with a payment gateway like Stripe, PayPal, or others for processing payments.
Use APIs to communicate with the payment service.
2.
Payment Status:
o After
a payment is made, update the payment status in the database to either
"Completed" or "Failed" based on the payment gateway
response.
3.
Invoice Generation:
o Once
payment is successful, generate an invoice by pulling the relevant details from
the payment and booking records.
4.
Admin Payment Management:
o Admin
should be able to see all payments, verify statuses, and confirm payments
manually if required.
5.
Payment Reports:
o Admin
can view detailed reports based on different filters (payment method, date
range, etc.) to analyze financial data.
✅ Module
5: User Profile & Account Management
🎯 Purpose:
This module allows users to manage their accounts, view and update personal
details, and manage preferences for bookings, such as saved payment methods and
room preferences. It also enables admins to manage users.
✅
Functionalities to Implement:
For Users (Customers):
1.
Create/Update User Profile:
o Users
should be able to create and update their personal profiles, including their
name, contact information, and address.
2.
Change Password:
o Users
should be able to change their account password securely.
3.
Manage Payment Methods:
o Allow
users to save, update, and delete payment methods for future bookings.
4.
Set Room Preferences:
o Users
should be able to save their room preferences (e.g., smoking/non-smoking,
king-size bed, specific floor) for future bookings.
5.
View Booking and Payment History:
o Users
should be able to view past bookings and payment history.
For Admin/Hotel Owner:
1.
View All Users:
o Admin
should be able to view a list of all registered users with their details,
booking history, and profile status.
2.
Edit User Profiles:
o Admin
should be able to update user profiles, including roles (e.g., change user to
admin) or any necessary corrections to their information.
3.
Deactivate or Delete User Accounts:
o Admin
should have the option to deactivate or delete user accounts based on the
business logic.
4.
Manage User Preferences:
o Admin
should be able to view and manage users’ saved room preferences if needed.
📁 File/Folder Structure:
/users/├── profile.php # View and update user profile├── change_password.php # Form to change user password├── manage_payment_methods.php # Manage payment methods├── manage_preferences.php # Manage user preferences (e.g., room preferences)├── booking_history.php # View user's past bookings├── manage_users.php # Admin view of all users├── user_reports.php # Admin report generation for users🧮
Database Tables:
1. users
Table:
This table stores user
profile information.
CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, password VARCHAR(255) NOT NULL, phone VARCHAR(15), address TEXT, role ENUM('User', 'Admin') DEFAULT 'User', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);- Fields:
id: Unique identifier for each user.name: Full name of the user.email: Email address, used for login.password: Hashed password for account security.phone: Optional phone number.address: Optional address field.role: The role of the user (User or Admin).created_at: Timestamp when the user account was created.updated_at: Timestamp when the user account was last updated.
2. payment_methods
Table:
This table stores the
payment methods saved by users.
CREATE TABLE payment_methods ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, card_type ENUM('Credit Card', 'Debit Card', 'PayPal') NOT NULL, card_number VARCHAR(20) NOT NULL, expiration_date DATE, billing_address TEXT, FOREIGN KEY (user_id) REFERENCES users(id));- Fields:
id: Unique identifier for each payment method.user_id: Foreign key reference to the user.card_type: The type of card or payment method.card_number: Card number or payment account.expiration_date: Expiration date for card-based methods.billing_address: Address associated with the payment method.
3. user_preferences
Table:
This table stores the
room preferences saved by users.
CREATE TABLE user_preferences ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, room_type VARCHAR(100), bed_type ENUM('Single', 'Double', 'King-size', 'Queen-size'), smoking_preference ENUM('Smoking', 'Non-smoking'), floor_preference INT, FOREIGN KEY (user_id) REFERENCES users(id));- Fields:
id: Unique identifier for each preference.user_id: Foreign key reference to the user.room_type: Type of room preferred (e.g., suite, single).bed_type: Preferred bed type (single, double, king-size, etc.).smoking_preference: User's smoking preference (smoking or non-smoking).floor_preference: User's preferred floor level.
💡
Implementation Tips:
1.
Profile Management:
o Provide
form validation on the profile and password change forms to ensure user data
integrity and security.
o Use
hashed passwords (e.g., bcrypt) to securely store user passwords.
2.
Payment Methods:
o Store
only the necessary information related to payment methods for security. For
example, never store full credit card numbers. You can use payment gateway
tokenization services to safely store payment information.
3.
Room Preferences:
o Allow
users to select their preferences while booking a room, and save those
preferences to pre-fill their future bookings.
4.
Admin User Management:
o Implement
CRUD operations for user accounts, allowing admins to update profiles,
deactivate, or delete users.
o Use
pagination for the user list to ensure it doesn't become too large and
difficult to manage.
5.
Security:
o Use
HTTPS for all data transmissions, especially when handling passwords, payment
methods, and sensitive information.
📂 /payments/make_payment.php
<?php// Example: Process Payment Formif ($_SERVER['REQUEST_METHOD'] == 'POST') { $user_id = 1; // Example user ID (You can fetch this from session) $amount = $_POST['amount']; $payment_method = $_POST['payment_method']; // DB connection (adjust according to your setup) include('../db_connect.php'); $query = "INSERT INTO payments (user_id, amount, payment_method, status) VALUES ('$user_id', '$amount', '$payment_method', 'Pending')"; if (mysqli_query($conn, $query)) { echo "Payment initiated successfully."; } else { echo "Error: " . mysqli_error($conn); }}?> <form method="POST" action=""> Amount: <input type="number" name="amount" required><br> Payment Method: <select name="payment_method" required> <option value="Credit Card">Credit Card</option> <option value="PayPal">PayPal</option> </select><br> <input type="submit" value="Pay Now"></form>📂
/payments/payment_status.php
<?phpinclude('../db_connect.php');$payment_id = $_GET['id']; // Get Payment ID from URL $result = mysqli_query($conn, "SELECT status FROM payments WHERE id = $payment_id"); if ($row = mysqli_fetch_assoc($result)) { echo "Payment Status: " . $row['status'];} else { echo "Payment not found!";}?>
📂
/payments/generate_invoice.php
<?phpinclude('../db_connect.php');$payment_id = $_GET['id']; $result = mysqli_query($conn, "SELECT * FROM payments WHERE id = $payment_id"); if ($row = mysqli_fetch_assoc($result)) { echo "<h1>Invoice</h1>"; echo "Payment ID: " . $row['id'] . "<br>"; echo "User ID: " . $row['user_id'] . "<br>"; echo "Amount: $" . $row['amount'] . "<br>"; echo "Method: " . $row['payment_method'] . "<br>"; echo "Status: " . $row['status'] . "<br>"; echo "Date: " . $row['created_at'] . "<br>";} else { echo "Invoice not available!";}?>
📂
/payments/payment_history.php
<?phpinclude('../db_connect.php');$user_id = 1; // Example (replace with session ID) $result = mysqli_query($conn, "SELECT * FROM payments WHERE user_id = $user_id"); echo "<h2>Your Payment History</h2><ul>";while ($row = mysqli_fetch_assoc($result)) { echo "<li>Amount: $" . $row['amount'] . " | Status: " . $row['status'] . "</li>";}echo "</ul>";?>
📂
/payments/manage_payments.php
(Admin View)
<?phpinclude('../db_connect.php'); $result = mysqli_query($conn, "SELECT * FROM payments ORDER BY created_at DESC"); echo "<h2>All Payments</h2><table border='1'>";echo "<tr><th>ID</th><th>User ID</th><th>Amount</th><th>Status</th><th>Method</th><th>Date</th></tr>";while ($row = mysqli_fetch_assoc($result)) { echo "<tr> <td>{$row['id']}</td> <td>{$row['user_id']}</td> <td>\${$row['amount']}</td> <td>{$row['status']}</td> <td>{$row['payment_method']}</td> <td>{$row['created_at']}</td> </tr>";}echo "</table>";?>
📂
/payments/payment_reports.php
<?phpinclude('../db_connect.php'); $result = mysqli_query($conn, "SELECT payment_method, SUM(amount) as total_amount FROM payments GROUP BY payment_method"); echo "<h2>Payment Report by Method</h2><table border='1'>";echo "<tr><th>Method</th><th>Total Amount</th></tr>";while ($row = mysqli_fetch_assoc($result)) { echo "<tr><td>{$row['payment_method']}</td><td>\${$row['total_amount']}</td></tr>";}echo "</table>";?>
💡
payments table for MySQL:
CREATE TABLE payments ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, amount DECIMAL(10, 2) NOT NULL, payment_method VARCHAR(100) NOT NULL, status ENUM('Pending', 'Completed', 'Failed') DEFAULT 'Pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id));📂
/users/profile.php
View and update user profile
<?phpsession_start();include('../db_connect.php'); $user_id = $_SESSION['user_id']; // Assuming you set session on login if ($_SERVER['REQUEST_METHOD'] == 'POST') { $name = $_POST['name']; $phone = $_POST['phone']; $address = $_POST['address']; $query = "UPDATE users SET name='$name', phone='$phone', address='$address' WHERE id=$user_id"; if (mysqli_query($conn, $query)) { echo "Profile updated successfully!"; } else { echo "Error: " . mysqli_error($conn); }} $result = mysqli_query($conn, "SELECT * FROM users WHERE id=$user_id");$user = mysqli_fetch_assoc($result);?> <form method="POST"> Name: <input type="text" name="name" value="<?= $user['name'] ?>" required><br> Phone: <input type="text" name="phone" value="<?= $user['phone'] ?>"><br> Address: <textarea name="address"><?= $user['address'] ?></textarea><br> <input type="submit" value="Update Profile"></form>📂
/users/change_password.php
Change user password
<?phpsession_start();include('../db_connect.php'); $user_id = $_SESSION['user_id']; if ($_SERVER['REQUEST_METHOD'] == 'POST') { $old = md5($_POST['old_password']); $new = md5($_POST['new_password']); $check = mysqli_query($conn, "SELECT * FROM users WHERE id=$user_id AND password='$old'"); if (mysqli_num_rows($check) > 0) { mysqli_query($conn, "UPDATE users SET password='$new' WHERE id=$user_id"); echo "Password updated successfully!"; } else { echo "Incorrect old password!"; }}?> <form method="POST"> Old Password: <input type="password" name="old_password" required><br> New Password: <input type="password" name="new_password" required><br> <input type="submit" value="Change Password"></form>📂
/users/manage_payment_methods.php
Manage user payment methods
<?phpsession_start();include('../db_connect.php'); $user_id = $_SESSION['user_id']; if ($_SERVER['REQUEST_METHOD'] == 'POST') { $card_type = $_POST['card_type']; $card_number = $_POST['card_number']; $expiration_date = $_POST['expiration_date']; $billing_address = $_POST['billing_address']; $query = "INSERT INTO payment_methods (user_id, card_type, card_number, expiration_date, billing_address) VALUES ('$user_id', '$card_type', '$card_number', '$expiration_date', '$billing_address')"; mysqli_query($conn, $query); echo "Payment method added!";} $result = mysqli_query($conn, "SELECT * FROM payment_methods WHERE user_id = $user_id"); echo "<h2>Your Payment Methods:</h2>";while ($row = mysqli_fetch_assoc($result)) { echo "Card: {$row['card_type']} - {$row['card_number']}<br>";}?> <form method="POST"> Card Type: <select name="card_type" required> <option value="Credit Card">Credit Card</option> <option value="Debit Card">Debit Card</option> <option value="PayPal">PayPal</option> </select><br> Card Number: <input type="text" name="card_number" required><br> Expiration Date: <input type="date" name="expiration_date"><br> Billing Address: <textarea name="billing_address"></textarea><br> <input type="submit" value="Add Payment Method"></form>📂
/users/manage_preferences.php
Manage user room preferences
<?phpsession_start();include('../db_connect.php'); $user_id = $_SESSION['user_id']; if ($_SERVER['REQUEST_METHOD'] == 'POST') { $room_type = $_POST['room_type']; $bed_type = $_POST['bed_type']; $smoking = $_POST['smoking']; $floor = $_POST['floor']; mysqli_query($conn, "INSERT INTO user_preferences (user_id, room_type, bed_type, smoking_preference, floor_preference) VALUES ('$user_id', '$room_type', '$bed_type', '$smoking', '$floor') ON DUPLICATE KEY UPDATE room_type='$room_type', bed_type='$bed_type', smoking_preference='$smoking', floor_preference='$floor'"); echo "Preferences saved!";}?> <form method="POST"> Room Type: <input type="text" name="room_type"><br> Bed Type: <select name="bed_type"> <option>Single</option> <option>Double</option> <option>King-size</option> <option>Queen-size</option> </select><br> Smoking: <select name="smoking"> <option>Smoking</option> <option>Non-smoking</option> </select><br> Floor Preference: <input type="number" name="floor"><br> <input type="submit" value="Save Preferences"></form>📂
/users/booking_history.php
View user's past bookings
<?phpsession_start();include('../db_connect.php'); $user_id = $_SESSION['user_id']; $result = mysqli_query($conn, "SELECT * FROM bookings WHERE user_id = $user_id"); echo "<h2>Your Bookings:</h2>";while ($row = mysqli_fetch_assoc($result)) { echo "Room ID: {$row['room_id']} - Check-in: {$row['check_in']} to Check-out: {$row['check_out']} - Status: {$row['status']}<br>";}?>
📂
/users/manage_users.php
Admin view of all users
<?phpinclude('../db_connect.php'); $result = mysqli_query($conn, "SELECT * FROM users"); echo "<h2>All Registered Users</h2>";echo "<table border='1'><tr><th>ID</th><th>Name</th><th>Email</th><th>Role</th></tr>";while ($row = mysqli_fetch_assoc($result)) { echo "<tr><td>{$row['id']}</td><td>{$row['name']}</td><td>{$row['email']}</td><td>{$row['role']}</td></tr>";}echo "</table>";?>
📂
/users/user_reports.php
Admin report generation for users
<?phpinclude('../db_connect.php'); $result = mysqli_query($conn, "SELECT role, COUNT(*) as total FROM users GROUP BY role"); echo "<h2>User Report by Role</h2>";echo "<table border='1'><tr><th>Role</th><th>Total Users</th></tr>";while ($row = mysqli_fetch_assoc($result)) { echo "<tr><td>{$row['role']}</td><td>{$row['total']}</td></tr>";}echo "</table>";?>