Digitaalisen median tekniikat S00, Harjoitus 4
Tehtävä 2: Query by number of rooms

Code of the PHP script

#!/usr/local/bin/php3
<HTML>
<HEAD>
<TITLE>Katonalle Appartment Search</TITLE>
</HEAD>
<BODY BGCOLOR="#007FFF" TEXT="#000000">
<H1 ALIGN="center"><FONT FACE="arial, helvetica, sans-serif"
COLOR="#FFFFFF"><EM>Katonalle</EM></FONT></H1>

<?php

$faces = "arial, helvetica, sans-serif";

function echo_result($rooms, $result) {

  global $faces;
  $fattr = "FACE=\"$faces\"";
  $fattrs = "FACE=\"$faces\" COLOR=\"#FFFFFF\"";

  echo "<H2 ALIGN=\"center\"><FONT $fattrs>" .
       "<EM>Appartments with " . $rooms . " room" .
       ($rooms < 2 ? "" : "s") . "</EM></FONT></H2>\n\n";

  echo "<TABLE ALIGN=\"center\" BORDER=\"0\" CELLSPACING=\"0\" " .
       "CELLPADDING=\"4\">\n" .
       "<TR VALIGN=\"baseline\" BGCOLOR=\"#002FDF\">\n" .
       "<TH ALIGN=\"left\"><FONT $fattrs>Type</FONT></TH>\n" .
       "<TH ALIGN=\"right\"><FONT $fattrs>Floor&nbsp;area</FONT></TH>\n" .
       "<TH ALIGN=\"left\"><FONT $fattrs>District</FONT></TH>\n" .
       "<TH ALIGN=\"left\"><FONT $fattrs>Address</FONT></TH>\n" .
       "<TH ALIGN=\"right\"><FONT $fattrs>Rent&nbsp;asked</FONT></TH>\n" .
       "</TR>\n";

  while (odbc_fetch_row($result)) {

    $no_of_apparts++;
    $no_of_apparts % 2 ? $bg_color = "#FFFFFF" : $bg_color = "#AFCFFF";

    echo "<TR VALIGN=\"baseline\" BGCOLOR=\"$bg_color\">\n" .
         "<TD ALIGN=\"left\"><FONT $fattr>" . odbc_result($result, 1) . "</FONT></TD>\n" .
         "<TD ALIGN=\"right\"><FONT $fattr>" . sprintf("%.1f", odbc_result($result, 2)) .
         "&nbsp;m<SUP>2</SUP></FONT></TD>\n" .
         "<TD ALIGN=\"left\"><FONT $fattr>" . odbc_result($result, 3) . "</FONT></TD>\n" .
         "<TD ALIGN=\"left\"><FONT $fattr>" . odbc_result($result, 4) . "</FONT></TD>\n" .
         "<TD ALIGN=\"right\"><FONT $fattr>" . odbc_result($result, 5) . "</FONT></TD>\n" .
         "</TR>\n";
  }

  echo "<TR VALIGN=\"baseline\" BGCOLOR=\"#002FDF\">\n" .
       "<TD COLSPAN=\"6\" ALIGN=\"center\">" .
       "<FONT $fattrs>Total number: $no_of_apparts</FONT>" .
       "</TD>\n" .
       "</TR>\n" .
       "</TABLE>\n\n";
}

function execute_query($rooms) {

  global $faces;
  $no_of_apparts = 0;

  error_reporting(0); // comment this to get warnings and error messages

  $db = odbc_connect("TCP/IP db.cs.Helsinki.FI 1414", "uusmed", "lehma");

  if ($db) {
    $result = odbc_exec($db,
      "SELECT ptype, pm2, parea, paddress, prent_asked
       FROM PROPERTY
       WHERE prooms = $rooms
       ORDER BY ptype, pm2, parea, paddress, prent_asked");

    if ($result) {
      echo_result($rooms, $result);
    } else {
      echo "<P ALIGN=\"center\"><B><FONT FACE=\"$faces\">Execution of " .
           "database query failed. Try again later.</FONT></B></P>\n\n";
    }
  } else {
    echo "<P ALIGN=\"center\"><B><FONT FACE=\"$faces\">Couldn't connect " .
         "to database. Try again later.</FONT></B></P>\n\n";
  }
}

function echo_date() {

  global $faces;
  $loc_date = date("d M Y");
 
  echo "<DIV ALIGN=\"right\">\n" .
       "<ADDRESS><FONT FACE=\"$faces\" COLOR=\"#FFFFFF\">" .
       "<BR>Katonalle<BR>$loc_date</FONT></ADDRESS>\n" .
       "</DIV>\n";
}

function echo_form($act_url) {

  global $faces;
 
  echo "<H2 ALIGN=\"center\"><FONT FACE=\"arial, helvetica, sans-serif\" ".
       "COLOR=\"#FFFFFF\"><EM>Appartment search</EM></FONT></H2>\n\n";

  echo "<DIV ALIGN=\"center\">\n" .
       "<FORM METHOD=\"post\" ACTION=\"$act_url\">\n" .
       "<FONT FACE=\"$faces\" COLOR=\"#FFFFFF\">" .
       "Give number of rooms:\n" .
       "<INPUT TYPE=\"text\" NAME=\"room_count\" SIZE=\"3\">\n" .
       "<INPUT TYPE=\"submit\" NAME=\"submit\" VALUE=\"Search\"></FONT>\n" .
       "</FORM>\n" .
       "</DIV>\n\n";
}

// THIS IS THE MAIN PART

if ($room_count != "") {

  $room_count = intval($room_count);

  if ($room_count < 1 || $room_count > 100) {
    echo "<P ALIGN=\"center\"><B><FONT FACE=\"$faces\">The number of " .
         "rooms given was incorrect.</FONT></B></P>\n\n";
  } else {
    execute_query($room_count);
  }
}

echo_form($PHP_SELF);
echo_date();

?>

</BODY>
</HTML>

Resulting page