Rooms availability checking

Question

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