In this first assignment, you will build skills in both client and server side web development, particularly in the essential areas of form processing, form validation and database interaction.
You are required to build a MySQL database driven XHTML and PHP website to manage online booking of rooms for ECU staff. Staff members can register an account on the website and make room bookings, and administrators can log into the website to manage the system.
This assignment forms the basis for assignment two, in which you will add new features to the system and apply more in-depth business rules.
People who visit the website without being logged in can only access the following:
These things do NOT need to be on the same page. You are welcome to design all sections of the website however you like, as long as the functionality is implemented.
An example of a possible layout has been provided below. You are not required to create a website that looks pretty or resembles the example. See image.
Before a staff member can log in, they must fill out the registration form to create an account. The registration form must include the following fields:
When the form is submitted and validated successfully, the details should be inserted as a row in a “staff” table in the database. The staff member can then log in to the website via the staff login form using their username and password. Your PHP validation will need to check that the staff username supplied in the registration form has not already been used by another staff member.
The main purpose of the staff section of the website is to allow staff members to manage their room bookings – they must be able to make bookings, see their upcoming bookings, and cancel bookings.
The page that staff members are taken to after logging in must contain the following:
Clicking the “Cancel” link next to a booking should delete the appropriate row in the “bookings” table in the database. See image.
Once a staff member has logged in, they can either search for a room number or view the list of rooms to find the room they would like to book. The list of rooms and results of a search only needs to include the room number and a link to view and book the room. See image.
Clicking a “View/Book” link will take the staff member to a page containing the room details, current bookings and a booking form where they specify the start time and end time of the booking. See image.
Once the staff member completes and submits the booking form, use PHP to check the following:
If the booking cannot be made because it does not meet all of the criteria above, display an error message, e.g. “End time must be later than start time” or “This booking overlaps existing bookings”.
If the booking meets all of the criteria above, details of the booking should be inserted into a “bookings” table in the database. This will need to include the room number, the start time, end time and username of the staff member making the booking.
Once the booking has been inserted into the database, the staff member should be taken back to the page which shows their upcoming bookings.
Use sessions to ensure that the staff functionality works properly:
The details of the website administrators are also stored in the database, in an “admins” table. You do not need to create a registration form for admins – simply add a few of them directly into the database. The only details that need to be stored about admins are their username and password.
Admins log in to the website with their username and password using the admin login form. Once logged in, they are taken to an admin-only section of the website that must contain the following:
Admin users are able to view and cancel any upcoming room booking. To find bookings, admin users can view a list of all staff members or a list of all rooms. Next to the details of each staff member or room there should be a “View Details/Bookings” link.
Clicking this link shows the details of the staff member or room, and all upcoming bookings for that staff member or that room, with a “Cancel” link next to each booking. Clicking the “Cancel” link next to a booking should delete the appropriate row in the “bookings” table in the database. See image.
Before any bookings can be made, there must obviously be details of rooms in the database. Details of rooms will be stored in a “rooms” table in the database, and must contain the following details:
Details of room types will also be stored in the database, in a “room_types” table. The room_types table only needs two columns – one for a room type ID number, and one for the room type name. The room types are: See image.
Admin users must be able to manage rooms and room types via the website. This includes the ability to view existing rooms and room types, complete forms to add new ones and edit the details of existing ones, and delete them. See image.
Use sessions to ensure that access to the admin pages and functionality is controlled:
Within the website, there are a number of business rules that must be adhered to. These are all common sense rules that ensure that the website runs smoothly and effectively.
As detailed, the website contains a public section, a staff section and an admin section. The staff- only and admin-only pages and functionality must be restricted to only allow access to the appropriately logged in users. Ensure that your website provides convenient, consistent and logical options to navigate from page to page.
The following diagram illustrates the structure of the website and its functionality. See image.
You can design/lay out your website however you like, as long as the functionality is implemented.
The MySQL database storing the data must be a well-structured and efficient. A suggested structure is presented below. If you wish to deviate from this structure, please discuss it with your tutor. See image.
It is recommended that you use auto-incrementing integers as primary keys for the “bookings” and “room_types” tables. Room numbers, staff usernames and admin usernames are appropriate primary keys for the rooms, staff and admins tables.
Consider the following sample content if you are having difficulty understanding how data is stored in a database. Primary key columns have been given a grey background. See image.
Based on this data, you can see that Adam Smith has a booked ML03.210 from 4PM on 12/07/11 until 8:30AM the next day, and that Barry Howard has two bookings – one for ML03.101 on 02/07/11, and one for ML03.260 on 24/08/11. We can also determine that ML03.101 is a Lecture Theatre with a capacity of 150, ML03.210 is a Classroom with a capacity of 26, and so on.
Ensure that all fields in your database tables use the most appropriate data type to efficiently store the data they require. Make sure that the start and end times of bookings use the “DATETIME” data type in MySQL, and that you adhere to the “YYYY-MM-DD HH:MM” format when inserting dates into the database (you can ignore the seconds). This will ensure that dates and times are recorded correctly, which is necessary for you to be able to validate bookings.
The admin section of the site must include a “Manage Admin Users” page. This page contains the following two items:
With regards to the marking key, these requirements are part of the admin section of the site.