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
Next revisionBoth sides next revision
eg-259:lecture19 [2011/05/09 07:25] – [PHP/MySQL: Result] eechriseg-259:lecture19 [2012/03/22 08:36] – [PHP/MySQL: Example] 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 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> 
eg-259/lecture19.txt · Last modified: 2013/03/08 18:04 by eechris