SitePoint Sponsor |
|
User Tag List
Results 1 to 10 of 10
-
Apr 30, 2009, 07:01 #1
- Join Date
- Sep 2007
- Location
- Philippines
- Posts
- 17
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Compute Vacant Rooms for Hotel Booking System
Hi! I'm not a PHP guru. I'm developing a Hotel Booking System and I'm having problem computing the available rooms per hotel. On my website I have search engine for searching available rooms based on check -in and check-out date. The result rooms that should be displayed are the rooms that are not booked for that span of time. I have no idea how to do it but I'll give you how I built my table on DB for rooms, hotel, booking, and room booked. Each room has corresponding quantity, lets say for one hotel there are 10 types of rooms. Example, there is 10 Single Bed (room type) rooms.
Attached files are the screenshots of the tables used in my application:
Anyone who encounter also this issue. Any suggestion would be very much appreciated. Thank you very much in advance.
Paul
-
Apr 30, 2009, 10:09 #2
I too recently had this same program to write for a client.
There are a few different ways to write this depending on your system.
Also is this for the front or back end?
Do you want the check to produce a table or a text output ?
Here is the basics of what you will need to do:
- cycle through the dates that you wish to check
- for each date check what rooms are available
-either build an array for each room
or
-check the next date for that room to see if it is also free.
(depends if you wish guests to be in the same room for their entire stay)
- output availability for chosen dates.www.supremewebsolutions.com
Aberdeen based web design.
Hotel booking systems - Content management systems
CMS - Search engine optimisation - SEO
-
May 1, 2009, 04:37 #3
- Join Date
- Sep 2001
- Location
- Portsmouth, UK
- Posts
- 735
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Just to jump in on a slightly different tangent...
Why are you using bigint(20) everywhere? You realise a an UNSIGNED SMALLINT can go upto 65535? Also, 20 is the optional padding, eg. 0000000000000000001. It's not actually restricting the value.
Of course you may well have more than 65535 hotels your implementing this system for... if so, forget what I said
http://dev.mysql.com/doc/refman/5.0/...ric-types.html
-
May 5, 2009, 01:17 #4
-
May 5, 2009, 01:20 #5
- Join Date
- Sep 2001
- Location
- Portsmouth, UK
- Posts
- 735
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
You can use UNSIGNED SMALLINT if you're sure any of those values aren't going to reach 65535...
Have a read of the link above and you'll get a feel which is best in your case
-
May 5, 2009, 01:39 #6
- Join Date
- Sep 2007
- Location
- Philippines
- Posts
- 17
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks suprewebdesign for your answer. Actually, I was thinking to use the second one that you have mentioned. But I'm thinking of one problem, I'm not sure if it is really a problem, please correct me if I'm wrong.
Let's say, a room booked for May 15-17 and it is also booked for May 20-25. On the search form, a visitor searched for room availability for May 18-19.
Will that room display on the result since the searched date is in between two booked dates? How will I computer for this? Or I will base the availability on the maximum booked dates for a room?
Would you mind elaborating the first option that you have mentioned? I attached the snapshot of the result rooms and the search form. I'm really having a hard time thinking for this.
Thanks for your help.
-
May 5, 2009, 01:53 #7
-
May 5, 2009, 02:41 #8
- Join Date
- Sep 2001
- Location
- Portsmouth, UK
- Posts
- 735
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Yes, you can see from the table in the link that a BIGINT is 4 times larger than SMALLINT to store. While it probably doesn't matter in the short term, long term it may become an issue as the database grows...
-
May 6, 2009, 02:50 #9
hope this helps
- create an array of the dates you wish to check for
- cycle through each room that you have of the type the client is looking for
- for each room check that the dates you are checking for are available
- if room is available for all dates store the room id in an array.
you now have an array of rooms available for your chosen dates.www.supremewebsolutions.com
Aberdeen based web design.
Hotel booking systems - Content management systems
CMS - Search engine optimisation - SEO
-
May 6, 2009, 03:20 #10
- Join Date
- Sep 2007
- Location
- Philippines
- Posts
- 17
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
thanks mate.
i just used a simple query to check the room availability and it worked. I got it from other discussion in this forum. Below is the query that I used, it might be also helpful to you guys.
Code PHP:function checkAvailableRooms($room_id) { $dates = $this->buildDates(); $query = "SELECT t2.id FROM room_booked AS t1 "; $query .= "LEFT JOIN hotel_bookings AS t2 ON t1.booking_id = t2.id "; $query .= "WHERE "; $query .= "("; $query .= "(t2.checkin_date>='" . $dates['checkin'] . "' AND t2.checkin_date<='" . $dates['checkout'] . "') "; $query .= "OR "; $query .= "(t2.checkout_date>='" . $dates['checkin'] . "' AND t2.checkout_date<='" . $dates['checkout'] . "') "; $query .= ") "; $query .= "AND "; $query .= "t1.room_id = $room_id "; $this->_db->setQuery($query); return $this->_db->loadObject(); }
Thanks again for your help man.
Bookmarks