PDO closing connection

133
votes

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?

5
phppdo
the reason i am asking is i'm not sure if i was closing the connection properly. but no not really just intrigued – Liam Sorsby
The database connection is automatically closed when your PHP script stops execution. – Martin Bean
If you're done using it then why not go ahead and terminate it, especially if there is some time consuming code once you've finished interacting with the database. Though, I don't really see the issue with waiting for the script to finish either though (other than reducing connections to the DB server.) – Kieran
github.com/php/php-src/blob/master/ext/pdo/pdo_dbh.c Find out for yourself how it works :P – Flosculus
Not all php scripts are short lived. There are php daemons out there. I think this is a great thing to clarify personally. – datUser

5 Answers

152
votes

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.

50
votes
$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.
14
votes

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;
8
votes

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);
0
votes
<?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.