Hotel Bookings / Reservations : Rooms with rates according to seasonal dates
Hi, I am designing a hotel website. I am having a problem with getting the room rate on a specified date. I have a form to check hotels in a given area and to check a single hotel, lets call the variables $arrival_date and $departure_date
I need to process this query in order to get the correct rates for the different rooms in a hotel for the $arrival_date and $departure_date given above - also need to do this for hotels within a specific area to bring up a list of hotels and their room rates
Also, what happens when $arrival_date and $departure_date overlapse into another hotel_season
Basically I need the following results to show on the page:
hotel_name
room_name (multiple rooms) , room_type
hotel_season_name , hotel_season_start_date , hotel_season_end_date
$arrival_date and $departure_date (from form input)
then the nights in between the above dates showing the date and the rate that corresponds to it
I also then need a total amount for the stay !
Thanks in anticipation ! (If you need more info, please let me know)
Another thing, how would I be able to put constraints on to certain rooms, like sleeps only 2 OR Sleeps 2 adults and 1 / 2 children only - and have corresponding rates ! - will submit another question for this, let me know if it interlinks with what we are doing here !
MySQL DB
city_table
------------
city_id (PK)
city_name
hotel_table
---------------
hotel_id (PK)
city_id (FK)
hotel_name
room_table
---------------
room_id (PK)
hotel_id (FK)
room_type_id (FK)
room_name
room_type_table
--------------------
room_type_id (PK)
room_type_name
room_rate_table
--------------------
room_rate_id (PK)
hotel_season_id (FK)
room_id (FK)
room_rate_name
room_rate_amount
hotel_season_table
-----------------------
hotel_season_id (PK)
hotel_id (FK)
hotel_season_name
hotel_season_start_date
hotel_season_end_date
I need to process this query in order to get the correct rates for the different rooms in a hotel for the $arrival_date and $departure_date given above - also need to do this for hotels within a specific area to bring up a list of hotels and their room rates
Also, what happens when $arrival_date and $departure_date overlapse into another hotel_season
Basically I need the following results to show on the page:
hotel_name
room_name (multiple rooms) , room_type
hotel_season_name , hotel_season_start_date , hotel_season_end_date
$arrival_date and $departure_date (from form input)
then the nights in between the above dates showing the date and the rate that corresponds to it
I also then need a total amount for the stay !
Thanks in anticipation ! (If you need more info, please let me know)
Another thing, how would I be able to put constraints on to certain rooms, like sleeps only 2 OR Sleeps 2 adults and 1 / 2 children only - and have corresponding rates ! - will submit another question for this, let me know if it interlinks with what we are doing here !
MySQL DB
city_table
------------
city_id (PK)
city_name
hotel_table
---------------
hotel_id (PK)
city_id (FK)
hotel_name
room_table
---------------
room_id (PK)
hotel_id (FK)
room_type_id (FK)
room_name
room_type_table
--------------------
room_type_id (PK)
room_type_name
room_rate_table
--------------------
room_rate_id (PK)
hotel_season_id (FK)
room_id (FK)
room_rate_name
room_rate_amount
hotel_season_table
-----------------------
hotel_season_id (PK)
hotel_id (FK)
hotel_season_name
hotel_season_start_date
hotel_season_end_date
ASKER
Thanks for your prompt reply G,
This is a great idea above - but not what the system needs to do !
We unfortunately do not have allocated rooms for all our hotels OR access to the hotels websites (many hotels), so we cant see which are occupied or not ! However,
It is more for informing the browser what the rate would be for particular dates, and showing them a total quote - similar to when you search at expedia.com or hotels.com - so that the browser can then choose a room in the price range they can afford !
The reservation details will then be stored on the db and sent to a reservations office to do the booking and get back to the client - will post more on that later for more ideas !
Surely for the above situation, without start and end occupied dates, the queries can still be done to get the rates for dates ?
Maybe even put the details above in, and leave them all as unoccupied and available ?
Let me know
Regards
Brad
This is a great idea above - but not what the system needs to do !
We unfortunately do not have allocated rooms for all our hotels OR access to the hotels websites (many hotels), so we cant see which are occupied or not ! However,
It is more for informing the browser what the rate would be for particular dates, and showing them a total quote - similar to when you search at expedia.com or hotels.com - so that the browser can then choose a room in the price range they can afford !
The reservation details will then be stored on the db and sent to a reservations office to do the booking and get back to the client - will post more on that later for more ideas !
Surely for the above situation, without start and end occupied dates, the queries can still be done to get the rates for dates ?
Maybe even put the details above in, and leave them all as unoccupied and available ?
Let me know
Regards
Brad
ASKER
Apologies - I did not put this in the brief, it is only seen in the table design !
The Hotel owns Hotel Seasons: Low Sea, High Season, Peak Season
Different Hotels have different start and end dates for their seasons
The rates for rooms in a hotel are depicted by which season they are in
So the query would need to match the $arrival_date and $departure_date to a season, or two seasons if they overlap, in order to pull out each night as a date and have a matching rate,
i.e. Peak Season Rates
i.e. 06/12/04 | 06/13/04 | 06/14/04
i.e. Rate Rate Rate
Hope this helps some more
The Hotel owns Hotel Seasons: Low Sea, High Season, Peak Season
Different Hotels have different start and end dates for their seasons
The rates for rooms in a hotel are depicted by which season they are in
So the query would need to match the $arrival_date and $departure_date to a season, or two seasons if they overlap, in order to pull out each night as a date and have a matching rate,
i.e. Peak Season Rates
i.e. 06/12/04 | 06/13/04 | 06/14/04
i.e. Rate Rate Rate
Hope this helps some more
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
G, Thanks for yet another prompt reply.
I am also at work now and cannot access the files I need to, but your proposed pseudocode solution looks as if it could foot the bill. However,
Reading your code, I am still pretty new to this: it looks like the arrival date has to be smaller than the hotel_season_start_date - however, the arrival date must fit into the season, so the hotel_season_start_date should be less than the arrival date !
---------------
// in which season does the client want the accomodation to start?
$sql1 = "select * from hotel_season_table
where hotel_season_start_date>'"
hotel_season_end_date<'" . $arrival_date . "'";
---------------
I will give you an example: Peak Season (15/12/2004 until 15/01/2005) which means that if someone wanted to stay from 23/12/2004 and leave on 03/01/2004, the script and query would need to check which season/s it falls under, in this case: Peak Season and then bring up the correct rates for the days !
Would the above example work with what you have written ?
The final output would need to either show, for one hotel, or many hotels within a city: the following info:
hotel name
the room / s
hotel season (or seasons if over 2 different seasonal rates) as a heading
and the rates per day, showing the day and the rate for that day
and some other info - like room info which I have on the db but is not shown in the first example (would I need to give you all this for your coding or could we put that in once we have got this done ?)
Let me know if we are following each other !!!
I am also at work now and cannot access the files I need to, but your proposed pseudocode solution looks as if it could foot the bill. However,
Reading your code, I am still pretty new to this: it looks like the arrival date has to be smaller than the hotel_season_start_date - however, the arrival date must fit into the season, so the hotel_season_start_date should be less than the arrival date !
---------------
// in which season does the client want the accomodation to start?
$sql1 = "select * from hotel_season_table
where hotel_season_start_date>'"
hotel_season_end_date<'" . $arrival_date . "'";
---------------
I will give you an example: Peak Season (15/12/2004 until 15/01/2005) which means that if someone wanted to stay from 23/12/2004 and leave on 03/01/2004, the script and query would need to check which season/s it falls under, in this case: Peak Season and then bring up the correct rates for the days !
Would the above example work with what you have written ?
The final output would need to either show, for one hotel, or many hotels within a city: the following info:
hotel name
the room / s
hotel season (or seasons if over 2 different seasonal rates) as a heading
and the rates per day, showing the day and the rate for that day
and some other info - like room info which I have on the db but is not shown in the first example (would I need to give you all this for your coding or could we put that in once we have got this done ?)
Let me know if we are following each other !!!
ya, I put the wrong signs... thanx for pointing this out :-)
$sql1 = "select * from hotel_season_table
where hotel_season_start_date<'"
hotel_season_end_date>'" . $arrival_date . "'";
As for your last question, I think we should take one step at a time, so that room info will be done later.
I have to make a remark: my goal is not to write the code for you, but help you write the code for yourself, so that you'll know how to do it next time. Of course, two pairs of eyes are better, as always ;-) We are developing a strategy together, and you test it, and come back if any little problems arise. OK?
$sql1 = "select * from hotel_season_table
where hotel_season_start_date<'"
hotel_season_end_date>'" . $arrival_date . "'";
As for your last question, I think we should take one step at a time, so that room info will be done later.
I have to make a remark: my goal is not to write the code for you, but help you write the code for yourself, so that you'll know how to do it next time. Of course, two pairs of eyes are better, as always ;-) We are developing a strategy together, and you test it, and come back if any little problems arise. OK?
ASKER
Sounds good to me, I think we're going in the right direction
---
Below I propose the general lines for a solution, mostly pseudocode (I have little time at my disposal, I'm at work now), but it's important that we agree on this first. Please check it out, and let me know if it's close to what you need.
---
Agreed !
A question: would we need to convert the the date format given by php into mysql format at all for this to work ?
---
Below I propose the general lines for a solution, mostly pseudocode (I have little time at my disposal, I'm at work now), but it's important that we agree on this first. Please check it out, and let me know if it's close to what you need.
---
Agreed !
A question: would we need to convert the the date format given by php into mysql format at all for this to work ?
For the date, I personally use a text field, and feed it with the output of the date function, using a format of my own choice, so that I can perform modifications at a later time.
But it is not mandatory to do so, you can use datetime fields. It will be up to us to write code that takes advantage of this particularity.
I will not be online anymore today, so please try to write / test some code based on the ideas outlined in a previous post, and come back tomorrow with fresh information.
Also, if this is urgent, I invite other experts to help you / us with this.
But it is not mandatory to do so, you can use datetime fields. It will be up to us to write code that takes advantage of this particularity.
I will not be online anymore today, so please try to write / test some code based on the ideas outlined in a previous post, and come back tomorrow with fresh information.
Also, if this is urgent, I invite other experts to help you / us with this.
ASKER
Hi G,
Thanks for all youve done so far, I have learnt a hell of a lot !
Everything is working with the arrays corresponding to the db, I have used alot of newly learnt array functions to check this out, however, after using comparison operators, trying to get values from the second array inside and out of the foreach, using in_array to compare indexes, and quite a few other things - I cant figure out how to get the values from the second array in order to compare them to the first - note: I am very new to PHP and coding - but I am learning !
From what I can see in the code you have written above, I think we are on the right track, a little more help please on the foreach and comparison (they do work as I checked the indexes and values against my db) but cant code it correctly !
Thanks for all youve done so far, I have learnt a hell of a lot !
Everything is working with the arrays corresponding to the db, I have used alot of newly learnt array functions to check this out, however, after using comparison operators, trying to get values from the second array inside and out of the foreach, using in_array to compare indexes, and quite a few other things - I cant figure out how to get the values from the second array in order to compare them to the first - note: I am very new to PHP and coding - but I am learning !
From what I can see in the code you have written above, I think we are on the right track, a little more help please on the foreach and comparison (they do work as I checked the indexes and values against my db) but cant code it correctly !
ASKER
G,
Thanks for your help so far, I am getting pretty stuck on this:
Am using isset and in array, things seem to be telling me now in the code that they are the same and value does exist, but dont really know where to go from here ! - help would be appreciated !
Thanks for your help so far, I am getting pretty stuck on this:
Am using isset and in array, things seem to be telling me now in the code that they are the same and value does exist, but dont really know where to go from here ! - help would be appreciated !
First of all, this morning I had only seen the 500 error on the experts-exchange site, and I was unable to read your message. Sorry.
Next, I believe you should use array_key_exists instead of in_array , because you do care about the indexes, they are the ones which are unique, and identify a particular record.
foreach( $accomodation_start as $index => $value ){
// compare: see if this index ($index) is in the indexes of the second array (array_key_exists function)
// --- you are here, right?
// if it is, compare the ids of the seasons (hotel_season_id), which is the $value for the current iteration;
// if different seasons, then divide this into 2 periods: $arrival_date -> $1st_season_end_date,
// $1st_season_end_date->$dep
// the first is from the first array, the second ... from the second array
// populate an array $possibilities like this: - index the array by the hotel_id
// - search for the hotel_season_id in the [rooms_table] and compute the amount for the period
// - if previously splitted into 2 sub-periods, sum up the amounts and insert the total value in the array
}
I tried to indent the comments, so that you can follow them more easy.
[Notice]: tomorrow I enter a week-long vacation, and I probably won't have Internet access (I'm going somewhere in the mountains, fresh air, silence, mmm).
Next, I believe you should use array_key_exists instead of in_array , because you do care about the indexes, they are the ones which are unique, and identify a particular record.
foreach( $accomodation_start as $index => $value ){
// compare: see if this index ($index) is in the indexes of the second array (array_key_exists function)
// --- you are here, right?
// if it is, compare the ids of the seasons (hotel_season_id), which is the $value for the current iteration;
// if different seasons, then divide this into 2 periods: $arrival_date -> $1st_season_end_date,
// $1st_season_end_date->$dep
// the first is from the first array, the second ... from the second array
// populate an array $possibilities like this: - index the array by the hotel_id
// - search for the hotel_season_id in the [rooms_table] and compute the amount for the period
// - if previously splitted into 2 sub-periods, sum up the amounts and insert the total value in the array
}
I tried to indent the comments, so that you can follow them more easy.
[Notice]: tomorrow I enter a week-long vacation, and I probably won't have Internet access (I'm going somewhere in the mountains, fresh air, silence, mmm).
ASKER
Ive got this so far !
foreach( $accommodation_start as $index => $value){
$index_exists = isset($accommodation_end[$
print "$index_exists"; // which prints 1 meaning true it does exist !
// dont know what to put next to compare by index ! not used to oop !!
}
But, I cannot find a function / dont know how to use the right function to compare the values by the index from first to second array !
That is where Im stuck !
foreach( $accommodation_start as $index => $value){
$index_exists = isset($accommodation_end[$
print "$index_exists"; // which prints 1 meaning true it does exist !
// dont know what to put next to compare by index ! not used to oop !!
}
But, I cannot find a function / dont know how to use the right function to compare the values by the index from first to second array !
That is where Im stuck !
http://ro.php.net/array_key_exists
this is the function you need
this is the function you need
:) it's not oop what we're doing here, so do not panic
ASKER
Thanks for that, the isset does the same thing: busy looking at how to use it now in an if statement,so:
I have the following:
foreach( $accommodation_start as $index => $value){
#$index_exists = isset($accommodation_end[$
#print "$index_exists";
if (array_key_exists($index, $accommodation_end)) {
print "the indexes match";
if (in_array( $value, $accommodation_end )){
print "value is in array";
}
}
it prints both, so I am guessing it works, but is it checking against the index, or is it just checking values the way I have written it: Coz the index is unique right ?
I have the following:
foreach( $accommodation_start as $index => $value){
#$index_exists = isset($accommodation_end[$
#print "$index_exists";
if (array_key_exists($index, $accommodation_end)) {
print "the indexes match";
if (in_array( $value, $accommodation_end )){
print "value is in array";
}
}
it prints both, so I am guessing it works, but is it checking against the index, or is it just checking values the way I have written it: Coz the index is unique right ?
ASKER
Thanks for all your help and fast responses : hope you enjoy your holiday in the mountains !
I am trying to work with above, but as Im stil learning it may take a while, will post another question once I get a little further and when you are back !!!
regards Brad
I am trying to work with above, but as Im stil learning it may take a while, will post another question once I get a little further and when you are back !!!
regards Brad
So, here we go: the fields described below will be added to [room_table]:
is_occupied // will take y/n values
occupation_start_date // since when it is occupied
occupation_end_date // when it will be available
After this, the queries shouldn't be too complicated. Please let me know if you need help with them :-)
Cheers,
G