Web Programming Step by Step, 2nd Edition

Lecture 11: Relational Databases and SQL

Reading: 13.1–13.2, 13.4–13.4.2

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 HTML5 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);

The MySQL console

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)

SQL console 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)

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("dbprogram:dbname=database;host=server", username, password);
$stmt = $db->query("SQL query");
foreach ($stmt as $row) {
	do something with $row;
}

A complete example

$db = new PDO("mysql:dbname=imdb_small", "jessica", "guinness");
$stmt = $db->query("SELECT * FROM actors WHERE last_name LIKE 'Del%'");
foreach ($stmt 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 doesn't return any records (INSERT, DELETE, UPDATE, etc.)
getAttribute,
setAttribute
get/set various DB connection properties
quote encodes a value for use within a query
prepare creates a SQL prepared statement

PDOStatement methods

The $stmt variable returned by PDO's query method is technically not an array but an object of type PDOStatement. It can be foreach-ed over like an array, but it also has the following methods:

columnCount() number of columns in the results
fetch() return the next row from the results
fetchAll() return an array of all rows
fetchColumn(number) return the next column from the results
rowCount() number of rows returned by the query
if ($stmt->rowCount() > 0) {
	$first_row = $stmt->fetch();
}
$remaining_rows = $stmt->fetchAll();