Hi
I am trying to build search query for hotel rooms availability but it seems that his query is way over my head, and i need
help to build it. Note that there will be multiply hotels in the database.
Even that I am looking for available rooms, my idea was not to build availability table, but instead to use reservation
table, and I assume that if the room is not in the reservation table, it is available. (If you have any suggestions about this solution
I would like to hear it)
I have the following fields in the search form:
area (represented with areaid), checkInDate, checkOutDate, rooms (how many rooms he need), adults and children’s.
Here are the tables that should be involved in this search:
room
roomType
reservationroom
hotels
Here are the tables:
CREATE TABLE `room` (
`roomID` int(11) NOT NULL AUTO_INCREMENT,
`hotelID` int(11) NOT NULL,
`roomtypeID` int(11) NOT NULL,
`roomNumber` int(11) NOT NULL,
`roomName` varchar(255) NOT NULL,
`roomDescription` text,
`roomVisible` tinyint(4) NOT NULL,
PRIMARY KEY (`roomID`),
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;
CREATE TABLE `roomtype` (
`roomtypeID` int(11) NOT NULL AUTO_INCREMENT,
`hotelID` int(11) NOT NULL,
`roomtypeName` varchar(255) NOT NULL,
`roomtypeAdults` int(11) NOT NULL,
`roomtypeChildrens` int(11) NOT NULL,
`roomtypeDescription` text,
PRIMARY KEY (`roomtypeID`),
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
CREATE TABLE `hotel` (
`hotelID` int(11) NOT NULL AUTO_INCREMENT,
`areaID` int(11) NOT NULL,
`hotelcategoryID` int(11) DEFAULT NULL,
`hotelName` varchar(255) NOT NULL,
`hotelShortDescription` text,
`hotelAddress` varchar(255) DEFAULT NULL
PRIMARY KEY (`hotelID`),
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
CREATE TABLE `reservationroom` (
`reservationroomID` int(11) NOT NULL AUTO_INCREMENT,
`reservationID` int(11) NOT NULL,
`hotelID` int(11) NOT NULL,
`roomID` int(11) NOT NULL,
PRIMARY KEY (`reservationroomID`)
) ENGINE=InnoDB DEFAULT CHARSET=greek;
Please note that I have removed the unnecessery fields from the tables, to make the code shorter and more easier to read.
Can anyone point me how to build this query to find available rooms? While I do know how to make simple join, this query is way over my head, and any help will be deeply appreciated.
Regards, zoreli