ICCT Wiki

User Tools

Site Tools

Trace:
eg-259:lecture19

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
Last revisionBoth sides next revision
eg-259:lecture19 [2007/12/04 09:36] – eechriseg-259:lecture19 [2012/03/22 08:37] – [Combining Form and Results] eechris
Line 1: Line 1:
 +~~SLIDESHOW~~
 +====== Database Access through the Web======
  
 +**Contact Hour 22**: To be given on Wednesday 28th April, 2012.
 +
 +**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]]
eg-259/lecture19.txt · Last modified: 2013/03/08 18:04 by eechris