Files in this tutorial

Introduction

Connecting to the Database: new PDO

The first step in working with databases is to specify which database we're connecting to and create a new connection. The new PDO(url) call creates a new connection to a database. The url describes which database to use. Quercus recognizes the PHP standard urls like mysql:dbname=test and it also can use JNDI names directly like java:comp/env/jdbc/resin.

$pdo = new PDO("java:comp/env/jdbc/resin");

Executing SQL statements: exec

Once the application has connected with a database, it needs to do something, and since relational databases require tables and some data, the application will need to create them. In PDO, the $pdo->exec(sql) method executes arbitrary SQL. We'll use exec to create the database and add some data.

$pdo = new PDO("java:comp/env/jdbc/resin");

$pdo->exec(<<<END
CREATE TABLE brooms (
  id INTEGER PRIMARY KEY auto_increment,
  name VARCHAR(255) UNIQUE,
  price INTEGER
)
END)
  or die("Can't create table 'brooms'");

$pdo->exec("INSERT INTO brooms (name, price)"
           . " VALUES ('cleansweep 5', 5)")
  or die("Can't insert data into table 'brooms'");

$pdo->exec("INSERT INTO brooms (name, price)"
           . " VALUES ('cleansweep 7', 15)")
  or die("Can't insert data into table 'brooms'");

Querying the database: query

Once the database has data, we'll want to query the database to see what we've stored. The $pdo->query(sql) method queries the database. For now, we'll use the foreach form of the query.

$pdo = new PDO("java:comp/env/jdbc/resin");

echo "<table>\n"

$sql = "SELECT name, price FROM brooms";
foreach ($pdo->query($sql) as $name => $price) {
  echo "<tr><td>$name<td>$price\n";
}

echo "</table>\n"

Moving forward: CRUD

For basic database access, the three calls we've introduced are sufficient. If you're just starting with PDO, you may want to stop right here, stick with new PDO(url), $pdo->exec(sql), and $pdo->query(sql) with the foreach pattern until you can write PDO code without checking the tutorial. Go ahead, add some foo.php with some sample tables and get learning!

Once you've tattooed the basic three PDO calls into your brain, it's time to start exploring the alternatives PDO provides. We'll introduce some of the main options using the CRUD framework.

CRUD (Create, Read, Update, Delete) is just a simple acronym to organize the basics of any database or persistence application. It's a handy mnemonic when sketching out a prototype, helping to avoid the embarrassment of forgetting to let the user delete an object. And it's handy when learning a new persistence framework or database API to make sure you've covered the bases.

Create

Read

$pdo->query and foreach

As we described in the introduction, your application can get away with using the basic PDO query pattern. The result of a PDO query can work with the PHP foreach statement to iterate through the rows of the result. Each result will be an associative array of the result values.

$pdo = new PDO("java:comp/env/jdbc/resin");

$sql = "SELECT * FROM brooms";

foreach ($pdo->query($sql) as $row) {
  echo $row['name'] . ", " . $row['price'] . "<br>\n";
}

$pdo->query and fetch(PDO::FETCH_ASSOC)

In some cases, an application might need more control over the row iteration than the foreach pattern provides. For example, an application might want to avoid creating a <table> if the database has no data. When more control is needed, PDO lets you split out the iteration from retrieving the row, using $stmt->fetch(...).

The $stmt->fetch(...) call will also let the application choose the type of object it wants to deal with. For now, we'll stick with an associative array.

$pdo = new PDO("java:comp/env/jdbc/resin");

$stmt = $pdo->query("SELECT * FROM brooms") or die();

echo "<table>";
while (($row = $stmt->fetch(PDO::FETCH_ASSOC))) {
  echo "<tr><td>${row['name']}<td>${row['price']}\n";
}
echo "</table>";

fetch(PDO::FETCH_OBJ)

PDO's fetch method provides many options for extracting data from a row other than a full associative list. In particular, you can return a PHP 5 object representing the row. In many cases, the object will be more efficient than an array in PHP programs. Since PHP automatically copies arrays but does not automatically copy PHP 5 objects, you can avoid stressing out the garbage collector by using objects over arrays.

The PHP code for getting an object from a row is identical to the associative array but swapping PDO::FETCH_OBJ for PDO::FETCH_ASSOC. PDO provides many other fetch options as well, but we'll restrict ourselves to PDO::FETCH_OBJ.

$pdo = new PDO("java:comp/env/jdbc/resin");

$stmt = $pdo->query("SELECT * FROM brooms") or die();

echo "<table>";
while (($row = $stmt->fetch(PDO::FETCH_OBJ))) {
  echo "<tr><td>$row->name<td>$row->price\n";
}
echo "</table>";

Update

Transactions

In this case, though, we'd like some extra reliability, so we'll add some transaction support. Transactions are a generalized lock in a database, allowing multiple statements to execute atomically. That means either all of the statements will update the database or none of them will.

In many examples, the all-or-none property is vital to the integrity of a database. A bank transfer, for example, must deduct a balance from one account and give it to another. Withdrawing from one without adding to the other would be a disaster. In a less critical example, a bulletin board forum which updates a comment's hierarchy might need to change multiple columns at once to avoid corrupting the forum. Although the price of failure is less for a forum, it's still important for producing professional code.

The transaction wraps the SQL statements between a beginTransaction() call and a commit() call. All the SQL statements in between will be executed as one indivisible block (atomic) or the commit will fail.

$stmt = $pdo->prepare("UPDATE brooms SET price=:price WHERE name=:broom");

$stmt->bindParam(":broom", $broom);
$stmt->bindParam(":price", $price);

$pdo->beginTransaction();

$brooms = array("nimbus 2000" => 120,
                "nimbus 2001" => 250);

foreach ($brooms as $broom => $price) {
  $stmt->execute();
}

$pdo->commit();

Delete