PDO (PHP Data Objects) Is Not a Fluent Interface

Details

Last Updated on Saturday, 11 June 2011 10:53

Published on Wednesday, 08 June 2011 16:04

Written by David L Norris

Problem

PDO does not provide a fluent interface contrary to the claims of many.  It is deceiving because it looks like a fluent interface on the surface due to its support of method chaining on some objects.  However, PDO doesn't even support method chaining properly.  It does not consistently throw exceptions and instead often uses return values of false to indicate failure.  PDO::query in particular leaves you with a "Fatal error: (method name) method called on non-object" when an error occurs.

Solution

Don't use PDO directly.  Unfortunately, this is your only option.  Zend_Db provides a fluent interface around PDO.  It works almost exactly the same with the same basic structure and methods but is designed with method chaining and fluency in mind.  An exception will be thrown if an error occurs giving you detailed information instead of a "something is broken" indicator.  Using Zend_Db fluently makes the code much more readable as well as saving memory and cpu cycles by not copying everything to a throw-away variable before you can use it.

Demo Database

First lets create the database.  For simplicity I am using a mysql server on localhost.  

-- Setup a new database for testing
CREATE DATABASE demo;
USE demo
GRANT ALL ON demo.* TO 'demo'@'localhost' IDENTIFIED BY 'd3m0';
-- create some simple tables to hold data
CREATE TABLE people ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255), PRIMARY KEY (id) ) ENGINE=innodb;
CREATE TABLE places LIKE people;
INSERT INTO people VALUES (NULL,'Bob Smith');
INSERT INTO people VALUES (NULL,'John Smith');
INSERT INTO people VALUES (NULL,'Boba Fett');
INSERT INTO people VALUES (NULL,'Jango Fett');
INSERT INTO places VALUES (NULL,'New York City');
INSERT INTO places VALUES (NULL,'Los Angeles City');
INSERT INTO places VALUES (NULL,'Mexico City');
INSERT INTO places VALUES (NULL,'Москва́');
-- It would be interesting if we could relate these tables arbitrarily
CREATE TABLE relate ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255), lht VARCHAR(32) NOT NULL, 
  rht VARCHAR(32) NOT NULL, lh INT NOT NULL, rh INT NOT NULL, PRIMARY KEY (id) ) ENGINE=innodb;
-- Relate Bob Smith to New York City
INSERT INTO relate VALUES ( NULL, 'Home Town', 'people', 'places', 1, 1 );
-- Now you have a full relationship to use for testing
SELECT places.name AS place_name, people.name AS people_name, relate.name AS relate_name FROM relate 
  JOIN people ON (people.id=relate.rh) JOIN places ON (places.id=relate.rh);
-- +---------------+-------------+-------------+
-- | place_name    | people_name | relate_name |
-- +---------------+-------------+-------------+
-- | New York City | Bob Smith   | Home Town   |
-- +---------------+-------------+-------------+
 

PDO Demo

<?php
 
// simple pdo mysql select demo
try {
        $db = new PDO( "mysql:host=localhost;dbname=demo", 'demo', 'd3m0');
}
catch( PDOException $e ) {
        print '<p>Failed to connect to database: ' . $e->getMessage() . '</p>';
        exit(1);
}
 
$results = $db->query( 'SELECT  places.name AS placeName, 
                                people.name AS peopleName, 
                                relate.name AS relateName 
                        FROM relate 
                        JOIN people ON (people.id=relate.rh) 
                        JOIN places ON (places.id=relate.rh)' 
);
 
// check for false 
if( $results === false ){
        print 'query failed';
        exit(2);
}
else {
    // output standard objects
    $results->setFetchMode( PDO::FETCH_OBJ );
 
    // print each result
    foreach( $results->fetchAll() as $result ){
        print '<p>The '. $result->relateName . ' of ' . $result->peopleName . ' is ' . $result->placeName . '.</p>';
    }
}
 

Zend_Db Demo

<?php
require_once( 'Zend/Db.php' );
 
// simple zend_db mysql select demo
try {
        $db = Zend_Db::factory('Pdo_Mysql', 
                array(
                        'host'     => 'localhost',
                        'username' => 'demo',
                        'password' => 'd3m0',
                        'dbname'   => 'demo',
                )
        );
}
catch( Zend_Exception $e ) {
        print '<p>Failed to connect to database: ' . $e->getMessage() . '</p>';
        exit(1);
}
 
// run query and print the results
try {
        // output standard objects
        $db->setFetchMode(Zend_Db::FETCH_OBJ);
 
        // print each result
        foreach($db->query( 'SELECT  places.name AS placeName, 
                                     people.name AS peopleName, 
                                     relate.name AS relateName 
                                FROM relate 
                                JOIN people ON (people.id=relate.rh) 
                                JOIN places ON (places.id=relate.rh)' 
                        )
                        ->fetchAll()
                as $result
        ) {
                print '<p>The '. $result->relateName . ' of ' . $result->peopleName . ' is ' . $result->placeName . '.</p>';
        }
}
catch( Zend_Exception $e ) {
        print '<p>Query Failed: ' . $e->getMessage() . '</p>';
        exit(2);
}
 

Output

The output of both scripts should be the text "The Home Town of Bob Smith is New York City."

Conclusion

Both PDO and Zend_Db provide the same functionality in roughly the same number of lines of code.  However, Zend_Db allows the code to be written in a manner which is more clear and concise.  You eliminate the distraction and confusion of having to handle errors in the middle of functional code.  And you have a much more robust error handling mechanism than testing for ===false, having no idea what kind of error occurred and then returning some nebulous answer trying to guess what could have happened.  Or, worse, with PDO its easy to forget to handle errors allowing the code to quietly explode in the background with no errors or warnings or indications of any kind.