Solutions to Test Exam in Web Publishing with Databases, Fall 2000

for Web Publishing with Databases, Fall 2000 (modified for PHP and translated into English)

Solution to Problem 1 (15 percent) - HTML

Problem 1.1

  <html>
    <title>About</title><body bgcolor=white>
    <table width=100% bgcolor=black border=0 cellpadding=5 cellspacing=0>
      <tr><td><font color=white size=+2><b>Video Projector Reservation System</b></font></td>
          <td align=right><img src="http://www.itu.dk/logo_black.jpg"></td>
      </tr>
    </table>
    
    <h2>About</h2>
    The Video Projector Reservation System allows registered users to 
    make reservations of video projectors at the IT University of Copenhagen.
    <hr>
    <address>
    <a href="mailto:mael@it.edu">mael@it.edu</a>
    </address>
    </body>
  </html>

Problem 1.2

  <select name=proj_id>
    <option value=1>Projektor 1
    <option value=2>Projektor 2
    <option value=3>Projektor 3
  </select>

Solution to Problem 2 (20 percent) - PHP

Problem 2.1

  <ul>
   <li> <a href=projector_show.php?id=4>Projector 4</a>
   <li> <a href=projector_show.php?id=1>Projector 1</a>
  </ul>

Problem 2.2

  function select_box ($n, $l) {
	$res = "<select name=$n>\n";
	for ( $i = 0 ; $i < count($l) ; $i ++ ) {
	   $id = $l[$i][0];
	   $text = $l[$i][1];
	   $res = $res . " <option value=$id>$text</option>\n";
	}
	$res = $res . "</select>";
	return $res;
  }

Problem 2.3

  function vp_return_page ( $title, $body ) {
    echo "<html>
      <title>$title</title><body bgcolor=white>
      <table width=100% bgcolor=black border=0 cellpadding=5 cellspacing=0>
	<tr><td><font color=white size=+2><b>Video Projector Reservation System</b></font></td>
	    <td align=right><img src=\"http://www.itu.dk/logo_black.jpg\"></td>
	</tr>
      </table>
      $body
      <hr>
      <address>
      <a href=\"mailto:mael@it.edu\">mael@it.edu</a>
      </address>
      </body>
      </html>
      ";
    exit;
  }

Solution to Problem 3 (15 percent) - Regular expressions

Problem 3.1

   0|([1-9][0-9]*)

Problem 3.2

   [0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]

Problem 3.3

  function vp_check_date ( $d ) {
    if ( !ereg ('^[0-9][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]$', $d ) ) {
      vp_return_page ("Error: Wrong date format", "Go back and
                      enter the date in the format YYYY-MM-DD.");
      exit;
    }
    return;
  }

Solution to Problem 4 (20 percent) - SQL

Problem 4.1

   NAME              EMAIL
   -------------------------------
   John Paulin       paulin@it.edu
   Niels Hallenberg  nh@it.edu

Problem 4.2

  select count(*) from vp_projector;

Problem 4.3

  create table vp_reservation (
    projector_id integer not null,
    person_id integer not null,
    res_date date not null,
    primary key(projector_id, res_date)
  );

Problem 4.4

  insert into vp_reservation (projector_id, person_id, res_date)
  values (2, 1, '2001-02-06');

  insert into vp_reservation (projector_id, person_id, res_date)
  values (1, 2, '2001-02-08');

Problem 4.5

  select vp_projector.id as proj_id, 
         vp_projector.name as proj_name, 
         vp_person.id as pers_id, 
         vp_person.name as pers_name, 
         email
  from vp_person, vp_projector, vp_reservation
  where vp_person.id = vp_reservation.person_id
    and vp_projector.id = vp_reservation.projector_id
    and res_date = '2001-02-06';

Problem 4.6

  select id as proj_id, name as proj_name
  from vp_projector left join vp_reservation
    on res_date = '2001-02-08'
   and id = projector_id
  group by id, name
  having count(res_date) = 0;

Solution to Problem 5 (30 percent) - Web-service

Problem 5.1

PHP-code for insertion after the definition of the variable $prevday:

  $nextweek = date("Y-m-d", mktime(0,0,0,$month,$day+7,$year));
  $prevweek = date("Y-m-d", mktime(0,0,0,$month,$day-7,$year));

Modifications to the call to vp_return_page:

  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=$prevweek>Prev Week</a> | 
        <a href=projector.php?date=$nextweek>Next Week</a> | 
        <a href=projector.php?date=$nextday>Next Day</a> ]
      <ul>$reservations</ul>");

Problem 5.2

Program point ## A ##:

  vp_check_date ($date);

Program point ## B ##:

  vp_return_page ("Add Projector Reservation", "
    <h3>Add Projector Reservation for $date</h3>
    <form action=res_add.php>
    <input type=hidden name=date value=$date>    
    <b>Email:</b><br>
    <input type=text name=email size=40><p>
    <b>Password:</b><br>
    <input type=password name=password size=40><p>
    $sel_box
    <input type=submit value=\"Add Reservation\">
    </form>
    ");

Problem 5.3

<?
  include ("vp.php");
  vp_check_date ($date);
  vp_check_email ($email);
  vp_check_id ($proj_id);
  vp_check_passwd ($password);

  mydb_connect();
  $rows = mysql_query ("select id 
  	                  from vp_person 
	                 where email = '$email'
	                   and password = '$password'");

  if ( $row = mysql_fetch_row ($rows) ) {
    $res = mysql_query ("insert into vp_reservation 
                            (person_id, projector_id, res_date)
	                 values ('$id', '$proj_id', '$date')");
    if (  $res == 0 ) { // ERROR
      vp_return_page ("Could not add reservation", 
        "<h3>Could not add reservation</h3>
         Perhaps the projector is already booked");
    } else {
      header ("Location: projector.php?date=$date");
    }
  } else {
    vp_return_page ("Could not add reservation", 
      "<h3>Could not add reservation</h3>
       Wrong password or email address");
  }
?>

mael@itu.dk