SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2011
    Posts
    70
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Best Way to create a database handler with PDO

    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 Code:
    <?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_ERRMODEPDO::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. #2
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    699
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    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:
    http://docs.doctrine-project.org/pro...ipulation.html


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •