Supplementary Material

Provided for Reference. This material is no longer taught on this module.

Lecturer: Dr Chris P. Jobling.

An introduction to relational databases, three-tier architecture and database driven web applications.

• A common feature of many Web applications is the use of relational databases for the efficient storage and retrieval of information.
• In this lecture we will discuss how databases can be added to web applications.
• Additional notes on SQL and interfaces to languages other than PHP are available.

Based on Chapter 14 of Robert W. Sebasta, Programming the World-Wide Web, 3rd Edition, Addison Wesley, 2006. and Chapters 11 and 13 of Chris Bates, Web Programming: Building Internet Applications, 3rd Edition, John Wiley, 2006. Chris Bates, Web Programming: Building Internet Applications, 3rd Edition, John Wiley, 2006.

Introducing Databases and Database Access with PHP/MySQL

• Relational Databases
• Introduction to SQL (not examined)
• Architectures for Database Access
• Introducing MySQL
• Database Access with PHP and MySQL

The Examples of Database Access through the Web.

You should be able to answer the following questions:

1. What is the purpose of the primary keys of a table in a relational database?
2. What is the purpose of a cross-reference table?
3. How are string literals delimited in SQL?
4. What does the NOT NULL constraint specify in a column of a CREATE TABLE SQL command?
5. What does an asterisk specify when it appears as the value of a SELECT clause?

You should be able to answer the following questions:

1. What is specified by the WHERE clause of a SELECT command?
2. How are the column names associated with the values in an INSERT command?
3. What is the purpose of an UPDATE command:
4. What exactly is a table join, and how is one specified in SQL?

If you have any difficulty, read the additional notes on SQL.

• A database is a collection of data organized to allow relatively easy access for retrievals, additions, and deletions
• A relational database is a collection of tables of data, each of which has one special column that stores the primary keys of the table
• Rows are sometimes called entities
• Much more detail in module CS-219

• A relational database for used Corvettes that are for sale
• Could just put all data in a single table, whose key would be a simple sequence number
• The table could have information about various equipment the cars could have
• Better to put the equipment in a different table and use a cross-reference table to relate cars to equipment
• Use a separate table for state names, with only references in the main table

“Reading” the diagram we have a corvette belongs to a state and there are many corvettes in a state. Corvettes have one or more pieces of equipment, equipment can belong to more than one corvette. 1 to many relationships indicate a foreign key in the many side. Many to many relationships require a relationship table.

• To define a database and Create, Retrieve, Update, and Delete records in a web application we need some knowledge of SQL
• Some examples follow:
  • Use CREATE TABLE to create a database
  • Creating new records with INSERT
  • Retrieving data with SELECT
  • Udating records with UPDATE
  • Deleting records with DELETE

CREATE TABLE 'states' (
  'State_id' INT(11) NOT NULL AUTO_INCREMENT,
  'State' VARCHAR(25) NOT NULL,
  PRIMARY KEY  ('State_id')
);
CREATE TABLE 'corvettes' (
  'Vette_id' INT(11) NOT NULL AUTO_INCREMENT,
  'Body_style' VARCHAR(25) NOT NULL,
  'Miles' FLOAT NOT NULL,
  'Year' YEAR(4) NOT NULL,
  'State' INT(11) NOT NULL,
  PRIMARY KEY  ('Vette_id')
);

  INSERT INTO Corvettes(Vette_id,
              Body_style, Miles, YEAR, State)
  VALUES (37, 'convertible', 25.5, 1986, 17)

  SELECT Body_style FROM Corvettes
  WHERE YEAR > 1994

• The WHERE clause is (often) the primary key of the row to be updated:

  UPDATE Corvettes
  SET YEAR = 1996
  WHERE Vette_id = 17

  DELETE FROM Corvettes
  WHERE Vette_id = 27

  DROP TABLE IF EXISTS States

• If you want all cars that have CD players, you need information from two tables, Corvettes and Equipment
• SELECT can build a temporary table with info from two tables, from which the desired results can be gotten – this is called a join of the two tables

• A SELECT that does a join operation specifies two tables in its FROM clause and also has a compound WHERE clause
• For our example, we must have three WHERE conditions
  1. Vette_ids from Corvettes and Corvettes_Equipment must match
  2. Equip from Corvettes_Equipment must match the Equip_id from Equipment
  3. The Equip from Equipment must be 'CD'

  SELECT Corvettes.Vette_id,
     Corvettes.Body_style, Corvettes.Miles,
     Corvettes.Year, Corvettes.State,
     Equipment.Equip
  FROM Corvettes, Equipment
  WHERE Corvettes.Vette_id = Corvettes_Equipment.Vette_id
    AND Corvettes_Equipment.Equip = Equipment.Equip_id
    AND Equipment.Equip = 'CD'

At the end of this lecture you should be able to answer these questions:

1. What is the purpose of a third tier in a client/server configuration for Web access to a database?
2. Why are two-tier client/server configurations sometimes inadequate?
3. Explain how SQL database access can be provided by extending a programming language.
4. What is the disadvantage of embedding SQL in a programming language?

• Client-Server Architectures
• Client tasks:
  • Provide a way for users to submit queries
  • Run applications that use the results of queries
  • Display results of queries
• Server tasks:
  • Implement a data manipulation language, which can directly access and update the database

• A two-tier system has clients that are connected directly to the database server
• Problems with a two-tier system:
  • Because the relative power of clients has grown considerably, we could shift processing to the client, but then keeping all clients current with application updates is difficult

• A solution to the problems of two-tier systems is to add a component in the middle to create a three-tier system
• For Web-based database access, the middle tier can run applications (client just gets results)

• SQL commands are embedded in programs written in a host programming language, whose compiler is extended to accept some form of SQL commands
• Advantage:
  • One package has computational support of the programming language, as well as database access with SQL
• Disadvantage:
  • Portability among database systems

At the end of this lecture you should be able to answer these questions:

1. What is MySQL?
2. What does the MySQL constraint auto_increment do?
3. What is the problem with quotes in a SQL command obtained from a form element in an XHTML document?
4. What is the purpose of the PHP mysql_select_db function?
5. How can a PHP program determine the number of rows in a query result?

At the end of this lecture you should be able to answer these questions:

1. What does the PHP function mysql_fetch_array do?
2. Explain the exact form of the value returned by mysql_fetch_array.
3. What is metadata?
4. How is the collection of metadata extracted from a database?
5. How can column labels can be obtained from an object of metadata?

• An API for each specific database system
• Also convenient for Web access to databases, because PHP is run on the Web server

• A free, efficient, widely used SQL implementation
• Available from http://www.mysql.org
• Logging on to MySQL (starting it):

  mysql [-h host] [-u username] [database name] [-p]

• The given database name becomes the “focus” of MySQL

<note>

• host is the name of the MySQL server – default is the user's machine
• username is that of the owner of the database – default is the name used to log into the system

</note>

• If you want to access an existing database, but it was not named in the mysql command, you must choose it for focus:

  use cars;

• Response is: Database changed
• If the focus has not been set and MySQL gets an SQL command, you get:

  ERROR 1046: No Database Selected

• To create a new database:

  CREATE DATABASE cars;

• Response:

  Query ok, 1 row affected (0.05 sec)

* Example:

CREATE TABLE Equipment
  (Equip_id INT UNSIGNED NOT NULL
            AUTO_INCREMENT PRIMARY KEY,
   Equip  CHAR(10));

• To see the tables of a database:

  SHOW TABLES;

• To see the description of a table (columns):

  DESCRIBE Corvettes;

• When values from a DB are to be put in XHTML, you must worry about XHTML special characters
• To get rid of the XHTML special characters, use the PHP function, htmlspecialchars($str)
• Replaces the special characters in the string with their corresponding XHTML entities

• Another problem with PHP and XHTML forms is the string special characters (?, “, \, and NULL), which could come from $_GET and $_POST
• To fix these, magic_quotes_gpc in the PHP.ini file is set to ON by default
  • This escapes these special characters

• Example:

  $query = "SELECT * FROM Names
       WHERE Name = $name";

• If this wasn't done and the value of $name is O'Shanter, it would prematurely terminate the query string
• But with magic_quotes_gpc on, it will be converted to O\'Shanter

• Unfortunately, this can create new problems
• For example, if a SELECT clause has a single-quoted part, like 'California', the single quotes will be implicitly backslashed, making the query illegal for MySQL
• So, magic_quotes_gpc must be turned off, or else the extra backslashes can be removed with stripslashes

• To connect PHP to a database, use mysql_connect, which can have three parameters:
  1. host (default is localhost)
  2. username (default is the username of the PHP script – i.e. effective user id of web server)
  3. password (default is blank, which works if the database does not require a password)

       $db = mysql_connect();

• Usually checked for failure
• Close the connection to the database with mysql_close

• To focus MySQL:

    mysql_select_db("cars");

• Requesting MySQL Operations:
  • Call mysql_query with a string parameter, which is an SQL command:

      $query = "SELECT * from States";
      $result = mysql_query($query);

• 1. Get the number of rows in the result:

    $num_rows = mysql_num_rows($result);

• 2. Get the rows with mysql_fetch_array:

    for ($row_num = 0; $row_num < $num_rows; $row_num++) {
      $row = mysql_fetch_array($result);
      print "<p> Result row number" . ($row_num + 1) . " State_id: ";
      print htmlspecialchars($row["State_id"]);
      print "State: ";
      : 
    }

• We have had it easy – the column titles were known
• If they are not known, we must get them
• The result rows are in PHP arrays, whose elements are actually double sets of elements
• Each pair has the value, but one has a numeric key and one has a string key

• For example, if the result has the field values (1, Alabama), the array has:

((0, 1),
 (State_id, 1),
 (1, Alabama),
 (State, Alabama))

• If the row is indexed with numbers, the element values are returned

• The following displays all field values from $row:

    $values = array_values($row);
    for ($index = 0; $index < $num_fields / 2; $index++)
      print "$values[2 * $index + 1] <br />";

• To display all column names:

    $keys = array_keys($row);
   for ($index = 0; $index < $num_fields; $index++)
     print "$keys[2 * $index + 1] <br />";

• carsdata.html ( carsdata.html @ localhost ) uses a form to collect a query against the cars database
• access_cars.php ( access_cars.php @ localhost ) is a PHP script to access the cars database through MySQL and execute the query passed through $_POST

<note warning> For reasons of security, examples are designed to work from a local installation. I used DocumentRoot/eg-259/examples/lecture19 and created the cars database using phpMyAdmin. Data for cars database etc, is available on Blackboard site. </note>

• The initial form:

<!DOCTYPE html>
<!-- carsdata.html
Uses a form to collect a query against the cars
database.
Calls the PHP script, access_cars.php to perform
the given query and display the results
-->
<html lang="en">
  <head>
    <meta charset="utf-8" />
    <title> Access to the corvettes database </title>
  </head>
  <body>
    <p>
      Try:       <pre>
SELECT Vette_id, Body_style, Year, States.State
FROM Corvettes, States
WHERE Corvettes.State = States.State_id AND States.State = 'California'
</pre>
      Please enter your query:
      <br />
      <form action  = "access_cars.php" method = "post">
        <textarea  rows = "2"  cols = "80" name = "query" >
        </textarea>
        <br />
        <br />
        <input type = "reset"  value = "Reset" />
        <input type = "submit"  value = "Submit request" />
      </form>
    </p>
  </body>
</html>

• The query result page:

<!DOCTYPE html>
<!-- access_cars.php
A PHP script to access the cars database
through MySQL
-->
<html lang="en">
  <head>
    <meta charset="utf-8" />
    <title> Access the cars database with MySQL </title>
  </head>
  <body>
    <?php
 
    // Connect to MySQL
 
    $db = mysql_connect("localhost", "eg-259", "eg-259");
    if (!$db) {
      print "Error - Could not connect to MySQL";
      exit ;
    }
 
    // Select the cars database
 
    $er = mysql_select_db("cars");
    if (!$er) {
      print "Error - Could not select the cars database";
      exit ;
    }
 
    // Get the query and clean it up (delete leading and trailing
    // whitespace and remove backslashes from magic_quotes_gpc)
    $query = $_POST['query'];
    trim($query);
    $query = stripslashes($query);
 
    // Display the query, after fixing html characters
 
    $query_html = htmlspecialchars($query);
    print "<p> <b> The query is: </b> " . $query_html . "</p>";
 
    // Execute the query
 
    $result = mysql_query($query);
    if (!$result) {
      print "Error - the query could not be executed";
      $error = mysql_error();
      print "<p>" . $error . "</p>";
      exit ;
 
    }
 
    // Display the results in a table
    print "<table border='1'><caption> <h2> Query Results </h2> </caption>";
    print "<tr>";
 
    // Get the number of rows in the result, as well as the first row
    //  and the number of fields in the rows
 
    $num_rows = mysql_num_rows($result);
    $row = mysql_fetch_array($result);
    $num_fields = mysql_num_fields($result);
 
    // Produce the column labels
 
    $keys = array_keys($row);
    for ($index = 0; $index < $num_fields; $index++)
      print "<th>" . $keys[2 * $index + 1] . "</th>";
 
    print "</tr>";
 
    // Output the values of the fields in the rows
 
    for ($row_num = 0; $row_num < $num_rows; $row_num++) {
      print "<tr>";
      $values = array_values($row);
      for ($index = 0; $index < $num_fields; $index++) {
        $value = htmlspecialchars($values[2 * $index + 1]);
        print "<td>" . $value . "</td> ";
      }
 
      print "</tr>";
      $row = mysql_fetch_array($result);
    }
    print "</table>";
  ?>
  </body>
</html>

* Result:

• The form display document and the PHP processing document can be combined

• After simply inserting the XHTML from the display document into the PHP document, several modifications are required:

1. Change the value of the action attribute of the form to the name of the combined document file
2. Create a hidden input element that sets its value when the document is first displayed. This provides a way for the document to determine which it is doing, displaying the form or processing the form data:

    <input type = "hidden"  name = "stage" value = "1" />

1. The PHP code to test this has the form:

    $stage = $_POST["stage"];
    if (!IsSet($stage))) { ... }

• the then clause includes the form processing;
• the else clause includes the form display

• Here is the result: access_cars2.php ( access_cars2.php @ localhost )

• The code:

<!DOCTYPE html>
<!-- access_cars2.php
A PHP script to both get a query from the user and
access the cars database through MySQL to get and
display the result of the query.
-->
<html lang="en">
  <head>
    <meta charset="utf-8" />
    <title> Access the cars database with MySQL </title>
  </head>
  <body>
    <?php
$stage = $_POST["stage"];
if (!IsSet($stage)) {
    ?>
    <p>
      Try:       <pre>
SELECT Vette_id, Body_style, Year, States.State
FROM Corvettes, States
WHERE Corvettes.State = States.State_id AND States.State = 'California'
</pre>
      Please enter your query:
      <br />
      <form  method = "POST"  action = "access_cars2.php" >
        <textarea  rows = "2"  cols = "80"  name = "query"> 
        </textarea>
        <br />
        <br />
        <input type = "hidden"  name = "stage"  value = "1" />
        <input type = "submit"  value = "Submit request" />
      </form>
    </p>
    <?php
    } else {  // $stage was set, so process the query
 
    // Connect to MySQL
 
     $db = mysql_connect("localhost", "eg-259", "eg-259");
     if (!$db) {
      print "Error - Could not connect to MySQL";
      exit;
     } 
 
    // Select the cars database
 
     $er = mysql_select_db("cars");
     if (!$er) {
      print "Error - Could not select the cars database";
      exit;
     }
 
    // Get the query and clean it up (delete leading and trailing
    // whitespace and remove backslashes from magic_quotes_gpc)
     $query = $_POST['query'];
     trim($query);
     $query = stripslashes($query);
 
    // Fix the query for browser display and display it
 
     $query_html = htmlspecialchars($query);
     print "<p> <b> The query is: </b> " . $query_html . "</p>";
 
    // Execute the query
 
     $result = mysql_query($query);
     if (!$result) {
      print "Error - the query could not be executed";
      $error = mysql_error();
      print "<p>" . $error . "</p>";
      exit;
     }
 
    // Display the results in a table
 
     print "<table border='1'><caption> <h2> Query Results </h2> </caption>";
     print "<tr>";
 
    // Get the number of rows in the result, as well as the first row
    //  and the number of fields in the rows
 
     $num_rows = mysql_num_rows($result);
     $row = mysql_fetch_array($result);
     $num_fields = mysql_num_fields($result);
     // Produce the column labels
 
     $keys = array_keys($row);
     for ($index = 0; $index < $num_fields; $index++) {
       print "<th>" . $keys[2 * $index + 1] . "</th>";
     }
 
     print "</tr>";
 
    // Output the values of the fields in the rows
 
     for ($row_num = 0; $row_num < $num_rows; $row_num++) {
      print "<tr>";
      $values = array_values($row);
      for ($index = 0; $index < $num_fields; $index++){
       $value = htmlspecialchars($values[2 * $index + 1]);
       print "<td>" . $value . "</td> ";
      }
 
      print "</tr>";
      $row = mysql_fetch_array($result);
     }
     print "</table>";
    }
  ?>
  </body>
</html>

Introducing Databases and Database Access with PHP/MySQL

• Relational Databases
• Introduction to SQL (not examined)
• Architectures for Database Access
• Introducing MySQL
• Database Access with PHP and MySQL

At the end of this lecture you should be able to answer these questions:

1. What is the purpose of a third tier in a client/server configuration for Web access to a database?
2. Why are two-tier client/server configurations sometimes inadequate?
3. At the end of this lecture you should be able to answer these questions:
4. Explain how SQL database access can be provided by extending a programming language.
5. What is the disadvantage of embedding SQL in a programming language?

At the end of this lecture you should be able to answer these questions:

1. What is MySQL?
2. What does the MySQL constraint auto_increment do?
3. What is the problem with quotes in a SQL command obtained from a form element in an XHTML document?
4. What is the purpose of the PHP mysql_select_db function?
5. How can a PHP program determine the number of rows in a query result?

At the end of this lecture you should be able to answer these questions:

1. What does the PHP function mysql_fetch_array do?
2. Explain the exact form of the value returned by mysql_fetch_array.
3. What is metadata?
4. How is the collection of metadata extracted from a database?
5. How can column labels can be obtained from an object of metadata?

• Modify and test the program access_cars.php to handle UPDATE and INSERT SQL commands.

Web Application Frameworks

• Three-tiers are not enough!
• The Problem of Web Application Development
• Web Application Frameworks
• Design Patterns
• Case-Study: Ruby-on-Rails

Previous Lecture | home | Next Lecture