-2

I am working for my degree project on a Hotel Room Reservation System. And i have a problem at the check availability stage.

Here is my tables structure :

bookings:

`ID` int(11) 

`roomID` int(11)

`userID` int(11)

`checkin` date
 
`checkout` date

rooms:

`ID`  int(11)              
`name`    varchar(255)                     
`room_qty`  int(11)

So here is the query that i have used in my code (the $checkin and the $checkout variable have the dates given by the client stored in them ) :

$avlrooms = "SELECT DISTINCT rooms.ID FROM rooms WHERE rooms.ID NOT IN (SELECT DISTINCT roomID FROM bookings WHERE (checkin <= '$checkin' AND checkout >='$checkout') OR  
               (checkin >= '$checkin' AND checkin <='$checkout') OR 
               (checkin <= '$checkin' AND checkout >='$checkin') )";

$room_query ="SELECT * FROM rooms WHERE ID IN ($avlrooms)";

So for example if i have a room called (single room) and has 3 room_qty in total, the problem is that even if just one of the (single room) is booked between 10/05/2022 and 15/05/2022 the rest of of the 2 rooms will not be displayed also between these dates.

So what can i do to solve this problem without changing my tables in the database ?

thank you

anes
  • 1
  • 1
  • 1
    Get rid of `room_qty` and give each room a row in the database. Then you can also add fields like `roomNo`, etc. – KIKO Software May 08 '22 at 15:36
  • Yeah mate that will be a good solution, but it will be good if i can solve the problem without making modification on my tables. – anes May 08 '22 at 15:42
  • I understand that, but you made a wrong design decision here. A room is clearly an entity, like a guest. You match _guest_ with _rooms_ through _bookings_. All three are entities you would want to represent in your database design. You're not going to make a guest table with two rows, one for business and one for private guests, and then add a 'guest_qty' field, it makes no sense. – KIKO Software May 08 '22 at 15:47
  • yeah you are right, so i should change some things in the tables. – anes May 08 '22 at 15:50
  • You'll find that, with each room having its own row in the database, the problem you encountered doesn't even occur. And counting rooms of the same type (= name) is still easy. – KIKO Software May 08 '22 at 15:52
  • **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) – Dharman May 08 '22 at 20:21

0 Answers0