PDO is a great OO library for interacting with your database from PHP. It has a very consistent interface and your code will be cleaner than ever. And your PHP script will be more portable because PDO is not related to any database, but is a generic library to work with MySQL, PostgreSQL, SQLite and a lot others.
The only thing I miss in PDO is a better query error management. When I execute a query I have to check every time the PDOStatement->errorCode property to get sure everything is working well. That’s very annoying.
Fortunately there is a way to extend the PDOStatement class to get more comfortable in writing our script. In the official docs there is not a lot about this, but here is an explanation.
The official documentation talks only about a PDO::ATTR_STATEMENT_CLASS attribute for the PDO instance to be set through the setAttribute method. That’s not much, but here is an example:
try {
$dbh = new PDO(
"mysql:dbname=my_database;host=localhost;charset=utf8",
$user,
$pass
);
$dbh->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('MyPDOStatement'));
} catch (PDOException $e) {
die('Connection failed: ' . $e->getMessage());
}
You can set the attribute after the connection, not a problem. MyPDOStatement is a custom class that extends PDOStatement. A minimal version will look like this:
class MyPDOStatement extends PDOStatement
{
private function __construct() {}
function execute($input = array()) {
parent::execute($input);
if ($this->errorCode() !== '00000') {
$err = $this->errorInfo();
error_log(SQLException($err[2], $err[1]));
return false;
}
return true;
}
}
The private constructor is mandatory. You can use it to pass some variable to every MyPDOStatement instance, very useful in many situations.
For example we could use an array to track query execution times. So the code will look like this:
$info = array('queries' => 0, 'query_time' => 0);
try {
$dbh = new PDO(
"mysql:dbname=my_database;host=localhost;charset=utf8",
$user,
$pass
);
$dbh->setAttribute(PDO::ATTR_STATEMENT_CLASS, array('MyPDOStatement', array($info)));
} catch (PDOException $e) {
die('Connection failed: ' . $e->getMessage());
}
class MyPDOStatement extends PDOStatement
{
private $info;
private function __construct(&info) {
$this->info = &$info;
}
function execute($input = array()) {
$t = microtime(true);
parent::execute($input);
$t = microtime(true) - $t;
if ($this->errorCode() !== '00000') {
$err = $this->errorInfo();
error_log(SQLException($err[2], $err[1]));
return false;
}
$this->info['queries']++;
$this->info['query_time'] += $t;
return true;
}
}
