473,224 Members | 1,588 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

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

Cheers,

Dave
Jul 17 '05 #1
8 18549
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/
Jul 17 '05 #2
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

Jul 17 '05 #3
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/
Jul 17 '05 #4
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

Jul 17 '05 #5
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>
Jul 17 '05 #6
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/
Jul 17 '05 #7
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

Jul 17 '05 #8
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...
Jul 17 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: evski | last post by:
kia ora I am after a set of php script to administer bookings for buses and huts and coordinate customer enquiries in our small tourism business I have looked extensively but not come up with...
2
by: PaulD | last post by:
Can anyone point me in the direction of a sample Access booking system? Trying to build a booking system were customers can book a computer by date and timeslot, each timeslot having a limited...
1
by: thehuby | last post by:
I am prototyping a ticket booking system and just wanted to ask a few questions. 1 - What is the standard way of temporarily reserving a number of tickets throughout the purchasing process (to...
7
by: windandwaves | last post by:
Hi Folk Is there anyone out there in a hotel reservation system. I have written one over the last few years and I would like to know if anyone has a similar interest. > Nicolaas
1
by: simba | last post by:
Hello, I am currently doing a project which requires me to develop an online booking system for hotels and integrate both bed and room booking. I have the room booking working but I cant seem to...
8
by: shrimpeh | last post by:
Hi im currently messing around with creating a diary/booking system for a leisure centre. And im stuggling with how I can display all the data. The booking is taking the activity the customer is...
5
by: Peter Sutton | last post by:
Greetings all, My booking system includes 2 tables: tblRooms with the key field RoomID, and some other fields AND a tblBooked Rooms with a number of fields including RoomID and BookedDate. ...
0
by: csgraham74 | last post by:
Hi guys, i just thought id throw this one out here but i was wondering if anyone knows of where i would find an open source booking system project for asp.net ?? ive been asked by a client to...
3
by: 88stevie | last post by:
Hi all, I have a system and I'm a little stuck. It's a booking system. It has rooms, etc and every room have a daily price. Then it has special daily rates when assigned. How do I...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.