Use of PDO in classes

Question

I have a few classes that perform some MySQL queries and prepared statements. However, I am lost in how to incorporate my PDO object within those classes. For example, I want to do something like this:

<?php

$dbh = new PDO(...);

class Foo extends PDO {
    public $dbh;

    public function bar() {
        $this->dbh->prepare('SELECT * FROM table');
        $this->dbh->execute();

    }
}


?>

Unfortunately, it doesn't work. Can anyone suggest an elegant way to do this? Thanks for your time. Sorry I'm new to this, please leave any comments if you are unclear about anything and I'll do my best to respond!

Solution 1

You can instantiate your connection to the database in a class that implement the singleton pattern. The connection will be done once and this class will be easily accessible by all of your other objects / scripts.

i use a class called "Core" in the following example;

class Core
{
    public $dbh; // handle of the db connexion
    private static $instance;

    private function __construct()
    {
        // building data source name from config
        $dsn = 'pgsql:host=' . Config::read('db.host') .
               ';dbname='    . Config::read('db.basename') .
               ';port='      . Config::read('db.port') .
               ';connect_timeout=15';
        // getting DB user from config                
        $user = Config::read('db.user');
        // getting DB password from config                
        $password = Config::read('db.password');

        $this->dbh = new PDO($dsn, $user, $password);
    }

    public static function getInstance()
    {
        if (!isset(self::$instance))
        {
            $object = __CLASS__;
            self::$instance = new $object;
        }
        return self::$instance;
    }

    // others global functions
}

this class take parameters from a static class called "Config" where you can store your configuration:

<?php
class Config
{
    static $confArray;

    public static function read($name)
    {
        return self::$confArray[$name];
    }

    public static function write($name, $value)
    {
        self::$confArray[$name] = $value;
    }

}

// db
Config::write('db.host', '127.0.0.1');
Config::write('db.port', '5432');
Config::write('db.basename', 'mydb');
Config::write('db.user', 'myuser');
Config::write('db.password', 'mypassword');

in all your scripts / objects you just have to get the instance of Core and then query the DB

$sql = "select login, email from users where id = :id";

try {
    $core = Core::getInstance();
    $stmt = $core->dbh->prepare($sql);
    $stmt->bindParam(':id', $this->id, PDO::PARAM_INT);

    if ($stmt->execute()) {
        $o = $stmt->fetch(PDO::FETCH_OBJ);
        // blablabla....

If you need more information about singleton look at the PHP doc http://php.net/manual/en/language.oop5.patterns.php

Solution 2

Here is a mostly complete working cut & paste example of Guillaume Boschini's answer above.

A populated DB table (MySQL):

CREATE TABLE `useraddress` (                                                                                                                                        
  `addressid` int(10) unsigned NOT NULL AUTO_INCREMENT,                                                                                                                             
  `userid` int(10) unsigned NOT NULL,                                                                                                                                               
  `addresstitle` char(100) NOT NULL,                                                                                                                        
  `streetaddressa` char(100) NOT NULL,
  `streetaddressb` char(100) DEFAULT NULL,
  `unit` char(50) DEFAULT NULL,
  `city` char(50) NOT NULL,
  `state` char(2) NOT NULL,
  `zip` int(5) NOT NULL,
  `zipplusfour` int(4) DEFAULT NULL,
  PRIMARY KEY (`addressid`),
  KEY `userid` (`userid`),
  CONSTRAINT `useraddress_fk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

In /DBLibrary/pdocore.php:

<?php

    Config::write('db.host', 'localhost');
    Config::write('db.port', '3306');
    Config::write('db.basename', 'DBName');
    Config::write('db.user', 'DBUser');
    Config::write('db.password', 'DBPassword');

    class Config {

        static $confArray;

        public static function read($name) {
            return self::$confArray[$name];
        }

        public static function write($name, $value) {
            self::$confArray[$name] = $value;
        }

    }

    class Core {
        public $dbh; // handle of the db connection
        private static $instance;

        private function __construct()  {

            // building data source name from config
            $dsn = 'mysql:host=' . Config::read('db.host') . ';dbname=' . Config::read('db.basename') . ';port=' . Config::read('db.port') .';connect_timeout=15';

            // getting DB user from config
            $user = Config::read('db.user');

            // getting DB password from config
            $password = Config::read('db.password');

            $this->dbh = new PDO($dsn, $user, $password);
            $this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        }

        public static function getInstance() {
            if (!isset(self::$instance)) {
                $object = __CLASS__;
                self::$instance = new $object;
            }
            return self::$instance;
        }

        // others global functions
    }
?>

In /objectsLibrary/SYS_UserAddress.php:

<?php

    define('k_uaddress_addressid','addressid');
    define('k_uaddress_userid','userid');
    define('k_uaddress_addresstitle','addresstitle');
    define('k_uaddress_addressa','streetaddressa');
    define('k_uaddress_addressb','streetaddressb');
    define('k_uaddress_unit','unit');
    define('k_uaddress_city','city');
    define('k_uaddress_state','state');
    define('k_uaddress_zip','zip');
    define('k_uaddress_zipplusfour','zipplusfour');

    require_once '../DBLibrary/pdocore.php';

    class SYS_UserAddress {

        public $addressid;
        public $userid;
        public $addresstitle;
        public $addressa;
        public $addressb;
        public $unit;
        public $city;
        public $state;
        public $zip;
        public $zipplusfour;

        public function SYS_UserAddressByAddressId($_addressid) {

            $returnValue=FALSE;

            $query='select * from useraddress where ' . k_uaddress_addressid . '=:addressid';

            try {
                $pdoCore = Core::getInstance();
                $pdoObject = $pdoCore->dbh->prepare($query);

                $queryArray = array(':addressid'=>$_addressid);

                if ($pdoObject->execute($queryArray)) {

                    $pdoObject->setFetchMode(PDO::FETCH_ASSOC);;

                    while ($addressrow = $pdoObject->fetch()) {

                        $this->addressid=$addressrow[k_uaddress_addressid];
                        $this->userid=$addressrow[k_uaddress_userid];
                        $this->addresstitle=$addressrow[k_uaddress_addresstitle];
                        $this->addressa=$addressrow[k_uaddress_addressa];
                        $this->addressb=$addressrow[k_uaddress_addressb];
                        $this->unit=$addressrow[k_uaddress_unit];
                        $this->city=$addressrow[k_uaddress_city];
                        $this->zip=$addressrow[k_uaddress_zip];
                        $this->zipplusfour=$addressrow[k_uaddress_zipplusfour];

                    }
                    $returnValue=TRUE;
                }
            }
            catch(PDOException $pe) {
                trigger_error('Could not connect to MySQL database. ' . $pe->getMessage() , E_USER_ERROR);
            }

            return $returnValue;

        }
    }

    $test=1;
    $testAddressId=2;

    if($test>0) {

        $testAddress = new SYS_UserAddress();

        $testAddress->SYS_UserAddressByAddressId($testAddressId);

        echo '<pre>';
        echo print_r($testAddress);
        echo '</pre>';

    }

?>

The post above really helped me. This post I am making now would have gotten me to where I wanted to be faster. That is all. If anything isn't right, I'll be around to fix it.

Solution 3

$dbh isn't within the scope of Foo, do this instead:

class Foo /*extends PDO*/
{
    public $dbh;

    public function __construct()
    {
        $dbh = new PDO(/*...*/);
    }

    public function bar()
    {
        $this->dbh->prepare('SELECT * FROM table');
        return $this->dbh->execute();
    }
}

Also, Foo doesn't need to extend PDO.

Comments

[VolkerK]

Does your class Foo really extend class PDO? Or does an instance of FOO simply use and instance PDO. Your example looks more like using than extending.

[axsuul]

Would that slow the database/script down since I am making a new connection each time I create an object?

[Alix Axel]

Why would you need to instantiate the object more than once?

[axsuul]

I meant to say I have 14 different objects in a script, and they all need database connections, so therefore wouldn't I be making 14 database connections?

[Alix Axel]

No, Foo will only be instantiated once, hence only one PDO connection.

[axsuul]

But what if I have other objects, like Bar, Moo, Cow, Barn and they also need to make database queries, so wouldn't that be +4 more db connections? Thanks for sticking with me

[Alix Axel]

Yes, in those casse we normally use a something called singleton. Read about it on Wikipedia and if you still have question ask another question here.

[axsuul]

Wow this is awesome and works perfectly. Thanks for the suggestion, it's nice and elegant :) This will definitely be one of the coding practices that will stick with me through different projects!

[Guillaume Boschini]

you're welcome; i use the Core class to store other usefull things that need to be accessible in many of my scripts, like the object that contains the logged user... it's very convenient

[Ben]

I think 'private static $instance;' needs to be declared as a property, otherwise you will get Access to undeclared static property: Core::$instance.

[Mr_Chimp]

You might want to make __construct() private as well, to ensure that there is only ever one instance. By doing this you will only be able to get an instance by using getInstance. "$core = new Core()" would raise an error.

[Dan]

Is this the most efficient way to have a PDO 'connection class' and then use queries throughout the site? and with this approach would you need to use $core = null;?

[Guillaume Boschini]

@Silver89 : you can also use the Dependency Injection pattern: you inject in your model classes by constructor or by setter an instance of your PDO object. There is a nice presentation of this pattern by Fabien Potencier on slideshare: slideshare.net/fabpot/dependency-injection-phpday-2010

[Klaaz]

I think this is a great way to instantiate PDO. But I like to use variables from a config file as values for the host, database, user and password. Is there a way to do this in this example? I guess I have to put them somewhere in the config class but when I create a __construct with global variables it does not work.

[Erdem Ece]

// others global functions. for example I want to use phpmailler with this. how can I add it?

[Erdem Ece]

for example I want to use PHPmailler with this. where should i add it or how? thanks

[BradChesney79]

This was a good start-- all my includes & requires are no longer in my webroot. We'll pick an arbitrary file name-- 'webpage.php'. In your 'webpage.php' file you would require /objectsLibrary/SYS_UserAddress.php and /thirdPartyLibrary/phpMailer.php; then you would be free to use both in 'webpage.php'.

[Abraham Brookes]

Is there any reason not to do this?