Why you Should be using PHPs PDO for Database Access

Many PHP programmers learned how to access databases by using either the MySQL or MySQLi extensions. As of PHP 5.1, there’s a better way. PHP Data Objects (PDO) provide methods for prepared statements and working with objects that will make you far more productive!

PDO Introduction

“PDO – PHP Data Objects – is a database access layer providing a uniform method of access to multiple databases.”

It doesn’t account for database-specific syntax, but can allow for the process of switching databases and platforms to be fairly painless, simply by switching the connection string in many instances.

This tutorial isn’t meant to be a complete how-to on SQL. It’s written primarily for people currently using the mysql or mysqli extension to help them make the jump to the more portable and powerful PDO.

Database Support

The extension can support any database that a PDO driver has been written for. At the time of this writing, the following database drivers are available:

• PDO_DBLIB ( FreeTDS / Microsoft SQL Server / Sybase )
• PDO_FIREBIRD ( Firebird/Interbase 6 )
• PDO_IBM ( IBM DB2 )
• PDO_INFORMIX ( IBM Informix Dynamic Server )
• PDO_MYSQL ( MySQL 3.x/4.x/5.x )
• PDO_OCI ( Oracle Call Interface )
• PDO_ODBC ( ODBC v3 (IBM DB2, unixODBC and win32 ODBC) )
• PDO_PGSQL ( PostgreSQL )
• PDO_SQLITE ( SQLite 3 and SQLite 2 )
• PDO_4D ( 4D )

All of these drivers are not necessarily available on your system; here’s a quick way to find out which drivers you have:

print_r(PDO::getAvailableDrivers());

Connecting

Different databases may have slightly different connection methods. Below, the method to connect to some of the most popular databases are shown. You’ll notice that the first three are identical, other then the database type – and then SQLite has its own syntax.

try {  # MS SQL Server and Sybase with PDO_DBLIB  $DBH = new PDO("mssql:host=$host;dbname=$dbname, $user, $pass");  $DBH = new PDO("sybase:host=$host;dbname=$dbname, $user, $pass");  # MySQL with PDO_MYSQL  $DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);  # SQLite Database  $DBH = new PDO("sqlite:my/database/path/database.db");}catch(PDOException $e) {    echo $e->getMessage();}

Please take note of the try/catch block – you should always wrap your PDO operations in a try/catch, and use the exception mechanism – more on this shortly. Typically you’re only going to make a single connection – there are several listed to show you the syntax. $DBH stands for ‘database handle’ and will be used throughout this tutorial.

You can close any connection by setting the handle to null.

# close the connection$DBH = null;

You can get more information on database specific options and/or connection strings for other databases from PHP.net.

Exceptions and PDO

PDO can use exceptions to > Updates an existing instance of the named class

In reality, there are three which will cover most situations: FETCH_ASSOC, FETCH_CLASS, and FETCH_OBJ. In order to set the fetch method, the following syntax is used:

$STH->setFetchMode(PDO::FETCH_ASSOC);

You can also set the fetch type directly within the ->fetch() method call.

FETCH_ASSOC

This fetch type creates an associative array, indexed by column name. This should be quite familiar to anyone who has used the mysql/mysqli extensions. Here’s an example of selecting data with this method:

# using the shortcut ->query() method here since there are no variable# values in the select statement.$STH = $DBH->query('SELECT name, addr, city from folks');# setting the fetch mode$STH->setFetchMode(PDO::FETCH_ASSOC);while($row = $STH->fetch()) {    echo $row['name'] . "\n";    echo $row['addr'] . "\n";    echo $row['city'] . "\n";}

The while loop will continue to go through the result set one row at a time until complete.

FETCH_OBJ

This fetch type creates an object of std class for each row of fetched data. Here’s an example:

# creating the statement$STH = $DBH->query('SELECT name, addr, city from folks');# setting the fetch mode$STH->setFetchMode(PDO::FETCH_OBJ);# showing the resultswhile($row = $STH->fetch()) {    echo $row->name . "\n";    echo $row->addr . "\n";    echo $row->city . "\n";}

FETCH_CLASS

The properties of your object are set BEFORE the constructor is called. This is important.

This fetch method allows you to fetch data directly into a class of your choosing. When you use FETCH_CLASS, the properties of your object are set BEFORE the constructor is called. Read that again, it’s important. If properties matching the column names don’t exist, those properties will be created (as public) for you.

This means if your data needs any transformation after it comes out of the database, it can be done automatically by your object as each object is created.

As an example, imagine a situation where the address needs to be partially obscured for each record. We could do this by operating on that property in the constructor. Here’s an example:

class secret_person {    public $name;    public $addr;    public $city;    public $other_data;    function __construct($other = '') {        $this->address = preg_replace('/[a-z]/', 'x', $this->address);        $this->other_data = $other;    }}

As data is fetched into this class, the address has all its lowercase a-z letters replaced by the letter x. Now, using the class and having that data transform occur is completely transparent:

$STH = $DBH->query('SELECT name, addr, city from folks');$STH->setFetchMode(PDO::FETCH_CLASS, 'secret_person');while($obj = $STH->fetch()) {    echo $obj->addr;}

If the address was ’5 Rosebud,’ you’d see ’5 Rxxxxxx’ as your output. Of course, there may be situations where you want the constructor called before the data is assigned. PDO has you covered for this, too.

$STH->setFetchMode(PDO::FETCH_CLASS | PDO::FETCH_PROPS_LATE, 'secret_person');

Now, when you repeat the previous example with this fetch mode (PDO::FETCH_PROPS_LATE) the address will NOT be obscured, since the constructor was called and the properties were assigned.

Finally, if you really need to, you can pass arguments to the constructor when fetching data into objects with PDO:

$STH->setFetchMode(PDO::FETCH_CLASS, 'secret_person', array('stuff'));

If you need to pass different data to the constructor for each object, you can set the fetch mode inside the fetch method:

$i = 0;while($rowObj =  $STH->fetch(PDO::FETCH_CLASS, 'secret_person', array($i))) {    // do stuff    $i++}

Some Other Helpful Methods

While this isn’t meant to cover everything in PDO (it’s a huge extension!) there are a few more methods you’ll want to know in order to do basic things with PDO.

$DBH->lastInsertId();

The ->lastInsertId() method is always called on the database handle, not statement handle, and will return the auto incremented id of the last inserted row by that connection.

$DBH->exec('DELETE FROM folks WHERE 1');$DBH->exec("SET time_zone = '-8:00'");

The ->exec() method is used for operations that can not return data other then the affected rows. The above are two examples of using the exec method.

$safe = $DBH->quote($unsafe);

The ->quote() method quotes strings so they are safe to use in queries. This is your fallback if you’re not using prepared statements.

$rows_affected = $STH->rowCount();

The ->rowCount() method returns an integer indicating the number of rows affected by an operation. In at least one known version of PDO, according to [this bug report](https://bugs.php.net/40822) the method does not work with select statements. If you’re having this problem, and can’t upgrade PHP, you could get the number of rows with the following:

$sql = "SELECT COUNT(*) FROM folks";if ($STH = $DBH->query($sql)) {    # check the row count    if ($STH->fetchColumn() > 0) {    # issue a real select here, because there's data!    }    else {        echo "No rows matched the query.";    }}

Conclusion

I hope this helps some of you migrate away from the mysql and mysqli extensions. What do you think? Are there any of you out there who might make the switch?