ICCT Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
~~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]]