Web Programming Step by Step, 2nd Edition

Lecture 17: SQL and PHP/HTML

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.

Valid HTML5 Valid CSS

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);
$rows = $db->query("SQL query");
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 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
fetchColumn(number) return the next column from the results
rowCount() number of rows returned by the query 
$rows = $db->query("...");   # query omitted
if ($rows->rowCount() > 0) {
	$first_row = $rows->fetch();
	...
}

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>

Don't use tables for layout!

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)