PHP Singleton design PDO

Question

I have been using singleton design for PDO on my websites for years with PHP5. I am now migrating to PHP7 and I am seeing errors in my apache log file:

PHP Notice:  Accessing static property Db::$PDOInstances as non static in /var/www/class/class_db_test.php on line 12
PHP Notice:  Accessing static property Db::$PDOInstances as non static in /var/www/class/class_db_test.php on line 13
PHP Notice:  Accessing static property Db::$PDOInstances as non static in /var/www/class/class_db_test.php on line 32

Here is my Singleton class for DB access:

<?php
require_once(dirname(__FILE__) . "/../setting.php");

class Db {
    private static $debug = 1;
    private static $debugPath = "/tmp/sql_debug.log";
    private static $PDOInstances = array("db1"=>"","db2"=>"");

    private function __construct($db_type){
        switch($db_type) {
            case "db1":
                $this->PDOInstances[$db_type] = new PDO('mysql:dbname='.DB_NAME.';host='.DB_HOST,DB_USER ,DB_PWD,array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));
                $this->PDOInstances[$db_type]->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
            break;
            case "db2":
                $this->PDOInstances[$db_type] = new PDO('mysql:dbname='.DB2_NAME.';host='.DB2_HOST,DB2_USER ,DB2_PWD,array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));
                $this->PDOInstances[$db_type]->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
            break;
        }
    }
    public static function getInstance($db_type) {
        if(self::$debug == 1){self::sqlDebug("connection","");}

        if(self::$PDOInstances[$db_type] == null){
            self::$PDOInstances[$db_type] = new self($db_type);
        }
        return self::$PDOInstances[$db_type];
    }

    public function query($db_type,$query){
        if(self::$debug == 1){self::sqlDebug("read request",$query);}
        return $this->PDOInstances[$db_type]->query($query);
    }

    public function exec($db_type,$query){
        if(self::$debug == 1){self::sqlDebug("request",$query);}  
        return $this->PDOInstances[$db_type]->exec($query);
    }

    public function lastInsertId($db_type){
        return $this->PDOInstances[$db_type]->lastInsertId();
    }

    public function quote($db_type,$string){
        if(self::$debug == 1){self::sqlDebug("quote",$string);} 
        return $this->PDOInstances[$db_type]->quote($string);
    }

    public static function sqlDebug($_mode,$_query){
        $today = date("m.d.y-H:m:s"); 
        if($_mode == "connection"){
            $line = $today." Connection: '".$_query."'\r\n";
        }
        else if($_mode == "quote"){
            $line = $today." Quote : '".$_query."'\r\n";
        }
        else if($_mode == "request"){
            $line = $today." Request : '".$_query."'\r\n";
        }
        else if($_mode == "read request"){
            $line = $today." Read request : '".$_query."'\r\n";
        }

        $file_debug = fopen(self::$debugPath, "a+");
        fwrite($file_debug, $line);
        fflush($file_debug);
        fclose($file_debug);
    }

}

?>

And my test code:

<?php
error_reporting(E_ALL); 

require_once(dirname(__FILE__) . "/setting.php");
require_once(dirname(__FILE__) . "/class/class_db_test.php");

$con =  Db::getInstance("db1");
$res = $con->query("db1","SELECT userId from user WHERE userName='test'");

if($res->rowCount() == 1){
    $line = $res->fetchAll();
    $res->closeCursor();
    echo $line[0]['userId'];
}
else{
    echo "0";
}

?>

When I change $this to self:: on line 12, 13 and 32, I get theses errors:

PHP Warning:  Missing argument 2 for Db::query(), called in /var/www/class/class_db_test.php on line 32 and defined in /var/www/class/class_db_test.php on line 30
PHP Notice:  Undefined variable: query in /var/www/class/class_db_test.php on line 31
PHP Notice:  Undefined index: SELECT userId from user WHERE userName='test' in /var/www/class/class_db_test.php on line 32
PHP Fatal error:  Uncaught Error: Call to a member function query() on null in /var/www/class/class_db_test.php:32\nStack trace:\n#0 /var/www/class/class_db_test.php(32): Db->query('SELECT userId f...')\n#1 /var/www/test.php(8): Db->query('db1', 'SELECT userId f...')\n#2 {main}\n  thrown in /var/www/class/class_db_test.php on line 32

Do you have ideas on how to adapt my code in order make it work on PHP7 ?

Thank you

Nigel Ren · Accepted Answer · 2018-10-06 09:06:01Z

One of the main things I can see is that you are using PDOInstances in two different ways. It is used for the list of instances of your Db class (set in the getInstance() static method). BUT it also seems to be where your trying to store the PDO instance for the database (in the constructor).

If you alter the variable you use to store the database (the Db class), then this will at least mean you have both pieces of data...

private $PDOInstances = array("db1"=>"","db2"=>"");  // Remove static
private static $DbInstances = array("db1"=>"","db2"=>"");


public static function getInstance($db_type) {
    if(self::$debug == 1){self::sqlDebug("connection","");}

    if(self::$DbInstances[$db_type] == null){
        self::$DbInstances[$db_type] = new self($db_type);
    }
    return self::$DbInstances[$db_type];
}

I'm also not sure why when you call

$con =  Db::getInstance("db1");

you then need to pass the db type to all of the other methods...

$res = $con->query("db1","SELECT userId from user WHERE userName='test'");

Surely this should be implied by the connection being called with the database type.

If in the constructor you just did (set the PDOInstances without the array part)

$this->PDOInstances = new PDO('mysql:dbname='.DB_NAME.';host='.DB_HOST,DB_USER ,DB_PWD,array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));

then all access of the database are done through this...

public function query($query){
    if(self::$debug == 1){self::sqlDebug("read request",$query);}
    return $this->PDOInstances->query($query);
}

Matthieu · Accepted Answer · 2018-10-06 12:01:46Z

Taking into account Nigel Ren's answer, here is the Db class file modified:

<?php
require_once(dirname(__FILE__) . "/../setting.php");

class Db {
    private static $debug = 1;
    private static $debugPath = "/tmp/sql_debug.log";
    private $PDOInstances = "";  // Remove static
    private static $DbInstances = array("db1"=>"","db2"=>"");

    private function __construct($db_type){
        switch($db_type) {
            case "db1":
                $this->PDOInstances = new PDO('mysql:dbname='.DB_NAME.';host='.DB_HOST,DB_USER ,DB_PWD,array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));
                $this->PDOInstances->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
            break;
            case "db2":
                $this->PDOInstances = new PDO('mysql:dbname='.DB2_NAME.';host='.DB2_HOST,DB2_USER ,DB2_PWD,array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));
                $this->PDOInstances->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
            break;
        }
    }
    public static function getInstance($db_type) {
        if(self::$debug == 1){self::sqlDebug("connection","");}

        if(self::$DbInstances[$db_type] == null){
            self::$DbInstances[$db_type] = new self($db_type);
        }
        return self::$DbInstances[$db_type];
    }

    public function query($query){
        if(self::$debug == 1){self::sqlDebug("read request",$query);}
        return $this->PDOInstances->query($query);
    }

    public function exec($query){
        if(self::$debug == 1){self::sqlDebug("request",$query);}  
        return $this->PDOInstances->exec($query);
    }

    public function lastInsertId(){
        return $this->PDOInstances->lastInsertId();
    }

    public function quote($string){
        if(self::$debug == 1){self::sqlDebug("quote",$string);} 
        return $this->PDOInstances->quote($string);
    }

    public static function sqlDebug($_mode,$_query){
        $today = date("m.d.y-H:m:s"); 
        if($_mode == "connection"){
            $line = $today." Connection: '".$_query."'\r\n";
        }
        else if($_mode == "quote"){
            $line = $today." Quote : '".$_query."'\r\n";
        }
        else if($_mode == "request"){
            $line = $today." Request : '".$_query."'\r\n";
        }
        else if($_mode == "read request"){
            $line = $today." Read request : '".$_query."'\r\n";
        }

        $file_debug = fopen(self::$debugPath, "a+");
        fwrite($file_debug, $line);
        fflush($file_debug);
        fclose($file_debug);
    }

}

?>

And the test file:

<?php
error_reporting(E_ALL); 

require_once(dirname(__FILE__) . "/setting.php");
require_once(dirname(__FILE__) . "/class/class_db_test.php");

$con =  Db::getInstance("db1");
$res = $con->query("SELECT userId from user WHERE userName='test'");

if($res->rowCount() == 1){
    $line = $res->fetchAll();
    $res->closeCursor();
    echo $line[0]['userId'];
}
else{
    echo "0";
}

?>

Everything is working without PHP Notice/Error.

Thank you !

Collectives™ on Stack Overflow

PHP Singleton design PDO

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related