CSE 190M Web Programming
Lecture 11: Relational Databases and SQL
Reading: 13.1 - 13.2, 13.4
Except where otherwise noted, the contents of this document are
Copyright 2012 Marty Stepp, Jessica Miller, Victoria Kirst and Roy McElmurry IV.
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.
A 2D table of rows and columns of data (block element)
<table>
<tr><td>1,1</td><td>1,2 okay</td></tr>
<tr><td>2,1 real wide</td><td>2,2</td></tr>
</table>
1,1 | 1,2 okay |
2,1 real wide | 2,2 |
table
defines the overall table, tr
each row, and td
each cell's data
- tables are useful for displaying large row/column data sets
-
NOTE: tables are sometimes used by novices for web page layout, but this is not proper semantic HTML and should be avoided
Table headers, captions:
<th>
,
<caption>
<table>
<caption>My important data</caption>
<tr><th>Column 1</th><th>Column 2</th></tr>
<tr><td>1,1</td><td>1,2 okay</td></tr>
<tr><td>2,1 real wide</td><td>2,2</td></tr>
</table>
My important data
Column 1 | Column 2 |
1,1 | 1,2 okay |
2,1 real wide | 2,2 |
th
cells in a row are considered headers; by default, they appear bold
- a
caption
at the start of the table labels its meaning
Styling tables
table { border: 2px solid black; caption-side: bottom; }
tr { font-style: italic; }
td { background-color: yellow; text-align: center; width: 30%; }
My important data
Column 1 | Column 2 |
1,1 | 1,2 okay |
2,1 real wide | 2,2 |
- all standard CSS styles can be applied to a table, row, or cell
- table specific CSS properties:
table, td, th { border: 2px solid black; }
table { border-collapse: collapse; }
Without border-collapse
Column 1 | Column 2 |
1,1 | 1,2 |
2,1 | 2,2 |
With border-collapse
Column 1 | Column 2 |
1,1 | 1,2 |
2,1 | 2,2 |
- by default, the overall table has a separate border from each cell inside
- the
border-collapse
property merges these borders into one
The rowspan
and colspan
attributes
<table>
<tr><th>Column 1</th><th>Column 2</th><th>Column 3</th></tr>
<tr><td colspan="2">1,1-1,2</td>
<td rowspan="3">1,3-3,3</td></tr>
<tr><td>2,1</td><td>2,2</td></tr>
<tr><td>3,1</td><td>3,2</td></tr>
</table>
colspan
makes a cell occupy multiple columns; rowspan
multiple rows
text-align
and vertical-align
control where the text appears within a cell
<table>
<col class="urgent" />
<colgroup class="highlight" span="2"></colgroup>
<tr><th>Column 1</th><th>Column 2</th><th>Column 3</th></tr>
<tr><td>1,1</td><td>1,2</td><td>1,3</td></tr>
<tr><td>2,1</td><td>2,2</td><td>2,3</td></tr>
</table>
.urgent {
background-color: pink;
}
.highlight {
background-color: yellow;
}
col
tag can be used to define styles that apply to an entire column (self-closing)
colgroup
tag applies a style to a group of columns (NOT self-closing)
Exercise: Score Table Styles
- Add a collapsed table border style.
- Zebra stripe the rows.
- Make the first column a different color.
Don't use tables for layout!
- (borderless) tables appear to be an easy way to achieve grid-like page layouts
- many "newbie" web pages do this (including many UW CSE web pages...)
- but, a
table
has semantics; it should be used only to represent an actual table of data
- instead of tables, use
div
s, widths/margins, floats, etc. to perform layout
- tables should not be used for layout!
- Tables should not be used for layout!!
- TABLES SHOULD NOT BE USED FOR LAYOUT!!!
- TABLES SHOULD NOT BE USED FOR LAYOUT!!!!
13.1: Database Basics
-
13.1: Database Basics
-
13.2: SQL
-
13.3: Multi-table Queries
-
13.4: Databases and PHP
Relational databases
- relational database: A method of structuring data as tables associated to each other by shared attributes.
- a table row corresponds to a unit of data called a record; a column corresponds to an attribute of that record
- relational databases typically use Structured Query Language (SQL) to define, manage, and search data
Why use a database?
- powerful: can search it, filter data, combine data from multiple sources
- fast: can search/filter a database very quickly compared to a file
- big: scale well up to very large data sizes
- safe: built-in mechanisms for failure recovery (e.g. transactions)
- multi-user: concurrency features let many users view/edit data at same time
- abstract: provides layer of abstraction between stored data and app(s)
- many database programs understand the same SQL commands
Database software
- Oracle
- Microsoft SQL Server (powerful) and Microsoft Access (simple)
- PostgreSQL (powerful/complex free open-source database system)
- SQLite (transportable, lightweight free open-source database system)
-
MySQL (simple free open-source database system)
- many servers run "LAMP" (Linux, Apache, MySQL, and PHP)
- Wikipedia is run on PHP and MySQL
- we will use MySQL in this course
Example simpsons
database
students
id | name | email |
123 | Bart | bart@fox.com |
456 | Milhouse | milhouse@fox.com |
888 | Lisa | lisa@fox.com |
404 | Ralph | ralph@fox.com |
|
teachers
id | name |
1234 | Krabappel |
5678 | Hoover |
9012 | Stepp |
|
courses
id | name | teacher_id |
10001 | Computer Science 142 | 1234 |
10002 | Computer Science 143 | 5678 |
10003 | Computer Science 190M | 9012 |
10004 | Informatics 100 | 1234 |
|
grades
student_id | course_id | grade |
123 | 10001 | B- |
123 | 10002 | C |
456 | 10001 | B+ |
888 | 10002 | A+ |
888 | 10003 | A+ |
404 | 10004 | D+ |
|
- to test queries on this database, use username
homer
, password d0ughnut
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 |
3793 | New York | USA | New York | 8008278 |
1 | Los Angeles | USA | California | 3694820 |
... | ... | ... | ... | ... |
|
languages
country_code | language | official | percentage |
AFG | Pashto | T | 52.4 |
NLD | Dutch | T | 95.6 |
... | ... | ... | ... |
|
- to test queries on this database, use username
traveler
, password packmybags
Example imdb
database
actors
id | first_name | last_name | gender |
433259 | William | Shatner | M |
797926 | Britney | Spears | F |
831289 | Sigourney | Weaver | F |
... |
movies
id | name | year | rank |
112290 | Fight Club | 1999 | 8.5 |
209658 | Meet the Parents | 2000 | 7 |
210511 | Memento | 2000 | 8.7 |
... |
roles
actor_id | movie_id | role |
433259 | 313398 | Capt. James T. Kirk |
433259 | 407323 | Sgt. T.J. Hooker |
797926 | 342189 | Herself |
... |
movies_genres
movie_id | genre |
209658 | Comedy |
313398 | Action |
313398 | Sci-Fi |
... |
directors
id | first_name | last_name |
24758 | David | Fincher |
66965 | Jay | Roach |
72723 | William | Shatner |
... |
movies_directors
director_id | movie_id |
24758 | 112290 |
66965 | 209658 |
72723 | 313398 |
... |
- also available,
imdb_small
with fewer records (for testing queries)
- to test queries on this database, use the username/password that we will email to you soon
13.2: SQL
-
13.1: Database Basics
-
13.2: SQL
-
13.3: Multi-table Queries
-
13.4: Databases and PHP
SQL basics
SELECT name FROM cities WHERE id = 17;
INSERT INTO countries VALUES ('SLD', 'ENG', 'T', 100.0);
- Structured Query Language (SQL): a language for searching and updating a database
- a standard syntax that is used by all database software (with minor incompatiblities)
-
generally case-insensitive
- a declarative language: describes what data you are seeking, not exactly how to find it
Issuing SQL commands directly in MySQL
SHOW DATABASES;
USE database;
SHOW TABLES;
- SSH to Webster, then type:
$ 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;
name | code |
China | CHN |
United States | IND |
Indonesia | USA |
Brazil | BRA |
Pakistan | PAK |
... | ... |
- the
SELECT
statement searches a database and returns a set of results
- the column name(s) written after
SELECT
filter which parts of the rows are returned
- table and column names are case-sensitive
SELECT * FROM table;
keeps all columns
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 |
... |
|
- eliminates duplicates from the result set
The WHERE
clause
SELECT column(s) FROM table WHERE condition(s);
SELECT name, population FROM cities WHERE country_code = "FSM";
name | population |
Weno | 22000 |
Palikir | 8600 |
WHERE
clause filters out rows based on their columns' data values
- in large databases, it's critical to use a
WHERE
clause to reduce the result set size
- suggestion: when trying to write a query, think of the
FROM
part first, then the WHERE
part, and lastly the SELECT
part
More about the WHERE
clause
WHERE column operator value(s)
SELECT name, gnp FROM countries WHERE gnp > 2000000;
code | name | gnp |
JPN | Japan | 3787042.00 |
DEU | Germany | 2133367.00 |
USA | United States | 8510700.00 |
... | ... | ... |
- the
WHERE
portion of a SELECT statement can use the following operators:
=
, >
, >=
, <
, <=
<>
: not equal
BETWEEN
min AND
max
LIKE
pattern
IN
(value, value, ..., value)
Multiple WHERE
clauses: AND, OR
SELECT * FROM cities WHERE code = 'USA' AND population >= 2000000;
id | name | country_code | district | population |
3793 | New York | USA | New York | 8008278 |
3794 | Los Angeles | USA | California | 3694820 |
3795 | Chicago | USA | Illinois | 2896016 |
... | ... | ... | ... | ... |
- multiple
WHERE
conditions can be combined using AND
and OR
Approximate matches: LIKE
WHERE column LIKE pattern
SELECT code, name, population FROM countries WHERE name LIKE 'United%';
code | name | population |
ARE | United Arab Emirates | 2441000 |
GBR | United Kingdom | 59623400 |
USA | United States | 278357000 |
UMI | United States Minor Outlying Islands | 0 |
LIKE 'text%'
searches for text that starts with a given prefix
LIKE '%text'
searches for text that ends with a given suffix
LIKE '%text%'
searches for text that contains a given substring
Sorting by a column: ORDER BY
ORDER BY column(s)
SELECT code, name, population FROM countries
WHERE name LIKE 'United%' ORDER BY population;
code | name | population |
UMI | United States Minor Outlying Islands | 0 |
ARE | United Arab Emirates | 2441000 |
GBR | United Kingdom | 59623400 |
USA | United States | 278357000 |
- can write
ASC
or DESC
to sort in ascending (default) or descending order:
SELECT * FROM countries ORDER BY population DESC;
- can specify multiple orderings in decreasing order of significance:
SELECT * FROM countries ORDER BY population DESC, gnp;
-
see also:
GROUP BY
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 |
-
can be used to get the top-N of a given category (
ORDER BY
and LIMIT
)
-
also useful as a sanity check to make sure your query doesn't return 107 rows
13.4: Databases and PHP
-
13.1: Database Basics
-
13.2: SQL
-
13.3: Multi-table 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");
$db = new PDO("mysql:dbname=world;host=localhost", "traveler", "packmybags");
$db->query("SELECT * FROM countries WHERE population > 100000000;");
- PDO database library allows you to connect to many different database programs
- replaces older, less versatile functions like
mysql_connect
- PDO object's
query
function returns rows that match a query
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
;
}
query
returns all result rows
- each row is an associative array of [column name -> value]
- example:
$row["population"]
gives the value of the population
column
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'");
- you should not directly include variables or query parameters in a query
- they might contain illegal characters or SQL syntax to mess up the query
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");
- call PDO's
quote
method on any variable to be inserted
quote
escapes any illegal chars and surrounds the value with '
quotes
- prevents bugs and security problems in queries containing user input
Database/query errors
$db = new PDO("mysql:dbname=imdb_small", "jessica", "guinness");
$rows = $db->query("SEEELECT * FROM movies WHERE year = 2000");
- database commands can often fail (invalid query; server not responding; etc.)
- normally, PDO commands fail silently by returning
FALSE
or NULL
- but this makes it hard to notice and handle problems
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");
- using
setAttribute
, you can tell PDO to throw (generate) a PDOException
when an error occurs
- the exceptions will appear as error messages on the page output
- you can catch the exception to gracefully handle the error
Catching an exception
try {
statement(s);
} catch (ExceptionType $name) {
code to handle the error;
}
- a
try/catch
statement attempts to run some code, but if it throws a given kind of exception, the program jumps to the catch
block and runs that 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
}
methods
The $rows
returned by PDO's query
method is technically not an array but an object of type PDOStatement
.
Here are its methods:
if ($db->rowCount() > 0) {
$first_row = $db->fetch();
...
}