Checking Room Availability For Hotel Booking System
I was wondering if anyone could help me with a problem I'm having.
I've been using Dreamweaver to create a hotel booking system for a
friend of mine, using MySQL (version 4.0.21) and PHP 5. The bit I'm
struggling with is checking the Room Availability based on dates that
are typed into a textfield and then returning a list of the available
rooms on the next page.
The three tables involved in this function are:
CREATE TABLE `room` (
Room_Number tinyint(1) UNSIGNED NOT NULL,
Price_Double_per_Night decimal(5,2) NOT NULL,
Price_Twin_per_Night decimal(5,2) NOT NULL,
Price_Single_per_Night decimal(5,2) NOT NULL,
Price_Double_per_Week decimal(5,2) NOT NULL,
Price_Twin_per_Week decimal(5,2) NOT NULL,
Price_Single_per_Week decimal(5,2) NOT NULL,
PRIMARY KEY(`Room_Number`),
)
TYPE=InnoDB
ROW_FORMAT=fixed;
CREATE TABLE `room_booking_link_entity` (
Room_Number tinyint(1) UNSIGNED NOT NULL,
Room_Booking_ID int(11) NOT NULL,
Single_Double_Twin enum('Single','Double','Twin') NOT NULL,
PRIMARY KEY(`Room_Number`, `Room_Booking_ID`),
INDEX `Room_Number`(`Room_Number`),
INDEX `Room_Booking_ID`(`Room_Booking_ID`),
FOREIGN KEY `Reference_14`(`Room_Number`)
REFERENCES `room`(`Room_Number`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY `Reference_85`(`Room_Booking_ID`)
REFERENCES `room_booking`(`Room_Booking_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
TYPE=InnoDB
ROW_FORMAT=fixed;
CREATE TABLE `room_booking` (
Room_Booking_ID int(11) NOT NULL AUTO_INCREMENT,
Customer_ID int(11) NOT NULL,
Bill_ID int(11) NOT NULL,
Date_of_Arrival date NOT NULL,
Date_of_Departure date NOT NULL,
Number_of_Nights tinyint(3) NOT NULL,
Date_Booking_Made date,
Status_of_Booking enum('Booked','Arrived','Departed') NOT NULL,
Total_Cost decimal(7,2) NOT NULL,
PRIMARY KEY(`Room_Booking_ID`),
INDEX `Room_Booking_ID`(`Room_Booking_ID`),
INDEX `Customer_ID`(`Customer_ID`),
INDEX `Bill_ID`(`Bill_ID`),
INDEX `Date_of_Arrival`(`Date_of_Arrival`),
INDEX `Date_of_Departure`(`Date_of_Departure`),
INDEX `Status_of_Booking`(`Status_of_Booking`),
FOREIGN KEY `Reference_03`(`Customer_ID`)
REFERENCES `customer`(`Customer_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY `Reference_14`(`Bill_ID`)
REFERENCES `bill`(`Bill_ID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
TYPE=InnoDB
ROW_FORMAT=fixed;
I've just discovered that MySQL 4.0.21 does not support subqueries.
I'm basically stuck with this version of MySQL. I think I've got
round the subquery problem by using a multi query option as follows:
1.Create a temptable that will store all of the room numbers that
have bookings for the particular dates requested as follows:
SELECT DISTINCT a.room_number INTO temptable
FROM booking_link_entity AS a INNER JOIN room_booking AS b
WHERE b.departure_date >$_POST[‘ARRIVAL'] And b.arrival_date <
$_POST[‘DEPART'];
2.Left join this with the room table and pull out rows with no match
SELECT a.room_number, b.room_number
FROM room AS a LEFT JOIN temptable AS b ON a.room_number=b.room_number
WHERE b.room_number IS NULL;
3.Delete temp table
I've tried doing this in Dreamweaver, but to no avail. I have a
feeling it is too complex for Dreamweaver.
My php knowledge is very basic and was wondering if anyone could give
me some pointers as how to do this in PHP?.
View Replies !
TRACKER
