checking room availability for hotel booking system

[Dave Robinson]

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?. Thanks very much.

Cheers,

Dave

[Geoff Berrow]

I noticed that Message-ID:
<48*************************@posting.google.com> from Dave Robinson
contained the following:

My php knowledge is very basic and was wondering if anyone could give
me some pointers as how to do this in PHP?. Thanks very much.

I must be tired. I can't see why you need three tables here (presumably
you have another table for customers).

Why not:

Rooms-<Bookings>-Customers

?

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[david.g.robinson]

Hi Geoff,

There is another table for customers. There are three tables here
because a room booking can have more than one room in it. The "room
booking link entity" therefore resolves the many-to-many relationship
between "room" and "room booking".

Dave

[Geoff Berrow]

I noticed that Message-ID:
<11*********************@c13g2000cwb.googlegroups. com> from
da**************@sunderland.ac.uk contained the following:

Hi Geoff,

There is another table for customers. There are three tables here
because a room booking can have more than one room in it. The "room
booking link entity" therefore resolves the many-to-many relationship
between "room" and "room booking".

Ah..ok.

The way I've done this before is to create an array of rooms then do a
query to create another array of all the rooms that are booked on the
date in question. Loop through the booked array checking if each is in
the allrooms array. If it is, unset it. All rooms will then contain
all available rooms.

Not saying this is the best way...
--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[david.g.robinson]

Hi Geoff,

Thanks for your reply. If it's not too much hassle, would it be
possible for you to post some example code to get me started. As I
say, I'm not too good with PHP. I've been using Dreamweaver's server
behaviours so far. Any pointers would be greatly appreciated. Thanks
in advance.

Dave

[Tim Van Wassenhove]

In article <11*********************@c13g2000cwb.googlegroups. com>, da**************@sunderland.ac.uk wrote:

Hi Geoff,

There is another table for customers. There are three tables here
because a room booking can have more than one room in it. The "room
booking link entity" therefore resolves the many-to-many relationship
between "room" and "room booking".

I had to do a little design for a hotel last week, this is what i came
up with: http://timvw.madoka.be/stuff/HOTEL.pdf

klant = customer
reservatie = reservation
gast = guest
kamer = room
kamertype = roomtype
besteding = consumption
accomodatie = accomodation (stuff people can consume, fe: a visit to the
sauna)


As already posted, finding the available rooms for a given period would
require sql like:

SELECT *
FROM rooms
WHERE room_ID NOT IN (
SELECT room_ID
FROM reservations
WHERE start <= $end AND end >= $start
)

--
Met vriendelijke groeten,
Tim Van Wassenhove <http://www.timvw.info>

[Geoff Berrow]

I noticed that Message-ID:
<11**********************@c13g2000cwb.googlegroups .com> from
da**************@sunderland.ac.uk contained the following:

Thanks for your reply. If it's not too much hassle, would it be
possible for you to post some example code to get me started. As I
say, I'm not too good with PHP. I've been using Dreamweaver's server
behaviours so far. Any pointers would be greatly appreciated. Thanks
in advance.

That's the problem with authoring systems, they let you do stuff easily
but can be a pain to sort out if they won't do exactly what you want.

I'm assuming you know how to run a query. then to get the results into
an array do something like

while ($myrow = mysql_fetch_array($result)){
$rooms[]=$myrow['room_id']
}

That will get you an array with all possible rooms.

Do the same but with a query like the subquery in Tim's example.

So now you have two arrays one with all rooms and one with all rooms
booked on the chosen date. Lets call it $booked.

So...

for($i=0;$i<count($rooms);$i++){
//check if this room is in the booked array
if(in_array($rooms[$i],$booked)){
unset($rooms[$i]);
}
}

Now the $rooms array contains all available rooms.
Loop through it to make list, drop down box, whatever.

Untested.

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

[david.g.robinson]

Thanks for that Geoff. That's a huge help. I'll dig my PHP books out
have a look at it it over the next couple of days.

Cheers

Dave

[Movie Maniac?]

da**************@sunderland.ac.uk (Dave Robinson) wrote in message news:<48*************************@posting.google.c om>...

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.

Probably I've not understood your question, but why not hink of
inserting in the db a field that can say if the room is booked or not,
on the fly?

Let me explain: insert a field named "booked", where you store 1 if
the room is booked and 0 if not. Obviously default vaslue is 0, and
you caìhange it to 1 when you book it, and return it to 0 once it's
not booked anymore...

I've thought of another solution, that could be simpler...
Insert three fields...
booked (1 means booked, 0 means free)
booked_from (date of booking start)
booked_for (number of days the room is booked for)

then, you can read the field "booked" and see if the rom is booked, if
so, you can see when the booking starts and when it ends. Or if the
booking is already ended and put the field "booked" to 0.

It's simpler than it seems, and I think it would save you a lot of
time...
Moreover, the even if the hotel has 1000 rooms, I think that this
trick wouldn't result in a really significant increment of db's
size...

that's all :D

P.S. Please, excuse my english...