Web Programming Step by Step, 2nd Edition

Chapter 13: Relational Databases and SQL

Except where otherwise noted, the contents of this document are Copyright 2012 Marty Stepp, Jessica Miller, and Victoria Kirst. All rights reserved. Any redistribution, reproduction, transmission, or storage of part or all of the contents in any form is prohibited without the author's expressed written permission.

Valid XHTML 1.1 Valid CSS!

13.1: Database Basics

Relational databases

Why use a database?

Database software

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+

Example world database

countries Other columns: region, surface_area, life_expectancy, gnp_old, local_name, government_form, capital, code2
code name continent independence_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
...............
languages
country_codelanguageofficialpercentage
AFGPashtoT52.4
NLDDutchT95.6
............

Example imdb database

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
...
movies_genres
movie_idgenre
209658Comedy
313398Action
313398Sci-Fi
...
directors
idfirst_namelast_name
24758DavidFincher
66965JayRoach
72723WilliamShatner
...
movies_directors
director_idmovie_id
24758112290
66965209658
72723313398
...

13.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

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 | 
| languages | 
+-----------+
3 rows in set (0.00 sec)

The SQL SELECT statement

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 languages;
language
Dutch
English
English
Papiamento
Spanish
Spanish
Spanish
...
SELECT DISTINCT language
FROM languages;
language
Dutch
English
Papiamento
Spanish
...

The WHERE clause

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

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

Limiting rows: LIMIT

LIMIT number
SELECT name FROM cities WHERE name LIKE 'K%' LIMIT 5;
name
Kabul
Khulna
Kingston upon Hull
Koudougou
Kafr al-Dawwar

Learning about databases and tables

SHOW DATABASES;
SHOW TABLES;
DESCRIBE table;
SHOW TABLES;
+-----------+
| students  | 
| courses   | 
| grades    | 
| teachers  |
+-----------+    4 rows in set

The SQL INSERT statement

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

More about INSERT

INSERT INTO table (columnName, columnName, ..., columnName)
VALUES (value, value, ..., value);
INSERT INTO students (name, email)
VALUES ("Lewis", "lewis@fox.com");

The SQL REPLACE statement

REPLACE INTO table (columnName, columnName, ..., columnName)
VALUES (value, value, ..., value);
REPLACE INTO students
VALUES (789, "Martin", "prince@fox.com");

The SQL UPDATE statement

UPDATE table
SET column = value,
    ...,
    column = value
WHERE column = value;
UPDATE students
SET email = "lisasimpson@gmail.com"
WHERE id = 888;

The SQL DELETE statement

DELETE FROM table
WHERE condition;
DELETE FROM students
WHERE id = 888;

Creating and deleting an entire database

CREATE DATABASE name;
DROP DATABASE name;
CREATE DATABASE warcraft;

Creating and deleting a table

CREATE TABLE name (
	columnName type constraints,
	...
	columnName type constraints
);
DROP TABLE name;
CREATE TABLE students (
	id INTEGER,
	name VARCHAR(20),
	email VARCHAR(32),
	password VARCHAR(16)
);

SQL data types

Column constraints

CREATE TABLE students (
	id INTEGER UNSIGNED NOT NULL PRIMARY KEY,
	name VARCHAR(20) NOT NULL,
	email VARCHAR(32),
	password VARCHAR(16) NOT NULL DEFAULT "12345"
);

Rename a table

ALTER TABLE name RENAME TO newName;
ALTER TABLE students RENAME TO children;

Add/remove/modify a column in a table

ALTER TABLE name
	ADD COLUMN columnName type constraints;

ALTER TABLE name DROP COLUMN columnName;

ALTER TABLE name
	CHANGE COLUMN oldColumnName newColumnName type constraints;

13.3: 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

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

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 id = student_id;
namecourse_idgrade
Bart10001B-
Bart10002C
Ralph10004D+
Milhouse10001B+
Lisa10002A+
Lisa10003A+

Filtered join (JOIN with WHERE)

SELECT name, course_id, grade
FROM students
JOIN grades ON id = student_id
WHERE name = 'Bart';
namecourse_idgrade
Bart10001B-
Bart10002C

What's wrong with this?

SELECT name, id, course_id, grade
FROM students
JOIN grades ON id = 123
WHERE id = student_id;
nameidcourse_idgrade
Bart12310001B-
Bart12310002C

Giving names to tables

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

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

Designing a query

Example imdb database

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
...
movies_genres
movie_idgenre
209658Comedy
313398Action
313398Sci-Fi
...
directors
idfirst_namelast_name
24758DavidFincher
66965JayRoach
72723WilliamShatner
...
movies_directors
director_idmovie_id
24758112290
66965209658
72723313398
...

IMDb table relationships / ids

IMDb tables tree

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 practice queries

13.4: Databases and PHP

Querying a Database in PHP with PDO

$name = new PDO("dbprogram:dbname=database;host=server", username, password);
$name->query("SQL query");
# connect to world database on local server
$db = new PDO("mysql:dbname=world;host=localhost", "traveler", "packmybags");
$db->query("SELECT * FROM countries WHERE population > 100000000;");

Result rows: query

$db = new PDO("mysql:dbname=world;host=localhost", "traveler", "packmybags");
$rows = $db->query("SELECT * FROM countries WHERE population > 100000000;");
foreach ($rows as $row) {
	do something with $row;
}

A complete example

$db = new PDO("mysql:dbname=imdb_small", "jessica", "guinness");
$rows = $db->query("SELECT * FROM actors WHERE last_name LIKE 'Del%'");
foreach ($rows as $row) {
	?>
	<li> First name: <?= $row["first_name"] ?>,
	     Last name:  <?= $row["last_name"]  ?> </li>
	<?php
}
  • First name: Benicio, Last name: Del Toro
  • First name: Michael, Last name: Delano
  • ...

PDO object methods

name description
query performs a SQL SELECT query on the database
exec performs a SQL query that modifies the database (INSERT, DELETE, UPDATE, etc.)
getAttribute,
setAttribute
get/set various DB connection properties
quote encodes a value for use within a query

Including variables in a query

# get query parameter for name of movie
$title = $_GET["movietitle"];
$rows = $db->query("SELECT year FROM movies WHERE name = '$title'");

Quoting variables

# get query parameter for name of movie
$title = $_GET["movietitle"];
$title = $db->quote($title);
$rows = $db->query("SELECT year FROM movies WHERE name = $title");

Database/query errors

$db = new PDO("mysql:dbname=imdb_small", "jessica", "guinness");
$rows = $db->query("SEEELECT * FROM movies WHERE year = 2000");   # FALSE

Exceptions for errors

$db = new PDO("mysql:dbname=imdb_small", "jessica", "guinness");
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$rows = $db->query("SEEELECT * FROM movies WHERE year = 2000");   # kaboom!

Catching an exception

try {
	statement(s);
} catch (ExceptionType $name) {
	code to handle the error;
}

Example with error checking

try {
	$db = new PDO("mysql:dbname=imdb_small", "jessica", "guinness");
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
	$rows = $db->query("SEEELECT * FROM movies WHERE year = 2000");
	foreach ($rows as row) { ... }
} catch (PDOException $ex) {
	?>
	<p>Sorry, a database error occurred. Please try again later.</p>
	<p>(Error details: <?= $ex->getMessage() ?>)</p>
	<?php
}

PDOStatement methods

The $rows returned by PDO's query method is technically not an array but an object of type PDOStatement. Here are its methods:

columnCount() number of columns in the results
fetch() return the next row from the results
fetchColumn(number) return the next column from the results
rowCount() number of rows returned by the query
if ($db->rowCount() > 0) {
	$first_row = $db->fetch();
	...
}

Database Design

Database design principles

First database design

student_grades
nameemailcourseteachergrade
Bartbart@fox.comComputer Science 142KrabappelB-
Bartbart@fox.comComputer Science 143HooverC
Milhousemilhouse@fox.comComputer Science 142KrabappelB+
Lisalisa@fox.comComputer Science 143HooverA+
Lisalisa@fox.comComputer Science 190MSteppA+
Ralphralph@fox.comInformatics 100KrabappelD+

Improved database design

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

Database design exercise

amazon cookie

Suppose we want to write a web store like Amazon.com. The store sells products that can be purchased by customers online. The customer can add items to their shopping cart and then order them. The customer can also check the order's status, whether it has shipped, etc.

Related tables and keys

students
idnameemail
123Bartbart@fox.com
456Milhousemilhouse@fox.com
888Lisalisa@fox.com
404Ralphralph@fox.com
courses
idname
10001Computer Science 142
10002Computer Science 143
10003Computer Science 190M
10004Informatics 100
grades
student_idcourse_idgrade
12310001B-
12310002C
45610001B+
88810002A+
88810003A+
40410004D+

Design question

students
idnameemail
123Bartbart@fox.com
456Milhousemilhouse@fox.com
888Lisalisa@fox.com
404Ralphralph@fox.com
courses
idname
10001Computer Science 142
10002Computer Science 143
10003Computer Science 190M
10004Informatics 100
grades
student_idcourse_idgrade
12310001B-
12310002C
45610001B+
88810002A+
88810003A+
40410004D+

Design answer

teachers
idname
1234Krabappel
5678Hoover
9012Stepp
courses
idnameteacher_id
10001Computer Science 1421234
10002Computer Science 1435678
10003Computer Science 190M9012
10004Informatics 1001234