PHP PDO close mysql connection in DatabaseFactory file

Question

do you guys have an idea why I can't close the mysql connection?

Here is my code:

class DatabaseFactory {
    private static $factory;
    private $connection = array(
        "DB_HOST" => "localhost",
        "DB_PASS" => "*************",
        "DB_USER" => "*************",
        "DB_PORT" => "3306",
        "DB_CHARSET" => "utf8"
    );

    public static
    function getFactory() {
        if(!self::$factory) {
            self::$factory = new DatabaseFactory();
        }
        return self::$factory;
    }

    private $db;

    public
    function getconnection($name) {
        echo "opened";
        try {
            $options = array(
                \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_OBJ,
                \PDO::ATTR_ERRMODE => \PDO::ERRMODE_WARNING
            );
            $this->db = new \PDO('mysql:host='.$this->connection["DB_HOST"].';dbname='.$name.';port=' .$this->connection["DB_PORT"].';charset='.$this->connection["DB_CHARSET"], $this->connection["DB_USER"], $this->connection["DB_PASS"], $options);
        } catch (PDOException $e) {
            exit($e->getMessage());
        }
        return $this->db;
    }

    public
    function __destruct() {
        echo "closed";
        try {
            $this->db = null;
            unset($this->db);
        } catch (PDOException $e) {
            exit($e->getMessage());
        }
    }
}

$database = DatabaseFactory::getFactory()->getconnection("Admin");
$query = $database->prepare("SELECT * FROM tester WHERE t_u_id = :u_id");
$query->execute(array(":u_id" => "281123341528-D050C4-91A0BA-1CB0C8-8112334152855AC373"));

The connection is connecting but it is not closed. The method __destruct will be called but the mysql total connections count doesn't decrease.

Possible duplicate of [Is closing the mysql connection important?](https://stackoverflow.com/questions/880885/is-closing-the-mysql-connection-important) — ficuscr, Sep 14 '17 at 16:49
My problem is not if closing is important, but how to close the mysql connection in my code. — Markus Nowotny, Sep 14 '17 at 16:51
Possible duplicate of [PDO closing connection](https://stackoverflow.com/questions/18277233/pdo-closing-connection) — M. Eriksson, Sep 14 '17 at 16:52
Linked wrong one, sec. Wanted to explain what you are seeing on mysql server side. — ficuscr, Sep 14 '17 at 16:53
*"mysql total connections count"* Do you mean `connections%` or `threads _connetecd`? Good info [here](https://stackoverflow.com/questions/6502036/how-can-i-see-how-many-mysql-connections-are-open) and [here](https://dev.mysql.com/doc/refman/5.6/en/connection-threads.html). — ficuscr, Sep 14 '17 at 17:00

idelara · Answer 1 · 2017-09-14T17:12:51.360

Why don't you implement and call a method (manually instead of relying on __destruct()) like Fil does in his response here?

Add the following static method to your DatabaseFactory class

static function closeConnection(&$conn) {
    $conn=null;
}

So after your connections, you can NULL your connection reference (which is akin to calling $mysqli_conn->close();) later in your code like this:

$database = DatabaseFactory::getFactory()->getconnection("Admin");
$query = $database->prepare("SELECT * FROM tester WHERE t_u_id = :u_id");
$query->execute(array(":u_id" => "281123341528-D050C4-91A0BA-1CB0C8-8112334152855AC373"));

DatabaseFactory::closeConnection($database)

EDIT:

Also, I wanted to mention that by relying on __destruct(), you are subject to the following as per the PHP docs:

PHP 5 introduces a destructor concept similar to that of other object-oriented languages, such as C++. The destructor method will be called as soon as there are no other references to a particular object, or in any order during the shutdown sequence.

In regards to your question, the method __destruct() might not be called because the static class var private static $factory is never being nulled. Therefore, the class always has a reference to "itself" preventing __destruct() from being called.

I hope this helps!

PHP PDO close mysql connection in DatabaseFactory file

1 Answers1