Web Programming Step by Step

Lecture 20
Databases and SQL; HTML Tables

Reading: 11.1 - 11.3; 2.2.2

References: SQL syntax reference, w3schools tutorial

Except where otherwise noted, the contents of this presentation are Copyright 2010 Marty Stepp and Jessica Miller.

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? (11.1.1)

• 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 (11.1.2)

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

Example imdb database (11.1.2)

• 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 (11.2.1 - 11.2.2)

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:
  • =, >, >=, <, <=
  • <>, !=, IS NOT : not equal (Java-like != is a common but non-standard addition)
  • 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
• AND has precedence; good idea to use parentheses when combining AND and OR

Wildcard patterns: LIKE

WHERE column LIKE pattern

SELECT code, name, population FROM countries WHERE name LIKE 'United%';

• % wildcard means 0 or more of any character
• _ wildcard means 1 of any character
• escape either with a backslash to use literal % or _ inside a LIKE term
• negate using NOT LIKE

Uses and pitfalls of LIKE

• 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
• common error:
  LIKE 'text' is no different than = 'text'
  • without wildcards, searches for text that is exactly equal to the given string
  • you probably meant LIKE '%text%'

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 the result set: 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)
• useful to get a glimpse of your query's output without seeing the entire thing (e.g., when constructing a query)

PHP MySQL functions

Complete PHP MySQL example

# connect to world database on local computer
$db = mysql_connect("localhost", "traveler", "packmybags");
mysql_select_db("world");

# execute a SQL query on the database
$results = mysql_query("SELECT * FROM countries WHERE population > 100000000;");

# loop through each country
while ($row = mysql_fetch_array($results)) {
	?>
	<li> <?= $row["name"] ?>, ruled by <?= $row["head_of_state"] ?> </li>
	<?php
}
?>

Connecting to MySQL: mysql_connect (11.3.1)

mysql_connect("host", "username", "password");
mysql_select_db("database name");

# connect to world database on local computer
mysql_connect("localhost", "traveler", "packmybags");
mysql_select_db("world");

• mysql_connect opens connection to database on its server
  • any/all of the 3 parameters can be omitted (default: localhost, anonymous)
• mysql_select_db sets which database to examine

Performing queries: mysql_query (11.3.2)

mysql_connect("host", "username", "password");
mysql_select_db("database name");

$results = mysql_query("SQL query");
...

$results = mysql_query("SELECT * FROM cities WHERE code = 'USA'
                        AND population >= 2000000;");

• mysql_query sends a SQL query to the database
• returns a special result-set object that you don't interact with directly, but instead pass to later functions
• SQL queries are in " ", end with ;, and nested quotes can be ' or \"

Result rows: mysql_fetch_array

mysql_connect("host", "username", "password");
mysql_select_db("database name");
$results = mysql_query("SQL query");

while ($row = mysql_fetch_array($results)) {
	do something with $row;
}

• mysql_fetch_array returns one result row as an associative array
  • the column names are its keys, and each column's values are its values
  • example: $row["population"] gives the population from that row of the results

Error-checking: mysql_error (11.3.3)

if (!mysql_connect("localhost", "traveler", "packmybags")) {
	die("SQL error occurred on connect: " . mysql_error());
}
if (!mysql_select_db("world")) {
	die("SQL error occurred selecting DB: " . mysql_error());
}
$query = "SELECT * FROM countries WHERE population > 100000000;";
$results = mysql_query($query);
if (!$results) {
	die("SQL query failed:\n$query\n" . mysql_error());
}

• SQL commands can fail: database down, bad password, bad query, ...
• for debugging, always test the results of PHP's mysql functions
  • if they fail, stop script with die function, and print mysql_error result to see what failed
  • give a descriptive error message and also print the query, if any

Complete example w/ error checking

# connect to world database on local computer
check(mysql_connect("localhost", "traveler", "packmybags"), "connect");
check(mysql_select_db("world"), "selecting db");

# execute a SQL query on the database
$query = "SELECT * FROM countries WHERE population > 100000000;";
$results = mysql_query($query);
check($results, "query of $query");

# loop through each country
while ($row = mysql_fetch_array($results)) {
	?>
	<li> <?= $row["name"] ?>, ruled by <?= $row["head_of_state"] ?> </li>
	<?php
}

# makes sure result is not false/null; else prints error
function check($result, $message) {
	if (!$result) {
		die("SQL error during $message: " . mysql_error());
	}
}
?>

Other MySQL PHP functions

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>

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

• 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 (3.2.6)

table { border: 2px solid black; caption-side: bottom; }
tr { font-style: italic; }
td { background-color: yellow; text-align: center; width: 30%; }

• all standard CSS styles can be applied to a table, row, or cell
• table specific CSS properties:
  • border-collapse, border-spacing, caption-side, empty-cells, table-layout

The border-collapse property

table, td, th { border: 2px solid black; }
table { border-collapse: collapse; }

• 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

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>

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

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 divs, 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!!!!