Web Programming Step by Step, 2nd Edition

Lecture 13: 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, 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.

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

• to test queries on this database, use username homer, password d0ughnut

Example world database

• to test queries on this database, use username traveler, password packmybags

Example imdb database

• 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

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;

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

SELECT DISTINCT language
FROM languages;

• 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";

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

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

• 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%';

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

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

• 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 10⁷ rows

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

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

PDO object methods

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");   # FALSE

• 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");   # kaboom!

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

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:

if ($db->rowCount() > 0) {
	$first_row = $db->fetch();
	...
}