Complete the following exercises to practice using PHP and MySQL (solutions):

  1. Create a web page (count.php) with an HTML form with two text boxes labeled N and K and submits this form to the same page (i.e., count.php). When submitted, the page should display the values of N and K and count up from 1 to N printing out the numbers that are divisible by K.

    <?php
// get the value of a request parameter
function get_value($key) {
  $value = NULL;
  if (array_key_exists($key, $_REQUEST))
    $value = $_REQUEST[$key];

  return $value;
}
 ?>
<!DOCTYPE html>
<html>
  <body>
    <form action="<?php echo $_SERVER["PHP_SELF"]; ?>" method="get">
      <label for="n">N</label>
      <input type="text" name="n"
             value="<?php echo get_value("n"); ?>" />
      <label for="k">K</label>
      <input type="text" name="k"
             value="<?php echo get_value("k"); ?>" />
      <input type="submit" value="Submit" />
    </form>
    <?php
      // check if there's a GET request with parameters
      if ($_SERVER["REQUEST_METHOD"] === "GET" && ! empty($_GET)) {
     ?>
    <hr />
    <table>
      <tr>
        <th>Name</th>
        <th>Value</th>
      </tr>
    <?php
        // display each request variable
        foreach ($_GET as $key => $value) {
     ?>
      <tr>
        <td><?php echo $key; ?></td>
        <td><?php echo $value; ?></td>
      </tr>
    <?php
        }
     ?>
    </table>

    <div>
      Counting from <strong>1</strong> to <strong><?php echo $_GET['n']; ?></strong> and displaying integers that are divisble by <strong><?php echo $_GET['k']; ?></strong>:<br />
      <?php
      for ($i = 1; $i <= $_GET['n']; $i++)
        if ($i % $_GET['k'] == 0)
          echo "$i<br />";
       ?>
    </div>
    <?php
      }
     ?>
  </body>
</html>

  2. Create a PHP script (setup.php) that creates the following tables and inserts the data shown for each table:

    Author
    id surname given_name
    52258 Gillenson Mark
    15396 Silberschatz Avi
    16617 Korth Henry F.
    184154 Sudarshan S.
    Writes
    author_id isbn
    15396 9780073523323
    16617 9780073523323
    184154 9780073523323
    52258 9780470624708
    Book
    isbn title copyright publisher
    9780073523323 Database System Concepts 2011 McGraw-Hill
    9780470624708 Fundamentals of Database Management Systems 2012 John Wiley & Sons, Inc.
    Publisher
    name location
    John Wiley & Sons, Inc. Hoboken, NJ
    McGraw-Hill New York, NY
    Pearson Education London, UK

    Execute this script, either from the terminal or by requesting it in your web browser, to insert the data. Use the following command to execute the script from the terminal:

    php --file setup.php

    Note that you must be in the same directory as the script when executing this command. Alternatively, the script will be executed if requested by opening it in the web browser – i.e., if the script is in your public_html directory, then navigating to http://localhost/~student/setup.php will execute it (assuming that your account user name is “student” as suggested in the software installation instructions).

    <?php
/* Embedding account credentials isn't ideal...preferable to
 * store in a separate file that is included by PHP (and not
 * accessible to others)
 */
$username = "student";
$password = "CompSci364";
$database = "student";

$connection = new mysqli("localhost", $username, $password,
                         $database);

$tables = <<<SQL
DROP TABLE IF EXISTS Writes;
DROP TABLE IF EXISTS Author;
DROP TABLE IF EXISTS Book;
DROP TABLE IF EXISTS Publisher;

CREATE TABLE Publisher (
  name CHARACTER VARYING(64) NOT NULL,
  location CHARACTER VARYING(64) NOT NULL,

  PRIMARY KEY (name)
);

CREATE TABLE Book (
  isbn CHARACTER(13) NOT NULL,
  title CHARACTER VARYING(128) NOT NULL,
  copyright INTEGER,
  publisher CHARACTER VARYING(64),

  PRIMARY KEY (isbn),
  FOREIGN KEY (publisher) REFERENCES Publisher (name)
      ON UPDATE CASCADE ON DELETE RESTRICT
);

CREATE TABLE Author (
  id INTEGER AUTO_INCREMENT NOT NULL,  -- AUTO_INCREMENT generates id
  surname CHARACTER VARYING(32) NOT NULL,
  given_name CHARACTER VARYING(32),

  PRIMARY KEY (id)
);

CREATE TABLE Writes (
  author_id INTEGER,
  isbn CHARACTER(13),

  PRIMARY KEY (author_id, isbn),
  FOREIGN KEY (author_id) REFERENCES Author (id)
      ON UPDATE CASCADE ON DELETE RESTRICT,
  FOREIGN KEY (isbn) REFERENCES Book (isbn)
      ON UPDATE CASCADE ON DELETE RESTRICT
);
SQL;

// normally only a single query is executed, but batch table creation
$connection->multi_query($tables)
    or die("Error: ".$connection->error);

// ensure that all the queries executed correctly
while ($connection->more_results())
  if (! $connection->next_result())
    echo "Error: ".$connection->error."\n";

$data = array(
  "INSERT INTO Publisher (name, location) VALUES ".
      "('John Wiley & Sons, Inc.', 'Hoboken, NJ'), ".
      "('McGraw-Hill', 'New York, NY'), ".
      "('Pearson Education', 'London, UK');",
  "INSERT INTO Book (isbn, title, copyright, publisher) VALUES ".
      "('9780073523323', 'Database System Concepts', 2011, 'McGraw-Hill'), ".
      "('9780470624708', 'Fundamentals of Database Management Systems', 2012, 'John Wiley & Sons, Inc.');",
  "INSERT INTO Author (id, surname, given_name) VALUES ".
      "(52258, 'Gillenson', 'Mark'), ".
      "(15396, 'Silberschatz', 'Avi'), ".
      "(16617, 'Korth', 'Henry F.'), ".
      "(184154, 'Sudarshan', 'S.');",
  "INSERT INTO Writes (author_id, isbn) VALUES ".
      "(15396, '9780073523323'), ".
      "(16617, '9780073523323'), ".
      "(184154, '9780073523323'), ".
      "(52258, '9780470624708');",
);

foreach ($data as $query) {
  if (! $connection->query($query))
    echo "Error: ".$connection->error."\n";
}

$connection->close();

// closing PHP tag intentionally omitted

  3. Create a web page (citations.php) that displays the citation of each book in the database. For simplicity, assume that citations adhere to the following format:

    Authors, Title, Publisher, Location, Copyright

    where the author list includes the first and last name of each author and each author is separated by a comma. For example, the prior data results in the following citations:

    • Mark Gillenson, Fundamentals of Database Management Systems, John Wiley & Sons, Inc. Hoboken, NJ, 2012
    • Avi Silberschatz, Henry F. Korth, S. Sudarshan, Database Systems Concepts, McGraw-Hill, New York, NY, 2011

    Start by displaying the title, publisher, location, and copyright – i.e., ignore the authors. Then, use a second query to retrieve the author(s) for each book.

    <?php
/* Embedding account credentials isn't ideal...preferable to
 * store in a separate file that is included by PHP (and not
 * accessible to others)
 */
$username = "student";
$password = "CompSci364";
$database = "student";

$connection = new mysqli("localhost", $username, $password,
                         $database);
 ?>
<!DOCTYPE html>
<html>
  <body>
    Citations:
    <ul>
    <?php
      $query = "SELECT * ".
               "FROM Book INNER JOIN Publisher ".
               "        ON Book.publisher = Publisher.name;";
      $book_results = $connection->query($query);
      while ($book = $book_results->fetch_assoc()) {
        $isbn = $book['isbn'];
        $title = $book['title'];
        $publisher = $book['publisher'];
        $location = $book['location'];
        $copyright = $book['copyright'];

        $authors = NULL;

        $query = "SELECT * ".
                 "FROM Author INNER JOIN Writes ".
                 "        ON Author.id = Writes.author_id ".
                 "WHERE Writes.isbn = '$isbn';";
        $author_results = $connection->query($query);
        while ($author = $author_results->fetch_assoc()) {
          if ($authors !== NULL) // not the first author
            $authors .= ", ";

          $authors .= $author['given_name']." ".$author['surname'];
        }
     ?>
       <li><?php echo "$authors, <em>$title</em>, ".
                      "$publisher, $location, $copyright"; ?></li>
    <?php
      }
     ?>
    </ul>
  </body>
</html>