How to select available date slice

Question

I am trying to make a simple reservation system. I have a table called reservation that holds reservation time, and id of the customer who reserved it (see below for schema). And I have another table room, this table is for room types, so each type of room has an entry in this table for it's size and price. And another table rooms, for actual entry about each room. In the query I try to make, I want to select one room, that is of particular type, and that is available in the given time slice. Something like this;

Select number from rooms where type = 4 and available between $start-date and $end-date;

I couldn't figure out how to write available between part of the query. Relevant part of my schema follows;

CREATE TABLE IF NOT EXISTS `reservation` (
  `rid` int(11) NOT NULL AUTO_INCREMENT, /* reservation id */
  `number` int(11) NOT NULL, /* number of the room for this reservation */
  `cid` int(11) NOT NULL, /* customer id */
  `begin` date NOT NULL,
  `end` date NOT NULL,
  PRIMARY KEY (`rid`),
);

CREATE TABLE IF NOT EXISTS `room` (
  `rid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `size` tinyint(3) unsigned NOT NULL,
  `price` float NOT NULL,
  PRIMARY KEY (`rid`)
);

CREATE TABLE IF NOT EXISTS `rooms` (
  `number` smallint(5) unsigned NOT NULL,
  `type` int(11) NOT NULL,
  `available` tinyint(1) NOT NULL,
  `cid` int(11) DEFAULT NULL,
  PRIMARY KEY (`number`),
);

You should join `rooms` and `reservation` and query the number of reservations between your start date & end date . — Raptor, May 16 '12 at 03:11

score 0 · Answer 1 · edited May 23 '17 at 12:27

Although not the exact same table structure as you have, but here's another link to a reservation query... you can review the how / why there, but you should be ok to get what you are looking for by doing...

SELECT DISTINCT        
      rooms.number
   FROM        
      rooms
         LEFT JOIN reservations res
            ON rooms.number = res.number
           AND (  res.begin between '2012-04-05' and '2012-04-08'                
               OR res.end between '2012-04-05' and '2012-04-08' )
   WHERE          
          rooms.type = 4
      AND res.number IS NULL

The premise is to look at each room and SPECIFICALLY LOOK for a reservation within the date in question. If ANY reservation being or end date is within the range, its occupied. if not, then NO reservation and thus available. So, the LEFT JOIN will always allow the room to be returned (provided type = 4), but ALSO, only if the reservations table number value is NULL, indicating no reservation on file, no record, null match... the room IS available.

score 0 · Accepted Answer · answered May 16 '12 at 06:05

I am not sure if this is a good solution, but I ended up doing this:

SELECT number, room.price
FROM rooms
JOIN room on room.rid = rooms.type 
WHERE rooms.type = ".$room_id."
AND rooms.number NOT IN (
    SELECT number FROM reservation
    WHERE end >= FROM_UNIXTIME(".$start.")
    AND begin <= FROM_UNIXTIME(".$end.")
) LIMIT 1;

How to select available date slice

2 Answers2