PDO - PHP Data Objects

What is PDO?

PDO (PHP Data Objects) is a PHP module created in the Object Oriented Paradigm, and its goal is to provide a standarized way to PHP connect/comunicate to a Relational Database. It was created in PHP version 5. So, PDO is an interface that defines a set of classes and its method signatures to comunicate with a Database.

Each Database Management System (DBMS) can provide its own driver to PDO. Although PDO provide method signatures, some features can not be supported by some DBMS. So, calling this kind of method may be "useless", depending the driver was used or the DBMS model/version. For example, some engines of MySQL DBMS do not support transactions, so, the method "beginTransation" (that begin a new transation) simply take no effect at this condition.

Contrary to what some people think, PDO is not a SQL abstraction layer. Each Relational DBMS has its own syntax rules to receive SQL. Although many of them are near of the SQL-92 specification, there are many diferences too. So, using PDO does not mean your application will be portable between diferents DBMS. It only means you can comunicate with a Relational DBMS using a well specified set of methods and classes.

Note: It is extremely complex to create an SQL abstraction layer. Many (environment) variables should be used and, often, we must give up many specific features because they are not portable between all DBMS. So, there are two development lines: the first defends the use of a unique DBMS model for application, to use and explore the specific features, but the second defends the generalization and the support to diferent DBMS models, that take the application more portable. The right strategy to execute depends of the goal of the application and the goal of the project.

---

Why to use PDO?

Before the release of PDO, the PHP language had some specific and atomic modules to communicate with diferents DBMS. Many have proven function libraries and used resource to represent the active connection, and other to represent a resultset (the result of a query). The operations was made over these resource variables.

Each driver has implemented their own operations, as though to be most apropriate. Although some of them have a similar behavior, the order of the parameters was not always in the same position and could cause confusion between developers.

Who has worked with the old MySQL or PostgreSQL libraries must know these behavior:

// MySQL
$c = mysql_connect('host', 'usuer', 'pass');
mysql_select_db('bd', $c);
mysql_set_charset('UTF8', $c);

$result = mysql_query('SELECT name FROM users', $c);
while ($obj = mysql_fetch_object($result)) {
    echo $obj->name;
}
mysql_free_result($result);
mysql_close($c);

// PostgreSQL
$c = pg_connect('host=host port=5432 dbname=test user=user password=pass');
pg_set_client_encoding($c, 'UNICODE');

$result = pg_query($c, 'SELECT name FROM users');
while ($obj = pg_fetch_object($result)) {
    echo $obj->name;
}
pg_free_result($result);
pg_close($c);

Note that the way the conection was done is using diferent strategies: MySQL pass conection data through parameters, while PostgreSQL use a "connection string" (with many data), that is a more extensible solution. Note also that MySQL pass the resource as the last parameter of its functions (and they are optional), while PostgreSQL pass the resource as the first parameter.

PDO joined the best of each driver and make a specification. Although this specification do not work with resource, it defines two classes with similar meaning: PDO (that represents the conection) and PDOStatement (that represents a ResultSet). There is also a third class named PDOException, that is triggered to treat exceptions, but may be configured.

Using PDO are often simpler than using function library, but you still need to know the SQL syntax of your DBMS model. Although many developers still use the old libraries, there is a promisse that PDO will be the standard way to connect to DBMS at future versions of PHP, while the libraries should get PECL extensions.

---

How to use PDO?

To use PDO, you need, at first, instantiate an PDO object, that represents a connection. At constructor, you should pass the "DSN". It is a connection string like that we saw in PostgreSQL library. Each driver specify the way the DSN is made. Beyond the DSN, you should also pass the user and password as parameter.

// Example of MySQL connection using PDO
$dsn = 'mysql:host=host;port=3306;dbname=test';
$user = 'user';
$password = 'pass';
$options = array(
    PDO::ATTR_PERSISTENT => true,
    PDO::ATTR_CASE => PDO::CASE_LOWER
);

try {
    $pdo = new PDO($dsn, $user, $password, $options);
} catch (PDOException $e) {
    echo 'Error: '.$e->getMessage();
}

After that, the queries may be done by two ways:

1. Using the "exec" or "query" methods of the connection class; or
2. Making a prepared statement with the "prepare" method, that returns a PDOStatement, and, then, calling the "execute" method of this class.

The method "query" is used to call operations that return tabular results (SELECT) and it returns a PDOStatement object. The method "exec" is used to call operations that do not return tabular results (INSERT, UPDATE, DELETE, etc.) and it returns only the number of affected rows.

These methods are useful to call fixed queries (without variable points). If the query envolves variables values (received by the user), then these values need to be escaped by the method "quote" (it avoids Security flaws like SQL Injection).

The method "prepare" is useful to make a query with variable values. It is possible to specify substitution points that, when they are replaced, they are also automaticaly escaped. Let's see some exmaples:

// Using "exec"
$inserted = $pdo->exec('INSERT INTO logs (operation) VALUES (1)');
$last_id = $pdo->lastInsertId();

// Using "query"
$stmt = $pdo->query('SELECT name, login FROM users');

// Traversing a resultset with "while"
while ($obj = $stmt->fetchObject()) {
    ...
}

// Traversing a resultset with foreach
foreach ($stmt as $row) {
    ...
}

Note that the PDOStatement class (object $stmt) implements the interface "Traversable", that provides a way to traverse the object with a foreach structure.

There are diferent ways to execute a prepared statement with PDO:

// 1 - Using "?" at substitution points
$stmt = $pdo->prepare('INSERT INTO users (name, login) VALUES (?,?)');

// Passing the values to be used in the first and second "?"
$data = array('Rubens', 'rubens');
$executed = $stmt->execute($data);

// 2 - Using named key-points
$stmt = $pdo->prepare('INSERT INTO users (name, login) VALUES (:name, :login)');

// Passing values to be used in :name and :login key-points
$data = array(
    ':name' => 'Rubens',
    ':login' => 'rubens'
);
$executed = $stmt->execute($data);

// 3 - Using linking values
$stmt = $pdo->prepare('INSERT INTO users (name, login) VALUES (:name, :login)');

// Linking variables to parameters
$name = 'Rubens';
$login = 'rubens';
$stmt->bindParam(':name', $name PDO::PARAM_STR, 128);
$stmt->bindParam(':login', $login, PDO::PARAM_STR, 20);

// Executing the SQL with the linked values
$executed = $stmt->execute();

Prepared statements tends to be faster then the conventional queries, because the query stay previously "compiled" and ready to be executed with new values. Instead of DBMS interprets all the SQL, it only sets the new values to key-points and execute the query. It is very useful to scripts that make bulk inserts or updates at same table.

---

Other benefits

1. Standard reception of DB errors, using the methods "errorInfo" and "errorCode" of PDO and PDOStatement classes.
2. Standard way to start and commit/rollback a transaction, using the methods "beginTransaction", "commit" and "rollback".
3. Easy way to work with file handle to make insert or select of large data.

---

Conclusion

The objective of this article is to motivate developers to use PDO. We note that many developers still do not use PDO because they are accustomed with their logics and old connection libraries.

Although, they should be attentive to the PHP future. It is growing to the Object Oriented Paradigm. In this future is PDO.