This is an old revision of the document!
Table of Contents
~~SLIDESHOW~~
Database Access through the Web
Lecture 19: To be given on Tuesday 1st December, 2008.
Lecturer: Dr Chris P. Jobling.
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 available.
Based on Chapter 14 of Robert W. Sebasta, Programming the World-Wide Web, 3rd Edition, Addison Wesley, 2006. and Chapters 11 and 13 of Chris Bates, Web Programming: Building Internet Applications, 3rd Edition, John Wiley, 2006. Chris Bates, Web Programming: Building Internet Applications, 3rd Edition, John Wiley, 2006.
Contents of this Lecture
Introducing Databases and Database Access with PHP/MySQL
- Introduction to SQL (not examined)
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
NOT NULL
constraint specify in a column of aCREATE TABLE SQL
command? - What does an asterisk specify when it appears as the value of a
SELECT
clause?
Assumed Knowledge (continued)
You should be able to answer the following questions:
- What is specified by the
WHERE
clause of aSELECT
command? - How are the column names associated with the values in an
INSERT
command? - What is the purpose of an
UPDATE
command: - What exactly is a table join, and how is one specified in SQL?
If you have any difficulty, read the additional notes on SQL.
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 entities
- Much more detail in module CS-219
An Example Database
- A relational database for used Corvettes that are for sale
- 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
“Reading” the diagram we have a corvette belongs to a state and there are many corvettes in a state. Corvettes have one or more pieces of equipment, equipment can belong to more than one corvette. 1 to many relationships indicate a foreign key in the many side. Many to many relationships require a relationship table.
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 Create, Retrieve, Update, and Delete records in a web application we need some knowledge of SQL
- Some examples follow:
- Use
CREATE TABLE
to create a database - Creating new records with
INSERT
- Retrieving data with
SELECT
- Udating records with
UPDATE
- Deleting records with
DELETE
Use CREATE TABLE to create a database
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
andEquipment
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 itsFROM
clause and also has a compoundWHERE
clause - For our example, we must have three
WHERE
conditionsVette_ids
fromCorvettes
andCorvettes_Equipment
must matchEquip
fromCorvettes_Equipment
must match theEquip_id
fromEquipment
- The
Equip
fromEquipment
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
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
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
<note>
host
is the name of the MySQL server – default is the user's machineusername
is that of the owner of the database – default is the name used to log into the system
</note>
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:
Database changed
- 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:
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 (
?
,“
,\
, andNULL
), which could come from$_GET
and$_POST
- To fix these,
magic_quotes_gpc
in thePHP.ini
file is set toON
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
isO'Shanter
, it would prematurely terminate the query string - But with
magic_quotes_gpc
on, it will be converted toO\'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 withstripslashes
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 "<p> 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] <br />";
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] <br />";
PHP/MySQL: Example
- carsdata.html ( carsdata.html @ localhost ) uses a form to collect a query against the cars database
- 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
<note warning>
For reasons of security, examples are designed to work from a local installation. I used DocumentRoot/eg-259/examples/lecture19
and created the cars database using phpMyAdmin. Data for cars database etc, is available on Blackboard site.
</note>
- The initial form:
<?xml version = "1.0" encoding = "utf-8"?> <!DOCTYPE html PUBLIC "-//w3c//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <!-- 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 xmlns = "http://www.w3.org/1999/xhtml" xml:lang="en"> <head><title> Access to the corvettes database </title> </head> <body> <p> Try: <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 action = "access_cars.php" method = "post"> <textarea rows = "2" cols = "80" name = "query" > </textarea> <br /><br /> <input type = "reset" value = "Reset" /> <input type = "submit" value = "Submit request" /> </form> </p> </body> </html>
- The query result page:
<?php echo("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n"); ?> <!DOCTYPE html PUBLIC "-//w3c//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <!-- access_cars.php A PHP script to access the cars database through MySQL --> <html xmlns = "http://www.w3.org/1999/xhtml" xml:lang="en"> <head> <title> Access the cars database with MySQL </title> </head> <body> <?php // Connect to MySQL $db = mysql_connect("localhost", "eg-259", ""); if (!$db) { print "Error - Could not connect to MySQL"; exit; } // Select the cars database $er = mysql_select_db("cars"); 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['query']; trim($query); $query = stripslashes($query); // Display the query, after fixing html characters $query_html = htmlspecialchars($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; } // Display the results in a table print "<table border='1'><caption> <h2> Query Results </h2> </caption>"; print "<tr>"; // 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 "<th>" . $keys[2 * $index + 1] . "</th>"; print "</tr>"; // 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> "; } print "</tr>"; $row = mysql_fetch_array($result); } print "</table>"; ?> </body> </html>
PHP/MySQL: 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
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:
<input type = "hidden" name = "stage" value = "1" />
- 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 us the result: access_cars2.php ( access_cars2.php @ localhost )
- The code:
<?php echo("<?xml version=\"1.0\" encoding=\"utf-8\"?>\n"); ?> <!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 access the cars database through MySQL to get and display the result of the query. --> <html xmlns = "http://www.w3.org/1999/xhtml" xml:lang="en"> <head> <title> Access the cars database with MySQL </title> </head> <body> <?php $stage = $_POST["stage"]; if (!IsSet($stage)) { ?> <p> Try: <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 /> <input type = "hidden" name = "stage" value = "1" /> <input type = "submit" value = "Submit request" /> </form> </p> <?php } else { // $stage was set, so process the query // Connect to MySQL $db = mysql_connect("localhost", "eg-259", ""); if (!$db) { print "Error - Could not connect to MySQL"; exit; } // Select the cars database $er = mysql_select_db("cars"); if (!$er) { print "Error - Could not select the cars database"; exit; } // Clean up the given query (delete leading and trailing whitespace) $query = $_POST['query']; 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>"; // 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; } // Display the results in a table print "<table border='1'><caption> <h2> Query Results </h2> </caption>"; print "<tr>"; // 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 "<th>" . $keys[2 * $index + 1] . "</th>"; print "</tr>"; // 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> "; } print "</tr>"; $row = mysql_fetch_array($result); } print "</table>"; } ?> </body> </html>
Summary of this Lecture
Introducing Databases and Database Access with PHP/MySQL
- Introduction to SQL (not examined)
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 handleUPDATE
andINSERT
SQL commands.