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 [2011/01/14 12:46] – external edit 127.0.0.1eg-259:lecture19 [2012/03/22 08:37] – [Combining Form and Results] 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.+**Contact Hour 22**: To be given on Wednesday 28th April, 2012.
  
 **Lecturer**: [[C.P.Jobling@Swansea.ac.uk|Dr Chris P. Jobling]]. **Lecturer**: [[C.P.Jobling@Swansea.ac.uk|Dr Chris P. Jobling]].
Line 88: Line 88:
 ===== 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 302:
 ===== 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 316:
 ===== 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 537: Line 537:
   * //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 573:
   * //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 666:
 * //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 701:
   * //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.txt · Last modified: 2013/03/08 18:04 by eechris