PDO Connections - max connections

2019-09-06 13:58发布

问题:

I have the following class:

<?php
class Database {

    protected static $_instance;
    protected $_connection;
    protected $_dbhost=DB_HOST;
    protected $_dbname=DB_DBNAME;
    protected $_username = DB_USER;
    protected $_password = DB_PASS;
    protected $_dbType = "mysql";

    /**
    * Singleton pattern implementation makes "new" unavailable
    */
    protected function __construct()
    {
        $this->_connection = 
            new PDO($this->_dbType . ":host=" . $this->_dbhost . ";dbname=" . $this->_dbname, $this->_username, $this->_password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8", PDO::ATTR_PERSISTENT => true));
    }

    public function getConnection()
    {
        return $this->_connection;
    }

    public static function getInstance()
    {
        if (null === self::$_instance) {
            self::$_instance = new self();
        }
        return self::$_instance;
    }

    /**
    * Singleton pattern implementation makes "clone" unavailable
    */
    protected function __clone()
    {}
}

?>  

From: https://stackoverflow.com/a/3010486

Now I have a second class, which has all the functions that access the DB.

My question:
I had a problem with max connections in my script, therefore I use the new Database class. In my helper class I do it like this:

<?php   
class helper {

    function getAllInvitesFromPlayer($uid) {
        $sql = "SELECT request_id FROM ".DBPREFIX."invites WHERE inviter_id = :uid AND joined = 1";

        try {
            $db = Database::getInstance()->getConnection();
            $stmt = $db->prepare($sql);
            $stmt->bindParam("uid", $uid);
            $stmt->execute();
            $content = $stmt->fetch(PDO::FETCH_LAZY);
            $db = null;
            return $content;
        } catch(PDOException $e) {
            echo $e->getMessage();
        }
    }
}

After the "try" is it correct to use the $db = Database::getInstance()->getConnection(); or shall I "outsource" it to a class variable and access it like $this->_db; in each function and try ?
What is better to avoid getting too much connections to my db?

And its surely better just to initialize the helper class once in a file, right? and not always calling $helper = new helper() because this would always create a new DB connection, right?

Thank you for your help!

回答1:

It doesn't really matter.
As long as you are using getInstance(), it would be always the same single connection, no matter which way or where you call it.

For sake of incapsulation, it's better to assign db connection to a class variable.

Also note that your use of try..catch is wrong. It shouldn't be there.

So, something like this

<?php   
class helper {

    protected function __construct()
    {
        $this->db = Database::getInstance()->getConnection();
    }

    function getAllInvitesFromPlayer($uid) {
        $sql = "SELECT request_id FROM ".DBPREFIX."invites WHERE inviter_id = ? AND joined = 1";
        $stmt = $this->db->prepare($sql);
        $stmt->execute(array($uid));
        return $stmt->fetchColumn(); // will return one invite actually
        //or
        return $stmt->fetchAll(PDO::FETCH_COLUMN, 0); // will return ALL invites indeed
    }
}