DB class having pdo, on inheriting should not re-initiate connection

Question

When I'm calling different models in one then multiple times db connection establishes, I want only one, is it possible. Below is piece of my mvc

class Database{
    private $db;
    private $stmt;
    function __construct()
    {
        parent::__construct();
        $root = dirname(dirname(__FILE__));
        $this->config = parse_ini_file($root . '/app/config.ini', true);

        $db = $this->config['database settings'];
        $host = $db['host'];
        $user = $db['user'];
        $pword = $db['pword'];
        $db_name = $db['db'];
        $this->db = new PDO("mysql:host=$host;dbname=$db_name", $user, $pword, array(
            PDO::MYSQL_ATTR_FOUND_ROWS => true, PDO::ATTR_PERSISTENT => true
        ));
        $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);        
    }
    function executeQuery($query, $params = array())
    {
         $this->stmt = $this->db->prepare($query);
         if (! ($this->stmt)) {
           throw new Exception('Query failed while preparing');
         }
         try {
            $this->stmt->execute($params);
         } catch (PDOException $e) {
            throw $e;
         }
     }
     function getRecords($query, $params = array(), $array = false, $all = false)
     {
          $this->executeQuery($query, $params);

           $records = array();
          if ($this->totalRecords() > 0) {
             if ($array) {
                $this->stmt->setFetchMode(PDO::FETCH_ASSOC);
             } else {
                 $this->stmt->setFetchMode(PDO::FETCH_OBJ);
             }
            if ($all) {
               $records = $this->stmt->fetchAll();
            } else {
                while (($record = $this->stmt->fetch()) !== false) {
                   $records[] = $record;
               }
           }
        }
        return $records;
     }
  }

Below is the model where I use it

class TestModel extends Database{
  function __construct(){
    parent::__construct();
  }
  function getUsers(){
     return $this->getRecords("SELECT * FROM users");
  }
}

Below is another model I want to call the method getusers to use it for some purpose.

require("path_to_testmodel"); 
require("path_to_some_model");
require("path_to_some_other_model");
class TestModel2 extends Database{
    function __construct(){
       parent::__construct();
       $this->test_model = new TestModel();
       $this->some_model = new SomeModel();
       $this->some_other_model = new SomeOtherModel();
    }
    function doSomething(){
       $users = $this->test_model->getUsers();
    }
}

If I am including multiple models like TestModel() in one of the models, here seen is every time a database connection is established, sometimes I get too many connections error or in some system I get memory allocation issue.

Please help me to fix this.

Answer 1

Try to save your DB connection in static property.

class Database{
    static private $theOnlyConnection = null;
    private $db;
    private $stmt;

    public function __construct()
    {
        parent::__construct(); // Database has no parent, what is this line for?
        if(null === self::$theOnlyConnection) {
            $root = dirname(dirname(__FILE__));
            $config = parse_ini_file($root . '/app/config.ini', true);

            $db = $config['database settings'];
            $host = $db['host'];
            $user = $db['user'];
            $pword = $db['pword'];
            $db_name = $db['db'];
            self::$theOnlyConnection = new PDO("mysql:host=$host;dbname=$db_name", $user, $pword, array(
                PDO::MYSQL_ATTR_FOUND_ROWS => true, PDO::ATTR_PERSISTENT => true
            ));
            self::$theOnlyConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            self::$theOnlyConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);        
        }

        $this->db = self::$theOnlyConnection;
    }

Note that this will cause connection to close at the end of your script, not on object destruction. It's OK for most use cases, yet if you want to disconnect from databse earlier, you'll need to track how many objects you have and destroy connection once the number is 0.

class Database{
    static private $theOnlyConnection = null;
    static private $modelObjectCount = 0;
    private $db;
    private $stmt;

    public function __construct()
    {
        self::$modelObjectCount++;
        if(null === self::$theOnlyConnection) {
            // connect
        }

        $this->db = self::$theOnlyConnection;
    }

    public function __destruct() {
        self::$modelObjectCount--;
        if(0 == self::$modelObjectCount) {
            // close connection
            self::$theOnlyConnection = null;
        }
    }