Differences

This shows you the differences between two versions of the page.

--- eg-259:lecture19 [2008/11/13 18:58] – external edit 127.0.0.1
+++ eg-259:lecture19 [2013/03/08 18:04] (current) – [Database Access through the Web] eechris
@@ Line 1: / Line 1: @@
+~~SLIDESHOW~~
+====== Database Access through the Web======
+**Supplementary Material**
+Provided for Reference. This material is no longer taught on this module.
+**Lecturer**: [[C.P.Jobling@Swansea.ac.uk|Dr Chris P. Jobling]].
+An introduction to relational databases, three-tier architecture and database driven web applications.
+===== Database Access through the Web =====
+  * 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 [[eg-259:extras:dbandsql|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.
+===== Contents of this Lecture =====
+//Introducing Databases and Database Access with PHP/MySQL//
+  * [[eg-259:lecture19#relational_databases|Relational Databases]]
+  * [[eg-259:extras:dbandsql|Introduction to SQL]] (not examined)
+  * [[eg-259:lecture19#architectures_for_database_access|Architectures for Database Access]]
+  * [[eg-259:lecture19#the_mysql_database_system|Introducing MySQL]]
+  * [[eg-259:lecture19#database_access_with_php_mysql|Database Access with PHP and MySQL]]
+The [[eg-259:examples:php1#examples_from_lecture_19|Examples of Database Access through the Web]].
+===== Assumed Knowledge =====
+//You should be able to answer the following questions://
+  - What is the purpose of the primary keys of a table in a relational database?
+  - What is the purpose of a cross-reference table?
+  - How are string literals delimited in SQL?
+  - What does the ''NOT NULL'' constraint specify in a column of a ''CREATE TABLE SQL'' command?
+  - What does an asterisk specify when it appears as the value of a ''SELECT'' clause?
+===== Assumed Knowledge (continued) =====
+//You should be able to answer the following questions://
+  - What is specified by the ''WHERE'' clause of a ''SELECT'' command?
+  - How are the column names associated with the values in an ''INSERT'' command?
+  - What is the purpose of an ''UPDATE'' command:
+  - What exactly is a //table join//, and how is one specified in SQL?
+If you have any difficulty, read the [[eg-259:extras:dbandsql|additional notes on SQL]].
+===== Relational Databases =====
+  * 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//
+===== An Example Database =====
+  * 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
+===== Logical Data Model =====
+{{eg-259:l19-logical-structure.png?800|Logical structure of the Corvettes database}}
+----
+"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//.
+===== The Corvettes table =====
+^ Vette_id ^ Body_style ^ Miles ^ Year ^ State ^
+| 1 | coupe | 18.0 | 1997 | 4 |
+| 2 | hatchback | 58.0 | 1996 | 7 |
+| 3 | convertible | 13.5 | 2001 | 1 |
+| 4 | hatchback | 19.0 | 1995 | 2 |
+| 5 | hatchback | 25.0 | 1991 | 5 |
+| 6 | hardtop | 15.0 | 2000 | 2 |
+| 7 | coupe | 55.0 | 1979 | 10 |
+| 8 | convertible | 17.0 | 1999 | 5 |
+| 9 | hardtop | 17.0 | 2000 | 5 |
+| 10 | hatchback | 50.0 | 1995 | 7 |
+===== The States table =====
+^ State_id ^ State ^
+| 1 | Alabama |
+| 2 | Alaska |
+| 3 | Arizona |
+| 4 | Arkansas |
+| 5 | California |
+| 6 | Colorado |
+| 7 | Connecticut |
+| 8 | Delaware |
+| 9 | Florida |
+| 10 | Georgia |
+| : | : |
+| 51 | Wyoming |
+===== The Equipment table =====
+^  Equp_id ^ Equip ^
+| 1 | Automatic |
+| 2 | 4-speed |
+| 3 | 5-speed |
+| 4 | 6-speed |
+| 5 | CD |
+| 6 | Leather |
+===== The Corvettes_Equipment table =====
+^ Vette_id ^ Equip ^
+| 1 | 1 |
+| 1 | 5 |
+| 1 | 6 |
+| 2 | 1 |
+| 2 | 5 |
+| 2 | 6 |
+| 3 | 1 |
+| 3 | 6 |
+| : | : |
+| 10 | 5 |
+===== Using SQL =====
+  * To define a database and **C**reate, **R**etrieve, **U**pdate, and **D**elete records in a web application we need some knowledge of SQL
+  * Some examples follow:
+    * Use ''CREATE TABLE'' to create a database
+    * **C**reating new records with ''INSERT''
+    * **R**etrieving data with ''SELECT''
+    * **U**dating records with ''UPDATE''
+    * **D**eleting records with ''DELETE''
+===== Use CREATE TABLE to create a database =====
+<code sql>
+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')
+);
+</code>
+===== Creating new records with INSERT =====
+<code sql>
+  INSERT INTO Corvettes(Vette_id,
+              Body_style, Miles, Year, State)
+  VALUES (37, 'convertible', 25.5, 1986, 17)
+</code>
+===== Retrieving data with SELECT =====
+<code sql>
+  SELECT Body_style FROM Corvettes
+  WHERE Year > 1994
+</code>
+===== Udating records with UPDATE =====
+  * The ''WHERE'' clause is (often) the primary key of the row to be updated:
+<code sql>
+  UPDATE Corvettes
+  SET Year = 1996
+  WHERE Vette_id = 17
+</code>
+===== Deleting records with DELETE=====
+<code sql>
+  DELETE FROM Corvettes
+  WHERE Vette_id = 27
+</code>
+===== Deleting tables with DROP =====
+<code>
+  DROP TABLE IF EXISTS States
+</code>
+===== Intro to SQL: Joins =====
+  * 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
+===== Joins =====
+  * 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
+    - ''Vette_ids'' from ''Corvettes'' and ''Corvettes_Equipment'' must match
+    - ''Equip'' from ''Corvettes_Equipment'' must match the ''Equip_id'' from ''Equipment''
+    - The ''Equip'' from ''Equipment'' must be '''CD'''
+===== The join statement! =====
+<code sql>
+  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'
+</code>
+===== The query results! =====
+^ ''VETTE_ID'' ^ ''BODY_STYLE'' ^ ''MILES'' ^ ''YEAR'' ^ ''STATE'' ^  ''EQUIP'' ^
+| 1  | coupe       | 18.0 | 1997 | 4    | CD |
+| 2  | hatchback   | 58.0 | 1996 | 7    | CD |
+| 8  | convertible | 17.0 | 1999 | 5    | CD |
+| 9  | hardtop     | 17.0 | 2000 | 5    | CD |
+| 10 | hatchback   | 50.0 | 1995 | 7    | CD |
+===== Learning Outcomes – Database Access =====
+//At the end of this lecture you should be able to answer these questions//:
+  - What is the purpose of a third tier in a client/server configuration for Web access to a database?
+  - Why are two-tier client/server configurations sometimes inadequate?
+  - Explain how SQL database access can be provided by extending a programming language.
+  - What is the disadvantage of embedding SQL in a programming language?
+===== Architectures for Database Access =====
+  * 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
+===== Two-tier system =====
+  * 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
+===== Three-Tier system =====
+  * 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)
+===== Three-Tier architecture =====
+{{eg-259:l19-3-tier.png?800|Three-tier architecture of a Web site supported by databases}}
+===== Database Access with Embedded SQL =====
+  * 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
+===== Common Database Access Architecture =====
+{{eg-259:dba-architecture.png?800|Common Database Access Architecture}}
+===== Learning Outcomes – MySQL and PHP =====
+//At the end of this lecture you should be able to answer these questions//:
+  - What is MySQL?
+  - What does the MySQL constraint ''auto_increment'' do?
+  - What is the problem with quotes in a SQL command obtained from a form element in an XHTML document?
+  - What is the purpose of the PHP ''mysql_select_db'' function?
+  - How can a PHP program determine the number of rows in a query result?
+===== Learning Outcomes (continued) =====
+//At the end of this lecture you should be able to answer these questions//:
+  - What does the PHP function ''mysql_fetch_array'' do?
+  - Explain the exact form of the value returned by ''mysql_fetch_array''.
+  - What is metadata?
+  - How is the collection of metadata extracted from a database?
+  - How can column labels can be obtained from an object of metadata?
+===== PHP & Database Access =====
+  * An API for each specific database system
+  * Also convenient for Web access to databases, because PHP is run on the Web server
+===== The MySQL Database System =====
+  * 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>
+===== Accessing Databases =====
+  * 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
+===== Creating Databases and Tables =====
+  * To create a new database:
+    CREATE DATABASE cars;
+  * Response:
+    Query ok, 1 row affected (0.05 sec)
+----
+  * Example:
+<code sql>
+CREATE TABLE Equipment
+  (Equip_id INT UNSIGNED NOT NULL
+            AUTO_INCREMENT PRIMARY KEY,
+   Equip  CHAR(10));
+</code>
+===== Accessing Metadata =====
+  * To see the tables of a database:
+    SHOW TABLES;
+  * To see the description of a table (columns):
+    DESCRIBE Corvettes;
+===== Database Access with PHP/MySQL =====
+  * 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
+===== Database Access with PHP/MySQL (continued) =====
+  * 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
+===== Database Access with PHP/MySQL (continued) =====
+  * Example:
+<code php>
+  $query = "SELECT * FROM Names
+       WHERE Name = $name";
+</code>
+  * 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''
+===== Database Access with PHP/MySQL (continued) =====
+  * 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''
+===== PHP: Connecting to a Database =====
+  * To connect PHP to a database, use ''mysql_connect'', which can have three parameters:
+    - ''host'' (default is localhost)
+    - ''username'' (default is the username of the PHP script -- i.e. effective user id of web server)
+    - ''password'' (default is blank, which works if the database does not require a password)
+<code php>
+       $db = mysql_connect();
+</code>
+    * Usually checked for failure
+  * Close the connection to the database with ''mysql_close''
+===== PHP: Selecting database and executing queries =====
+  * //To focus MySQL//:
+<code php>
+    mysql_select_db("cars");
+</code>
+  * //Requesting MySQL Operations//:
+    * Call ''mysql_query'' with a string parameter, which is an SQL command:
+<code php>
+      $query = "SELECT * from States";
+      $result = mysql_query($query);
+</code>
+===== PHP: Dealing with the result =====
+  * 1. Get the number of rows in the result:
+<code php>
+    $num_rows = mysql_num_rows($result);
+</code>
+  * 2. Get the rows with ''mysql_fetch_array'':
+<code php>
+    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: ";
+      :
+    }
+</code>
+===== PHP: Using Metadata =====
+  * 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
+===== PHP: Example of Metadata =====
+  * 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
+===== PHP: Accessing field values =====
+  * The following displays all field values from ''$row'':
+<code php>
+    $values = array_values($row);
+    for ($index = 0; $index < $num_fields / 2; $index++)
+      print "$values[2 * $index + 1] <br />";
+</code>
+===== PHP: Displaying all column names =====
+  * To display all column names:
+<code php>
+    $keys = array_keys($row);
+   for ($index = 0; $index < $num_fields; $index++)
+     print "$keys[2 * $index + 1] <br />";
+</code>
+===== PHP/MySQL: Example =====
+  * [[http://eespectre.swan.ac.uk/~eechris/eg-259/examples/lecture19/carsdata.html|carsdata.html]]  ( [[http://localhost/eg-259/examples/lecture19/carsdata.html|carsdata.html  @ localhost]] ) uses a form to collect a query against the cars database
+  * [[http://eespectre.swan.ac.uk/~eechris/eg-259/examples/lecture19/access_cars.php|access_cars.php]] ( [[http://localhost/eg-259/examples/lecture19/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//:
+<code html>
+<!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>
+</code>
+  * //The query result page//:
+<code php>
+<!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>
+</code>
+===== PHP/MySQL: Result =====
+* //Result//:
+ {{eg-259:l19-access_cars_php.png?800|A result of a typical query access cars}}
+===== PHP/MySQL: Example 2 =====
+  * The form display document and the PHP processing document can be combined
+===== Combining Form and Results =====
+  * After simply inserting the XHTML from the display document into the PHP document, several modifications are required:
+  - Change the value of the ''action'' attribute of the form to the name of the combined document file
+  - 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:
+<code html>
+    <input type = "hidden"  name = "stage" value = "1" />
+</code>
+  - The PHP code to test this has the form:
+<code php>
+    $stage = $_POST["stage"];
+    if (!IsSet($stage))) { ... }
+</code>
+    * the ''then'' clause includes the form processing;
+    * the ''else'' clause includes the form display
+===== Combining Form and Results =====
+  * Here is the result: [[http://eespectre.swan.ac.uk/~eechris/eg-259/examples/lecture19/access_cars2.php|access_cars2.php]] ( [[http://localhost/eg-259/examples/lecture19/access_cars2.php|access_cars2.php @ localhost]] )
+----
+  * //The code//:
+<code php>
+<!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>
+</code>
+===== Summary of this Lecture =====
+//Introducing Databases and Database Access with PHP/MySQL//
+  * [[eg-259:lecture19#relational_databases|Relational Databases]]
+  * [[eg-259:extras:dbandsql|Introduction to SQL]] (not examined)
+  * [[eg-259:lecture19#architectures_for_database_access|Architectures for Database Access]]
+  * [[eg-259:lecture19#the_mysql_database_system|Introducing MySQL]]
+  * [[eg-259:lecture19#database_access_with_php_mysql|Database Access with PHP and MySQL]]
+====== Learning Outcomes =====
+//At the end of this lecture you should be able to answer these questions//:
+  - What is the purpose of a third tier in a client/server configuration for Web access to a database?
+  - Why are two-tier client/server configurations sometimes inadequate?
+  - At the end of this lecture you should be able to answer these questions:
+  - Explain how SQL database access can be provided by extending a programming language.
+  - What is the disadvantage of embedding SQL in a programming language?
+===== Learning Outcomes (continued) =====
+//At the end of this lecture you should be able to answer these questions//:
+  - What is MySQL?
+  - What does the MySQL constraint ''auto_increment'' do?
+  - What is the problem with quotes in a SQL command obtained from a form element in an XHTML document?
+  - What is the purpose of the PHP ''mysql_select_db'' function?
+  - How can a PHP program determine the number of rows in a query result?
+===== Learning Outcomes (continued) =====
+//At the end of this lecture you should be able to answer these questions//:
+  - What does the PHP function ''mysql_fetch_array'' do?
+  - Explain the exact form of the value returned by ''mysql_fetch_array''.
+  - What is metadata?
+  - How is the collection of metadata extracted from a database?
+  - How can column labels can be obtained from an object of metadata?
+===== Homework Exercise =====
+  * Modify and test the program ''access_cars.php'' to handle ''UPDATE'' and ''INSERT'' SQL commands.
+===== What's Next? =====
+**Web Application Frameworks**
+  * [[eg-259:lecture20#three-tiers_are_not_enough|Three-tiers are not enough!]]
+  * [[eg-259:lecture20#the_problem_with_web_applications|The Problem of Web Application Development]]
+  * [[eg-259:lecture20#web_application_frameworks_1|Web Application Frameworks]]
+  * [[eg-259:lecture20#design_patterns_3_4|Design Patterns]]
+  * [[eg-259:lecture20#case-studyruby-on-rails|Case-Study: Ruby-on-Rails]]
+[[eg-259:lecture18|Previous Lecture]] | [[eg-259:home]] | [[eg-259:lecture20|Next Lecture]]