[an error occurred while processing this directive]

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

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)
[an error occurred while processing this directive]