(c) Server-side

i.Create a database table

Modify table-manager.php to create a new table in your database.

(3 marks)

So far the code has used an existing database table called tt284_guests, but that has no provision for a booking reference field. You will now create a new table in your database.

Examine the file table-manager.php

Notice the variable $database_table which must be set to the name of your new table required. You will create a table called tt284_oucu. (Where 'oucu' is replaced with your own OUCU).

Your new table will contain all the columns which exist in tt284_guests, with the addition of the new booking reference column. Base your answer closely on the existing SQL statement.

The booking reference field may contain up to 10 characters.

Modify the SQL statement in the $create_sql variable to meet these requirements. Remember that column names cannot contain spaces, so you must choose a suitable name. Once you are happy with your SQL, open table-manager.php on the TT284 server and click the create button to execute the SQL to create the table.

Once a table is created it cannot be modified, so if you create a table with the incorrect structure, you must delete it before you can create a corrected version. This can be done using the drop button when executing table-manager.php.

In your solution document, provide your code and a screenshot of your browser displaying the result of executing table-manager.php

ii.Configure to use the new table

Modify tma02_admin.php to use the new table.

(2 marks)

Only a single line of code should be changed.

In your solution document, provide your code.

You should perform some tests to be sure that your code is using the new table before moving on, but you do not need to provide evidence of those tests.

iii.Store booking reference in the database

Modify tma02_save-row.php to add the data from the booking reference field to the database.

(8 marks)

Look carefully at how the other data elements are added to the database and reflect that for the booking reference field.

Note carefully that tma02_save-row.php handles two distinct situations; one when the record has been edited and the other when the record is new. Here we are only considering a new record and you need only update that part, leaving the other unchanged.

Add the following two entries:

Table 1: Data to add to database

First name	Last name	Email	Booking Reference
Zinolla	Zanda	z.zanda@zinc.ac.uk	ABC-87654
Zeua	Avrenim	zeua.avrenim@zmail.ac.uk	ACD-54321

If you have created any other records when testing, they should be removed prior to adding the two entries above. The quickest way to clear the test data is to drop and recreate the table using table-manager.php.

Table 2: Database table display

firstname	lastname	email
Zinolla	Zanda	z.zanda@zinc.ac.uk
Zeua	Avrenim	zeua.avrenim@zmail.ac.uk

Table 2 shows the data table which should now be displayed. Notice that the new booking reference column is not currently shown.

In your solution document, provide your code and a screenshot of the web page showing the new table containing entries with the fields completed. You can use the Show/Hide: Data from database table to produce the screenshot if required.

iv.Display booking reference column

Modify tma02_data-table.php to display the booking reference column using data from the database.

(4 marks)

You will need to display the booking reference column from the database in the same way the name and email columns are displayed. You are not required to add the booking reference column to the search feature, but there is no penalty for doing so.

The data table should now be as shown in Table 3 (your choice of booking reference column name may vary):

Table 3: Database table display with Booking Reference column

firstname	lastname	email	reference
Zinolla	Zanda	z.zanda@zinc.ac.uk	ABC-87654
Zeua	Avrenim	zeua.avrenim@zmail.ac.uk	ACD-54321

In your solution document, provide your code and a screenshot of the web page showing the table containing the new entries.

v.Server-side Validation

Modify tma02_validate.php to provide server-side validation for booking reference field.

(8 marks)

Although the client-side validation checks the correct form of the booking reference, it does not actually check it is a valid booking reference.

For a reference to be valid, the server-side should validate the reference to the same format as the client-side, plus the following additional constraints: the three-letter group must be one of ABC, ACD, BCD. Anything else is invalid.

The next character must be a hyphen. Anything else is invalid.

The first digit may only be 5 or 8. The other digits may be any value between 0 and 9 inclusive.

You need to provide checks and only if all are valid, proceed to write the data to the database. If not, provide feedback to the user that the booking reference is invalid.

Add a comment to your validation code explaining the meaning of each part of your booking reference validation expression.

Again, examine the existing code closely, it will guide you.

The easiest way to test regular expressions is using a free online tool search the web for regex tester to discover these.

In your solution document, provide a copy of your code.