Pdo Closing Connection

Is it necessary to close PDO connections

Upon successful connection to the database, an instance of the PDO class is returned to your script. 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.

http://php.net/manual/en/pdo.connections.php

So the answer is no, you don't need to do anything unless you need to explicitly close the connection during the script execution for whatever reason, in which case just set your PDO object to null.

PHP PDO close connection set to null but return statement

Setting the result to null does not set the connection to null.

It may not really be necessary to explicitly close the connection, but if you want to be able to do that, you need to have something you can set to null. You'll have to have some reference to the connection, and your current method does not store that reference anywhere. Defining the connection as a property of the class will take care of that for you.

I modified your connect function to show an example.

Basically instead of just returning a connection, you check for an existing connection and return that, or make a new connection if one has not been made yet, but the important part is that you set the property $this->pdo instead of using a $pdo variable that only exists in the connect function scope.

// ...
private $pdo;

protected function connect() {
    // I suggest setting these in the constructor rather than hard coding them here
    // $this->servername = "localhost";
    // $this->username = "someUser";
    // $this->pwd = "somePswd";
    // $this->dbname = "someDB";
    // $this->charset = "utf8mb4";

    if ($this->pdo) {
        return $this->pdo;
    else {
        try{
            $dsn = "mysql:host=" . $this->servername . ";dbname=" . $this->dbname . ";charset=" . $this->charset;
            $this->pdo = new PDO($dsn, $this->username, $this->pwd);
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            return $this->pdo;
        } catch(PDOException $e) {
            echo "Message: " . $e->getMessage();
        }
    }
}

Setting $this->pdo instead of just using a local variable in the connect function will give you something you can set to null to disconnect.

protected function disconnect() {
    $this->pdo = null;
}

You can call disconnect() after executing all the queries you need for the request. If you disconnect before that, you'll have to reconnect to run additional queries, which is unnecessary and will hurt the performance of your application.

PHP PDO connect and close connection in different functions

Yes, this is possible. I'd recommend using a constructor that initiates a connection to MySQL and a destructor that nullifies it. That way, you won't have to manually call the function every time you want to open and close the connection. The connection will open whenever a new instance of the object is called, and when there are no further references to the object, the connection will be nullified.

It might look something like this:

    private $l; //MySQL Connection

    //Called automatically upon initiation
    function __construct() {
        try {
            $this->l = new PDO("mysql:host=".MYSQL_HOST.";dbname=".MYSQL_DATABASE, MYSQL_USER, MYSQL_PASSWORD); //Initiates connection
            $this->l->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ); // Sets error mode
        } catch (PDOException $e) {
            file_put_contents("log/dberror.log", "Date: " . date('M j Y - G:i:s') . " ---- Error: " . $e->getMessage().PHP_EOL, FILE_APPEND);
            die($e->getMessage()); // Log and display error in the event that there is an issue connecting
        } 
    }

    //Called automatically when there are no further references to object
    function __destruct() {
        try {
            $this->l = null; //Closes connection
        } catch (PDOException $e) {
            file_put_contents("log/dberror.log", "Date: " . date('M j Y - G:i:s') . " ---- Error: " . $e->getMessage().PHP_EOL, FILE_APPEND);
            die($e->getMessage());
        }
    }

You might find this reference about constructors and destructors useful: http://php.net/manual/en/language.oop5.decon.php

Closing PDO connection inside function

Per the documentation:

you [close the connection] 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.

So the connection will not be closed at the end of the function since you did not assign null. Although it is inaccessible since its scope is local to the function, it technically won't be closed until the end of the script.

Note: In general, I don't recommend establishing and closing database connections within function calls.

PDO MySQL Connection close - unset vs null

They do the same thing. Unsetting the $pdo handle and setting it null both close the connection.

You can test this for yourself. Run the following script in one window, and in a second window open the MySQL client and run SHOW PROCESSLIST every couple of seconds to see when the connection disappears.

<?php

$pdo = new PDO(..);
sleep(10);
unset($pdo);
echo "pdo unset!\n";
sleep(10);

Then change unset($pdo) to $pdo=null; and run the test again.

<?php

$pdo = new PDO(..);
sleep(10);
$pdo = null;
echo "pdo set null!\n";
sleep(10);

The extra sleep() at the end is there to give you a moment to see that the connection has dropped, before the PHP script terminates (which would drop the connection anyway).

Destroy PDO connection?

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.

http://php.net/manual/en/pdo.connections.php

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

Refer the example below for using connection in classes

class Db
{
    # Class properties
    private $DBH; // Database Handle
    private $STH; // Statement Handle

    # Func: __construct()
    # Desc: Connects to DB
    public function __construct()
    {
        // Connection information
        $host   = 'localhost';
        $dbname = 'removed';
        $user   = 'removed';
        $pass   = 'removed';

        // Attempt DB connection
        try
        {
            $this->DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
            $this->DBH->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            echo 'Successfully connected to the database!';
        }
        catch(PDOException $e)
        {
            echo $e->getMessage();
        }
    }

    # Func: query(sql statement)
    # Desc: Sends a query to the DB
    public function query($sql_statement)
    {
        $sql = array(':color' => $sql_statement);
        $this->STH = $this->DBH->prepare("INSERT INTO color_table (color) value ( :color )");
        $this->STH->execute($sql);
    }

    # Func: __destruct()
    # Desc: Disconnects from the DB
    public function __destruct()
    {
        // Disconnect from DB
        $this->DBH = null;
        echo 'Successfully disconnected from the database!';
    }