Jump to content
chrisrulez001

Booking System, MySQL Issue

Recommended Posts

Hi,

I'm not sure if I'm going about this the right way, I'm trying to create a booking system which allows you to book hotel rooms but I'm having trouble selecting the rooms that are not booked between a certain date period.

I've attached a picture of my database schema.

Here's what I've got when I query the database specifically for the booked rooms between a certain date period:

mysql> SELECT rooms.Room, roomtype.Type, roomtype.Capacity, roomtype.Adults, roomtype.Children,
    ->  bookings.ArriveDate, bookings.DepartureDate
    -> FROM rooms
    -> INNER JOIN roomtype on rooms.TypeID = roomtype.TypeID
    -> INNER JOIN bookings ON rooms.Room = bookings.Room
    -> WHERE roomtype.Adults = 1 AND roomtype.Children = 0 AND bookings.ArriveDate BETWEEN '2019-02-14' AND '2019-02-16';
+------+--------+----------+--------+----------+------------+---------------+
| Room | Type   | Capacity | Adults | Children | ArriveDate | DepartureDate |
+------+--------+----------+--------+----------+------------+---------------+
|    1 | Single |        1 |      1 |        0 | 2019-02-14 | 2019-02-16    |
+------+--------+----------+--------+----------+------------+---------------+
1 row in set (0.00 sec)

If I do a left join but remove the date part of the where clause I get this:

mysql> SELECT rooms.Room, roomtype.Type, roomtype.Capacity, roomtype.Adults, roomtype.Children,
    -> bookings.ArriveDate, bookings.DepartureDate
    -> FROM rooms
    -> INNER JOIN roomtype on rooms.TypeID = roomtype.TypeID
    -> LEFT JOIN bookings ON rooms.Room = bookings.Room WHERE roomtype.Adults = 1 AND roomtype.Children = 0;
+------+--------+----------+--------+----------+------------+---------------+
| Room | Type   | Capacity | Adults | Children | ArriveDate | DepartureDate |
+------+--------+----------+--------+----------+------------+---------------+
|    1 | Single |        1 |      1 |        0 | 2019-02-14 | 2019-02-16    |
|    2 | Single |        1 |      1 |        0 | NULL       | NULL          |
|    3 | Single |        1 |      1 |        0 | NULL       | NULL          |
+------+--------+----------+--------+----------+------------+---------------+
3 rows in set (0.00 sec)

Next, I tried doing the left join with the date part in the where clause and I get an empty set:

mysql> SELECT rooms.Room, roomtype.Type, roomtype.Capacity, roomtype.Adults, roomtype.Children,
    -> bookings.ArriveDate, bookings.DepartureDate
    -> FROM rooms
    -> INNER JOIN roomtype on rooms.TypeID = roomtype.TypeID
    -> LEFT JOIN bookings ON rooms.Room = bookings.Room
    -> WHERE roomtype.Adults = 1 AND roomtype.Children = 0 AND bookings.ArriveDate BETWEEN '2019-02-18' AND '2019-02-20';
Empty set (0.00 sec)

Now I assume this problem is down to ArriveDate and DepartureDate of the other entries being null.

I'm not quite sure where to proceed from this point.

Database-Schema.jpg

Share this post


Link to post
Share on other sites

Right now your query is going along the lines of taking a room, finding bookings for it, and filtering the results according to the date. By putting the date range in the WHERE you require that all rows match the criteria - that it find bookings in the range. That won't help you find rooms without bookings.

What you need to be doing is taking a room, finding bookings for it in the date range, and filtering the results according to whether there was a booking. With an outer join on the bookings you'll have all rooms with and without bookings in the range, then you use the WHERE to find the room+booking rows that did not actually have a matching booking.

Make sense?

Share this post


Link to post
Share on other sites

Thank you both for your replies.

This is the query I ended up with:

mysql> SELECT rooms.room, roomtype.Type, roomtype.Price, roomtype.Adults, roomtype.Children,
    -> bookings.ArriveDate, bookings.DepartureDate
    -> FROM rooms
    -> INNER JOIN roomtype ON rooms.TypeID = roomtype.TypeID
    -> LEFT JOIN bookings ON rooms.Room = bookings.Room AND bookings.ArriveDate < '2019-02-20' AND bookings.DepartureDate > '2019-02-18'
    -> WHERE bookings.Room IS NULL AND roomtype.Adults = 1 AND roomtype.Children = 0 ORDER BY Room;
+------+--------+-------+--------+----------+------------+---------------+
| room | Type   | Price | Adults | Children | ArriveDate | DepartureDate |
+------+--------+-------+--------+----------+------------+---------------+
|    1 | Single | 10.99 |      1 |        0 | NULL       | NULL          |
|    2 | Single | 10.99 |      1 |        0 | NULL       | NULL          |
|    3 | Single | 10.99 |      1 |        0 | NULL       | NULL          |
+------+--------+-------+--------+----------+------------+---------------+
3 rows in set (0.00 sec)

 

Share this post


Link to post
Share on other sites

Hi,

I recently made a change to my database to make the pricing more efficient.

This is the pricing table:

mysql> SELECT * FROM roomprice;
+--------------+--------+-------+------------+------------+
| RoomPrice_ID | TypeID | Price | FromDate   | ToDate     |
+--------------+--------+-------+------------+------------+
|            1 |      1 | 15.99 | 2019-01-01 | 2019-12-31 |
|            2 |      2 | 10.99 | 2019-01-01 | 2019-12-31 |
|            3 |      1 | 16.99 | 2020-01-01 | 2020-12-31 |
+--------------+--------+-------+------------+------------+
3 rows in set (0.00 sec)

Now if I use my original query but change the query to use the new pricing table I ended up with this query, I put the TypeID column in twice in to check it was matching on both tables:

mysql> SELECT rooms.Room, roomtype.Type, ROUND(roomprice.Price * DATEDIFF('2019-02-24', '2019-02-20'), 2) AS Cost, roomtype.Capacity, roomtype.Adults,
    -> roomtype.Children, roomprice.TypeID, roomtype.TypeID,
    -> (SELECT GROUP_CONCAT(facilities.Facility SEPARATOR ', ')
    -> FROM room_facilities
    -> INNER JOIN facilities ON room_facilities.FacilityID = facilities.FacilityID
    -> WHERE room_facilities.Room = rooms.Room) AS Facilities
    -> FROM rooms
    -> INNER JOIN roomtype ON rooms.TypeID = roomtype.TypeID
    -> INNER JOIN roomprice ON rooms.TypeID = roomprice.TypeID AND `roomprice`.`ToDate` >= '2019-02-20' AND `roomprice`.`FromDate` <= '2019-02-24'
    -> LEFT JOIN bookings ON rooms.Room = bookings.Room AND bookings.ArriveDate < '2019-02-24' AND bookings.DepartureDate > '2019-02-20'
    -> WHERE bookings.Room IS NULL AND roomtype.TypeID = 1 AND roomtype.Adults >= 1 AND roomtype.Children >= 0 ORDER BY Room;
+------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+
| Room | Type   | Cost  | Capacity | Adults | Children | TypeID | TypeID | Facilities                                                 |
+------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+
|    4 | Double | 63.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
|    5 | Double | 63.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
|    6 | Double | 63.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
+------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+
3 rows in set (0.00 sec)

This works however if I alter the pricing table and the pricing of the booking goes into the new pricing range like this:

mysql> SELECT * FROM roomprice;
+--------------+--------+-------+------------+------------+
| RoomPrice_ID | TypeID | Price | FromDate   | ToDate     |
+--------------+--------+-------+------------+------------+
|            1 |      1 | 15.99 | 2019-01-01 | 2019-02-22 |
|            2 |      2 | 10.99 | 2019-01-01 | 2019-12-31 |
|            3 |      1 | 16.99 | 2019-02-23 | 2019-12-31 |
+--------------+--------+-------+------------+------------+
3 rows in set (0.00 sec)
mysql> SELECT rooms.Room, roomtype.Type, ROUND(roomprice.Price * DATEDIFF('2019-02-24', '2019-02-20'), 2) AS Cost, roomtype.Capacity, roomtype.Adults,
    -> roomtype.Children, roomprice.TypeID, roomtype.TypeID,
    -> (SELECT GROUP_CONCAT(facilities.Facility SEPARATOR ', ')
    -> FROM room_facilities
    -> INNER JOIN facilities ON room_facilities.FacilityID = facilities.FacilityID
    -> WHERE room_facilities.Room = rooms.Room) AS Facilities
    -> FROM rooms
    -> INNER JOIN roomtype ON rooms.TypeID = roomtype.TypeID
    -> INNER JOIN roomprice ON rooms.TypeID = roomprice.TypeID AND `roomprice`.`ToDate` >= '2019-02-20' AND `roomprice`.`FromDate` <= '2019-02-24'
    -> LEFT JOIN bookings ON rooms.Room = bookings.Room AND bookings.ArriveDate < '2019-02-24' AND bookings.DepartureDate > '2019-02-20'
    -> WHERE bookings.Room IS NULL AND roomtype.TypeID = 1 AND roomtype.Adults >= 1 AND roomtype.Children >= 0 ORDER BY Room;
+------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+
| Room | Type   | Cost  | Capacity | Adults | Children | TypeID | TypeID | Facilities                                                 |
+------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+
|    4 | Double | 63.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
|    4 | Double | 67.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
|    5 | Double | 63.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
|    5 | Double | 67.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
|    6 | Double | 63.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
|    6 | Double | 67.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
+------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+
6 rows in set (0.00 sec)

I end up with it repeating the rows and I've been scratching my head trying to figure it out.

Share this post


Link to post
Share on other sites

1. You could have a 2 day booking where the price on the first day is different from the price on the second (check in 2019-02-22, check out 2019-02-24) so you cannot just multiply the price by the number of days, you need to get price for each day and SUM() them.

2. You get two records for type 1 rooms because two price records are being matched.

So , match each day that is booked for a booking against the price table to get price for each day, SUM the prices and GROUP BY room_id

I think your booking table is like

+---------------+--------+---------------+----------------+
| booking_id    |  room  |  arrive       |  depart        |
+---------------+--------+---------------+----------------+
|  101          |  1     | 2019-02-22    | 2019-02-24     |  (difficult to find prices for intervening days)
+---------------+--------+---------------+----------------+

I prefer

booking
+---------------+--------+---------------+----------------+
| booking_id    | client |  arrive       |  depart        |
+---------------+--------+---------------+----------------+
|  101          |  123   | 2019-02-22    | 2019-02-24     |
+---------------+--------+---------------+----------------+

room_booking
+--------+---------------+---------------+
|Room_id |  occupied     | booking_id    |
+--------+---------------+---------------+
|   1    | 2019-02-22    |  101          |     (match against price where occupied between the price dates)
|   1    | 2019-02-23    |  101          |
+--------+---------------+---------------+

 

Share this post


Link to post
Share on other sites

Yeah, that makes life a lot easier with the dates that are booked in between the arrive date and departure date in a separate table.

Here's the query:

SELECT rooms.Room, roomtype.Type, roomtype.Capacity, roomtype.Adults,
roomtype.Children, Cost,
	(SELECT GROUP_CONCAT(facilities.Facility SEPARATOR ', ')
	FROM room_facilities
	INNER JOIN facilities ON room_facilities.FacilityID = facilities.FacilityID
	WHERE room_facilities.Room = rooms.Room) AS Facilities
FROM rooms
INNER JOIN(SELECT rooms.Room, SUM(roomprice.Price) AS Cost
		   FROM rooms
		   CROSS JOIN temp_room_booking
		   INNER JOIN roomtype ON rooms.TypeID = roomtype.TypeID
		   INNER JOIN roomprice
		   		ON roomprice.TypeID = rooms.TypeID
				AND night BETWEEN roomprice.FromDate AND roomprice.ToDate
			GROUP BY rooms.Room) calc_price ON calc_price.Room = rooms.Room
LEFT JOIN room_booking ON rooms.Room = room_booking.Room
AND room_booking.occupied >= '2019-03-15' AND room_booking.occupied < '2019-03-19'
INNER JOIN roomtype ON rooms.TypeID = roomtype.TypeID
WHERE room_booking.occupied IS NULL AND roomtype.TypeID = 1 AND roomtype.Adults >= 1 AND roomtype.Children >= 0
ORDER BY rooms.Room;

There was a bit of head scratching before I realised I needed to use a temporary table to work out the cost of the room. 

Thanks again for your help ?

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.