Web Programming Step by Step

Chapter 11
Relational Databases and SQL

References: SQL syntax reference, w3schools tutorial

Except where otherwise noted, the contents of this presentation are Copyright 2009 Marty Stepp and Jessica Miller.

Valid XHTML 1.1 Valid CSS!

11.1: Database Basics

Relational databases

Why use a database? (11.1.1)

Database software

Example world database (11.1.2)

Countries
Other columns: region, surface_area, life_expectancy, gnp_old, local_name, government_form, capital, code2
code name continent independance_year population gnp head_of_state ...
AFG Afghanistan Asia 1919 22720000 5976.0 Mohammad Omar ...
NLD Netherlands Europe 1581 15864000 371362.0 Beatrix ...
........................
Cities
id name country_code district population
3793New YorkUSANew York8008278
1Los AngelesUSACalifornia3694820
...............
CountriesLanguages
country_codelanguageofficialpercentage
AFGPashtoT52.4
NLDDutchT95.6
............

11.2: SQL

SQL basics

SELECT name FROM Cities WHERE id = 17;

INSERT INTO Countries VALUES ('SLD', 'ENG', 'T', 100.0);

Issuing SQL commands directly in MySQL (11.2.1 - 11.2.2)

SHOW DATABASES;
USE database;
SHOW TABLES;
$ mysql -u yourusername -p
Password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> USE world;
Database changed

mysql> SHOW TABLES;
+--------------------+
| Cities             | 
| Countries          | 
| CountriesLanguages | 
+--------------------+
3 rows in set (0.00 sec)

The SQL SELECT statement (11.2.3)

SELECT column(s) FROM table;
SELECT name, code FROM Countries;
namecode
ChinaCHN
United StatesIND
IndonesiaUSA
BrazilBRA
PakistanPAK
......

The DISTINCT modifier

SELECT DISTINCT column(s) FROM table;
SELECT language
FROM CountriesLanguages;
language
Dutch
English
English
Papiamento
Spanish
Spanish
Spanish
...
SELECT DISTINCT language
FROM CountriesLanguages;
language
Dutch
English
Papiamento
Spanish
...

The WHERE clause (11.2.4)

SELECT column(s) FROM table WHERE condition(s);
SELECT name, population FROM Cities WHERE country_code = "FSM";
namepopulation
Weno22000
Palikir8600

More about the WHERE clause

WHERE column operator value(s)
SELECT name, gnp FROM Countries WHERE gnp > 2000000;
codenamegnp
JPNJapan3787042.00
DEUGermany2133367.00
USAUnited States8510700.00
.........

Multiple WHERE clauses: AND, OR

SELECT * FROM Cities WHERE code = 'USA' AND population >= 2000000;
idnamecountry_codedistrictpopulation
3793New YorkUSANew York8008278
3794Los AngelesUSACalifornia3694820
3795ChicagoUSAIllinois2896016
...............

Approximate matches: LIKE

WHERE column LIKE pattern
SELECT code, name, population FROM Countries WHERE name LIKE 'United%';
codenamepopulation
AREUnited Arab Emirates2441000
GBRUnited Kingdom59623400
USAUnited States278357000
UMIUnited States Minor Outlying Islands0

Sorting by a column: ORDER BY (11.2.5)

ORDER BY column(s)
SELECT code, name, population FROM Countries
WHERE name LIKE 'United%' ORDER BY population;
codenamepopulation
UMIUnited States Minor Outlying Islands0
AREUnited Arab Emirates2441000
GBRUnited Kingdom59623400
USAUnited States278357000

The SQL INSERT statement (11.2.6)

INSERT INTO table
VALUES (value, value, ..., value);
INSERT INTO student 
VALUES (789, "Nelson", "muntz@fox.com");

The SQL UPDATE and DELETE statements

UPDATE table
SET column = value,
    ...,
    column = value
WHERE condition;

DELETE FROM table
WHERE condition;
UPDATE student
SET email = "lisasimpson@gmail.com"
WHERE SID = 888;

DELETE FROM student WHERE SID < 800;

11.3: Databases and PHP

PHP MySQL functions

name description
mysql_connect connects to a database server
mysql_select_db chooses which database on server to use (similar to SQL USE database; command)
mysql_query performs a SQL query on the database
mysql_real_escape_string encodes a value to make it safe for use in a query
mysql_fetch_array, ... returns the query's next result row as an associative array
mysql_close closes a connection to a database

Complete PHP MySQL example

# connect to world database on local computer
$db = mysql_connect("localhost", "traveler", "packmybags");
mysql_select_db("world");

# execute a SQL query on the database
$results = mysql_query("SELECT * FROM Countries WHERE population > 100000000;");

# loop through each country
while ($row = mysql_fetch_array($results)) {
	?>

	<li> <?= $row["name"] ?>, ruled by <?= $row["head_of_state"] ?> </li>

	<?php
}
?>

Connecting to MySQL: mysql_connect (11.3.1)

mysql_connect("host", "username", "password");
mysql_select_db("database name");
# connect to world database on local computer
mysql_connect("localhost", "traveler", "packmybags");
mysql_select_db("world");

Performing queries: mysql_query (11.3.2)

mysql_connect("host", "username", "password");
mysql_select_db("database name");

$results = mysql_query("SQL query");
...
$results = mysql_query("SELECT * FROM Cities WHERE code = 'USA'
                        AND population >= 2000000;");

Result rows: mysql_fetch_array

mysql_connect("host", "username", "password");
mysql_select_db("database name");
$results = mysql_query("SQL query");

while ($row = mysql_fetch_array($results)) {
	do something with $row;
}

Error-checking: mysql_error (11.3.3)

if (!mysql_connect("localhost", "traveler", "packmybags")) {
	die("SQL error occurred on connect: " . mysql_error());
}
if (!mysql_select_db("world")) {
	die("SQL error occurred selecting DB: " . mysql_error());
}
$query = "SELECT * FROM Countries WHERE population > 100000000;";
$results = mysql_query($query);
if (!$results) {
	die("SQL query failed:\n$query\n" . mysql_error());
}

Complete example w/ error checking

# connect to world database on local computer
check(mysql_connect("localhost", "traveler", "packmybags"), "connect");
check(mysql_select_db("world"), "selecting db");

# execute a SQL query on the database
$query = "SELECT * FROM Countries WHERE population > 100000000;";
$results = mysql_query($query);
check($results, "query of $query");

# loop through each country
while ($row = mysql_fetch_array($results)) {
	?>
	<li> <?= $row["name"] ?>, ruled by <?= $row["head_of_state"] ?> </li>
	<?php
}

# makes sure result is not false/null; else prints error
function check($result, $message) {
	if (!$result) {
		die("SQL error during $message: " . mysql_error());
	}
}
?>

Other MySQL PHP functions

name description
mysql_num_rows returns number of rows matched by the query
mysql_num_fields returns number of columns per result in the query
mysql_list_dbs returns a list of databases on this server
mysql_list_tables returns a list of tables in current database
mysql_list_fields returns a list of fields in the current data
complete list

11.4: Multi-table Queries

Example simpsons database

students
idnameemail
123Bartbart@fox.com
456Milhousemilhouse@fox.com
888Lisalisa@fox.com
404Ralphralph@fox.com
teachers
idname
1234Krabappel
5678Hoover
9012Stepp
courses
idnameteacher_id
10001Computer Science 1421234
10002Computer Science 1435678
10003Computer Science 190M9012
10004Informatics 1001234
grades
student_idcourse_idgrade
12310001B-
12310002C
45610001B+
88810002A+
88810003A+
40410004D+

Querying multi-table databases

When we have larger datasets spread across multiple tables, we need queries that can answer high-level questions such as:

To do this, we'll have to join data from several tables in our SQL queries.

Cross product with JOIN (11.4.1)

SELECT column(s) FROM table1 JOIN table2;
SELECT * FROM students JOIN grades;
idnameemailstudent_idcourse_idgrade
123Bartbart@fox.com12310001B-
404Ralphralph@fox.com12310001B-
456Milhousemilhouse@fox.com12310001B-
888Lisalisa@fox.com12310001B-
123Bartbart@fox.com12310002C
404Ralphralph@fox.com12310002C
... (24 rows returned)

Joining with ON clauses (11.4.2)

SELECT column(s)
FROM table1
     JOIN table2 ON condition(s)
     ...
     JOIN tableN ON condition(s);
SELECT *
FROM students
     JOIN grades ON id = student_id;

Join example

SELECT *
FROM students
     JOIN grades ON id = student_id;
idnameemailstudent_idcourse_idgrade
123Bartbart@fox.com12310001B-
123Bartbart@fox.com12310002C
404Ralphralph@fox.com40410004D+
456Milhousemilhouse@fox.com45610001B+
888Lisalisa@fox.com88810002A+
888Lisalisa@fox.com88810003A+

Filtering columns in a join

SELECT name, course_id, grade
FROM students
     JOIN grades ON students.id = student_id;
namecourse_idgrade
Bart10001B-
Bart10002C
Ralph10004D+
Milhouse10001B+
Lisa10002A+
Lisa10003A+

Giving names to tables

SELECT name, g.*
FROM students s
     JOIN grades g ON s.id = g.student_id;
namestudent_idcourse_idgrade
Bart12310001B-
Bart12310002C
Ralph40410004D+
Milhouse45610001B+
Lisa88810002A+
Lisa88810003A+

Filtered join (JOIN with WHERE) (11.4.3)

SELECT name, course_id, grade
FROM students s
     JOIN grades g ON s.id = g.student_id
WHERE s.id = 123;
namecourse_idgrade
Bart10001B-
Bart10002C

Multi-way join

SELECT c.name
FROM courses c
     JOIN grades g ON g.course_id = c.id
     JOIN students bart ON g.student_id = bart.id
WHERE bart.name = 'Bart' AND g.grade <= 'B-';
name
Computer Science 142

A suboptimal query

Improved query

Practice queries

Example imdb database (11.1.2)

actors
idfirst_namelast_namegender
433259WilliamShatnerM
797926BritneySpearsF
831289SigourneyWeaverF
...
movies
idnameyearrank
112290Fight Club19998.5
209658Meet the Parents20007
210511Memento20008.7
...
roles
actor_idmovie_idrole
433259313398Capt. James T. Kirk
433259407323Sgt. T.J. Hooker
797926342189Herself
...

IMDb query example

[stepp@webster ~]$ mysql -u myusername -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

mysql> use imdb_small;
Database changed

mysql> select * from actors where first_name like '%mick%';
+--------+------------+-----------+--------+
| id     | first_name | last_name | gender |
+--------+------------+-----------+--------+
|  71699 | Mickey     | Cantwell  | M      | 
| 115652 | Mickey     | Dee       | M      | 
| 470693 | Mick       | Theo      | M      | 
| 716748 | Mickie     | McGowan   | F      | 
+--------+------------+-----------+--------+
4 rows in set (0.01 sec)

IMDb table relationships / ids (11.4.3)

IMDb tables tree

Designing a query (11.4.4)