PHP PDO close connection set to null but return statement

Question

I have created a class which connects to the DB. All other classes can then use the connect function within this class to open a connection to the DB. At the end of the function, I return the results. So how do I close the connection after I have returned the results?

<?php

class DbhPdo {
  private $servername;
  private $username;
  private $pwd;
  private $dbname;

  protected function connect() {
    $this->servername = "localhost";
    $this->username = "someUser";
    $this->pwd = "somePswd";
    $this->dbname = "someDB";
    $this->charset = "utf8mb4";

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

If I set the following after the return, then it is never called to close the connection:

$pdo = null;

Or does PDO close this connection automatically because it is done executing commands after - return $pdo?

Or do I have to close the connection in the class that extended the connection?

The following is the class that extends the aforementioned class, but I am returning the results as well in this class, so I can not set stmt to null here either:

<?php

require_once('dbh.pdo.inc.php');

class LoginPdo extends DbhPdo {
  public $name;
  public $pass1;
  public $hashed;
  public $salted;


  protected function getSomething($name) {
    try{
      $stmt = $this->connect()->query("select something from table where name ='" . $name . "'");
      return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }catch (PDOException $e){
      echo 'Message: ' . $e->getMessage();
    }
  }
}

Which leaves the original class that started this:

  try{
    $this->result = $this->getSomething($this->name);
    echo json_encode($this->result);
    $this->result = null;
  }catch (PDOException $e){
    echo 'Message: ' . $e->getMessage();
  }

Is setting the $this->result = null going to close the connection? If it does, then I do not understand. Can someone explain it to me? Or do I understand it correctly when setting $pdo = null would be where I need to close the connection?

If so, then how can I set it to null after the return $pdo is executed?

Thanks in advance

Update: Just in case someone else wants to be able to verify if a connection was closed, these were the steps that I took to confirm @Don'tPanic comments to tail the general log files:

mysql -u root -p
show variables like '%log%';
set global general_log=ON;

tail -f /usr/local/mysql/data/<your log file's name>.log

Which then showed the connection, as I used PostMan to post the following query were opened and closed instantly:

190130 11:00:17  2581 Query show variables like '%log%'
190130 11:02:14  2582 Connect   root@localhost on <table name>
    2582 Query  select * from something where name ='whatever'
    2582 Quit

This was accomplished by following @Don'tPanic answer to add a new function in the DbhPdo class:

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

Then I added $this->pdo = null; after echo json_encode($this->result);

Thank you for all the comments

Answer 1

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.

Answer 2

Your instance of the PDO API is returned from $this->connect(). This is the only instance you use. If you wanted to save the state to later use or close it you could use:

$stmt = ($con =& $this->connect())->query(...);
$con  = null; # Or use the instance else where
return $stmt->fetchAll(PDO::FETCH_ASSOC);

If the PDOPrepareStatement uses the PDO instance by reference, you can use clone to copy the instance rather than write to it.

$stmt = ($con =& clone $this->connect())->query(...);

Your $stmt variable is a PDOPrepareStatement since PDO::query does not return the PDO instance so by doing so, you're already setting it to null.

Point to note: You should think about designing a singleton approach if you want to build models. You can look at the example I made for Laravel

trait Singleton
{
    private static $instance;

    public static function getInstance()
    {
        return self::$instance ?? (self::$instance = new self());
    }

    //
}

Then you can create your Super Model:

class Model
{
    use Singleton;

    protected $pdo;

    public function __construct()
    {
        // instance your PDO
    }

    public function select()
    {
        // super method all models can use
    }
}

Then just create your models!

class UserModel extends Model
{
    public function find($id)
    {
        return $this->select(...)->fetch();
    }
}

You can use it like so:

UserModel::getInstance()
    ->find(1);