CREATE TABLE 'states' (
'State_id' int(11) NOT NULL auto_increment,
'State' varchar(25) NOT NULL,
PRIMARY KEY ('State_id')
);
CREATE TABLE 'corvettes' (
'Vette_id' int(11) NOT NULL auto_increment,
'Body_style' varchar(25) NOT NULL,
'Miles' float NOT NULL,
'Year' year(4) NOT NULL,
'State' int(11) NOT NULL,
PRIMARY KEY ('Vette_id')
);
===== Creating new records with INSERT =====
INSERT INTO Corvettes(Vette_id,
Body_style, Miles, Year, State)
VALUES (37, 'convertible', 25.5, 1986, 17)
===== Retrieving data with SELECT =====
SELECT Body_style FROM Corvettes
WHERE Year > 1994
===== Udating records with UPDATE =====
* The ''WHERE'' clause is (often) the primary key of the row to be updated:
UPDATE Corvettes
SET Year = 1996
WHERE Vette_id = 17
===== Deleting records with DELETE=====
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, ''Corvettes'' and ''Equipment''
* ''SELECT'' can build a temporary table with info from two tables, from which the desired results can be gotten -- this is called a //join// of the two tables
===== Joins =====
* A ''SELECT'' that does a join operation specifies two tables in its ''FROM'' clause and also has a compound ''WHERE'' clause
* For our example, we must have three ''WHERE'' conditions
- ''Vette_ids'' from ''Corvettes'' and ''Corvettes_Equipment'' must match
- ''Equip'' from ''Corvettes_Equipment'' must match the ''Equip_id'' from ''Equipment''
- The ''Equip'' from ''Equipment'' must be '''CD'''
===== The join statement! =====
SELECT Corvettes.Vette_id,
Corvettes.Body_style, Corvettes.Miles,
Corvettes.Year, Corvettes.State,
Equipment.Equip
FROM Corvettes, Equipment
WHERE Corvettes.Vette_id = Corvettes_Equipment.Vette_id
AND Corvettes_Equipment.Equip = Equipment.Equip_id
AND Equipment.Equip = 'CD'
===== The query results! =====
^ ''VETTE_ID'' ^ ''BODY_STYLE'' ^ ''MILES'' ^ ''YEAR'' ^ ''STATE'' ^ ''EQUIP'' ^
| 1 | coupe | 18.0 | 1997 | 4 | CD |
| 2 | hatchback | 58.0 | 1996 | 7 | CD |
| 8 | convertible | 17.0 | 1999 | 5 | CD |
| 9 | hardtop | 17.0 | 2000 | 5 | CD |
| 10 | hatchback | 50.0 | 1995 | 7 | CD |
===== 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/server configuration for Web access to a database?
- Why are two-tier client/server configurations sometimes inadequate?
- 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, we could shift processing to the client, but then keeping all clients current with application updates is difficult
===== 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:l19-3-tier.png?800|Three-tier architecture of a Web site supported by databases}}
===== 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
* //Advantage//:
* One package has computational support of the programming language, as well as database access with SQL
* //Disadvantage//:
* Portability among database systems
===== Common Database Access Architecture =====
{{eg-259:dba-architecture.png?800|Common Database Access Architecture}}
===== 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 ''auto_increment'' do?
- 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 ''mysql_select_db'' function?
- 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 ''mysql_fetch_array'' do?
- Explain the exact form of the value returned by ''mysql_fetch_array''.
- 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://www.mysql.org
* Logging on to MySQL (starting it):
mysql [-h host] [-u username] [database name] [-p]
* The given database name becomes the "focus" of MySQL
----
CREATE TABLE Equipment
(Equip_id INT UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY,
Equip CHAR(10));
===== 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, ''htmlspecialchars($str)''
* 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 (''?'', ''"'', ''\'', and ''NULL''), which could come from ''$_GET'' and ''$_POST''
* To fix these, ''magic_quotes_gpc'' in the ''PHP.ini'' file is set to ''ON'' by default
* This //escapes// these special characters
===== Database Access with PHP/MySQL (continued) =====
* Example:
$query = "SELECT * FROM Names
WHERE Name = $name";
* If this wasn't done and the value of ''$name'' is ''O'Shanter'', it would prematurely terminate the query string
* But with ''magic_quotes_gpc'' on, it will be converted to ''O\'Shanter''
===== Database Access with PHP/MySQL (continued) =====
* Unfortunately, this can create new problems
* For example, if a ''SELECT'' clause has a single-quoted part, like '''California''', the single quotes will be implicitly backslashed, making the query illegal for MySQL
* So, ''magic_quotes_gpc'' must be turned off, or else the extra backslashes can be removed with ''stripslashes''
===== PHP: Connecting to a Database =====
* To connect PHP to a database, use ''mysql_connect'', which can have three parameters:
- ''host'' (default is localhost)
- ''username'' (default is the username of the PHP script -- i.e. effective user id of web server)
- ''password'' (default is blank, which works if the database does not require a password)
$db = mysql_connect();
* Usually checked for failure
* Close the connection to the database with ''mysql_close''
===== PHP: Selecting database and executing queries =====
* //To focus MySQL//:
mysql_select_db("cars");
* //Requesting MySQL Operations//:
* Call ''mysql_query'' with a string parameter, which is an SQL command:
$query = "SELECT * from States";
$result = mysql_query($query);
===== PHP: Dealing with the result =====
* 1. Get the number of rows in the result:
$num_rows = mysql_num_rows($result);
* 2. Get the rows with ''mysql_fetch_array'':
for ($row_num = 0; $row_num < $num_rows; $row_num++) {
$row = mysql_fetch_array($result);
print " Result row number" . ($row_num + 1) . " State_id: ";
print htmlspecialchars($row["State_id"]);
print "State: ";
:
}
===== 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 ''(1, Alabama)'', the array has:
((0, 1),
(State_id, 1),
(1, Alabama),
(State, Alabama))
* If the row is indexed with numbers, the element values are returned
===== PHP: Accessing field values =====
* The following displays all field values from ''$row'':
$values = array_values($row);
for ($index = 0; $index < $num_fields / 2; $index++)
print "$values[2 * $index + 1]
";
===== PHP: Displaying all column names =====
* To display all column names:
$keys = array_keys($row);
for ($index = 0; $index < $num_fields; $index++)
print "$keys[2 * $index + 1]
";
===== PHP/MySQL: Example =====
* [[http://eespectre.swan.ac.uk/~eechris/eg-259/examples/lecture19/carsdata.html|carsdata.html]] ( [[http://localhost/eg-259/examples/lecture19/carsdata.html|carsdata.html @ localhost]] ) uses a form to collect a query against the cars database
* [[http://eespectre.swan.ac.uk/~eechris/eg-259/examples/lecture19/access_cars.php|access_cars.php]] ( [[http://localhost/eg-259/examples/lecture19/access_cars.php|access_cars.php @ localhost]] ) is a PHP script to access the cars database through MySQL and execute the query passed through ''$_POST''
----
Access to the corvettes database
Try:
SELECT Vette_id, Body_style, Year, States.State
FROM Corvettes, States
WHERE Corvettes.State = States.State_id AND States.State = 'California'
Please enter your query:
* //The query result page//:
Access the cars database with MySQL
The query is: " . $query_html . "";
// Execute the query
$result = mysql_query($query);
if (!$result) {
print "Error - the query could not be executed";
$error = mysql_error();
print "" . $error . "
";
exit ;
}
// Display the results in a table
print " Query Results
";
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; $index++)
print "" . $keys[2 * $index + 1] . " ";
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; $index++) {
$value = htmlspecialchars($values[2 * $index + 1]);
print "" . $value . " ";
}
print " ";
$row = mysql_fetch_array($result);
}
print "
";
?>
===== PHP/MySQL: Result =====
* //Result//:
{{eg-259:l19-access_cars_php.png?800|A result of a typical query access cars}}
===== 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 ''action'' attribute of the form to the name of the combined document file
- 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:
- The PHP code to test this has the form:
$stage = $_POST["stage"];
if (!IsSet($stage))) { ... }
* the ''then'' clause includes the form processing;
* the ''else'' clause includes the form display
===== Combining Form and Results =====
* Here is the result: [[http://eespectre.swan.ac.uk/~eechris/eg-259/examples/lecture19/access_cars2.php|access_cars2.php]] ( [[http://localhost/eg-259/examples/lecture19/access_cars2.php|access_cars2.php @ localhost]] )
----
* //The code//:
Access the cars database with MySQL
Try:
SELECT Vette_id, Body_style, Year, States.State
FROM Corvettes, States
WHERE Corvettes.State = States.State_id AND States.State = 'California'
Please enter your query:
The query is: " . $query_html . "";
// Execute the query
$result = mysql_query($query);
if (!$result) {
print "Error - the query could not be executed";
$error = mysql_error();
print "" . $error . "
";
exit;
}
// Display the results in a table
print " Query Results
";
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; $index++) {
print "" . $keys[2 * $index + 1] . " ";
}
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; $index++){
$value = htmlspecialchars($values[2 * $index + 1]);
print "" . $value . " ";
}
print " ";
$row = mysql_fetch_array($result);
}
print "
";
}
?>
===== Summary of this Lecture =====
//Introducing Databases and Database Access with PHP/MySQL//
* [[eg-259:lecture19#relational_databases|Relational Databases]]
* [[eg-259:extras:dbandsql|Introduction to SQL]] (not examined)
* [[eg-259:lecture19#architectures_for_database_access|Architectures for Database Access]]
* [[eg-259:lecture19#the_mysql_database_system|Introducing MySQL]]
* [[eg-259:lecture19#database_access_with_php_mysql|Database Access with PHP and MySQL]]
====== 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/server configuration for Web access to a database?
- Why are two-tier client/server configurations sometimes inadequate?
- 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 ''auto_increment'' do?
- 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 ''mysql_select_db'' function?
- 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 ''mysql_fetch_array'' do?
- Explain the exact form of the value returned by ''mysql_fetch_array''.
- 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 ''access_cars.php'' to handle ''UPDATE'' and ''INSERT'' SQL commands.
===== What's Next? =====
**Web Application Frameworks**
* [[eg-259:lecture20#three-tiers_are_not_enough|Three-tiers are not enough!]]
* [[eg-259:lecture20#the_problem_with_web_applications|The Problem of Web Application Development]]
* [[eg-259:lecture20#web_application_frameworks_1|Web Application Frameworks]]
* [[eg-259:lecture20#design_patterns_3_4|Design Patterns]]
* [[eg-259:lecture20#case-studyruby-on-rails|Case-Study: Ruby-on-Rails]]
[[eg-259:lecture18|Previous Lecture]] | [[eg-259:home]] | [[eg-259:lecture20|Next Lecture]]