Web Programming Step by Step

Lecture 23
Relational 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 2009 Marty Stepp and Jessica Miller.

Valid XHTML 1.1 Valid CSS!

11.1: Database Basics

Relational databases

Why use a database? (11.1.1)

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

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

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

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

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

11.3: Databases and PHP

PHP MySQL functions

name description
mysql_connect connects to a database server
mysql_select_db chooses which database on server to use (similar to SQL USE database; command)
mysql_query performs a SQL query on the database
mysql_real_escape_string encodes a value to make it safe for use in a query
mysql_fetch_array, ... returns the query's next result row as an associative array
mysql_close closes a connection to a database

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

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

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

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

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

name description
mysql_num_rows returns number of rows matched by the query
mysql_num_fields returns number of columns per result in the query
mysql_list_dbs returns a list of databases on this server
mysql_list_tables returns a list of tables in current database
mysql_list_fields returns a list of fields in the current data
complete list

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

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>

Don't use tables for layout!