ICCT Wiki

User Tools

Site Tools

Trace: • lecture19 • lecture1
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
eg-259:lecture19 [2009/11/30 21:34] – eechriseg-259:lecture19 [2013/03/08 18:04] (current) – [Database Access through the Web] eechris
Line 2: Line 2:
 ====== Database Access through the Web====== ====== Database Access through the Web======
  
-**Lecture 19**: To be given on Tuesday 1st December, 2009.+**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]]. **Lecturer**: [[C.P.Jobling@Swansea.ac.uk|Dr Chris P. Jobling]].
  
 An introduction to relational databases, three-tier architecture and database driven web applications. An introduction to relational databases, three-tier architecture and database driven web applications.
- 
  
 ===== Database Access through the Web ===== ===== Database Access through the Web =====
Line 88: Line 89:
 ===== Logical Data Model ===== ===== Logical Data Model =====
  
-{{eg-259:l19-logical-structure.png|Logical structure of the Corvettes database}}+{{eg-259:l19-logical-structure.png?800|Logical structure of the Corvettes database}}
  
 ---- ----
Line 302: Line 303:
 ===== Three-Tier architecture ===== ===== Three-Tier architecture =====
  
-{{eg-259:l19-3-tier.png|Three-tier architecture of a Web site supported by databases}}+{{eg-259:l19-3-tier.png?800|Three-tier architecture of a Web site supported by databases}}
  
    
Line 316: Line 317:
 ===== Common Database Access Architecture ===== ===== Common Database Access Architecture =====
  
-{{eg-259:dba-architecture.png|Common Database Access Architecture}}+{{eg-259:dba-architecture.png?800|Common Database Access Architecture}}
  
 ===== Learning Outcomes – MySQL and PHP ===== ===== Learning Outcomes – MySQL and PHP =====
Line 526: Line 527:
 ===== PHP/MySQL: Example ===== ===== PHP/MySQL: Example =====
  
-  * [[http://77.68.39.12/~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/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://77.68.39.12/~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''+  * [[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''
  
 ----  ---- 
Line 537: Line 538:
   * //The initial form//:   * //The initial form//:
 <code html> <code html>
-<?xml version = "1.0" encoding = "utf-8"?> +<!DOCTYPE html>
-<!DOCTYPE html PUBLIC "-//w3c//DTD XHTML 1.1//EN" +
- "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> +
- +
 <!-- carsdata.html <!-- carsdata.html
-     Uses a form to collect a query against the cars +Uses a form to collect a query against the cars 
-     database. +database. 
-     Calls the PHP script, access_cars.php to perform +Calls the PHP script, access_cars.php to perform 
-     the given query and display the results +the given query and display the results 
-    --> +--> 
-<html xmlns = "http://www.w3.org/1999/xhtml" xml:lang="en"> +<html lang="en"> 
-  <head><title> Access to the corvettes database </title>+  <head> 
 +    <meta charset="utf-8" /> 
 +    <title> Access to the corvettes database </title>
   </head>   </head>
   <body>   <body>
-   <p> +    <p> 
-      Try: +      Try:       <pre>
-<pre>+
 SELECT Vette_id, Body_style, Year, States.State SELECT Vette_id, Body_style, Year, States.State
 FROM Corvettes, States FROM Corvettes, States
 WHERE Corvettes.State = States.State_id AND States.State = 'California' WHERE Corvettes.State = States.State_id AND States.State = 'California'
-</pre>   +</pre>
       Please enter your query:       Please enter your query:
       <br />       <br />
       <form action  = "access_cars.php" method = "post">       <form action  = "access_cars.php" method = "post">
-<textarea  rows = "2"  cols = "80" name = "query" >+        <textarea  rows = "2"  cols = "80" name = "query" >
         </textarea>         </textarea>
-        <br /><br />+        <br /> 
 +        <br />
         <input type = "reset"  value = "Reset" />         <input type = "reset"  value = "Reset" />
         <input type = "submit"  value = "Submit request" />         <input type = "submit"  value = "Submit request" />
-      </form>    +      </form>
     </p>     </p>
   </body>   </body>
Line 574: Line 574:
   * //The query result page//:   * //The query result page//:
 <code php> <code php>
-<?php echo("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n"); ?>  +<!DOCTYPE html>
-<!DOCTYPE html PUBLIC "-//w3c//DTD XHTML 1.1//EN" +
- "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">+
 <!-- access_cars.php <!-- access_cars.php
-     A PHP script to access the cars database +A PHP script to access the cars database 
-     through MySQL +through MySQL 
-     --> +--> 
-<html xmlns = "http://www.w3.org/1999/xhtml" xml:lang="en"> +<html lang="en"> 
-<head> +  <head> 
-<title> Access the cars database with MySQL </title> +    <meta charset="utf-8" /> 
-</head> +    <title> Access the cars database with MySQL </title> 
-<body> +  </head> 
-<?php+  <body> 
 +    <?php
  
-// Connect to MySQL+    // Connect to MySQL
  
-$db = mysql_connect("localhost", "eg-259", ""); +    $db = mysql_connect("localhost", "eg-259", "eg-259"); 
-if (!$db) { +    if (!$db) { 
-     print "Error - Could not connect to MySQL"; +      print "Error - Could not connect to MySQL"; 
-     exit; +      exit ; 
-}+    }
  
-// Select the cars database+    // Select the cars database
  
-$er = mysql_select_db("cars"); +    $er = mysql_select_db("cars"); 
-if (!$er) { +    if (!$er) { 
-    print "Error - Could not select the cars database"; +      print "Error - Could not select the cars database"; 
-    exit; +      exit ; 
-}+    }
  
-// Get the query and clean it up (delete leading and trailing  +    // Get the query and clean it up (delete leading and trailing 
-// whitespace and remove backslashes from magic_quotes_gpc) +    // whitespace and remove backslashes from magic_quotes_gpc) 
-$query = $_POST['query']; +    $query = $_POST['query']; 
-trim($query); +    trim($query); 
-$query = stripslashes($query);+    $query = stripslashes($query);
  
-// Display the query, after fixing html characters+    // Display the query, after fixing html characters
  
-$query_html = htmlspecialchars($query); +    $query_html = htmlspecialchars($query); 
-print "<p> <b> The query is: </b> " . $query_html . "</p>";+    print "<p> <b> The query is: </b> " . $query_html . "</p>";
  
-// Execute the query+    // Execute the query
  
-$result = mysql_query($query); +    $result = mysql_query($query); 
-if (!$result) { +    if (!$result) { 
-    print "Error - the query could not be executed"; +      print "Error - the query could not be executed"; 
-    $error = mysql_error(); +      $error = mysql_error(); 
-    print "<p>" . $error . "</p>"; +      print "<p>" . $error . "</p>"; 
-    exit;+      exit ;
  
 +    }
  
-}+    // Display the results in a table 
 +    print "<table border='1'><caption> <h2> Query Results </h2> </caption>"; 
 +    print "<tr>";
  
-// Display the results in a table +    // Get the number of rows in the result, as well as the first row 
-print "<table border='1'><caption> <h2> Query Results </h2> </caption>"; +    //  and the number of fields in the rows
-print "<tr>";+
  
-// Get the number of rows in the result, as well as the first row +    $num_rows = mysql_num_rows($result); 
-//  and the number of fields in the rows+    $row = mysql_fetch_array($result); 
 +    $num_fields = mysql_num_fields($result);
  
-$num_rows = mysql_num_rows($result); +    // Produce the column labels
-$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>";
  
-$keys = array_keys($row); +    print "</tr>";
-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
  
-// 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> "; 
 +      }
  
-for ($row_num = 0; $row_num < $num_rows; $row_num++) { +      print "</tr>"; 
-    print "<tr>"; +      $row = mysql_fetch_array($result);
-    $values = array_values($row); +
-    for ($index = 0; $index < $num_fields; $index++){ +
-      $value = htmlspecialchars($values[2 * $index + 1]); +
-      print "<td>" . $value . "</td> ";+
     }     }
- +    print "</table>"; 
-    print "</tr>"; +  ?> 
-    $row = mysql_fetch_array($result); +  </body>
- } +
-print "</table>"; +
-?> +
-</body>+
 </html> </html>
 </code>  </code> 
Line 669: Line 667:
 * //Result//: * //Result//:
  
- {{eg-259:l19-access_cars_php.png|A result of a typical query access cars}}+ {{eg-259:l19-access_cars_php.png?800|A result of a typical query access cars}}
  
  
Line 704: Line 702:
   * //The code//:   * //The code//:
 <code php> <code php>
-<?php echo("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n"); ?>  +<!DOCTYPE html>
-<!DOCTYPE html PUBLIC "-//w3c//DTD XHTML 1.1//EN" +
- "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">+
 <!-- access_cars2.php <!-- access_cars2.php
-     A PHP script to both get a query from the user and +A PHP script to both get a query from the user and 
-     access the cars database through MySQL to get and +access the cars database through MySQL to get and 
-     display the result of the query. +display the result of the query. 
-     --> +--> 
-<html xmlns = "http://www.w3.org/1999/xhtml" xml:lang="en">+<html lang="en">
   <head>   <head>
 +    <meta charset="utf-8" />
     <title> Access the cars database with MySQL </title>     <title> Access the cars database with MySQL </title>
   </head>   </head>
   <body>   <body>
-<?php+    <?php
 $stage = $_POST["stage"]; $stage = $_POST["stage"];
 if (!IsSet($stage)) { if (!IsSet($stage)) {
-?> +    ?> 
-   <p> +    <p> 
-      Try: +      Try:       <pre>
-<pre>+
 SELECT Vette_id, Body_style, Year, States.State SELECT Vette_id, Body_style, Year, States.State
 FROM Corvettes, States FROM Corvettes, States
 WHERE Corvettes.State = States.State_id AND States.State = 'California' WHERE Corvettes.State = States.State_id AND States.State = 'California'
-</pre>   +</pre>
       Please enter your query:       Please enter your query:
       <br />       <br />
       <form  method = "POST"  action = "access_cars2.php" >       <form  method = "POST"  action = "access_cars2.php" >
         <textarea  rows = "2"  cols = "80"  name = "query">          <textarea  rows = "2"  cols = "80"  name = "query"> 
-        </textarea>  +        </textarea> 
-        <br /><br />+        <br /> 
 +        <br />
         <input type = "hidden"  name = "stage"  value = "1" />         <input type = "hidden"  name = "stage"  value = "1" />
         <input type = "submit"  value = "Submit request" />         <input type = "submit"  value = "Submit request" />
       </form>       </form>
     </p>     </p>
-<?php +    <?php 
-} else {  // $stage was set, so process the query+    } else {  // $stage was set, so process the query
  
-// Connect to MySQL+    // Connect to MySQL
  
-  $db = mysql_connect("localhost", "eg-259", ""); +     $db = mysql_connect("localhost", "eg-259", "eg-259"); 
-  if (!$db) { +     if (!$db) { 
-print "Error - Could not connect to MySQL"; +      print "Error - Could not connect to MySQL"; 
-     exit; +      exit; 
-  }+     } 
  
-// Select the cars database+    // Select the cars database
  
-  $er = mysql_select_db("cars"); +     $er = mysql_select_db("cars"); 
-  if (!$er) { +     if (!$er) { 
-    print "Error - Could not select the cars database"; +      print "Error - Could not select the cars database"; 
-    exit; +      exit; 
-  }+     }
  
-// Clean up the given query (delete leading and trailing whitespace)+    // 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);
  
-  $query = $_POST['query']; +    // Fix the query for browser display and display it
-  trim($query);+
  
-// Fix the query for browser display and display it+     $query_html = htmlspecialchars($query); 
 +     print "<p> <b> The query is: </b> " . $query_html . "</p>";
  
-  $query_html = htmlspecialchars($query); +    // Execute the 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; 
 +     }
  
-  $result = mysql_query($query); +    // Display the results in a table
-  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>";
  
-  print "<table border='1'><caption> <h2> Query Results </h2> </caption>"; +    // Get the number of rows in the result, as well as the first row 
-  print "<tr>";+    //  and the number of fields in the rows
  
-// Get the number of rows in the result, as well as the first row +     $num_rows = mysql_num_rows($result); 
-//  and the number of fields in the rows+     $row = mysql_fetch_array($result); 
 +     $num_fields = mysql_num_fields($result); 
 +     // Produce the column labels
  
-  $num_rows = mysql_num_rows($result); +     $keys = array_keys($row); 
-  $row = mysql_fetch_array($result); +     for ($index = 0; $index < $num_fields; $index++) { 
-  $num_fields = mysql_num_fields($result); +       print "<th>" . $keys[2 * $index + 1] . "</th>"; 
-// Produce the column labels+     }
  
-  $keys = array_keys($row); +     print "</tr>";
-  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
  
-// 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> "; 
 +      }
  
-  for ($row_num = 0; $row_num < $num_rows; $row_num++) { +      print "</tr>"; 
-    print "<tr>"; +      $row = mysql_fetch_array($result); 
-    $values = array_values($row); +     } 
-    for ($index = 0; $index < $num_fields; $index++){ +     print "</table>";
-      $value = htmlspecialchars($values[2 * $index + 1]); +
-      print "<td>" . $value . "</td> ";+
     }     }
- +  ?> 
-    print "</tr>"; +  </body>
-    $row = mysql_fetch_array($result); +
-  } +
-  print "</table>"; +
-} +
-?> +
-</body>+
 </html> </html>
 </code> </code>
eg-259/lecture19.1259616883.txt.gz · Last modified: 2011/01/14 12:28 (external edit)