eg-259:lecture19
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
eg-259:lecture19 [2008/11/20 18:00] – eechris | eg-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, 2008. | + | **Contact Hour 22**: To be given on Wednesday 28th April, 2012. |
**Lecturer**: | **Lecturer**: | ||
Line 88: | Line 88: | ||
===== Logical Data Model ===== | ===== Logical Data Model ===== | ||
- | {{eg-259: | + | {{eg-259: |
---- | ---- | ||
Line 302: | Line 302: | ||
===== Three-Tier architecture ===== | ===== Three-Tier architecture ===== | ||
- | {{eg-259: | + | {{eg-259: |
Line 316: | Line 316: | ||
===== Common Database Access Architecture ===== | ===== Common Database Access Architecture ===== | ||
- | {{eg-259: | + | {{eg-259: |
===== Learning Outcomes – MySQL and PHP ===== | ===== Learning Outcomes – MySQL and PHP ===== | ||
Line 526: | Line 526: | ||
===== PHP/MySQL: Example ===== | ===== PHP/MySQL: Example ===== | ||
- | * [[http://77.68.39.12/ | + | * [[http://eespectre.swan.ac.uk/ |
- | * [[http://77.68.39.12/ | + | * [[http://eespectre.swan.ac.uk/ |
---- | ---- | ||
Line 537: | Line 537: | ||
* //The initial form//: | * //The initial form//: | ||
<code html> | <code html> | ||
- | <?xml version = " | + | < |
- | < | + | |
- | " | + | |
- | + | ||
<!-- 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. |
- | | + | Calls the PHP script, access_cars.php to perform |
- | | + | the given query and display the results |
- | --> | + | --> |
- | < | + | <html lang=" |
- | < | + | < |
+ | <meta charset=" | ||
+ | | ||
</ | </ | ||
< | < | ||
- | <p> | + | |
- | Try: | + | Try: |
- | <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 = ' | WHERE Corvettes.State = States.State_id AND States.State = ' | ||
- | </ | + | </ |
Please enter your query: | Please enter your query: | ||
<br /> | <br /> | ||
<form action | <form action | ||
- | < | + | |
</ | </ | ||
- | <br /><br /> | + | <br /> |
+ | | ||
<input type = " | <input type = " | ||
<input type = " | <input type = " | ||
- | </ | + | </ |
</p> | </p> | ||
</ | </ | ||
Line 574: | Line 573: | ||
* //The query result page//: | * //The query result page//: | ||
<code php> | <code php> | ||
- | <?php echo("<? | + | < |
- | < | + | |
- | " | + | |
<!-- access_cars.php | <!-- access_cars.php | ||
- | A PHP script to access the cars database | + | A PHP script to access the cars database |
- | | + | through MySQL |
- | | + | --> |
- | < | + | <html lang=" |
- | < | + | < |
- | < | + | < |
- | </ | + | |
- | < | + | </ |
- | <?php | + | < |
+ | <?php | ||
- | // Connect to MySQL | + | |
- | $db = mysql_connect(" | + | |
- | if (!$db) { | + | if (!$db) { |
- | | + | print "Error - Could not connect to MySQL"; |
- | | + | exit ; |
- | } | + | } |
- | // Select the cars database | + | |
- | $er = mysql_select_db(" | + | |
- | 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 | + | |
- | // whitespace and remove backslashes from magic_quotes_gpc) | + | // whitespace and remove backslashes from magic_quotes_gpc) |
- | $query = $_POST[' | + | $query = $_POST[' |
- | trim($query); | + | trim($query); |
- | $query = stripslashes($query); | + | $query = stripslashes($query); |
- | // Display the query, after fixing html characters | + | |
- | $query_html = htmlspecialchars($query); | + | |
- | print "< | + | print "< |
- | // Execute the 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 "< | + | print "< |
- | exit; | + | exit ; |
+ | } | ||
- | } | + | // Display the results in a table |
+ | print "< | ||
+ | print "< | ||
- | // Display | + | |
- | print "< | + | // and the number of fields in the rows |
- | print "< | + | |
- | // 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_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; | ||
+ | print "< | ||
- | $keys = array_keys($row); | + | |
- | for ($index = 0; $index < $num_fields; | + | |
- | | + | |
- | print "</tr>"; | + | |
- | // Output the values | + | for ($row_num = 0; $row_num < $num_rows; $row_num++) { |
+ | print "< | ||
+ | $values | ||
+ | for ($index = 0; $index < $num_fields; | ||
+ | $value = htmlspecialchars($values[2 * $index + 1]); | ||
+ | print "< | ||
+ | } | ||
- | for ($row_num = 0; $row_num < $num_rows; $row_num++) { | + | |
- | | + | $row = mysql_fetch_array($result); |
- | | + | |
- | for ($index = 0; $index < $num_fields; | + | |
- | $value | + | |
- | print "< | + | |
} | } | ||
- | + | | |
- | print "</ | + | ?> |
- | $row = mysql_fetch_array($result); | + | </ |
- | } | + | |
- | print "</ | + | |
- | ?> | + | |
- | </ | + | |
</ | </ | ||
</ | </ | ||
Line 669: | Line 666: | ||
* //Result//: | * //Result//: | ||
- | | + | |
Line 698: | Line 695: | ||
===== Combining Form and Results ===== | ===== Combining Form and Results ===== | ||
- | * Here us the result: [[http://77.68.39.12/ | + | * Here is the result: [[http://eespectre.swan.ac.uk/ |
---- | ---- |
eg-259/lecture19.txt · Last modified: 2013/03/08 18:04 by eechris