 |
| by Martin Elsman | modified for PHP/MySQL and translated into English |
The personel in the reception at the IT University each day have the need to see who have booked the University's 5 video projectors. The personel also have the need to see which video projectors are available for reservations. In the following, it can be assumed that video projectors are lend out one day at a time. The system should give individual teachers and other employees the possibility of reserving projectors for teaching and research purposes via the Web.
Although administrative features are important for such a reservation system, we shall restrain ourselves not to consider these in what follows.
|
Write the HTML code for presenting the page shown above (the frame is
not part of the page). The solution should appear as HTML-code
containing both <html> and </html>
tags.
The black bar on the page should be implemented using a table of width
100 percent, with background color black (attribute
bgcolor=black), and containing one row with two fields:
For this problem you are asked to write HTML-code representing an HTML-form
for reserving a projector. The HTML-code should take the form
<select ...>
...
</select>
and show a SELECT-box with three of the projectors owned by the IT University. (see the image to the right).
For use in the HTML-form, the name for the SELECT-box,
must be proj_id and the values for each of the projectors
should be the projector number. In this way, the choice with the text
"Projector 1" has the value "1", the choice with the text "Projector
2" has the value "2", and so on.
projector_list,
presented below, with the array $pjs given as argument:
$pjs[0][0] = "4";
$pjs[0][1] = "Projector 4";
$pjs[1][0] = "1";
$pjs[1][1] = "Projector 1";
function projector_list ( $l ) {
$res = "<ul>\n";
for ( $i = 0 ; $i < count($l) ; $i ++ ) {
$id = $l[$i][0];
$text = $l[$i][1];
$res = $res . " <li> <a href='projector_show.php?id=$id'>$text</a></li>\n";
}
$res = $res . "</ul>";
return $res;
}
select_box that returns HTML-code
for a SELECT-box with a list of choices. The function
should take two arguments, a name for the SELECT-box, to
be used for the name-attribute, and an array of
projectors as in Problem 2.1. You can use the function
projector_list from Problem 2.1 as a template for
the solution.
Note: The following call to the function select_box
should return the answer to Problem 1.2:
$pjs[0][0] = "1";
$pjs[0][1] = "Projector 1";
$pjs[1][0] = "2";
$pjs[1][1] = "Projector 2";
$pjs[2][0] = "3";
$pjs[2][1] = "Projector 3";
select_box ("proj_id", $pjs);
vp_return_page that uses the built-in
function echo to return a page to the Web client (the
browser). The function vp_return_page should take two
arguments. The first argument is the title for the page
(title), which should appear in the
<title>...</title> tags in the header for the
resulting page. The second argument is the content of the page
(body). All pages that are returned using the function
vp_return_page should - independent of the arguments -
contain the black bar and the email signature, as in Problem 1.1.
Note: The following call to the function vp_return_page
should return the page presented in Problem 1:
vp_return_page ("About",
"<h2>About</h2>
The Video Projector Reservation System allows registered users to
make reservations of video projectors at the IT University of Copenhagen.");
[0-9]+ is matched by one or more
appearances of the digits 0-9. Write a regular expression that is
matched by either YYYY-MM-DD, where YYYY represents a year
(e.g., 2000), MM represents a month (e.g., 05), and
DD represents a day (e.g., 12).
vp_check_date that can determine if
a string has the form YYYY-MM-DD, as described in
Problem 3.2. If a string, given to the function as argument, has
the correct form, the function should return immediately to the point
from which the function was called. Otherwise, the function should
send an error message to the user's browser, whereafter the script
should stop by calling the built-in PHP-function
exit.
Use the function vp_return_page from Problem 2.3 to
return a message to the user's browser, in case the date is not on the
correct form. To check whether the date takes the correct form, use
the built-in PHP-function ereg.
vp_person - contains information about persons who can reserve projectors
vp_projector - contains information about projectors
vp_reservation - links persons to projectors for individual days
vp_person and vp_projector are
constructed using the following MySQL SQL commands:
create table vp_person (
id integer auto_increment primary key,
name varchar(100) not null,
email varchar(100) not null,
password varchar(100) not null
);
create table vp_projector (
id integer auto_increment primary key,
name varchar(100)
);
It can be assumed that the following SQL insert commands have been executed:
insert into vp_person (id, name, email, password) values (1, 'John Paulin', 'paulin@itu.dk', 'yes!'); insert into vp_person (id, name, email, password) values (2, 'Niels Hallenberg', 'nh@itu.dk', 'no!'); insert into vp_projector (id, name) values (1, 'Projector 1'); insert into vp_projector (id, name) values (2, 'Projector 2'); insert into vp_projector (id, name) values (3, 'Projector 3');
select statement:
select name, email from vp_person;
select statement for returning the number of
projectors in the table vp_projector.
vp_reservation. The table should have three columns:
projector_id - reference to the vp_projector table
person_id - reference to the vp_person table
res_date - the date for the reservation (use MySQL's date datatype)
primary key-constraint to specify that no two
rows contains the same projector_id and
res_date.
vp_reservation:
2001-02-06)
2001-02-08)
2001-02-06. The result of executing the statement
should result in the following data - column names are written in
bold:
| proj_id | proj_name | pers_id | pers_name | |
|---|---|---|---|---|
| 2 | Projector 2 | 1 | John Paulin | paulin@itu.dk |
| proj_id | proj_name |
|---|---|
| 2 | Projector 2 |
| 3 | Projector 3 |
Note: It is necessary to use a left join together with a
"group by ... having ..." construct.
projector.php. It can be
assumed that the library vp.php includes the
mydb.php-library from the lectures and the functions
vp_check_date and vp_return_page.
<?php
include("vp.php");
if ( $_REQUEST['date'] == "" ) {
// if a form variable `date' is not set, we use todays date
$date = date("Y-m-d");
} else {
vp_check_date($date);
}
# Now date is set and on the form YYYY-MM-DD. Let's ask
# MySQL about the dates of the day following $date
# and the day preceeding $date; we do this by adding one
# to $date and subtracting one from $date, respectively.
$year = substr($date, 0, 4);
$month = substr($date, 5, 2);
$day = substr($date, 8, 2);
$nextday = date("Y-m-d", mktime(0,0,0,$month,$day+1,$year));
$prevday = date("Y-m-d", mktime(0,0,0,$month,$day-1,$year));
mydb_connect();
# Extract information about the projector reservations for `date'
$query = " -- YOUR ANSWER TO PROBLEM 4.5 WITH THE DATE '2001-02-06'
REPLACED WITH '$date' -- ";
$rows = mysql_query($query);
$reservations = "";
while ( $row = mysql_fetch_row($rows) ) {
$reservations = $reservations . "<li>$row[1] - $row[3]</a></li>";
}
$reservations = $reservations . "<p>
<li> <a href='res_add_form.php?date=$date'>Add reservation</a></li>";
vp_return_page ("Projector Reservations",
"<h3>Projector Reservations for $date</h3>
[ <a href='projector.php?date=$prevday'>Prev Day</a> |
<a href='projector.php?date=$nextday'>Next Day</a> ]
<ul>$reservations</ul>");
?>
$nextweek and $prevweek, containing the date a
week later than $date and a week earlier than
$date, respectively. The idea is that the two definitions
are inserted in the PHP-code after the definition of the variable
$prevday.
Rewrite the call to the function vp_return_page, at the
end of the script projector.php, so that links appear to
the week after and the week before the given date.
res_add_form.php. It can be assumed that the script is
always requested together with a form variable date
containing the date for which a projector should be reserved.Here is a template for the script:
<?php
include ("vp.php");
// CHECK FORM VARIABLES
## A ## ...
mydb_connect();
$query = " -- YOUR ANSWER TO PROBLEM 4.6 WITH THE DATE '2001-02-08'
REPLACED WITH '$date' -- ";
$rows = mysql_query($query);
// loop through the rows
$i = 0;
while ( $row = mysql_fetch_row($rows) ) {
$pjs[$i][0] = $row[0]; // id
$pjs[$i][1] = $row[1]; // name
$i ++;
}
// use the function selectbox to actually create a
// selection box to put in an HTML form
$sel_box = select_box("proj_id", $pjs);
// CODE FOR RETURNING A FORM TO THE USER
## B ## ...
?>
Notice that the function select_box from Problem 2.2
is used for constructing the SELECT-box.
As the answer to the problem, you should write code for examining the
content of the form variable date (program point ## A ##
in the code above). Moreover, you should write code for returning a
form to the user (program point ## B ## in the code above).
add_res.php. Remember to write code
for checking that the form variables have the correct form. You can
assume that the function vp_check_date is available,
besides the functions vp_check_id and
vp_check_email, which can be used to check if a string
contains an integer or an email address. Remember that a person should be allowed to reserve a projector only if the submitted password is identical to the password appearing in the database for that person.