Differences

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

--- eg-259:lecture19 [2012/03/22 08:36] – [PHP/MySQL: Example] eechris
+++ eg-259:lecture19 [2013/03/08 18:04] (current) – [Database Access through the Web] eechris
@@ Line 2: / Line 2: @@
 ====== Database Access through the Web======
-**Contact Hour 22**: To be given on Wednesday 28th April, 2012.
+**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 =====
@@ Line 701: / Line 702: @@
   * //The code//:
 <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
-     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>
+    <meta charset="utf-8" />
     <title> Access the cars database with MySQL </title>
   </head>
   <body>
-<?php
+    <?php
 $stage = $_POST["stage"];
 if (!IsSet($stage)) {
-?>
+    ?>
-   <p>
+    <p>
-      Try:
+      Try:       <pre>
-<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 />
+        <br />
+        <br />
         <input type = "hidden"  name = "stage"  value = "1" />
         <input type = "submit"  value = "Submit request" />
       </form>
     </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>
 </code>