Bed and Breakfast Tutorial  

• Home
• Projects
• Blog
• Contact
• Resume

If you could work anywhere on anything what would it be? If I had my choice I think I'd spend my time making simple systems which would make a huge difference to their users. One idea is to build a B&B reservation web site. Imagine a charming B & B off the beaten path; maybe in the mountains maybe near the beach. I'm going to have to stay there a week or two to fully capture the charm and atmosphere. It would practically be a free vacation; I say practically because somewhere along the way you're going to have to write some code but as I can show you here it's fairly straight forward and will not cut into your time exploring the area.

To build our B & B reservation system we will be using PHP and MySQL but these techniques are fairly generic and should work with any DBMS and programming language.

In this tutorial I expect you have a web server and a database installed and running. I also expect that you know how to connect to the database and to run a SQL script which will set up everything we need. If you need help getting that far let me suggest << other excellent WM tutorials>>.

We have three languages we are dealing with here: HTML which draws the web pages, PHP will do the flow and control (things like IF and WHILE) and SQL which is the language to control the database.

Before we begin to write any code the first thing to think about is the flow of the application. What is the first page? What is the next page? What information is needed to proceed to the next page? And so on until you have completed your goal. Write this down, it doesn't need to be pages long but it should be complete and define all the transactions you will allow. In our case this is booking a room. It would be a mistake to start with the reservation page because there are at least 2 pages that have to come first and these pages will tell you things that the reservation page will need to know.

It helps to think of this transaction as if it were happening in person or over the phone. The first question is "Do you have a room available on...?" The answer might be "It depends, how many will be in your party?" Then we get to the "Yes we have 2 rooms..." or "no I'm sorry..."

If the answer was "Yes" there is another question: which room would you like? After that there is some collecting of information: name, phone, credit card and some sort of confirmation.

I see the user interaction like this:

Page 1 requires nothing and it sends 3 fields, number of occupants, Arrive Date and Depart Date to page 2. Page 2 takes those 3 fields and displays a list of available rooms; each row is a link to page 3 which will pass the arrive date, depart date and the room number which is determined by which one was clicked. Page 3 will take this information and use it to fill out a reservation form, the user will have to add their name and contact information then it all gets passed to page 4, the confirmation page. Notice that page 2 searched the database but no page has written anything yet, we've just been collecting the pieces we'll need. It's all done in page 4 including all the verification that this data is still valid (the room is still available, the values makes sense).

The data flow will look like this:

I should point out that I have what looks like 4 pages but personally I prefer to keep these all in two PHP files. The first contains some common functions that create the web pages and it can be modified with text and graphics unique to that site without affecting the workings of the application. The second contains all the rest of the PHP code; it can be upgraded by me and a new one sent to the webmaster who can replace it without loosing their custom look. This arrangement makes life much easier to later, even if I am the Webmaster. There's little risk of only upgrading some of the pages and breaking the application. If the webmaster wants to implement a "new look" they can do it without risking the application.

The first file, "headerFooter.php" is straight forward, it is one I've created and used many times before. For now it contains two functions: writeHeader() and footer() and we'll add some variables such as your database login information. writeHeader will write the opening HTML and footer will write the closing HTML. In this example you will see they a quite simple but put a nav bar and maybe a graphic into them and you'll transform this application into something unique for our client. Maybe you'll get to sell the whole thing to another client just by changing these functions.

Create this file and name it headerFooter.php and put it on your web server:

function writeHeader($title)
{
print <<<EOF
<HTML>
	<HEAD>
		<TITLE>$title</TITLE>
	</HEAD>
	<BODY>
		<H2 ALIGN=CENTER>$title</H2>

EOF;
}

function footer()
{
print <<<EOF
	</BODY>
</HTML>
EOF;
}

?>

Now you can see that we could write a page that would look like this:

<?
include 'headerFooter.php';

writeHeader("Test Page");

// Page body goes here

footer();
?>

I'll use these two functions for the PHP pages from now on because it let's us focus on the PHP code. You can see from the Data Flow above each page has at least one unique parameter except for page one which expects nothing. If you have a Guest Name then you must be on page four, if not and you have a "roomID" you have to be on page 3. If you don't have either of those and you do have a number of guests then you have to be page 2 and if you don't have any of those then you're at page 1. To write this in PHP is very straight forward:

if($_POST["guestName"] > "")
{
	// page 4 goes here
	
} else if($_POST["roomID"] > "")
{ 
	//page 3 goes here
...

Pages 1 and 2 present an interesting opportunity. We've all used these web forms to search for something (room, airline flight, CD, book or such) and had the experience of not finding what we're looking for then having to use the back button of your browser to search again. We're going to be cooler than that; we're going to combine page 1 (the search form) and page 2 (the results) on one page. If there are no search criteria we'll only print the search form. But if there are some search criteria we will print the search form with those criteria in it and put the results under it. That way if there are no results or for some reason our customer is not happy with the options available they'll be able to re-run the search instantly. It's small touches like this, just a few lines of code, that make your application significantly easier to use.

After the page 3 test we'll have to do our combined page 1 and 2. The test here is only to see if we will bother to query the database and display results.

} else 
{ 	
	/* Search form */
	
	if($_POST["numGuests"] > "") 
	{
		/* Display Search results */
		
	}
}

Since we are dealing with dates and we will have users input these dates we are also going to need to make date popup menus and then to validate the data that was input. Since we will do both of these more than once we'll use functions to do it.

PHP is fairly flexible with dates; MySQL is more strict. So we will be taking the dates we receive as input and turning them into PHP dates then formatting them so that MySQL will understand them. PHP will, for example, accept the date "November 31, 2004" (November only has 30 days) and translate it to "December 1, 2004" without raising an error. This can cause problems but in this case it is very handy for us.

There are many solutions to inputting dates; I've chosen to to use 3 popup menus for each date, year, month and day. This has an advantage that by keeping the values separate I can make sure they are formatted in the correct order. This is very important since we might expect folks from different places to use this and in other places, Europe for example, they put the dates parts in a different order.

First the validDate($myDate) function will take a variable and make sure it has a valid date value in it. If so it will return that date (rewritten so it would be a valid date for MySQL) if not it will return a blank value.

function validDate($myDate)
{
	$datePart = explode("-", $myDate);
	$today = getdate(); 
	$year = $today['year'];
			/* check year (only allow this year or next) */
	if (($datePart[0] < $year) | ($datePart[0] > ($year +1))) 
	{
		return("");
	}
	if (($datePart[1] < 1) | ($datePart[1] > 12)) /* check the month */
	{
		return("");
	}
	if (($datePart[2] < 1) | ($datePart[2] > 31)) /* check day */
	{
		return("");
	}
	$mydate = date ("Y-n-j", mktime(0,0,0,$datePart[1],$datePart[2],$datePart[0]));
	return($mydate);
}

The second function: datePopups($myDate, $fieldName) takes two parameters, a date which it uses to set the default date (put the "SELECTED" in the correct place) and the name of this date which it uses to name these three popup fields. So given a call like datePopups($myDate, $fieldName) where $myDate = July 17, 2005 and the $fieldName = "arriveDate" raw HTML of our date popup would look like this:

<SELECT NAME=arriveDateMonth>
<OPTION VALUE=1>Jan</OPTION>
<OPTION VALUE=2>Feb</OPTION>
<OPTION VALUE=3>Mar</OPTION>
<OPTION VALUE=4>Apr</OPTION>
<OPTION VALUE=5>May</OPTION>
<OPTION VALUE=6>Jun</OPTION>
<OPTION VALUE=7 SELECTED>Jul</OPTION>
<OPTION VALUE=8>Aug</OPTION>
<OPTION VALUE=9>Sep</OPTION>
<OPTION VALUE=10>Oct</OPTION>
<OPTION VALUE=11>Nov</OPTION>
<OPTION VALUE=12>Dec</OPTION>
</SELECT>

<SELECT NAME=arriveDateDay>
<OPTION VALUE=1>1</OPTION>
<OPTION VALUE=2>2</OPTION>
<OPTION VALUE=3>3</OPTION>
<OPTION VALUE=4>4</OPTION>
<OPTION VALUE=5>5</OPTION>
<OPTION VALUE=6>6</OPTION>
<OPTION VALUE=7>7</OPTION>
<OPTION VALUE=8>8</OPTION>
<OPTION VALUE=9>9</OPTION>
<OPTION VALUE=10>10</OPTION>
<OPTION VALUE=11>11</OPTION>
<OPTION VALUE=12>12</OPTION>
<OPTION VALUE=13>13</OPTION>
<OPTION VALUE=14>14</OPTION>
<OPTION VALUE=15>15</OPTION>
<OPTION VALUE=16>16</OPTION>
<OPTION VALUE=17 SELECTED>17</OPTION>
<OPTION VALUE=18>18</OPTION>
<OPTION VALUE=19>19</OPTION>
<OPTION VALUE=20>20</OPTION>
<OPTION VALUE=21>21</OPTION>
<OPTION VALUE=22>22</OPTION>
<OPTION VALUE=23>23</OPTION>
<OPTION VALUE=24>24</OPTION>
<OPTION VALUE=25>25</OPTION>
<OPTION VALUE=26>26</OPTION>
<OPTION VALUE=27>27</OPTION>
<OPTION VALUE=28>28</OPTION>
<OPTION VALUE=29>29</OPTION>
<OPTION VALUE=30>30</OPTION>
<OPTION VALUE=31>31</OPTION>
</SELECT>

<SELECT NAME=arriveDateYear>
<OPTION VALUE=2004>2004</OPTION>
<OPTION VALUE=2005 SELECTED>2005</OPTION>
</SELECT>

The function to do this:

function datePopups($myDate, $fieldName)
{
	if ($myDate > "")
	{
		$myDate = validDate($myDate);
	}
	if ($myDate == "")
	{
		$myDate = date ("Y-n-j", mktime());
	}
	$datePart = explode("-", $myDate);
	$months = array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");
	
	print "<SELECT NAME=" . $fieldName . "Month>\n";
	for ($i = 1; $i < 13; $i++)
	{
		print "<OPTION VALUE=" . $i;
		if ($datePart[1] == $i)
		{
			print " SELECTED";
		}
		print ">" . $months[($i -1)] . "</OPTION>\n";
	}
	print "</SELECT>";
	print "<SELECT NAME=" . $fieldName . "Day>\n";
	for ($i = 1; $i < 32; $i++)
	{
		print "<OPTION VALUE=" . $i;
		if ($datePart[2] == $i)
		{
			print " SELECTED";
		}
		print ">" . $i . "</OPTION>\n";
	}
	print "</SELECT>";
	print "<SELECT NAME=" . $fieldName . "Year>\n";
	$i = date ("Y", mktime());
	print "<OPTION VALUE=" . $i;
	if ($datePart[0] == $i)
	{
		print " SELECTED";
	}
	print ">" . $i . "</OPTION>\n";
	print "<OPTION VALUE=" . ($i + 1);
	if ($datePart[0] == ($i + 1))
	{
		print " SELECTED";
	}
	print ">" . ($i + 1) . "</OPTION>\n";
	
	print "</SELECT>";
}

BUILD THE DATABASE

Before we can do anything with the web pages we'll need to create and populate our database. So we're going to set aside PHP and turn to SQL (the database language). You should have the MySQL database installed and running. The directions are a little different depending on your OS but it will work on Windows, Macintosh, Linux and many others. See MySQL.com for the binaries and the directions to install the database.

To create and load the database weÕll need to connect to the database as root; this is root in the database which is different than root in your OS - although both are all powerful in their respective arenas. Then we will run all of these SQL commands. I have included them in a single script called "cerate_BnB.sql" but here I will walk through them and explain each step. You can run the script or copy each statement into the command window of your database (in MySQL a statement may be many lines long but it always ends with the semicolon ";").

First we need to create the database (a discrete area for our data); this helps keep this data separate from other date you may want to have.

CREATE DATABASE BnB;

Now we must "use" the database we just created, this tells the database engine to run all the following commands in the database we just created.

USE BnB;

Now we will create the tables we need, the "CREATE TABLE" statement names the table and sets up the fields.

CREATE TABLE Room 
(
    roomID		    INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name	 		VARCHAR(40)  NOT NULL,
    description	 	VARCHAR(250) NOT NULL,
    maxOccupants 	SMALLINT UNSIGNED  NOT NULL,
    rate 			SMALLINT UNSIGNED  NOT NULL,
    PRIMARY KEY (roomID) 
)
;

CREATE TABLE Reservation 
(
    resID	 		 	INT UNSIGNED NOT NULL AUTO_INCREMENT,
    arriveDate  		DATE  NOT NULL,
    departDate	 		DATE NOT NULL,
    roomID	 			INT UNSIGNED  NOT NULL,
    guest	 			VARCHAR(80) NOT NULL,
    comment 		 	VARCHAR(250) NOT NULL,
    PRIMARY KEY (resID) 
)
;

Now we must create a user who will have access to these tables. Notice this user has SELECT (read) permissions on Room, this is all they will need on this table. They also have SELECT and INSERT (create new records) on Reservation. The other permissions for tables are DELETE and UPDATE but the webUser doesn't need these. Also notice the @localhost, it means that these permissions are only for the user 'webUser' when they connect from localhost (which is universal for this computer). This user cannot connect to the database from another computer which is fine so long as your web server and database are on the same computer. If not you will have to change localhost to the name of the computer or IP address of the web server. Finally we see IDENTIFIED BY which sets their password, in this case to 'webUserPassWord' but you should change it here and remember it.

	/* For gosh sakes you WILL change these passwords before you run this */
GRANT SELECT ON Room TO webUser@localhost IDENTIFIED BY 'webUserPassWord';
GRANT SELECT, INSERT ON Reservation TO webUser@localhost IDENTIFIED BY 'webUserPassWord';

Remember that our webuser can only read the room information. We need to load that data here. You can see the format of the command below; INSERT INTO [table name] ([column name], [column name], ...) then you have several options but I am using the VALUES option where you can add static data. Each set of () is one record and needs to exactly match the order that you used in the INSERT statement.

Notice I did not set the "roomID" field, the database will set this for me.

	/* Lost some sample data */
INSERT INTO Room (name, description, maxOccupants, rate)
	VALUES ("Blue Room", "A delightful view and all decor is blue.", 2, 100),
		("Washington Bedroom", "George really did sleep here", 2, 100),
		("Honeymoon Suite", "Heart shaped tub, phone and of course bed", 2, 150),
		("Presidential Suite", "Pre-bugged for your convenience", 10, 250),
		("Family Room", "Big and cheap, ideal for large families", 10, 50),
		("Garden Room", "Not for those who suffer from hay fever", 4, 150);
		

In this database weÕve got two tables Room and Reservation.

The Room table has a record for each room and all of the information particular to that room while the Reservation table has the information for each particular booking including customer and payment information. The arrow indicates that the field "roomID" in the Reservation table is a foreign key which means that a value in this column will point to one and only one record in the Room table.

BUILD SOME WEB PAGES

At this point we have all foundation and nothing to look at but this is about to change as we put this together. We'll use the first file, headerFooter.php and create a second file, BnB.php. We will add headerFooter.php as an include file, we'll add the two date functions and put in place holders for each page. Then as we develop the application we can try it and watch each page come to life.

Create this file and name it "BnB.php" and put it in the same directory as the first file "headerFooter.php".

<?
include 'headerFooter.php';

function validDate($myDate)
{
	$datePart = explode("-", $myDate);
	$today = getdate(); 
	$year = $today['year'];
			/* check year (only allow this year or next) */
	if (($datePart[0] < $year) | ($datePart[0] > ($year +1))) 
	{
		return("");
	}
	if (($datePart[1] < 1) | ($datePart[1] > 12)) /* check the month */
	{
		return("");
	}
	if (($datePart[2] < 1) | ($datePart[2] > 31)) /* check day */
	{
		return("");
	}
	$mydate = date ("Y-n-j", mktime(0,0,0,$datePart[1],$datePart[2],$datePart[0]));
	return($mydate);
}

function datePopups($myDate, $fieldName)
{
	if ($myDate > "")
	{
		$myDate = validDate($myDate);
	}
	if ($myDate == "")
	{
		$myDate = date ("Y-n-j", mktime());
	}
	$datePart = explode("-", $myDate);
	$months = array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec");
	
	print "<SELECT NAME=" . $fieldName . "Month>\n";
	for ($i = 1; $i < 13; $i++)
	{
		print "<OPTION VALUE=" . $i;
		if ($datePart[1] == $i)
		{
			print " SELECTED";
		}
		print ">" . $months[($i -1)] . "</OPTION>\n";
	}
	print "</SELECT>";
	print "<SELECT NAME=" . $fieldName . "Day>\n";
	for ($i = 1; $i < 32; $i++)
	{
		print "<OPTION VALUE=" . $i;
		if ($datePart[2] == $i)
		{
			print " SELECTED";
		}
		print ">" . $i . "</OPTION>\n";
	}
	print "</SELECT>";
	print "<SELECT NAME=" . $fieldName . "Year>\n";
	$i = date ("Y", mktime());
	print "<OPTION VALUE=" . $i;
	if ($datePart[0] == $i)
	{
		print " SELECTED";
	}
	print ">" . $i . "</OPTION>\n";
	print "<OPTION VALUE=" . ($i + 1);
	if ($datePart[0] == ($i + 1))
	{
		print " SELECTED";
	}
	print ">" . ($i + 1) . "</OPTION>\n";
	
	print "</SELECT>";
}


/* the web page starts here */
if($_POST["guestName"] > "") 
{
	writeHeader("Confirmation");
		/* Page 4 */
		
	footer();
} else if($_POST["roomID"] > "")  
{ 
	writeHeader("Reserve a Room");
		/* Page 3 */
		
	footer();
} else 
{ 
	writeHeader("Search for Available Rooms");
	
	/* Search form */
	
	if($_POST["numGuests"] > "") 
	{
		/* Search and display results */
		
	}
	footer();
}
?>

Navigate to this file from your web browser and you should see a page with "Search for Available Rooms" in the middle at the top. Although there's nothing else on the page we're actually quite far along.

PAGE 1, SEARCH FOR A ROOM:

To build a room browser should be straight forward; first the search form and then if we have our criteria input query the Room table then show the results.

Here is a very simple search form, notice the first and last lines "print >>>EOT" and "EOT;" this is a PHP command that says print everything starting with the line after the "print" command until you get to "EOT" on a line by itself; EOT must be the first thing PHP sees on that line, no space or tabs allowed (the semicolon is the way PHP ends all of it's commands).

The very first thing I do before I do the form is to take the $_POST array (the values passed into this page) and build the $arriveDate and $departDate values. These are important to have so I can set the dates in the form as I build the form. This allows a user to change their search with minimal effort and adds a bit of sophistication to the application.

Take this text and put it into the BnB.php file, put it right under "/*Search Form*/" and save it. Now load the page in your browser - there's the search form; enter a date and click search. It will take you back to the same page, not so impressive but hang on things are about to start flying.

	$arriveDate=validDate($_POST["arriveDateYear"] . "-" . $_POST["arriveDateMonth"] . "-" . $_POST["arriveDateDay"]);
	$departDate=validDate($_POST["departDateYear"] . "-" . $_POST["departDateMonth"] . "-" . $_POST["departDateDay"]);
	
	print <<<EOT
	<center>
	<form action="BnB4.php" method="post" name="reservation">
	<table border="1">
		<tr>
			<td>Number<br>of Guests</td><td>Arrive</td><td>Depart</td>
		</tr>
		<tr>
			<td><select name="numGuests">
EOT;
	for($i = 1; $i < 11;  $i++)
	{
		if ($_POST["numGuests"] == $i)
		{
			print "<option value='$i' SELECTED>$i</option>\n";
		} else {
			print "<option value='$i'>$i</option>\n";
		}
	}
	print "</select></td>\n<td>";
	print datePopups($arriveDate, arriveDate);
	print "</td><td>";
	print datePopups($departDate, departDate);
	print  <<<EOT
		</td>
			</tr>
	</table><br>
	<input type="submit" value="search for rooms"></form>
	</center>
EOT;

PAGE 2, SHOW THE AVAILABLE ROOMS.

This page will open a connection to the database, query the Room table and display the results.

/* this block opens a connection to the database */
$link = mysql_connect($DBhost, $webUser, $webUserPassWord)
    	or die("I'm sorry this is not available now. ");
    	
	if (!mysql_select_db("Bnb", $link)) {
		echo "I'm sorry this is not available now. ";
		exit;
	}

/* This block builds the query notice that we added a search condition to the end (WHERE maxOccupants >  " .  $_POST["numGuests"]) */
	$myQuery = "SELECT Room.roomID, name, description, maxOccupants, rate ";
	$myQuery = 	$myQuery . " FROM Room, Reservation ";
	$myQuery = $myQuery . " WHERE Room.roomID = Reservation.roomID ";
	$myQuery = $myQuery . " AND maxOccupants >=  " .  $_POST["numGuests"] ;
	$myQuery = $myQuery . " AND arriveDate >=  '" .  $_POST["departDate"] . "' ";
	$myQuery = $myQuery . " AND departDate <=  '" .  $_POST["arriveDate"] . "' ";
	$myQuery = $myQuery . " GROUP BY roomID, name, description, maxOccupants, rate ";
	$myQuery = $myQuery . " HAVING count(*) > 0 ";
	$myQuery = $myQuery . " ORDER BY maxOccupants asc, rate desc ";

/* Now we run the query and put a pointer to the results into $result*/
	$result = mysql_query($myQuery, $link)
    	or die("I'm sorry this is not available now. "); 
    
/* And we check to see if we did in fact get any rows back, if so we print them*/
	if (mysql_num_rows($result) > 0)
	{
		print "<center><br><br>Results<BR><table border=1 width=90%><tr><th></th><th>Name</th><th>Description</th><th>Maximum<br>Occupants</th><th>Rate</th></tr>\n";
		/* print each row */
		while ($row = mysql_fetch_array($result))
		{ 
			print "<tr><td><A HREF='reservation.php?roomID=" . $row["id"] . "'&arriveDate=" . $_POST["arriveDate"] . "&leaveDate=" . $_POST["leaveDate"] . ">Reserve</td><td>" . $row["name"] . "</A></td><td>" . $row["description"] . "</td><td>" . $row["maxOccupants"] . "</td><td>" . $row["rate"] . "</td></tr>";
		} 
		print "</table></center><br>";
		} else {
		/*or print a message saying there are no rooms*/
		print "<center>Sorry, could not find any rooms available.</center><br>";
	}
	/* a little housekeeping - we need to close the connection to the database */
    mysql_close($link);

Before we go any further let's put these two things together and we should have a working page. It won't reserve anything but it will connect to the database and show a list of rooms.

Next we need to create an ability to create and save a reservation before we can search for available room. A reservation is a roomID, the arrive and leave dates and the customers information. We will know the dates because they were entered in the search form, we know the room by which row was clicked; we can take these pieces and pass them to a new form where the customer can enter their information.

Notice the first column is a link to a page "reservation.php" and that it is being passed the roomID, arriveDate and the leaveDate. They will be put into a form and the customer will be asked to fill out information about themselves before we will actually set a room aside for them.

This page needs to receive the 3 parameters passed to it and to put those values into a form with the blank spaces for the customer information.

<html>
<head>
	<title>Create Reservation</title>
</head>
<body>
<h2>Request a Reservation</h2>
<form action="confirm.php" method="post" name="reservation">
<table border="1">
	<tr>
		<td>Room Number</td><td><? print $_GET["roomID"] ?><input type="hidden" name="roomID" value="<? print $_GET["roomID"] ?>"></td>
	</tr>
	<tr>
		<td>Arrival</td><td><? print $_GET["arriveDate"] ?><input type="hidden" name="arriveDate" value="<? print $_GET["arriveDate"] ?>"></td>
	</tr>
	<tr>
		<td>Depart</td><td><? print $_GET["leaveDate"] ?><input type="hidden" name="leaveDate" value="<? print $_GET["leaveDate"] ?>"></td>
	</tr>
	<tr>
		<td>Your Name</td><td><input type="text" name="custName" size="40" maxlength="80"></td>
	</tr>
	<tr>
		<td>Phone Number</td><td><input type="text" name="custName" size="40" maxlength="80"></td>
	</tr>
	<tr>
		<td>Email Address</td><td><input type="text" name="custName" size="40" maxlength="80"></td>
	</tr>
	<tr>
		<td>Credit Card</td><td><input type="text" name="custName" size="40" maxlength="80"></td>
	</tr>
	<tr>
		<td>Billing Address</td><td><input type="text" name="custName" size="40" maxlength="80"></td>
	</tr>
</table><br>
<input type="submit" value="Make Reservation"></form>
</body>
</html>

Notice we have not yet written anything to the database nor have we verified any of this information. Because we are passing data in URL's and using hidden fields which are easily changed by hackers we'll need to verify all of these fields and only if they pass create a record in the Reservation table. I do all of this in the confirmation page.

NEED TO MAKE THE CONFIRMATION PAGE -CODE TO VERRIFY DATA -INSERT INTO DATABASE

About holding Credit Card information. I have saved the customers credit card information in a very simple manner here for the example of this tutorial. There is very little that will upset your customers more quickly than having their credit cards stolen while in your care. It could also mean the banks will stop processing credit cards for you altogether. You need to be very careful with this.

Personally I recommend using a service, many ISP's offer credit card processing as part of a business package. Ebay's PayPal service, Kagi, Digital River and others like it may be options. Using one of these services means you will not have to worry about holding this sensitive information, only a transaction number which is harmless in the hands of a hacker. -We could write a whole article on Credit Cards alone. -We should research the services I recommended

Now back to the beginning; we really want to only show the rooms that are available during our guests travel. How do you write a page and query to show all of the available rooms between two dates?

The particular problem is you are looking for what is not there, all of the rooms with no Reservation records that overlap with the desired dates. In most DBMS's this can be solved with a sub-query and the NOT IN clause.

For example: using our schema it is easy to write a query to show all of the rooms that are booked between two dates. Given $_POST["arriveDate"] and $_POST["leaveDate"] from a search form we can write a query:

SELECT roomID 
FROM Reservation
WHERE arriveDate < $_POST["leaveDate"]
AND leaveDate > $_POST["arriveDate"]

OK but this is the opposite of what we want - to get the rooms that are available we put this query into a sub-query and use NOT to get the opposite of these rooms:

SELECT id, name, description, maxOccupants, rate
FROM Room
WHERE id NOT IN (SELECT roomID 
		FROM Reservation
		WHERE arriveDate < $_POST["leaveDate"]
		AND leaveDate > $_POST["arriveDate"])
		

Most DBMS's will support this but MySQL before version 4.1 does not support sub queries still there is a very simple work around.

Since you cannot use a sub-query to rule out a set of records (the rooms that have been reserved) in MySQL you have to count the number of reservations during the time period defined and then only return the ones with a count of 0. This is just as functional:

SELECT roomID, name, description, maxOccupants, rate 
	FROM Room LEFT JOIN Reservation 
		ON (Room.roomID = Reservation.roomID 
			AND arriveDate >= '' AND leaveDate <= '') 
	WHERE maxOccupants >= 5  
	GROUP BY roomID, name, description, maxOccupants, rate 
		HAVING count(*) = 0 ORDER BY maxOccupants asc, rate desc 

In fact the above will do just what you need, return only available rooms between these two dates.

NEED TO ADD THIS QUERY TO THE SEARCH PAGE AND WE WILL HAVE A WORKING SYSTEM -WILL SEARCH FOR AVAILABLE ROOMS BETWEEN DATES -WILL ALLOW USER TO CLICK ON ONE WHICH WILL OPEN A RESERVATION WINDOW -WILL ACCEPT A RESERVATION