fit3084: Database Access via the WWW

FIT3084: (mySQL) Database Access via the WWW

In the previous lecture:

Ajax is a collection of web technologies (CSS, DOM, JavaScript, XML) used to asynchronously request and receive data from a web server.
Ajax smooths the interaction between a user and web server.
Ajax requires the use of an XMLHttpRequest object to send data to a server, monitor the request and receive the reply.

In this lecture:

What is SQL?
How can a mySQL database be accessed through a WWW interface?
How does PHP simplfy the task of accessing a database and returning the results of a search?

References

Sebasta, R.W., "Programming the WWW 2009", 5th edition, Pearson, chapter 13.

http://www.mysql.com/

http://au.php.net/manual/en/ref.mysql.php

About

mySQL is an open-source database system that implements SQL, the Structured Query Language which has become a standard for database access.
Data may be added to a mySQL database, deleted and changed, all via a PHP script.

Relational Database Examples

A database is an organised collection of data.
Databases permit easy access to data: additions, retrievals and modifications.
A relational database organises data as a collection of related tables.
These tables consist of rows (entities) and columns (attributes).

Example 1.

For every attribute of each entity there could be a column with true or false in it.

ID	Type	Scales	Bark	Fur
1	fish	true	false	false
2	tree	false	true	false
3	reptile	true	false	false
4	marsupial	false	false	true
5	dog	false	false	true

The primary key for each entity must be a unique id.
All other fields may contain any data.

The table design above is not ideal. It is better to have one attribute such as "distinctive features" and enter relevant data (scales, bark, legs, wings or fur) into it. What if an entity has more than one attribute simultaneously?

In a relational database, there may be multiple tables, some establishing the relations between tables.

Example 2.

A more complete example organisms table.

life_id	common_name	type_id	length
1	Murray Cod	3	1
2	Victorian Ash	6	100
3	Coastal Taipan	5	3
4	Hairy-Nosed Wombat	4	1
5	Dingo	2	1.2
6	Laughing Kookaburra	1	0.4

The organisms table needs to be used in conjunction with these:

type_id	type
1	bird
2	dog
3	fish
4	marsupial
5	reptile
6	tree

feature_id	feature
1	bark
2	branches
3	feathers
4	fins
5	fur
6	leaves
7	legs
8	scales
9	tail
10	wings

life_id	feature_id
1	8
1	4
1	9
2	1
2	2
2	6
3	8
4	5
4	7
5	5
5	7
5	9
6	3
6	10

region_id	region
1	N
2	NE
3	E
4	SE
5	S
6	SW
7	W
8	NW
9	Central

life_id	region_id
1	4
2	4
3	2
4	4
5	9
5	7
5	8
5	1
5	2
5	3
6	2
6	3
6	4
6	6

types
Abbreviates the names of the organism types. (Indexed from column 3 of the main table)

features
Abbreviates the names of the organism features.

organism_features
Cross-references the primary keys of the organisms with their features.

regions
Abbreviates the habitat locations within Australia.

organism_regions
Cross-references the primary keys of the organisms' habitat range.

About SQL - Structured Query Language

SQL is a standard way of accessing and modifying data in a relational database.
SQL is supported by all major database vendors.
SQL reserved words are not case-sensitive.
The names of database tables and columns may or may not be case sensitive depending on the database vendor.
Whitespace between reserved words in SQL is not significant.
Single 'quotes' denote character strings.

About mySQL and PHP

mySQL is a free, reliable and widely used relational database system.
mySQL implements SQL.
Documentation is available from http://www.mysql.com/
A mySQL database can be accessed form the command line by typing in commands e.g. mysql -h host_name
We will not discuss command-line mySQL access, but rather access through PHP scripts and the WWW.
PHP access to mySQL parallels command-line access:
- A query to the database is constructed in a string variable, e.g. $query
- The mysql_query( ) method is called, with $query as a parameter.
- The result is kept in a variable for interpretation, $result = mysql_query($query);

Login to mySQL using PHP	mysql_connect(database-host-name, username, password) or die(mysql_error());
Select an existing database...	mysql_select_db(database-name) or die(mysql_error());
...or create a new database	$query = 'CREATE DATABASE database-name'; mysql_query($query);

mySQL commands

Here are some SQL commands. The tables listed above will be used to illustrate them.

CREATE DATABASE	CREATE DATABASE database-name;
Create a new database called wildlife	CREATE DATABASE wildlife;

CREATE TABLE

CREATE TABLE table-name
(column1-name column1-type [other options],
 column2-name column2-type [other options],
     ...   ...
 columnN-name columnN-type [other options]);

Create a new table called organisms

With columns named life_id, type_id, length.
With column types INT, CHAR string of length 15, REAL number.
NOT NULL column must be assigned a value for every entity.
PRIMARY KEY column must have a unique value for every entity.
AUTO_INCREMENT values of this column don't need to be specified when adding entities to the database. Value is automatically given as one plus the previous value in this column, 1,2,3...

CREATE TABLE organisms
(life_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 type_id INT NOT NULL,
 common_name CHAR(15) NOT NULL,
 length REAL);

DESCRIBE

DESCRIBE database-name;

Describe the database organisms

DESCRIBE organisms;

Output:

Field	Type	Null	Key	Default	Extra
life_id	int(6)	-	PRI	0	auto_increment
common_name	varchar(15)	-	-	-	-
type_id	int(6)	-	-	0	-
length	double(16,4)	-	-	0.0000	-

SHOW TABLES

Shows the tables in the current database

SHOW TABLES;

Output:

Tables in wildlife
organisms

INSERT INTO / VALUES

Insert values into a table in the database

INSERT INTO table-name
(column1-name, column2-name, ..., columnN-name)
VALUES
(value1, value2, ..., valueN);

This example inserts the first entry into the organisms table, i.e. "Murray Cod" is inserted into the column called common_name, 3 into type_id and 1 into length. Below we insert the second entry of organisms.

If a column specified as NOT NULL is not included in the INSERT command, mySQL will report an error.

INSERT INTO organisms
(common_name, type_id, length)
VALUES
("Murray Cod", 3, 1);

INSERT INTO organisms
(common_name, type_id, length)
VALUES
("Victorian Ash", 6, 100);

SELECT

Select a set of columns from a table in the current database

SELECT column-names FROM table-names [WHERE condition];

To select all columns of all the entities in the organism table.

SELECT * FROM organisms;

Output:

life_id	common_name	type_id	length
1	Murray Cod	3	1.0000
2	Victorian Ash	6	100.0000

To select the common_name column of all entities in the organism table with a length less than 10.

SELECT common_name
FROM organisms
WHERE length < 10;

Output:

life_id	common_name	type_id	length
1	Murray Cod	3	1.0000

A join of two tables is specified by SELECT-ing multiple tables and including a compound WHERE clause. This example selects all columns from all entities in the organisms and types tables where the type is tree. (The types table must be added and its entries inserted before this would work of course!)

SELECT *
FROM organisms, types
WHERE organisms.type_id = types.type_id AND types.type = 'tree';

Output:

life_id	common_name	type_id	length	5
2	Victorian Ash	6	100.0000	tree

Other Useful mySQL commands.

UPDATE

Update any entities in the named table with the specified column value, using the new values listed.

UPDATE table-name
SET column1-name = value1, column2-name=value2, ..., columnN-name=valueN
WHERE column-name = value;

DELETE

Delete any rows of a table that match the WHERE clause.

DELETE FROM table-name
WHERE column-name=value;

DROP

Delete any entire database or table. The optional IF EXISTS avoids errors in case the table or database doesn't exist.

DROP (TABLE | DATABASE) [IF EXISTS] name;

Interpretting mySQL Queries.

mysql_query( ) doesn't return the output shown above directly as XHTML.

It usually returns a table that can be interpretted with the help of a PHP function like this...

function outputResults($result)
{
  // Get the number of rows in the result of the query
  $num_rows = mysql_num_rows($result);

  // Get the number of columns in the result of the query
  $num_fields = mysql_num_fields($result);

  // Get the first row of the data.
  // This will be the header row that contains the names of the columns
  // We will acquire it in the form of an array.
  $rowData = mysql_fetch_array($result);

  // Extract from the header the names of the columns
  $keys = array_keys($rowData);

  // Create an XHTML table to output the
  // result of this PHP script to the browser.
  print "<table border=1>";
  print "<tr align='left'>";

  // Make the header row...
  for ($col=0; $col<$num_fields; $col++)
  {
     print "<th>";
     print $keys[2*$col +1];                // <--------- See note on mySQL result row interpretation below
     print "</th>";
  }
  print "</tr>";
 
  // Iterate through the data rows
  for ($row=0; $row<$num_rows; $row++)
  {
     print "<tr align='left'>";
     // Extract the contents of each data row as an array
     $rowValues = array_values($rowData);
 
     for ($col=0; $col<$num_fields; $col++)
     {
        // Extract each value that corresponds to a column
        $value = $rowValues[2 * $col + 1]); // <--------- See note on mySQL result row interpretation below
        print "<th>" . $value . "</th>";
     }
     print "</tr>";

     // Get the next row of data
     $rowData = mysql_fetch_array($result);
  }
 
  print "</table>";

}

mySQL Result Row Interpretation

PHP arrays are hashes.

The results (confusingly) of a mySQL query therefore contain double sets of elements, one with a numeric key, and one with a string key.

E.g. The result that would be returned for a SELECT that matches the first data row of the types table

type_id	type
1	bird

would be:

(0,1), (type_id, 1), (1, bird), (type, bird)

If this data was stored in $result then:

The data can be indexed with numbers: $result[0] would be 1 (the value of the first field in the row) and $result[1] would be bird, the value of the second field in the row.
The data can also be indexed with strings: $result["type"] would be bird.

..so the result rows have twice as many elements as there are fields in the result.

To neatly extract the data once we must extract every second entry of the table.

This explains the highlighted lines in the example above. I.e...

print $keys[2*$col +1];

...and...

$value = $rowValues[2 * $col + 1]);

Example PHP Database Access

This PHP file contains a complete worked example, illustrating the concepts discussed in the examples above. To run this code you will need access to a mySQL database.
Run this PHP database access script on the lecturer's machine with some extra debugging information printed out to show you the queries.

This lecture's key point(s):

Databases are boring but they are very useful! :-)
PHP makes database access through the WWW easy.

Courseware | Lecture notes