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_pe r_Night decimal(5,2) NOT NULL,
Price_Twin_per_ Night decimal(5,2) NOT NULL,
Price_Single_pe r_Night decimal(5,2) NOT NULL,
Price_Double_pe r_Week decimal(5,2) NOT NULL,
Price_Twin_per_ Week decimal(5,2) NOT NULL,
Price_Single_pe r_Week decimal(5,2) NOT NULL,
PRIMARY KEY(`Room_Numbe r`),
)
TYPE=InnoDB
ROW_FORMAT=fixe d;


CREATE TABLE `room_booking_l ink_entity` (
Room_Number tinyint(1) UNSIGNED NOT NULL,
Room_Booking_ID int(11) NOT NULL,
Single_Double_T win enum('Single',' Double','Twin') NOT NULL,
PRIMARY KEY(`Room_Numbe r`, `Room_Booking_I D`),
INDEX `Room_Number`(` Room_Number`),
INDEX `Room_Booking_I D`(`Room_Bookin g_ID`),
FOREIGN KEY `Reference_14`( `Room_Number`)
REFERENCES `room`(`Room_Nu mber`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
FOREIGN KEY `Reference_85`( `Room_Booking_I D`)
REFERENCES `room_booking`( `Room_Booking_I D`)
ON DELETE NO ACTION
ON UPDATE NO ACTION
)
TYPE=InnoDB
ROW_FORMAT=fixe d;

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_Departu re date NOT NULL,
Number_of_Night s tinyint(3) NOT NULL,
Date_Booking_Ma de date,
Status_of_Booki ng enum('Booked',' Arrived','Depar ted') NOT NULL,
Total_Cost decimal(7,2) NOT NULL,
PRIMARY KEY(`Room_Booki ng_ID`),
INDEX `Room_Booking_I D`(`Room_Bookin g_ID`),
INDEX `Customer_ID`(` Customer_ID`),
INDEX `Bill_ID`(`Bill _ID`),
INDEX `Date_of_Arriva l`(`Date_of_Arr ival`),
INDEX `Date_of_Depart ure`(`Date_of_D eparture`),
INDEX `Status_of_Book ing`(`Status_of _Booking`),
FOREIGN KEY `Reference_03`( `Customer_ID`)
REFERENCES `customer`(`Cus tomer_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=fixe d;

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_en tity AS a INNER JOIN room_booking AS b
WHERE b.departure_dat e >$_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]

Re: checking room availability for hotel booking system

I noticed that Message-ID:
<4869e87b.04112 00707.4f5f8f5@p osting.google.c om> from Dave Robinson
contained the following:
[color=blue]
>
>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.[/color]

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@sunderland.ac.uk]

Re: checking room availability for hotel booking system

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]

Re: checking room availability for hotel booking system

I noticed that Message-ID:
<1100975405.893 710.62540@c13g2 000cwb.googlegr oups.com> from
david.g.robinso n@sunderland.ac .uk contained the following:
[color=blue]
>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".[/color]

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@sunderland.ac.uk]

Re: checking room availability for hotel booking system

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]

Re: checking room availability for hotel booking system

In article <1100975405.893 710.62540@c13g2 000cwb.googlegr oups.com>, david.g.robinso n@sunderland.ac .uk wrote:[color=blue]
> 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".[/color]

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]

Re: checking room availability for hotel booking system

I noticed that Message-ID:
<1100979835.077 642.240280@c13g 2000cwb.googleg roups.com> from
david.g.robinso n@sunderland.ac .uk contained the following:
[color=blue]
>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.[/color]

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_arr ay($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<cou nt($rooms);$i++ ){
//check if this room is in the booked array
if(in_array($ro oms[$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@sunderland.ac.uk]

Re: checking room availability for hotel booking system

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?]

Re: checking room availability for hotel booking system

david.g.robinso n@sunderland.ac .uk (Dave Robinson) wrote in message news:<4869e87b. 0411200707.4f5f 8f5@posting.goo gle.com>...[color=blue]
> 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.[/color]

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...