The Hotel Booking System

Chapter 6 - Manipulating the Database
byGareth Downes-Powellet al.
Wrox Press 2003

Our first job is to plan the system, and decide which pages we need, and which operations they need to perform. In Chapter 4 we discussed the basic design of the booking system, in this chapter we're going to create the actual pages that make up its three sections. We're also going to look at each of the three sections for adding, modifying, and deleting records individually, and define the actual pages that will carry out the operations for each section.

Making a Booking

Our first set of pages allows the user to make a booking. First we need to get the user's name and address details. If the user has booked before, then we will already have their details in the clients table in the database. We need a way of pulling that user's record from the database though, and ignoring the others. We do this by asking the user to enter their e-mail address, as this will be unique to each user.

If the user hasn't booked at the hotel before, then we need to collect the user's details before they actually make the booking, and insert them into the clients table. Next we need a page to allow the user to enter their booking details, and again insert them into the database. Lastly, we'll display the user's booking details, so that they can print them out and keep them as a permanent reminder of their booking details.

We can cover all these functions with the following four pages:

• add_user_record.php

• book_a_room.php

• booking_details.php

• confirm_booking.php

We will look at the roles of each of these pages in turn, and how they link to each other.

add_user_record.php

As the name suggests, this page allows the user to add their name and address details to the database. We will insert the user's data into the clients table in the database we created earlier in the book.

This page will add the user details to the clients table in the database by using the Insert Record Server Behavior. After the record has been inserted, we will redirect the user to the page booking_details.php, where the user can enter their required booking details. We pass the user's e-mail address to the booking details page, so that we can correctly tie the user to their booking.

book_a_room.php

This is actually the first page the user sees when they select "Book a Room" from the Main menu. If the user is a new user, we have a link to the add_user_record.php page above, which allows them to enter their details. If the user has booked with The Dreamweaver Hotel before, we can pull their details from the database. To do this we ask them to enter their e-mail address and we use this to find their details, by searching the clients table for the e-mail address the user supplied.

booking_details.php

This page allows the user to enter their booking details, which we will insert into the database table, bookings, again using an Insert Record server behavior. As a reminder the database structure for the bookings table is shown opposite.

    # Table structure for table 'bookings'    #    CREATE TABLE bookings (      ID tinyint(11) NOT NULL auto_increment,      roomID tinyint(11) NOT NULL default '0',      clientID tinyint(11) NOT NULL default '0',      startDate date NOT NULL default '0000-00-00',      endDate date NOT NULL default '0000-00-00',      adults int(11) NOT NULL default '1',      children int(11) NOT NULL default '0',      roomType varchar(30) NOT NULL default ' ',      roomOptions int(8) NOT NULL default '0',      networkConnection int(8) NOT NULL default '0',      PC int(8) NOT NULL default '0',      requirements varchar(250) NOT NULL default '',      PRIMARY KEY (ID)    ) TYPE=MyISAM COMMENT='Room Booking Table'; 

When the booking form is submitted, we will add the booking record to the database, and we will then redirect to the booking_confirm.php page, and again we will pass the user's e-mail address, so that we can locate the correct record on the next page.

confirm_booking.php

This page will pull the users details, and also the booking just created from the database, and displays it on the page, so the user has a copy of their booking details. It also shows the user their booking number, which they will need if they want to change or cancel a booking in the future.

Changing a Booking

The next section we will look at is to allow the user to change their booking. To do this we need to get some information from the user so that we can identify them, and select the correct booking record from the database.

We can't use the user's e-mail address this time, as one user can have a number of different booking records. Instead we ask the user to enter their booking number, which is unique, so that the correct booking record can be selected from the bookings table in the database. Once we have the correct booking record, we can read the user's ID number, and we can then retrieve their name and address details from the clients table.

We also display the existing details from the bookings record, and allow the user to modify them. We can then update the record in the database, and then again we display the details so the user can print them out.

The following pages will perform these operations.

• change_booking.php

• change_booking_details.php

• booking_updated.php

We will now look at each of these pages individually.

change_booking.php

This page will allow the user to enter a previous booking, so that they make alterations to it. The form will submit to the next page, change_booking_details.php, and the booking ID is passed to this page.

change_booking_details.php

This page will display the user's booking details, by using the booking ID passed from the previous page, change_booking.php.

The user can now change their booking details. When the form is inserted we use the Update Record server behavior. We pass the client's ID and booking ID to the next page, booking_updated.php.

booking_updated.php

This page takes a booking ID number, and a client ID number, and pulls the correct records from the database, to confirm to the user that their booking has been changed, and show the details of the new booking.

Canceling a Booking

The last section will allow the user to cancel their existing booking. To do this we need the user to enter the booking ID that they want to cancel. We can then delete the record with the same booking id from the bookings table. Finally we show the user a message to confirm that the record has been canceled. We will do this with the following pages:

• cancel_booking.php

• booking_cancelled.php

Again, we will look at each page:

cancel_booking.php

This page is to allow the user to cancel their booking. We allow the user to enter their booking number, and then we use Delete Record server behavior to delete the record. Finally, we redirect to the next page, booking_canceled.php passing the booking ID.

booking_cancelled.php

This page displays a message to the user, confirming their booking has been canceled. It uses the booking ID number passed to it from the previous page to show the user the ID of the booking that was deleted.