My PDO (PHP Database Object) Singleton Class

If you aren’t using PDO instead of mysqli (or just plain’ol’mysql) functions by now, it probably is time to look into what you are missing out on.  If you are anything like me, mysql_connect() and mysql_query() were probably some of the first PHP functions you ever learned; it seems that many PHP “developers” think that PHP and MySQL are synonymous, that they belong together.

The fact is, the old mysql and mysqli functions are little more than hacks that were implemented as a work-around so that we could at least get the data and do something with it.  I mean where else in PHP do you ever deal with a “result resource?”  

PDO does everything in proper Object Oriented fashion, which is just how I like it.  There are a number of parameters you can set which define just how you get the returned data; I always prefer to end up with a nice associative array.  So gone are the days of using a while loop with mysql_fetch_array; instead we get to use foreach to run through the array just like everywhere else in PHP.  Overall, dealing with the data that is retrieved becomes much more familiar.

One other major benefit I want to hit on is portability.  The only place that you specify in the code that you are using MySQL or PostgreSQL or whatever else it is you are using is in the instantiation of the PDO object.  So if you need to move your application to a different server and use another database type (assuming you are a careful coder and don’t use database type specific queries of course) it can be a very simple thing to pick it up and move it to another server!


Now, on to the meat and potatoes of this post.  

While I like PDO a lot, switching over to it from mysqli can be a pain.  There are a lot of tutorials around on how to get started with PDO, and I’m not going to duplicate that by any means.  My goal was to come up with a way that I could create a single database connection and pass it around through all the functions and classes of an application, thereby avoid opening multiple connections.  I also wanted to have several functions that could be used to perform frequent database actions such as insert, update, delete, etc.

After pondering it a bit, I decide to go with the Singleton model.  Of course this does mean that it can only be used to connect to a single database, and that can be a significant limitation.  But in an application where only one database exists, this class has proven to be extremely useful to me!

I’ll probably update it in the future, after all this has been an ongoing work for the past few months.  But the class currently is as follows:

class pdos {

    private static $singleton;

    private $server   = ""; //database server
    private $user     = ""; //database login name
    private $pass     = ""; //database login password
    private $database = ""; //database name

    private $dbh;

    private function __construct($server=null, $user=null, $pass=null, $database=null) {
        if ( $server == null || $user == null || $pass == null || $database == null ) {
            throw new Exception("Connection information must be passed in when the object is first created.");
        }
        $this->server = $server;
        $this->user = $user;
        $this->pass = $pass;
        $this->database = $database;
        try {
            $this->dbh = new PDO("mysql:host=" . $this->server . ";dbname=" . $this->database, $this->user, $this->pass);
            $this->dbh->setAttribute (PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            throw new Exception("Could not connect to database: " . $e->getMessage());
        }
    }

    public static function obtain($server=null, $user=null, $pass=null, $database=null) {
        //get the PDO handle
        if ( !self::$singleton ) {
            self::$singleton = new pdos($server, $user, $pass, $database);
        }

        return self::$singleton->dbh;
    }

    public static function pass($server=null, $user=null, $pass=null, $database=null){
        //get this class itself
        if ( !self::$singleton ) {
            self::$singleton = new pdos($server, $user, $pass, $database);
        }

        return self::$singleton;
    }

    public function insert($table, $data){
        //perform an insertion query

        $columns = "";
        $values = "";

        foreach( $data as $key => $value ){
            $columns .= $key . ", ";
            $values .= ":" . $key . ", ";
        }
        $columns = rtrim($columns, ", ");
        $values = rtrim($values, ", ");

        $sth = $this->dbh->prepare("INSERT INTO " . $table . " (" . $columns . ") VALUES(" . $values . ")");
        foreach( $data as $key => $value ){
            if ( strpos($key, ".") !== false ){
                $sp = explode(".", $key);
                $p = $sp[1];
            } else {
                $p = $key;
            }
            $sth->bindValue(":" . $p, $value);
        }
        if ( !$sth->execute() ){
            return false;
        }
        $id = $this->dbh->lastInsertID();
        $sth->closeCursor();

        return $id;
    }

    public function update($table, $data, $where){
        //perform an update query; requires "where" array

        $set = "";
        foreach( $data as $key => $value ){
            $set .= $key . "=:" . $key . ", ";
        }
        $set = rtrim($set, ", ");

        $wh = "";
        foreach( $where as $key => $value ){
            if ( strpos($key, ".") !== false ){
                $sp = explode(".", $key);
                $p = $sp[1];
            } else {
                $p = $key;
            } 
            $wh .= $key . "=:" . $p . " AND ";
        }
        $wh = rtrim($wh, " AND ");

        $sth = $this->dbh->prepare("UPDATE " . $table . " SET " . $set . " WHERE " . $wh);
        foreach( $data as $key => $value ){
            $sth->bindValue(":" . $key, $value);
        }
        foreach( $where as $key => $value ){
            if ( strpos($key, ".") !== false ){
                $sp = explode(".", $key);
                $p = $sp[1];
            } else {
                $p = $key;
            }
            $sth->bindValue(":" . $p, $value);
        }
        if ( !$sth->execute() ){
            return false;
        }
        $id = $this->dbh->lastInsertID();
        $sth->closeCursor();

        return $id;
    }

    public function query($table, $columns, $where=false, $like=false, $misc=false){
        //perform a query and return an associative array containing all rows
        //accepts either string or array for columns
        //like takes array and the % should be included where you want it in the value
        //$misc is for things like ORDER BY, LIMIT, GROUP BY, etc --> probably best to avoid using this
        //in these cases if there is a variable as it won't be passed through bindValue and therefore not
        //sanitized
        if ( is_array($columns) ){
            $cols = "";
            foreach( $columns as $col ){
                $cols .= $col . ", ";
            }
            $columns = rtrim($cols, ", ");
        }

        $wh = ""; //determine if we need "WHERE"

        if ( ($where) && ( is_array($where) ) ){
            $w = "";
            $wh = " WHERE ";
            foreach( $where as $key => $value ){
                if ( strpos($key, ".") !== false ){
                    $sp = explode(".", $key);
                    $p = $sp[1];
                } else {
                    $p = $key;
                }
                $w .= $key . "=:" . $p . " AND ";
            }
            $w = substr($w, 0, -5);
        } else {
            $w = "";
        }

        if ( ($like) && ( is_array($like) ) ){
            if ( strlen($w) > 0 ){
                $l = " AND ";
            } else {
                $l = "";
            }
            $wh = " WHERE ";
            foreach( $like as $key => $value ){
                if ( strpos($key, ".") !== false ){
                    $sp = explode(".", $key);
                    $p = $sp[1];
                } else {
                    $p = $key;
                }
                $l .= $key . " LIKE :" . $p . " AND ";
            }
            $l = substr($l, 0, -5);
        } else {
            $l = "";
        }

        $sth = $this->dbh->prepare("SELECT " . $columns . " FROM " . $table . $wh . $w . $l . " " . $misc);
        if ( ($where) && ( is_array($where) ) ){
            foreach( $where as $key => $value ){
                if ( strpos($key, ".") !== false ){
                    $sp = explode(".", $key);
                    $p = $sp[1];
                } else {
                    $p = $key;
                }
                $sth->bindValue(":" . $p, $value);
            }
        }
        if ( ($like) && ( is_array($like) ) ){
            foreach( $like as $key => $value ){
                if ( strpos($key, ".") !== false ){
                    $sp = explode(".", $key);
                    $p = $sp[1];
                } else {
                    $p = $key;
                }
                $sth->bindValue(":" . $p, $value);
            }
        }
        if ( !$sth->execute() ){
            return false;
        }
        $result = $sth->fetchAll(PDO::FETCH_ASSOC);
        $sth->closeCursor();
        $return = array();
        foreach($result as $row){
            $return[] = $row;
        }
        return $return;
    }

    public function select_single($table, $columns, $where=false, $like=false, $misc=false){
        //perform a query that will return only a single row, return false if no rows
        //accepts either string or array for columns
        //like takes array and the % should be included where you want it in the value
        if ( is_array($columns) ){
            $cols = "";
            foreach( $columns as $col ){
                $cols .= $col . ", ";
            }
            $columns = rtrim($cols, ", ");
        }

        $wh = ""; //determine if we need "WHERE"

        if ( ($where) && ( is_array($where) ) ){
            $w = "";
            $wh = " WHERE ";
            foreach( $where as $key => $value ){
                if ( strpos($key, ".") !== false ){
                    $sp = explode(".", $key);
                    $p = $sp[1];
                } else {
                    $p = $key;
                }
                $w .= $key . "=:" . $p . " AND ";
            }
            $w = substr($w, 0, -5);
        } else {
            $w = "";
        }

        if ( ($like) && ( is_array($like) ) ){
            if ( strlen($w) > 0 ){
                $l = " AND ";
            } else {
                $l = "";
            }
            $wh = " WHERE ";
            foreach( $like as $key => $value ){
                if ( strpos($key, ".") !== false ){
                    $sp = explode(".", $key);
                    $p = $sp[1];
                } else {
                    $p = $key;
                }
                $l .= $key . " LIKE :" . $p . " AND ";
            }
            $l = substr($l, 0, -5);
        } else {
            $l = "";
        }

        $sth = $this->dbh->prepare("SELECT " . $columns . " FROM " . $table . $wh . $w . $l . $misc);
        if ( ($where) && ( is_array($where) ) ){
            foreach( $where as $key => $value ){
                if ( strpos($key, ".") !== false ){
                    $sp = explode(".", $key);
                    $p = $sp[1];
                } else {
                    $p = $key;
                }
                $sth->bindValue(":" . $p, $value);
            }
        }
        if ( ($like) && ( is_array($like) ) ){
            foreach( $like as $key => $value ){
                if ( strpos($key, ".") !== false ){
                    $sp = explode(".", $key);
                    $p = $sp[1];
                } else {
                    $p = $key;
                }
                $sth->bindValue(":" . $p, $value);
            }
        }
        if ( !$sth->execute() ){
            return false;
        }
        $row = $sth->fetch(PDO::FETCH_ASSOC);
        $sth->closeCursor();
        if ( count($row) == 0 ){
            return false;
        }
        return $row;
    }

    public function delete($table, $where){
        //function to delete a column, where is an associative array
        if ( ($where) && ( is_array($where) ) ){
            $w = " WHERE ";
            foreach( $where as $key => $value ){
                if ( strpos($key, ".") !== false ){
                    $sp = explode(".", $key);
                    $p = $sp[1];
                } else {
                    $p = $key;
                }
                $w .= $key . "=:" . $p . " AND ";
            }
            $w = substr($w, 0, -5);
        } else {
            $w = "";
        }

        $sth = $this->dbh->prepare("DELETE FROM " . $table . $w);
        if ( ($where) && ( is_array($where) ) ){
            foreach( $where as $key => $value ){
                if ( strpos($key, ".") !== false ){
                    $sp = explode(".", $key);
                    $p = $sp[1];
                } else {
                    $p = $key;
                }
                $sth->bindValue(":" . $p, $value);
            }
        }
        if ( !$sth->execute() ){
            return false;
        }
        $sth->closeCursor();
        return true;
    }


}

Now, the question is: how the heck do I use this thing?  To find out, read on my friend!

Cody (17 Posts)