PDO Connection Class, please help me understand if I have this right.

Question

Hi everyone,

I've been playing around with PDO for the last few days, I'm working on a small CMS system to teach myself OOP skills, but even though it's only a small CMS, I want it to be able to handle whatever the web can throw at it.

This is what I've come up with so far, I'm going to add connection pooling to the constructor to enable large amounts of concurrent connects on demand. I'm very new to this OOP stuff so I'm wanting a little advise and critism, no doubt I've done something terribly wrong here.

I took the top answer to http://stackoverflow.com/questions/130878/global-or-singleton-for-database-connection as the base design, although I've added a private constructor as I want to use $this->dbConnectionInstance throughout the class for numerous helper functions to use.

Thanks very much for your time, I really will appreciate any advise you can give me,

-Drew

// Usage Example: $dbconn = dbManager::getConnection();
//                $dbconn->query("SELECT * FROM accounts WHERE id=:id", "':id' => $id");

<?php

class dbManager {
    private static $dbManagerInstance;
    private $dbConnectionInstance;
    private $stime;
    private $etime;
    public $timespent;
    public $numqueries;
    public $queries = array();

    public static function getManager(){
        if (!self::$dbManagerInstance){
            self::$dbManagerInstance = new dbManager();
        }
        return self::$dbManagerInstance;
    }

    // Server details stored in definition file
    private function __construct($db_server=DB_SERVER, $db_user=DB_USER, $db_pass=DB_PASS, $db_params=array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")) {
        if(!$this->dbConnectionInstance)
        {
            try{
                $this->dbConnectionInstance = new PDO($db_server, $db_user, $db_pass, $db_params);
                $this->dbConnectionInstance->setAttribute(PDO::ATTR_PERSISTENT, PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            } catch (PDOException $e) {
                $this->dbConnectionInstance = null;
                die($e->getMessage());
            }
        }
        return $this->dbConnectionInstance;
    }

    private function __destruct(){
        $this->dbConnectionInstance = null;
    }

    private function query($sql, $params = array()) {
        $this->queries[] = $sql;
        $this->numqueries++;
        $this->sTime = microtime();
        $stmt = $this->dbConnectionInstance->prepare($sql);
        $stmt->execute($params);
        $this->eTime = microtime();
        $this->timespent += round($this->eTime - $this->sTime, 4);
        return $stmt;
    }

}

?>

Answer 1

The code you have dosent look too bad. however if i could make a couple small changes (mainly error handling).

both the prepare and execute statements will return false on error. and you can access the error from $this->dbConnectionInstance->errorInfo() in your example above.

also if you plan on using any large queries I suggest using a buffered query: PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true

looks like a good start. Good luck on your CMS.

Answer 2

Looks good, I would add rollback functionality, along with the buffered query/errorInfo suggestions (If you're using a RDBMS that supports transactions):

try {
    $this->dbConnectionInstance->beginTransaction();
    $stmt = $this->dbConnectionInstance->prepare($sql);
    $stmt->execute($params);
    $this->dbConnectionInstance->commit();
}catch(PDOException $e){
    $this->dbConnectionInstance->rollback();
}

commit() , beginTransaction()

EDIT: added links below for more info on buffered queries:

• mysql performance blog
• pdo mysql buffered query support
• stack overflow: pdo buffered query problem

Answer 3

Thank you both for your suggestions, I've now added the rollback and commit into my exception handling, I'm just researching the use of buffered queries, I'm not entirely sure what ths will give me?