PDO has several variations on those three statements, but you can always
get by with the first three.

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.

connecting to a database in JNDI

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

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

The first step in any database application is to create the database
and to create the first entries in the database. In PDO, creating the
database table and creating entries can use the
same $pdo->exec(sql) call as we
introduced above. From a relation database perspective,
creating a table is very different from adding a new item, but at the PDO
level, they're similar. We'll use the exec call to create
the database as above, and then introduce the
prepare(sql) call for prepared statements to add
items.

Although we could use the basic exec method to add the
data, this time we'll introduce prepared statements and
the $pdo->prepare(sql) method.

Prepared statements precompile the SQL for a database query and assign
parameters for each call. Most importantly, this can create cleaner code.
Prepared statements can also avoid some of the security problems associated
with web applications. Because the parameter assignment are always values
and never raw SQL, Quercus can properly escape the values automatically.
Prepared statements can also improve efficiency by allowing the SQL to
be parsed only once and then used multiple times.

Prepared statements in PDO split SQL execution into three phases:
prepare, bindParam and execute.
$pdo->prepare(sql) parses the SQL and
returns a PDOStatement object. $stmt->bindParam(...)
assigns the parameters to PHP variables. And $stmt->execute()
actually executes the statement.

In this example, we'll add some more brooms to the database. This time,
we'll loop across a PHP array to insert the values.

Read

Because most database accesses are reads, most applications will
spend extra time creating useful queries, and optimizing and
caching for performance. PDO provides the basic
$pdo->query(sql), but it also supports many ways
of extracting data.

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

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.

Update

Once an application has data, it will often need to update it.
In this case, we'll raise the prices of some of the brooms. As
with the creation, PDO's updates use the exec and
prepare methods.

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.

Delete

For completeness, and to clean up the example, we'll need to
delete the entries we've added. PDO uses the exec
and prepare methods for DELETE just as for
INSERT. We'll use the prepared statement method since we'll be deleting
several items.

Cloud-optimized Resin Server is a Java EE certified Java Application Server, and Web Server, and Distributed Cache Server (Memcached).Leading companies worldwide with demand for reliability and high performance web applications including SalesForce.com, CNET, DZone and many more are powered by Resin.