Forum Moderators: coopster
I need to create a simple script that checks the availability of a holiday rental or hotel.
Has anyone had any experience in this sort of program and is it complicated to construct?
What will be required to get started for a basic setup and then later on expand it easily to produce more user friendly results producing tables of dates available etc.
I need a short term solution that i can expand easily.
Can anyone give me some points to think about that I may have to do.
Thanks for your time
i dont think that's something which you can just rattle off ;-)
it needs a front end where surfers can search for available dates, and if found, then be able to reserve according to the specifics of the holiday / hotel in question - full / half board, prices for all adults, extra adults, kids where applicable, sea views or not, suites, doubles, singles, etc
and a back end to look after all this detail, and of course the inventory availability.
the system would have to be able to calculate all manor of prices (including discounts?) and it would have to automatically update itself when inventory is sold - overbooking is against the law in many countries.
you would need to tie it in with a secure SSL server which enables you at the very least to collect credit card numbers as a guarantee of reservation. actually tying it into a payment gateway for direct payments (authorize.net) would be trickier still.
in our case, we worked out that to put something together which worked as securely and reliably as we needed would have cost 10,000s of dollars and real professional programming. even now with 2 months PHP experience under my back ;-) i would not dream of attempting such a project.
this is why there are so many third party suppliers of hotel reservation software who will either sell you it, or (the really good ones) will charge a % of sales.
i know it is not encouraging, but for a production environment where you are serious about earning money, i would look into pre-built solutions.
p.s. that all sounds so boring - why not have a go anyway - be a fantastic learning experience :-)
the first thing you'll need is to consider your data source - is it internal or external?
if it's internal - you'll need a well designed table in mysql
if it's external - you'll need to work out a way to query it and parse the data well
without knowing more details it's very hard to say more on that
the actual queries for availability should be straight forward as it gets, given that you've sorted your data source. mysql queries are pretty flexible in that kind of way.
finally, outputting results into tables is very simple.
now, i suggest you have a good think about the logistics of your plans, and then write a more detailed post if you want more useful help :-)
This would cut down the extra work of replying to unwanted enquiries.
-- Jamie --
You are correct about the complete system it would be far better for me to use my skills in other ways which I have already done :)
I have over 100 properties each advertised by a seperate owner with all enquries direct to the owner (hence the reason why I don't need a reservation system)
I would like to offer every owner the chance to have a check availability for the property and let owners update their availability for their own property.
This would make my web site more owner friendly and user friendly like killing two birds with one stone.
I am a beginner at PhP and I can easily work with Mysql just need some ideas on where I should begin.
Each apartment has an ID and the client just fills in a simple date form. The results would say yes or no.
I have tried creating it but run into situations whereby the the apartments should say yes instead of no. This is because I am trying to use the <= for searching the dates in the database of any reservations made for that particular apartment.
I would be very grateful for any ideas on how i should set up the tables one for each apartment or all apartments in one table.
I have tried
$sql = "select id from reservations where id='001'
AND startd <= '01'
AND startm = '03'
AND starty = '01'
AND endd <= '07'
AND endm = '03'
AND endy = '01'";
I have then used if statements to instruct what should happen when a result has been found however this also takes into account results that are very old and does nopt filter it out.
I am very new to php and have created log in programs and search forms etc but this seems to be a little more complex.
Thank you for your time
SELECT *
FROM table
WHERE
bookdate BETWEEN startdate AND enddate;
SN
this is what I have
$sql = "SELECT * FROM check WHERE id='cv001' AND '010201'
BETWEEN start AND end";
In my database I have
id start end ref
cv001 010201 010207 010
cv000 001229 010101 008
cv000 010101 010107 009
The worst thing about it was that if I put the year last like this
D m y
29 12 00
The whole thing would be wrong when I think about it now it seems logical because you are now counting dates but numbers and it would always read the same 290.... where as
01.... would be more unique.
I am now going to take this further and see what I can build up I must now make sure all dates are put into the database back to front, so confusing if your a european dates man.
Thanks for the hint!
If you see any problems with what I have done please post any tips would be appreciated.
Cheers
I am now trying to create a simple form which will list all the apartments or rooms that are available for the dates entered.
I have been using the
SELECT * FROM check WHERE date BETWEEN start AND end
and have also tried using many if loops etc with the sql query, but always seems to be some little error with the resulting output.
I need to extract the full list of two bedroom apartments from one table and delete those that appear fully booked from the reservation table and then create a nice output list of urls using echo for the visitor to click on to see the apartment information.
Any ideas or nice little hints that will get my mind round it.
cheers
How can I set up a search facility to produce a list of all my accommodation available after the user has filled in a form?
I can produce a check availability for one apartment but its no good if you have many nor is it practical.
Shall I create two tables one with a list of all the bookings and one with all the apartment info i.e links etc.
Would it then be possible to select all apartments in the main table and run it through the reservation table which would delete any it finds reserved and echo only those that are left.
I have tried many loops but end up getting in a mess, plus their is no sql = SELECT query that I need.
I am very lost and frustrated.
Have you taken a look at MySQL JOIN Syntax [mysql.com]?
I think you should probably create 2 tables as you mentioned. As you also said that would allow you to select all apts or one specific apt and check that data against the reservation table.
Your Apt table could be
apt_id ¦ name ¦ address
Plus whatever other information you need to store about the apartment itself.
Res table could be
res_id ¦ apt_id ¦ start ¦ end ¦ client_info
Now the client info could be stored there or maybe in a third table with client_id referenced in the res table. You may not need to reference that often and not want it in the same table. You might not even have client info at all, I don't know.
This is only really a basic layout and hopefully gives you an idea as to how to proceed. One important thing to remember, If the database is properly constructed and well thought out the queries take care of themselves.
As to date fields, I always use the DATE or DATETIME column type [mysql.com] that helps mysql be able to handle a lot of the functionality. Otherwise you end up doing a lot of work in php to manage your custom date style.
Yes, I have been looking at the join method for tables and have tried one or two ways not sure if I get the wrong one or if I get it the wrong way around, very easy to get lost.
I have two tables like you say
reservation one is
id ¦ start ¦ end ¦ ref ¦
---------------------------------
001¦ 01010100 ¦ 01010700 ¦ 01 ¦
the other is for the apartments
id ¦ type ¦ url ¦
----------------------------
001¦two bedroom ¦ http:www ¦
The dates I am storing as just numbers also including hours as I have found that the check out time comes into play when a search is made for the same day when someone leaves and some one is arriving.
The problem I do have is if I have two records of a booking and I try to do a search for date not == to date it will produce results for bookings in the past etc.
Its the most complex challange I have had, and it won't be easy for to understand as experience comes into play with this one.
Here's the tables (using datetime columns):
reservations table:
-----------------------------------------------
ID ¦ fromdate ¦ todate
-----------------------------------------------
01 ¦ 2003-07-01 15:00:00 ¦ 2003-07-15 12:30:00
-----------------------------------------------apartments table:
----------------
ID ¦ other info
----------------
01 ¦ blah blah
----------------
02 ¦ bleh bleh
----------------Selecting only apartments that are available today:
SELECT apartments.* FROM apartments LEFT JOIN reservations ON reservations.ID=apartments.ID WHERE now() NOT BETWEEN reservations.fromdate AND reservations.todate OR reservations.ID IS NULL;
Select apartments that are available between May 2, 2003 and August 2, 2003:
SELECT apartments.* FROM apartments LEFT JOIN reservations ON reservations.ID=apartments.ID WHERE
('2003-05-02' NOT BETWEEN reservations.fromdate AND reservations.todate AND
'2003-08-02' NOT BETWEEN reservations.fromdate AND reservations.todate AND
reservations.fromdate NOT BETWEEN '2003-05-02' AND '2003-08-02' AND
reservations.todate NOT BETWEEN '2003-05-02' AND '2003-08-02')
OR reservations.ID IS NULL;
I actually tested this, so it should work :)
I am going to create and test it straight away will let you know how I got on.
I knew the answer was in the SELECT command and then I would have to join the tables, and with my lack of experience I would of never have been able to get my head around the command.
I find myself trying to think more logically but sometimes my logic is different of that of a computer.
Nice one Jamesa really appreciate your time and effort.
:)
I have tested and works great.
I will have to use the the hours so that when someone checks for a day that someone leaves it will show the apartment has come avaialble.
For example everyone will check out at 12:00:00 the
check availability will use 12:00:01. This I will made standard for all entries into a reservation and for the check availability.
Is this a good solution?
It would have taken me forever to work out the JOIN method of tables I now begin to see the real power of Mysql and Php.
Thank you again for your help its really apreciated!
I have done a few of these systems, some tips from what I have found .
Make sure you test all your date functions well, one way to do this is to change the server clock settings - 13th October sometimes falls down :)
Build in some flexibility with a database table of rules thats can be edited, things like:
- minimum hours notice for new bookings
- min hours space between bookings
- last availability shown on site
Some other things you might add are a script to create new availability, unless you want to allow bookings at any dates in the future, And a way to remove availability that bypasses the payment gateway.
HTH
Want I am doing at the moment is a short term solution for a property management company who have many apartments and they have too many enquiries for apartments that are full and they need to reduce the work load i.e stopping unwanted enquiries.
What I need to do now is let the visitor only check all apartments that are two bedrooms or three bedrooms
check availability FOR two bedroom FROM start TO end
Thanks to jamesa
I now have this which works great!
$sql = "SELECT apartments.* FROM apartments LEFT JOIN reservations ON reservations.ID=apartments.ID WHERE( '$start' NOT BETWEEN reservations.fromdate AND reservations.todate AND '$end' NOT BETWEEN reservations.fromdate AND reservations.todate AND
reservations.fromdate NOT BETWEEN '$start' AND '$end' AND
reservations.todate NOT BETWEEN '$start' AND '$end')
OR reservations.ID IS NULL";
I have tried to use
$sql = SELECT apartments.* FROM apartments WHERE type="$type" LEFT JOIN........
but this won't work any ideas on how I can refine the search to just $type.
I have in both tables the column TYPE with values 'two bedroom' or 'three bedroom' etc.
Any ideas?
I use something like...
SELECT Appartment.ID
FROM Reservations INNER JOIN Appartments
ON Reservations.AppartmentID = Appartment.ID
WHERE Appartment.NumberOfRooms IN(2,3)
AND [Restrict Date Range]
There is a much easier way to do this if the database supports a cross tab query.
lasko, just make sure this is in the WHERE statement, and that the WHERE statement comes after the JOIN...
$sql = "SELECT apartments.* FROM apartments LEFT JOIN reservations ON reservations.ID=apartments.ID WHERE( '$start' NOT BETWEEN reservations.fromdate AND reservations.todate AND '$end' NOT BETWEEN reservations.fromdate AND reservations.todate AND
reservations.fromdate NOT BETWEEN '$start' AND '$end' AND
reservations.todate NOT BETWEEN '$start' AND '$end')
OR reservations.ID IS NULL AND apartments.type IN(2,3)";
You don't need the 'type' column in the reservations table.
I am using
$sql = "SELECT apartments.* FROM apartments LEFT JOIN reservations ON reservations.ID=apartments.ID WHERE( '$start' NOT BETWEEN reservations.fromdate AND reservations.todate AND '$end' NOT BETWEEN reservations.fromdate AND reservations.todate AND
reservations.fromdate NOT BETWEEN '$start' AND '$end' AND
reservations.todate NOT BETWEEN '$start' AND '$end')
OR reservations.ID IS NULL AND apartments.type IN(2,3)";
My table structure is
reservations (
id varchar(5) NOT NULL,
todate DATETIME NOT NULL,
fromdate DATETIME NOT NULL,
type varchar(5) NOT NULL,
book ref varchar(5) NOT NULL,
Primary key (book ref) )
And for the apartments
apartments (
id varchar(5) NOT NULL,
type varchar(5) NOT NULL,
PRIMARY KEY (id) )
I have tried changing the numbers (2,3)in the WHERE section
but the results are still the same. I want to limit the search only to a one bedroom or a three bedroom etc.
Am I doing something really wrong thats obvious?
Should type NOT BE varchar(5)
In my apartments table I currently have
¦ id ¦ type
-----¦------
¦000 ¦ 1
¦001 ¦ 1
¦002 ¦ 2
¦003 ¦ 2
¦004 ¦ 3
¦005 ¦ 3
I don't have the TYPE in the reservations table.
This is puzzle?
Thank you for time :)
>OR reservations.ID IS NULL
And the fact you are using a left join (which will select all appartments).
Try changing the brackets to :
( '$start' NOT BETWEEN reservations.fromdate AND reservations.todate AND '$end' NOT BETWEEN reservations.fromdate AND reservations.todate AND
reservations.fromdate NOT BETWEEN '$start' AND '$end' AND
reservations.todate NOT BETWEEN '$start' AND '$end'
OR reservations.ID IS NULL)
If this doesnt work, try an INNER JOIN and see if it changes the results, and make sure Appartments.Type is numeric not varchar.
I am doing this
$sql = "SELECT apartments.* FROM apartments LEFT JOIN reservations ON reservations.ID=apartments.ID WHERE
( '$start' NOT BETWEEN reservations.fromdate AND reservations.todate AND '$end' NOT BETWEEN reservations.fromdate AND reservations.todate AND
reservations.fromdate NOT BETWEEN '$start' AND '$end' AND
reservations.todate NOT BETWEEN '$start' AND '$end'
OR reservations.ID IS NULL) AND apartments.type IN(2)";
and it seems to be working just going to test it more and will let you now.
I still don't understand these JOIN commands, i knew I had to join the tables but didn't have a clue.
Thanks for all your help guys
Yes I was loosing it,
Because we are checking weather the apartment comes free on the same day as someone leaves we use time.
So someone checks out at 12:00:00 and the next check in at
12:00:01.
Great but script is checking the other way around it checks to see if the apartment comes free at 12:00:01.
What a very silly thing.
ASPdaddy and jamesa I want to thank you for your help you been great.
Before I close the chapter any tips on how I can stop an entry of a double booking.
currently the Booking Ref is the Primary key but its still possible to put into the reservations table the same apartment and the same dates.
The dates can not be primary key of course so I believe I need to create some thing that will check the table first before it lets me write into it.
Maybe an IF statement will do it.
Again thanks for your help guys :)
There's no way that I know of to do that on the database level with mysql, so in the script I'd run the same query we just came up with to check for conflicts right before adding a new booking to the db.
1 booking = Apartment not displayed
2 bookings (different dates) = 1 apartment displayed
3 bookings (" " " ) = 2 apartments displayed
4 bookings (" " " ) = 3 apartments displayed
and so on......
If it finds a reservation it doesn't display the apartment however it will display the apartment if their are other reservations for the same apartment.
I now have a nice little system ready for the management company I just can not get this check availability correct.
You guys have really helped me and its very much appreciated :)
Even though the apartments are booked because the script matches a second reservation with out the dates it selects it from the apartments and extracts it.
Is their something else I should put maybe and IF or a FOR after the sql = SELECT.
The whole thing works until we put another reservation in,
which would suggest that we are missing a condition.
I am baffled :(
Ok, here's what I came up with...
Instead of checking the reservations table for apartments that are available, lets check for apartments that are booked. And we'll use 'DISTINCT' to filter out duplicates (if an apartment is booked, no need to list it more than once). So now the query is:
SELECT DISTINCT apartments.* FROM apartments LEFT JOIN reservations ON reservations.ID=apartments.ID WHERE
( $start BETWEEN reservations.fromdate AND reservations.todate OR
$end BETWEEN reservations.fromdate AND reservations.todate OR
reservations.fromdate BETWEEN $start AND $end OR
reservations.todate BETWEEN $start AND $end) AND
apartments.type IN(2);
That will give you all of the apartments that are booked for the given time range.
To go one step further: take all the IDs from the result of the above query and build a comma delimited list... for example if ID2 and ID3 are both booked then you'll have:
$booked_list = "2,3";
Now to dislay the info on the apartments that are available, do a select query on the apartments table for all apartments except the ones that are booked:
SELECT * FROM apartments WHERE ID NOT IN (2,3);
Let us know how this works.
Your idea seems a better way of processing it,
I could not manage to do the Dilimated list because I am not quite sure how you wanted me to do this or how to extract the results into a diliamted list, I didn't know you could in php. I have used Dilimated lists to import and export data from databases but not in php programing
I did try to put all the results into a string and then ask it to only select apartments that are not inludeded in the string and it worked until I selected a large date and it should have picked several apartments as booked, but it only picked one which would suggest that it stopped findind the reserved apartments after it found 1 apartment.
I am really grateful for your time and patients with me
Thanks
$query1 = "SELECT DISTINCT apartments.* FROM apartments LEFT JOIN reservations ON reservations.ID=apartments.ID WHERE
( '$start' BETWEEN reservations.fromdate AND reservations.todate OR
'$end' BETWEEN reservations.fromdate AND reservations.todate OR
reservations.fromdate BETWEEN '$start' AND '$end' OR
reservations.todate BETWEEN '$start' AND '$end') AND
apartments.type IN(2)";
$result = mysql_query($query1) or die("Query failed : " . mysql_error());
$num_rows = mysql_num_rows($result);
if ($num_rows > 0) {
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$booked_array[] = $line['ID'];
}
$booked_list = implode(",",$booked_array);
$where_stuff = "WHERE ID NOT IN ($booked_list)";
}
$query2 = "SELECT * FROM apartments $where_stuff"
I have used this approach before to create a graphical diary-style view of availability.
The way i did it was to disconnect the recordset, expand it with an array of all the dates in the range of the query, marking 0's and 1's for unavailable/available.
This is basically a DIY cross tab report :), you may want to chech that you db version hasnt got this feature built in.
Still working on it,
It now only producers a list of all our apartments
I also put an echo command after the IMPLODE to see if it worked and the output from the IMPLODE was nothing
I think this approach seems more logic then the other. We just need to list all the reservations and tell the script not to display the apartments that are on the list.
Without the implode it produces a nice list of all the reserved apartments, nice but we want it the other way around.
Heres some of the code I just tested :-
$result = mysql_query($query1) or die("Query failed : " . mysql_error());
$num_rows = mysql_num_rows($result);
if ($num_rows > 0) {
while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
extract($line);
{
$booked_array[] = $line['ID'];
}
$booked_list = implode(",",$booked_array);
$where_stuff = "WHERE ID NOT IN ('$booked_list')";
echo "$booked_list"; // testing the implode
}
$query = "SELECT * FROM apartments $where_stuff";
