PDO closing connection

Question

Just a rather simple question with regards to PDO compared to MySQLi.

With MySQLi, to close the connection you could do:

$this->connection->close();

However with PDO it states you open the connection using:

$this->connection = new PDO();

but to close the connection you set it to null.

$this->connection = null;

Is this correct and will this actually free the PDO connection? (I know it does as it is set to null.) I mean with MySQLi you have to call a function (close) to close the connection. Is PDO as easy as = null to disconnect? Or is there a function to close the connection?

Answer 1

According to documentation you're correct (http://php.net/manual/en/pdo.connections.php):

The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.

Note that if you initialise the PDO object as a persistent connection it will not automatically close the connection.

Answer 2

$conn=new PDO("mysql:host=$host;dbname=$dbname",$user,$pass);
    // If this is your connection then you have to assign null
    // to your connection variable as follows:
$conn=null;
    // By this way you can close connection in PDO.

Answer 3

Its more than just setting the connection to null. That may be what the documentation says, but that is not the truth for mysql. The connection will stay around for a bit longer (Ive heard 60s, but never tested it)

If you want to here the full explanation see this comment on the connections https://www.php.net/manual/en/pdo.connections.php#114822

To force the close the connection you have to do something like

$this->connection = new PDO();
$this->connection->query('KILL CONNECTION_ID()');
$this->connection = null;

Answer 4

I created a derived class to have a more self-documented instruction instead of $conn=null;.

class CMyPDO extends PDO {
    public function __construct($dsn, $username = null, $password = null, array $options = null) {
        parent::__construct($dsn, $username, $password, $options);
    }

    static function getNewConnection() {
        $conn=null;
        try {
            $conn = new CMyPDO("mysql:host=$host;dbname=$dbname",$user,$pass);
        }
        catch (PDOException $exc) {
            echo $exc->getMessage();
        }
        return $conn;
    }

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

So I can call my code between:

$conn=CMyPDO::getNewConnection();
// my code
CMyPDO::closeConnection($conn);

Answer 5

You can also close it by destroying the PDO object with:

unset($this -> connection);

Answer 6

<?php if(!class_exists('PDO2')) {
    class PDO2 {
        private static $_instance;
        public static function getInstance() {
            if (!isset(self::$_instance)) {
                try {
                    self::$_instance = new PDO(
                        'mysql:host=***;dbname=***',
                        '***',
                        '***',
                        array(
                            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_general_ci",
                            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION
                        )
                    );
                } catch (PDOException $e) {
                    throw new PDOException($e->getMessage(), (int) $e->getCode());
                }
            }
            return self::$_instance;
        }
        public static function closeInstance() {
            return self::$_instance = null;
        }
    }
}
$req = PDO2::getInstance()->prepare('SELECT * FROM table');
$req->execute();
$count = $req->rowCount();
$results = $req->fetchAll(PDO::FETCH_ASSOC);
$req->closeCursor();
// Do other requests maybe
// And close connection
PDO2::closeInstance();
// print output

Full example, with custom class PDO2.