Room availability check

[New-Reign]

I am currently working on a hotel booking system. There are 4 rooms types, 3 have 5 rooms and 1 has 10 rooms. I have everything working apart from the ability to check which are booked or not.
The SQL I used to create the tables is listed below;

CREATE TABLE `Rooms` (
  `roomID` tinyint(2) NOT NULL auto_increment,
  `roomType` tinyint(1) NOT NULL,
  `occupied` tinyint(1) NOT NULL,
  PRIMARY KEY  (`roomID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=26 ;



CREATE TABLE `Bookings` (
  `bookingID` tinyint(4) NOT NULL auto_increment,
  `userID` tinyint(4) NOT NULL,
  `roomID` tinyint(2) NOT NULL,
  `roomType` tinyint(1) NOT NULL,
  `bookingDate` date NOT NULL,
  `dateFrom` date NOT NULL,
  `dateTo` date NOT NULL,
  `cost` decimal(4,2) NOT NULL,
  `fulfilled` tinyint(1) NOT NULL,
  PRIMARY KEY  (`bookingID`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;




CREATE TABLE `RoomTypes` (
  `roomType` tinyint(1) NOT NULL auto_increment COMMENT '1 = FB, 2 = SDB, 3 = TB, 4 = SB',
  `price` decimal(4,2) NOT NULL COMMENT 'Per night',
  PRIMARY KEY  (`roomType`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

My current fucntion to check the availability is;

function dateCheck($date1, $date2) {
    global $mysqli;
    global $roomType;
    $dates = $mysqli->query("SELECT * From Bookings WHERE ('$date1' BETWEEN dateFrom - INTERVAL 1 DAY AND dateTo - INTERVAL 1 DAY) AND ('$date2' BETWEEN dateFrom - INTERVAL 1 DAY AND dateTo - INTERVAL 1 DAY)") or die($mysqli->error);
    $datesRows = $dates->num_rows;
    $check = $mysqli->query("SELECT * From Bookings WHERE occupied = 0 AND roomType = '$roomType'") or die($mysqli->error);
    $checkRows = $check->num_rows;
    var_dump($datesRows);
    while($checkOB = $check->fetch_object()){
        echo "Room ID: " . $checkOB->roomID . ".<br />";
    }
}

$date1 and $date2 are read in from a form.

Any help or even an indication of the way to go about this would be great.

[radow]

This query select all numbers avaible from $date1 to date2

SELECT distinct r.roomID 
FROM Rooms as r 
INNER JOIN Bookings as b 
ON r.roomID = b.roomID
WHERE ('2013-03-20' NOT BETWEEN dateFrom AND dateTo) 
    AND ('2013-03-21' NOT BETWEEN dateFrom AND dateTo)

[New-Reign]

This seems to be along the right lines but wouldn't it omit rows that were booked before the $date1 and are still booked?

[radow]

The query which above, gets numbers which not booked. If you want get booked numbers change "NOT BETWEEN" on "BETWEEN".

[diafol]

<off topic>

Why is the field roomType in bookings? All you need is room_id.

[New-Reign]

@diafol - Thank you, it has now been removed. I hadn't got around to check allmy tables due to being stuck at this point.
The final solution that gives me what I need is;

function dateCheck($date1, $date2) {
    global $mysqli;
    global $roomType;
    $booked = $mysqli->query("UPDATE Rooms AS R INNER JOIN Bookings AS B SET occupied=1 WHERE ('$date1' <= dateTo AND '$date2' >= dateFrom) AND R.roomID = B.roomID") or die($mysqli->error);
    $bookedRows = $booked->num_rows;
    $notBooked = $mysqli->query("SELECT * FROM Rooms AS R WHERE occupied != 1 AND R.roomType = '$roomType'");
    $notBookedRows = $notBooked->num_rows;
    while($notBookedOb = $notBooked->fetch_object()) {
        echo "Room No " . $notBookedOb->roomID . " is free for this booking. <br />"; 
    }
}

There is probably an easier and more efficient way of doing it but until someone points it out, this one will suffice.

Thanks.