Best Way to create a database handler with PDO

PHP
1

Hello,

I am trying to create an OO database handler using PDO but I’m not sure which way to go.

I have created this so far. What I am not sure about however, is whether I would just be better off extending the PDO object itself and adding any features that I want to add that way.

<?php
/**
 * This class establishes connection to database via PDO and provides functions
 * for adding, updating and deleting data.
 *
 * @version 1.0
 * @package school
 */

namespace school\\abs;

use PDO;

abstract class  Abstract_Database {
    /**
     *
     * @var string
     * @access protected
     */
    protected $dbType;
    /**
     *
     * @var string
     * @access protected
     */
    protected $host;
    /**
     *
     * @var string
     * @access protected
     */
    protected $dbName;
    /**
     *
     * @var string
     * @access protected
     */
    protected $user;
    /**
     *
     * @var string
     * @access protected
     */
    protected $pass;
    /**
     *
     * @var PDO object
     * @access protected
     */
    protected $db;
    /**
     *
     * @var string
     * @access protected
     */
    protected $query;



    /**
     * Instantiates the class and prepares the PDO object for interaction.
     *
     * @access public
     * @param string $db_typeIn Database Type
     * @param string $hostIn Hostname
     * @param string $dbNameIn Database name
     * @param string $userIn Username
     * @param string $passIn Password
     */
    public function __construct($db_typeIn, $hostIn, $dbNameIn, $userIn, $passIn)
    {
        // declare local var and assign modular vars their start value.
        $datasource;
        $this->dbType = $db_typeIn;
        $this->host = $hostIn;
        $this->dbName = $dbNameIn;
        $this->user = $userIn;
        $this->pass = $passIn;

        // create data source string
        $datasource = "{$this->dbType}:host={$this->host};dbname={$this->dbName};";
        try
        {
            // create new PDO object and set error mode attributes.
            $this->db = new \\PDO($datasource, $this->user, $this->pass);
            $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        catch (PDOException $e)
        {
            // catch exception
            echo 'Connection Error: ' . $e.getMessage();
        }
    }

   /**
    * Prepares sql statement and binds any parameters that are passed in.
    *
    * @access public
    * @param string $sqlIn SQL statement
    * @param array $paramsIn sql statement parameters
    */
    public function executeQuery($sqlIn, array $paramsIn = null)
    {
        try
        {
            $sql = $sqlIn;
            $params = $paramasIn;

            // prepare statement and execute
            $this->query = $this->db->prepare( $sql );
            if ($paramasIn)
            {
                $this->query->execute( $params );
            }
            else
            {
                $this->query->execute();
            }
        }
        catch ( PDOException $e )
        {
            // catch exception
            echo 'Exception Caught: ' . $e->getMessage();
        }

    }

    /**
     * Fetches results from the PDO statement returned from the execute method.
     *
     * @access public
     * @param String $type NUM, ASSOC, OBJ
     * @param String $quantity ALL, SINGLE
     */
    public function fetch( $type, $quantity )
    {
        $fetchType = ($quantity = 'ALL') ? 'fetch' : 'fetchAll';


        switch($type)
        {
            case 'NUM':
                $result = $this->query->$fetchType(PDO::FETCH_NUM);
                break;
            case 'ASSOC':
                $result = $this->query->$fetchType(PDO::FETCH_ASSOC);
                break;
            case 'OBJ':
                $result = $this->query->$fetchType(PDO::FETCH_OBJ);
                break;
        }
        return $result;
    }

    /**
     * Counts the number results based on the criteria passed as arguments
     *
     * @access public
     * @param string $from table name of from which the count will be taken
     * @param string $where SQL WHERE clause ex: "foo = 'bar'"
     * @return integer
     */
    public function resultCount( $from, $where = null)
    {

        $sql = "SELECT COUNT(*) FROM {$from}";
        if ( !is_null( $where ) )
        {
            str_replace('\\"', '\\'', $where);
            $sql .= " WHERE {$where}";
        }
        $this->executeQuery($sql);
        $result = $this->query->fetchColumn();
        return $result;
    }

    /**
     * Returns the number of rows effected by add, update and delete queries.
     *
     * @access public
     * @return integer
     */
    public function affectedRows()
    {
        $result = $this->query->rowCount();
        return $result;
    }

}
2

You don’t have to use PDO for this and you don’t have to use the type hinting like I did. The concept is still applicable for any database connection.

Whether it is Mysql (hopefully not), Mysqli, or PDO, create the connection into a variable and then pass the connection into the classes that need a database connection.

3

To a certain extent it’s a question of taste. I myself would not extend PDO as it would tie my class directly to PDO. It’s possible that at some point you may want to connect to a non-PDO supported database.

Consider looking through the Doctrine2 connection object’s documentation. It very similar to yours: