PHP MySQL query to selecting available room in hotel booking system

PHP MySQL query to selecting available room in hotel booking system

To select available rooms in a hotel booking system using PHP and MySQL, you typically need to query the database to find rooms that are not booked during a specified date range. Here's a step-by-step guide on how to construct this query:

Assumptions

Let's assume you have two relevant tables in your database:

  1. rooms: Contains information about each room, such as room_id and room_name.
  2. bookings: Contains bookings made for each room, with fields like booking_id, room_id, checkin_date, and checkout_date.

Query Logic

To find available rooms for a given date range (e.g., from checkin_date to checkout_date), you need to find rooms that do not have any bookings that overlap with this date range.

MySQL Query

Here's how you can construct the MySQL query to select available rooms:

<?php // Define your date range (checkin and checkout dates) $checkin = '2024-07-15'; $checkout = '2024-07-20'; // Connect to your MySQL database $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "your_database"; $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } // Construct the SQL query to find available rooms $sql = "SELECT r.room_id, r.room_name FROM rooms r WHERE r.room_id NOT IN ( SELECT b.room_id FROM bookings b WHERE b.checkin_date < '$checkout' AND b.checkout_date > '$checkin' )"; $result = $conn->query($sql); if ($result->num_rows > 0) { // Output data of each row while ($row = $result->fetch_assoc()) { echo "Room ID: " . $row["room_id"] . " - Room Name: " . $row["room_name"] . "<br>"; } } else { echo "No available rooms found for the selected dates."; } $conn->close(); ?>

Explanation

  • Subquery: The inner subquery (SELECT b.room_id ...) selects room_id from bookings where the checkin_date is before the specified checkout date and the checkout_date is after the specified checkin date. This identifies rooms that are already booked during the specified date range.

  • Main Query: The outer query then selects rooms (rooms r) that are not in the list of booked rooms identified by the subquery. This gives you a list of available rooms for the given date range.

Notes

  1. SQL Injection: Always sanitize and validate user inputs to prevent SQL injection attacks. Consider using prepared statements instead of directly inserting variables into your SQL queries.

  2. Date Handling: Ensure your date format matches the format stored in your database (YYYY-MM-DD for MySQL's DATE type).

  3. Indexing: Consider indexing checkin_date and checkout_date in the bookings table for improved query performance, especially if dealing with large datasets.

By using this approach, you can effectively retrieve a list of available rooms in your hotel booking system for a specified date range using PHP and MySQL. Adjust the query and date handling as per your specific application requirements and database structure.

Examples

  1. How to select available rooms based on check-in and check-out dates?

    $check_in = '2024-07-01'; $check_out = '2024-07-05'; $query = "SELECT * FROM rooms WHERE id NOT IN ( SELECT room_id FROM bookings WHERE (check_in < '$check_out' AND check_out > '$check_in') )"; $result = mysqli_query($conn, $query);

    Description: This query selects all rooms that are not booked during the specified check-in and check-out dates by checking against the bookings table.

  2. How to filter available rooms by room type?

    $room_type = 'Deluxe'; $check_in = '2024-07-01'; $check_out = '2024-07-05'; $query = "SELECT * FROM rooms WHERE type = '$room_type' AND id NOT IN ( SELECT room_id FROM bookings WHERE (check_in < '$check_out' AND check_out > '$check_in') )"; $result = mysqli_query($conn, $query);

    Description: This code filters available rooms by a specific type while checking for overlapping bookings.

  3. How to check availability for a specific room number?

    $room_number = 101; $check_in = '2024-07-01'; $check_out = '2024-07-05'; $query = "SELECT * FROM rooms WHERE room_number = $room_number AND id NOT IN ( SELECT room_id FROM bookings WHERE (check_in < '$check_out' AND check_out > '$check_in') )"; $result = mysqli_query($conn, $query);

    Description: This query checks if a specific room is available for the selected dates.

  4. How to display available rooms with their prices?

    $check_in = '2024-07-01'; $check_out = '2024-07-05'; $query = "SELECT rooms.*, prices.price FROM rooms JOIN prices ON rooms.id = prices.room_id WHERE rooms.id NOT IN ( SELECT room_id FROM bookings WHERE (check_in < '$check_out' AND check_out > '$check_in') )"; $result = mysqli_query($conn, $query);

    Description: This code selects available rooms along with their prices from a related prices table.

  5. How to count the number of available rooms?

    $check_in = '2024-07-01'; $check_out = '2024-07-05'; $query = "SELECT COUNT(*) AS available_rooms FROM rooms WHERE id NOT IN ( SELECT room_id FROM bookings WHERE (check_in < '$check_out' AND check_out > '$check_in') )"; $result = mysqli_query($conn, $query); $row = mysqli_fetch_assoc($result); $available_rooms = $row['available_rooms'];

    Description: This query counts the total number of available rooms for the specified dates.

  6. How to get room amenities for available rooms?

    $check_in = '2024-07-01'; $check_out = '2024-07-05'; $query = "SELECT rooms.*, amenities.amenity FROM rooms JOIN amenities ON rooms.id = amenities.room_id WHERE rooms.id NOT IN ( SELECT room_id FROM bookings WHERE (check_in < '$check_out' AND check_out > '$check_in') )"; $result = mysqli_query($conn, $query);

    Description: This code retrieves available rooms along with their associated amenities.

  7. How to sort available rooms by price?

    $check_in = '2024-07-01'; $check_out = '2024-07-05'; $query = "SELECT rooms.*, prices.price FROM rooms JOIN prices ON rooms.id = prices.room_id WHERE rooms.id NOT IN ( SELECT room_id FROM bookings WHERE (check_in < '$check_out' AND check_out > '$check_in') ) ORDER BY prices.price ASC"; $result = mysqli_query($conn, $query);

    Description: This query selects and sorts available rooms by price in ascending order.

  8. How to filter available rooms with a minimum rating?

    $min_rating = 4; $check_in = '2024-07-01'; $check_out = '2024-07-05'; $query = "SELECT rooms.*, ratings.rating FROM rooms JOIN ratings ON rooms.id = ratings.room_id WHERE ratings.rating >= $min_rating AND rooms.id NOT IN ( SELECT room_id FROM bookings WHERE (check_in < '$check_out' AND check_out > '$check_in') )"; $result = mysqli_query($conn, $query);

    Description: This code filters available rooms based on a minimum rating requirement.

  9. How to implement pagination for available rooms?

    $check_in = '2024-07-01'; $check_out = '2024-07-05'; $limit = 10; $offset = ($page - 1) * $limit; $query = "SELECT rooms.* FROM rooms WHERE rooms.id NOT IN ( SELECT room_id FROM bookings WHERE (check_in < '$check_out' AND check_out > '$check_in') ) LIMIT $limit OFFSET $offset"; $result = mysqli_query($conn, $query);

    Description: This code implements pagination by limiting the number of available rooms returned per page.

  10. How to handle overlapping bookings using transactions?

    mysqli_begin_transaction($conn); try { $check_in = '2024-07-01'; $check_out = '2024-07-05'; $query = "SELECT * FROM rooms WHERE id NOT IN ( SELECT room_id FROM bookings WHERE (check_in < '$check_out' AND check_out > '$check_in') ) FOR UPDATE"; $result = mysqli_query($conn, $query); // Handle booking logic here... mysqli_commit($conn); } catch (Exception $e) { mysqli_rollback($conn); }

    Description: This code demonstrates how to manage overlapping bookings safely using transactions.

More Tags

webviewclient paragraph calculated-field python-module alfresco ora-01017 greenplum android-snackbar user-roles reset

More Programming Questions

More Geometry Calculators

More General chemistry Calculators

More Dog Calculators

More Date and Time Calculators