Compute Vacant Rooms for Hotel Booking System

[unknown_022]

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

[supremewebdesign]

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.

[philm]

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

[unknown_022]

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

Thanks Philm for the information. Actually, I have no big idea regarding the use of those data types. I can use int instead of bigint, right?

[philm]

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

[unknown_022]

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.

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.

[unknown_022]

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

Thanks man. But what is the effect of using inappropriate data types? Does it take large memory or file size on the database?

[philm]

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

[supremewebdesign]

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.

[unknown_022]

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.

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.

	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.