eg-259:lecture19
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionLast revisionBoth sides next revision | ||
eg-259:lecture19 [2008/11/13 18:58] – external edit 127.0.0.1 | eg-259:lecture19 [2012/03/22 08:37] – [Combining Form and Results] eechris | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ~~SLIDESHOW~~ | ||
+ | ====== Database Access through the Web====== | ||
+ | **Contact Hour 22**: To be given on Wednesday 28th April, 2012. | ||
+ | |||
+ | **Lecturer**: | ||
+ | |||
+ | An introduction to relational databases, three-tier architecture and database driven web applications. | ||
+ | |||
+ | |||
+ | ===== Database Access through the Web ===== | ||
+ | |||
+ | * A common feature of many Web applications is the use of relational databases for the efficient storage and retrieval of information. | ||
+ | * In this lecture we will discuss how databases can be added to web applications. | ||
+ | * Additional notes on SQL and interfaces to languages other than PHP are [[eg-259: | ||
+ | |||
+ | ---- | ||
+ | |||
+ | Based on Chapter 14 of Robert W. Sebasta, // | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===== Contents of this Lecture ===== | ||
+ | |||
+ | // | ||
+ | |||
+ | * [[eg-259: | ||
+ | * [[eg-259: | ||
+ | * [[eg-259: | ||
+ | * [[eg-259: | ||
+ | * [[eg-259: | ||
+ | |||
+ | The [[eg-259: | ||
+ | |||
+ | |||
+ | ===== Assumed Knowledge ===== | ||
+ | |||
+ | //You should be able to answer the following questions:// | ||
+ | |||
+ | - What is the purpose of the primary keys of a table in a relational database? | ||
+ | - What is the purpose of a cross-reference table? | ||
+ | - How are string literals delimited in SQL? | ||
+ | - What does the '' | ||
+ | - What does an asterisk specify when it appears as the value of a '' | ||
+ | |||
+ | |||
+ | ===== Assumed Knowledge (continued) ===== | ||
+ | |||
+ | //You should be able to answer the following questions:// | ||
+ | |||
+ | - What is specified by the '' | ||
+ | - How are the column names associated with the values in an '' | ||
+ | - What is the purpose of an '' | ||
+ | - What exactly is a //table join//, and how is one specified in SQL? | ||
+ | |||
+ | If you have any difficulty, read the [[eg-259: | ||
+ | |||
+ | ===== Relational Databases ===== | ||
+ | |||
+ | * A database is a collection of data organized to allow relatively easy access for retrievals, additions, and deletions | ||
+ | * A relational database is a collection of tables of data, each of which has one special column that stores the primary keys of the table | ||
+ | * Rows are sometimes called // | ||
+ | * Much more detail in module // | ||
+ | |||
+ | ===== An Example Database ===== | ||
+ | |||
+ | * A relational database for used // | ||
+ | * Could just put all data in a single table, whose key would be a simple sequence number | ||
+ | * The table could have information about various equipment the cars could have | ||
+ | * Better to put the equipment in a different table and use a cross-reference table to relate cars to equipment | ||
+ | * Use a separate table for state names, with only references in the main table | ||
+ | |||
+ | |||
+ | |||
+ | ===== Logical Data Model ===== | ||
+ | |||
+ | {{eg-259: | ||
+ | |||
+ | ---- | ||
+ | |||
+ | " | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===== The Corvettes table ===== | ||
+ | |||
+ | ^ Vette_id ^ Body_style ^ Miles ^ Year ^ State ^ | ||
+ | | 1 | coupe | 18.0 | 1997 | 4 | | ||
+ | | 2 | hatchback | 58.0 | 1996 | 7 | | ||
+ | | 3 | convertible | 13.5 | 2001 | 1 | | ||
+ | | 4 | hatchback | 19.0 | 1995 | 2 | | ||
+ | | 5 | hatchback | 25.0 | 1991 | 5 | | ||
+ | | 6 | hardtop | 15.0 | 2000 | 2 | | ||
+ | | 7 | coupe | 55.0 | 1979 | 10 | | ||
+ | | 8 | convertible | 17.0 | 1999 | 5 | | ||
+ | | 9 | hardtop | 17.0 | 2000 | 5 | | ||
+ | | 10 | hatchback | 50.0 | 1995 | 7 | | ||
+ | |||
+ | ===== The States table ===== | ||
+ | |||
+ | ^ State_id ^ State ^ | ||
+ | | 1 | Alabama | | ||
+ | | 2 | Alaska | | ||
+ | | 3 | Arizona | | ||
+ | | 4 | Arkansas | | ||
+ | | 5 | California | | ||
+ | | 6 | Colorado | | ||
+ | | 7 | Connecticut | | ||
+ | | 8 | Delaware | | ||
+ | | 9 | Florida | | ||
+ | | 10 | Georgia | | ||
+ | | : | : | | ||
+ | | 51 | Wyoming | | ||
+ | |||
+ | |||
+ | ===== The Equipment table ===== | ||
+ | |||
+ | ^ Equp_id ^ Equip ^ | ||
+ | | 1 | Automatic | | ||
+ | | 2 | 4-speed | | ||
+ | | 3 | 5-speed | | ||
+ | | 4 | 6-speed | | ||
+ | | 5 | CD | | ||
+ | | 6 | Leather | | ||
+ | |||
+ | |||
+ | ===== The Corvettes_Equipment table ===== | ||
+ | |||
+ | ^ Vette_id ^ Equip ^ | ||
+ | | 1 | 1 | | ||
+ | | 1 | 5 | | ||
+ | | 1 | 6 | | ||
+ | | 2 | 1 | | ||
+ | | 2 | 5 | | ||
+ | | 2 | 6 | | ||
+ | | 3 | 1 | | ||
+ | | 3 | 6 | | ||
+ | | : | : | | ||
+ | | 10 | 5 | | ||
+ | |||
+ | ===== Using SQL ===== | ||
+ | |||
+ | * To define a database and **C**reate, **R**etrieve, | ||
+ | * Some examples follow: | ||
+ | * Use '' | ||
+ | * **C**reating new records with '' | ||
+ | * **R**etrieving data with '' | ||
+ | * **U**dating records with '' | ||
+ | * **D**eleting records with '' | ||
+ | |||
+ | |||
+ | ===== Use CREATE TABLE to create a database ===== | ||
+ | |||
+ | <code sql> | ||
+ | CREATE TABLE ' | ||
+ | ' | ||
+ | ' | ||
+ | PRIMARY KEY (' | ||
+ | ); | ||
+ | CREATE TABLE ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | ' | ||
+ | PRIMARY KEY (' | ||
+ | ); | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== Creating new records with INSERT ===== | ||
+ | |||
+ | <code sql> | ||
+ | INSERT INTO Corvettes(Vette_id, | ||
+ | Body_style, Miles, Year, State) | ||
+ | VALUES (37, ' | ||
+ | </ | ||
+ | |||
+ | ===== Retrieving data with SELECT ===== | ||
+ | |||
+ | <code sql> | ||
+ | SELECT Body_style FROM Corvettes | ||
+ | WHERE Year > 1994 | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===== Udating records with UPDATE ===== | ||
+ | |||
+ | * The '' | ||
+ | <code sql> | ||
+ | UPDATE Corvettes | ||
+ | SET Year = 1996 | ||
+ | WHERE Vette_id = 17 | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== Deleting records with DELETE===== | ||
+ | |||
+ | <code sql> | ||
+ | DELETE FROM Corvettes | ||
+ | WHERE Vette_id = 27 | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== Deleting tables with DROP ===== | ||
+ | |||
+ | < | ||
+ | DROP TABLE IF EXISTS States | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===== Intro to SQL: Joins ===== | ||
+ | |||
+ | * If you want all cars that have CD players, you need information from two tables, '' | ||
+ | * '' | ||
+ | |||
+ | ===== Joins ===== | ||
+ | |||
+ | * A '' | ||
+ | * For our example, we must have three '' | ||
+ | - '' | ||
+ | - '' | ||
+ | - The '' | ||
+ | |||
+ | |||
+ | |||
+ | ===== The join statement! ===== | ||
+ | |||
+ | <code sql> | ||
+ | SELECT Corvettes.Vette_id, | ||
+ | | ||
+ | | ||
+ | | ||
+ | FROM Corvettes, Equipment | ||
+ | WHERE Corvettes.Vette_id = Corvettes_Equipment.Vette_id | ||
+ | AND Corvettes_Equipment.Equip = Equipment.Equip_id | ||
+ | AND Equipment.Equip = ' | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===== The query results! ===== | ||
+ | |||
+ | ^ '' | ||
+ | | 1 | coupe | 18.0 | 1997 | 4 | CD | | ||
+ | | 2 | hatchback | ||
+ | | 8 | convertible | 17.0 | 1999 | 5 | CD | | ||
+ | | 9 | hardtop | ||
+ | | 10 | hatchback | ||
+ | |||
+ | |||
+ | ===== Learning Outcomes – Database Access ===== | ||
+ | |||
+ | //At the end of this lecture you should be able to answer these questions//: | ||
+ | |||
+ | |||
+ | - What is the purpose of a third tier in a client/ | ||
+ | - Why are two-tier client/ | ||
+ | - Explain how SQL database access can be provided by extending a programming language. | ||
+ | - What is the disadvantage of embedding SQL in a programming language? | ||
+ | |||
+ | |||
+ | ===== Architectures for Database Access ===== | ||
+ | |||
+ | * Client-Server Architectures | ||
+ | * //Client tasks//: | ||
+ | * Provide a way for users to submit queries | ||
+ | * Run applications that use the results of queries | ||
+ | * Display results of queries | ||
+ | * //Server tasks//: | ||
+ | * Implement a data manipulation language, which can directly access and update the database | ||
+ | |||
+ | ===== Two-tier system ===== | ||
+ | |||
+ | * A two-tier system has clients that are connected directly to the database server | ||
+ | * Problems with a two-tier system: | ||
+ | * Because the relative power of clients has grown considerably, | ||
+ | |||
+ | ===== Three-Tier system ===== | ||
+ | |||
+ | * A solution to the problems of two-tier systems is to add a component in the middle to create a three-tier system | ||
+ | * For Web-based database access, the middle tier can run applications (client just gets results) | ||
+ | |||
+ | ===== Three-Tier architecture ===== | ||
+ | |||
+ | {{eg-259: | ||
+ | |||
+ | |||
+ | |||
+ | ===== Database Access with Embedded SQL ===== | ||
+ | |||
+ | * SQL commands are embedded in programs written in a host programming language, whose compiler is extended to accept some form of SQL commands | ||
+ | * // | ||
+ | * One package has computational support of the programming language, as well as database access with SQL | ||
+ | * // | ||
+ | * Portability among database systems | ||
+ | |||
+ | ===== Common Database Access Architecture ===== | ||
+ | |||
+ | {{eg-259: | ||
+ | |||
+ | ===== Learning Outcomes – MySQL and PHP ===== | ||
+ | |||
+ | //At the end of this lecture you should be able to answer these questions//: | ||
+ | |||
+ | - What is MySQL? | ||
+ | - What does the MySQL constraint '' | ||
+ | - What is the problem with quotes in a SQL command obtained from a form element in an XHTML document? | ||
+ | - What is the purpose of the PHP '' | ||
+ | - How can a PHP program determine the number of rows in a query result? | ||
+ | |||
+ | |||
+ | ===== Learning Outcomes (continued) ===== | ||
+ | |||
+ | //At the end of this lecture you should be able to answer these questions//: | ||
+ | |||
+ | - What does the PHP function '' | ||
+ | - Explain the exact form of the value returned by '' | ||
+ | - What is metadata? | ||
+ | - How is the collection of metadata extracted from a database? | ||
+ | - How can column labels can be obtained from an object of metadata? | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===== PHP & Database Access ===== | ||
+ | |||
+ | * An API for each specific database system | ||
+ | * Also convenient for Web access to databases, because PHP is run on the Web server | ||
+ | |||
+ | |||
+ | ===== The MySQL Database System ===== | ||
+ | |||
+ | * A free, efficient, widely used SQL implementation | ||
+ | * Available from http:// | ||
+ | * Logging on to MySQL (starting it): | ||
+ | |||
+ | mysql [-h host] [-u username] [database name] [-p] | ||
+ | |||
+ | * The given database name becomes the " | ||
+ | |||
+ | ---- | ||
+ | < | ||
+ | * '' | ||
+ | * '' | ||
+ | </ | ||
+ | |||
+ | ===== Accessing Databases ===== | ||
+ | |||
+ | * If you want to access an existing database, but it was not named in the //mysql// command, you must choose it for focus: | ||
+ | |||
+ | use cars; | ||
+ | |||
+ | * Response is: '' | ||
+ | * If the focus has not been set and MySQL gets an SQL command, you get: | ||
+ | |||
+ | ERROR 1046: No Database Selected | ||
+ | |||
+ | ===== Creating Databases and Tables ===== | ||
+ | |||
+ | * To create a new database: | ||
+ | |||
+ | CREATE DATABASE cars; | ||
+ | |||
+ | * Response: | ||
+ | |||
+ | Query ok, 1 row affected (0.05 sec) | ||
+ | |||
+ | ---- | ||
+ | | ||
+ | * Example: | ||
+ | <code sql> | ||
+ | CREATE TABLE Equipment | ||
+ | (Equip_id INT UNSIGNED NOT NULL | ||
+ | AUTO_INCREMENT PRIMARY KEY, | ||
+ | | ||
+ | </ | ||
+ | |||
+ | ===== Accessing Metadata ===== | ||
+ | |||
+ | * To see the tables of a database: | ||
+ | |||
+ | SHOW TABLES; | ||
+ | |||
+ | * To see the description of a table (columns): | ||
+ | |||
+ | DESCRIBE Corvettes; | ||
+ | |||
+ | |||
+ | ===== Database Access with PHP/MySQL ===== | ||
+ | |||
+ | * When values from a DB are to be put in XHTML, you must worry about XHTML special characters | ||
+ | * To get rid of the XHTML special characters, use the PHP function, '' | ||
+ | * Replaces the special characters in the string with their corresponding XHTML entities | ||
+ | |||
+ | |||
+ | |||
+ | ===== Database Access with PHP/MySQL (continued) ===== | ||
+ | |||
+ | * Another problem with PHP and XHTML forms is the string special characters (''?'', | ||
+ | * To fix these, '' | ||
+ | * This //escapes// these special characters | ||
+ | |||
+ | ===== Database Access with PHP/MySQL (continued) ===== | ||
+ | |||
+ | * Example: | ||
+ | <code php> | ||
+ | $query = " | ||
+ | WHERE Name = $name"; | ||
+ | </ | ||
+ | * If this wasn't done and the value of '' | ||
+ | * But with '' | ||
+ | |||
+ | ===== Database Access with PHP/MySQL (continued) ===== | ||
+ | |||
+ | * Unfortunately, | ||
+ | * For example, if a '' | ||
+ | * So, '' | ||
+ | |||
+ | |||
+ | |||
+ | ===== PHP: Connecting to a Database ===== | ||
+ | |||
+ | * To connect PHP to a database, use '' | ||
+ | - '' | ||
+ | - '' | ||
+ | - '' | ||
+ | <code php> | ||
+ | $db = mysql_connect(); | ||
+ | </ | ||
+ | * Usually checked for failure | ||
+ | * Close the connection to the database with '' | ||
+ | |||
+ | |||
+ | ===== PHP: Selecting database and executing queries ===== | ||
+ | |||
+ | * //To focus MySQL//: | ||
+ | <code php> | ||
+ | mysql_select_db(" | ||
+ | </ | ||
+ | * // | ||
+ | * Call '' | ||
+ | <code php> | ||
+ | $query = " | ||
+ | $result = mysql_query($query); | ||
+ | </ | ||
+ | |||
+ | |||
+ | ===== PHP: Dealing with the result ===== | ||
+ | |||
+ | * 1. Get the number of rows in the result: | ||
+ | <code php> | ||
+ | $num_rows = mysql_num_rows($result); | ||
+ | </ | ||
+ | * 2. Get the rows with '' | ||
+ | <code php> | ||
+ | for ($row_num = 0; $row_num < $num_rows; $row_num++) { | ||
+ | $row = mysql_fetch_array($result); | ||
+ | print "< | ||
+ | print htmlspecialchars($row[" | ||
+ | print " | ||
+ | : | ||
+ | } | ||
+ | </ | ||
+ | |||
+ | ===== PHP: Using Metadata ===== | ||
+ | |||
+ | * We have had it easy -- the column titles were known | ||
+ | * If they are not known, we must get them | ||
+ | * The result rows are in PHP arrays, whose elements are actually double sets of elements | ||
+ | * Each pair has the value, but one has a numeric key and one has a string key | ||
+ | |||
+ | |||
+ | ===== PHP: Example of Metadata ===== | ||
+ | |||
+ | * For example, if the result has the field values '' | ||
+ | |||
+ | ((0, 1), | ||
+ | | ||
+ | (1, Alabama), | ||
+ | | ||
+ | |||
+ | * If the row is indexed with numbers, the element values are returned | ||
+ | |||
+ | ===== PHP: Accessing field values ===== | ||
+ | |||
+ | * The following displays all field values from '' | ||
+ | <code php> | ||
+ | $values = array_values($row); | ||
+ | for ($index = 0; $index < $num_fields / 2; $index++) | ||
+ | print " | ||
+ | </ | ||
+ | |||
+ | ===== PHP: Displaying all column names ===== | ||
+ | |||
+ | * To display all column names: | ||
+ | <code php> | ||
+ | $keys = array_keys($row); | ||
+ | for ($index = 0; $index < $num_fields; | ||
+ | print " | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===== PHP/MySQL: Example ===== | ||
+ | |||
+ | * [[http:// | ||
+ | * [[http:// | ||
+ | |||
+ | ---- | ||
+ | |||
+ | <note warning> | ||
+ | For reasons of security, examples are designed to work from a local installation. I used '' | ||
+ | </ | ||
+ | |||
+ | * //The initial form//: | ||
+ | <code html> | ||
+ | < | ||
+ | <!-- carsdata.html | ||
+ | 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: < | ||
+ | SELECT Vette_id, Body_style, Year, States.State | ||
+ | FROM Corvettes, States | ||
+ | WHERE Corvettes.State = States.State_id AND States.State = ' | ||
+ | </ | ||
+ | Please enter your query: | ||
+ | <br /> | ||
+ | <form action | ||
+ | < | ||
+ | </ | ||
+ | <br /> | ||
+ | <br /> | ||
+ | <input type = " | ||
+ | <input type = " | ||
+ | </ | ||
+ | </p> | ||
+ | </ | ||
+ | </ | ||
+ | </ | ||
+ | |||
+ | * //The query result page//: | ||
+ | <code php> | ||
+ | < | ||
+ | <!-- access_cars.php | ||
+ | A PHP script to access the cars database | ||
+ | through MySQL | ||
+ | --> | ||
+ | <html lang=" | ||
+ | < | ||
+ | <meta charset=" | ||
+ | < | ||
+ | </ | ||
+ | < | ||
+ | <?php | ||
+ | |||
+ | // Connect to MySQL | ||
+ | |||
+ | $db = mysql_connect(" | ||
+ | if (!$db) { | ||
+ | print "Error - Could not connect to MySQL"; | ||
+ | exit ; | ||
+ | } | ||
+ | |||
+ | // Select the cars database | ||
+ | |||
+ | $er = mysql_select_db(" | ||
+ | if (!$er) { | ||
+ | print "Error - Could not select the cars database"; | ||
+ | exit ; | ||
+ | } | ||
+ | |||
+ | // Get the query and clean it up (delete leading and trailing | ||
+ | // whitespace and remove backslashes from magic_quotes_gpc) | ||
+ | $query = $_POST[' | ||
+ | trim($query); | ||
+ | $query = stripslashes($query); | ||
+ | |||
+ | // Display the query, after fixing html characters | ||
+ | |||
+ | $query_html = htmlspecialchars($query); | ||
+ | print "< | ||
+ | |||
+ | // Execute the query | ||
+ | |||
+ | $result = mysql_query($query); | ||
+ | if (!$result) { | ||
+ | print "Error - the query could not be executed"; | ||
+ | $error = mysql_error(); | ||
+ | print "< | ||
+ | exit ; | ||
+ | |||
+ | } | ||
+ | |||
+ | // Display the results in a table | ||
+ | print "< | ||
+ | print "< | ||
+ | |||
+ | // Get the number of rows in the result, as well as the first row | ||
+ | // and the number of fields in the rows | ||
+ | |||
+ | $num_rows = mysql_num_rows($result); | ||
+ | $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 "< | ||
+ | |||
+ | print "</ | ||
+ | |||
+ | // Output the values of the fields in the rows | ||
+ | |||
+ | for ($row_num = 0; $row_num < $num_rows; $row_num++) { | ||
+ | print "< | ||
+ | $values = array_values($row); | ||
+ | for ($index = 0; $index < $num_fields; | ||
+ | $value = htmlspecialchars($values[2 * $index + 1]); | ||
+ | print "< | ||
+ | } | ||
+ | |||
+ | print "</ | ||
+ | $row = mysql_fetch_array($result); | ||
+ | } | ||
+ | print "</ | ||
+ | ?> | ||
+ | </ | ||
+ | </ | ||
+ | </ | ||
+ | |||
+ | ===== PHP/MySQL: Result ===== | ||
+ | |||
+ | * //Result//: | ||
+ | |||
+ | | ||
+ | |||
+ | |||
+ | ===== PHP/MySQL: Example 2 ===== | ||
+ | |||
+ | * The form display document and the PHP processing document can be combined | ||
+ | |||
+ | ===== Combining Form and Results ===== | ||
+ | |||
+ | * After simply inserting the XHTML from the display document into the PHP document, several modifications are required: | ||
+ | - Change the value of the '' | ||
+ | - Create a //hidden// input element that sets its value when the document is first displayed. This provides a way for the document to determine which it is doing, displaying the form or processing the form data: | ||
+ | <code html> | ||
+ | <input type = " | ||
+ | </ | ||
+ | - The PHP code to test this has the form: | ||
+ | <code php> | ||
+ | $stage = $_POST[" | ||
+ | if (!IsSet($stage))) { ... } | ||
+ | </ | ||
+ | * the '' | ||
+ | * the '' | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===== Combining Form and Results ===== | ||
+ | |||
+ | * Here is the result: [[http:// | ||
+ | |||
+ | ---- | ||
+ | |||
+ | * //The code//: | ||
+ | <code php> | ||
+ | < | ||
+ | <!-- access_cars2.php | ||
+ | A PHP script to both get a query from the user and | ||
+ | access the cars database through MySQL to get and | ||
+ | display the result of the query. | ||
+ | --> | ||
+ | <html lang=" | ||
+ | < | ||
+ | <meta charset=" | ||
+ | < | ||
+ | </ | ||
+ | < | ||
+ | <?php | ||
+ | $stage = $_POST[" | ||
+ | if (!IsSet($stage)) { | ||
+ | ?> | ||
+ | <p> | ||
+ | Try: < | ||
+ | SELECT Vette_id, Body_style, Year, States.State | ||
+ | FROM Corvettes, States | ||
+ | WHERE Corvettes.State = States.State_id AND States.State = ' | ||
+ | </ | ||
+ | Please enter your query: | ||
+ | <br /> | ||
+ | < | ||
+ | < | ||
+ | </ | ||
+ | <br /> | ||
+ | <br /> | ||
+ | <input type = " | ||
+ | <input type = " | ||
+ | </ | ||
+ | </p> | ||
+ | <?php | ||
+ | } else { // $stage was set, so process the query | ||
+ | |||
+ | // Connect to MySQL | ||
+ | |||
+ | $db = mysql_connect(" | ||
+ | if (!$db) { | ||
+ | print "Error - Could not connect to MySQL"; | ||
+ | exit; | ||
+ | | ||
+ | |||
+ | // Select the cars database | ||
+ | |||
+ | $er = mysql_select_db(" | ||
+ | if (!$er) { | ||
+ | print "Error - Could not select the cars database"; | ||
+ | exit; | ||
+ | } | ||
+ | |||
+ | // Get the query and clean it up (delete leading and trailing | ||
+ | // whitespace and remove backslashes from magic_quotes_gpc) | ||
+ | | ||
+ | | ||
+ | | ||
+ | |||
+ | // Fix the query for browser display and display it | ||
+ | |||
+ | | ||
+ | print "< | ||
+ | |||
+ | // Execute the query | ||
+ | |||
+ | | ||
+ | if (!$result) { | ||
+ | print "Error - the query could not be executed"; | ||
+ | $error = mysql_error(); | ||
+ | print "< | ||
+ | exit; | ||
+ | } | ||
+ | |||
+ | // Display the results in a table | ||
+ | |||
+ | print "< | ||
+ | print "< | ||
+ | |||
+ | // Get the number of rows in the result, as well as the first row | ||
+ | // and the number of fields in the rows | ||
+ | |||
+ | | ||
+ | $row = mysql_fetch_array($result); | ||
+ | | ||
+ | // Produce the column labels | ||
+ | |||
+ | $keys = array_keys($row); | ||
+ | for ($index = 0; $index < $num_fields; | ||
+ | print "< | ||
+ | } | ||
+ | |||
+ | print "</ | ||
+ | |||
+ | // Output the values of the fields in the rows | ||
+ | |||
+ | for ($row_num = 0; $row_num < $num_rows; $row_num++) { | ||
+ | print "< | ||
+ | $values = array_values($row); | ||
+ | for ($index = 0; $index < $num_fields; | ||
+ | | ||
+ | print "< | ||
+ | } | ||
+ | |||
+ | print "</ | ||
+ | $row = mysql_fetch_array($result); | ||
+ | } | ||
+ | print "</ | ||
+ | } | ||
+ | ?> | ||
+ | </ | ||
+ | </ | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== Summary of this Lecture ===== | ||
+ | |||
+ | // | ||
+ | |||
+ | * [[eg-259: | ||
+ | * [[eg-259: | ||
+ | * [[eg-259: | ||
+ | * [[eg-259: | ||
+ | * [[eg-259: | ||
+ | |||
+ | |||
+ | ====== Learning Outcomes ===== | ||
+ | |||
+ | //At the end of this lecture you should be able to answer these questions//: | ||
+ | |||
+ | |||
+ | - What is the purpose of a third tier in a client/ | ||
+ | - Why are two-tier client/ | ||
+ | - At the end of this lecture you should be able to answer these questions: | ||
+ | - Explain how SQL database access can be provided by extending a programming language. | ||
+ | - What is the disadvantage of embedding SQL in a programming language? | ||
+ | |||
+ | |||
+ | ===== Learning Outcomes (continued) ===== | ||
+ | |||
+ | //At the end of this lecture you should be able to answer these questions//: | ||
+ | |||
+ | - What is MySQL? | ||
+ | - What does the MySQL constraint '' | ||
+ | - What is the problem with quotes in a SQL command obtained from a form element in an XHTML document? | ||
+ | - What is the purpose of the PHP '' | ||
+ | - How can a PHP program determine the number of rows in a query result? | ||
+ | |||
+ | |||
+ | ===== Learning Outcomes (continued) ===== | ||
+ | |||
+ | //At the end of this lecture you should be able to answer these questions//: | ||
+ | |||
+ | - What does the PHP function '' | ||
+ | - Explain the exact form of the value returned by '' | ||
+ | - What is metadata? | ||
+ | - How is the collection of metadata extracted from a database? | ||
+ | - How can column labels can be obtained from an object of metadata? | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===== Homework Exercise ===== | ||
+ | |||
+ | * Modify and test the program '' | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===== What's Next? ===== | ||
+ | |||
+ | **Web Application Frameworks** | ||
+ | |||
+ | * [[eg-259: | ||
+ | * [[eg-259: | ||
+ | * [[eg-259: | ||
+ | * [[eg-259: | ||
+ | * [[eg-259: | ||
+ | |||
+ | [[eg-259: |
eg-259/lecture19.txt · Last modified: 2013/03/08 18:04 by eechris