Web Programming Step by Step, 2nd Edition

Lecture 12: PDO

Reading: 13.4.2–13.4.3, 13.5; 2.2.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.4: Databases and PHP

Including variables in a query - BAD WAY 

// get query parameter for name of movie
$title = $_GET["movietitle"];
$stmt = $db->query("SELECT year FROM movies WHERE name = '$title'");

Including variables in a query - OKAY WAY 

// get *and safely escape* query parameter for name of movie
$title = $db->quote($_GET["movietitle"]);
$stmt = $db->query("SELECT year FROM movies WHERE name = $title");

Including variables in a query - GOOD WAY 

// prepare and execute a statement with a :title parameter
$stmt = $db->prepare('SELECT year FROM movies WHERE name = :title');
$stmt->execute(array(':title' => $_GET['movietitle']));

Parameterized queries with prepare/execute

$db = new PDO("dbprogram:dbname=database;host=server", username, password);
$stmt = $db->prepare("parameterized SQL query with placeholder markers");
$stmt->execute(array(
	marker => value,
	marker => value,
	...
));
foreach ($stmt as $row) {
	do something with $row;
}

Example: Population Search

$db = new PDO("mysql:dbname=world", "jessica", "guinness");
$stmt = $db->prepare("SELECT * FROM cities WHERE country_code = :code and population >= :pop");
$stmt->execute(array(
	':code' => $_GET['c'], // safely inject user-supplied 'c' value at :code placeholder
	':pop' => $_GET['p']   // safely inject user-supplied 'p' value at :pop placeholder
));
<h2>Cities in '<?= $_GET['c'] ?>' with &ge; <?= $_GET['p'] ?> residents:</h2>
<?php foreach ($stmt as $row) { ?>
	<li> <?= $row['name'] ?>: <?= $row['population'] ?> </li>
<?php } ?>

population.php?c=USA&p=1000000

Cities in 'USA' with ≥ 1000000 residents:

  • New York: 8008278
  • Los Angeles: 3694820
  • ...

Common bug: Quoted parameters

$stmt = $db->prepare("SELECT * FROM languages
                      WHERE country_code = ':code' // BAD: quotes
                      AND percentage > ':pctg'");  // BAD: quotes
$stmt = $db->prepare("SELECT * FROM languages
                      WHERE country_code = :code // GOOD: no quotes
                      AND percentage > :pctg");  // GOOD: no quotes

query vs. prepare/execute - Which do I use?

Database/query errors 

$db = new PDO("mysql:dbname=imdb_small", "jessica", "guinness");
$stmt = $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);
$stmt = $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);
	$stmt = $db->query("SEEELECT * FROM movies WHERE year = 2000");
	foreach ($stmt as row) { ... }
} catch (PDOException $ex) {
	?>
	<p>Sorry, a database error occurred. Please try again later.</p>
	<p>(Error details: <?= $ex->getMessage() ?>)</p>
	<?php
}

2.2.2: HTML Tables

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!