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.

Valid HTML5 Valid CSS

HTML tables: <table>, <tr>, <td>

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,11,2 okay
2,1 real wide2,2

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 1Column 2
1,11,2 okay
2,1 real wide2,2

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 1Column 2
1,11,2 okay
2,1 real wide2,2

The border-collapse property

table, td, th { border: 2px solid black; }
table { border-collapse: collapse; }
Without border-collapse
Column 1Column 2
1,11,2
2,12,2
With border-collapse
Column 1Column 2
1,11,2
2,12,2

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>

Column styles: <col>, <colgroup>

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

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!


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

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();
	...
}