CSG2431/4431: Interactive Web Development

Assignment Overview

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.

Public Section of the Website

People who visit the website without being logged in can only access the following:

• A registration page where staff can register an account
• A login form containing fields for a username and a password, and a submit button
• An admin login form containing fields for a username and a password, and a submit button

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.

Staff Registration

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:

• Staff username
• First name and surname
• Telephone extension number
• Password (and a password confirmation field)

The form should be validated in JavaScript or PHP (preferably both). All fields are required, the telephone extension number should be a four digit number (e.g. “6283”) and the password should be at least 5 characters long.

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.

Staff Section of the Website

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:

• Their name, e.g. “Welcome, Joe Bloggs” or “You are logged in as Joe Bloggs”
• A link to log out
• A room search form containing a text field and a submit button
• A room list link or button that will show a list of all rooms
• A list of the staff member’s upcoming room bookings
  • Only bookings that have not yet ended should appear in the list
  • Order the list by the start time of the bookings
  • Beside each booking there should be a link for the staff to cancel the booking

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:

• The start time and end time are both valid (use the strtotime() function for this)
• The start time is later than the current time
• The end time is later than the start time
• The new booking does not overlap any other existing bookings for that room

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:

• When a staff member logs in, store their name in a session variable called “name” and their username in a session variable called “staff_username”
• Check that the “staff_username” session variable exists and is not empty in order to allow access to all pages and functionality that require a staff member to be logged in
• Use the “name” session variable when displaying the name of the logged in staff member
• Use the “staff_username” session variable when inserting booking details into the database
• Destroy the session variables and session when the logout link is clicked

Admin Section of Website

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:

• A link to log out
• A room list link or button that will show a list of all rooms
• A staff list link or button that will show a list of all staff members
• The ability to view and cancel any upcoming booking
• The ability to manage rooms. This involves:
  • Viewing details of the rooms that are currently in the database
  • Adding new rooms to the database
  • Editing the details of rooms in the database
  • Deleting rooms from the database
• The ability to manage room types. This involves:
  • Viewing details of the room types that are currently in the database
  • Adding new room types to the database
  • Editing the names of room types in the database
  • Deleting room types from the database See image.

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:

• The room number (e.g. “ML13.225” or “JO19.109”)
• The capacity of the room (e.g. “25” or “250”)
• The ID number of the room type

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:

• When an admin logs in, store their username as a session variable called “admin_username”
• Check that the “admin_username” session variable exists and is not empty in order to allow access to pages and functionality that require an admin to be logged in
• Destroy the session variables and session when the logout link is clicked

Business Rules

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.

• Room numbers and room type names must be unique (e.g. there cannot be two rooms that have the same room number)
• Admins cannot edit the number of a room once it has been added to the database
• Admins cannot delete rooms that have any bookings (upcoming or in the past)
• Admins cannot delete room types that are linked to any rooms (i.e. they can only delete a room type if there are no rooms of that type in the database)
• A confirmation prompt must be shown before any action that will result in data being deleted, such as cancelling a booking or deleting a room.

Website Structure & Functionality Summary

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.

Database Structure

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.

CSG4431 Additional Requirements

The admin section of the site must include a “Manage Admin Users” page. This page contains the following two items:

• A list of all current admin usernames, with a “Delete” link next to each one. Clicking the delete link deletes that admin user (remember to include a confirmation prompt)
  • Make sure that it is not possible to delete the only admin user – i.e. there must always be at least one admin user
• A form containing a drop-down list containing the names of all staff and a submit button. Submitting the form will copy the username and password of the selected staff member into the admin table, making them an admin user (their staff member account also still exists)
  • Make sure that staff members can only be promoted once – i.e. if their staff username already exists in the admin table, they cannot be promoted

With regards to the marking key, these requirements are part of the admin section of the site.