Do SQL Connections Opened with Pdo in PHP Have to Be Closed

Do SQL connections opened with PDO in PHP have to be closed

Use $link = null to let PDO know it can close the connection.

PHP: PDO Connections & Connection Management

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.

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.

Does PHP close SQL connections opened with PDO at the end of the script if I don't explicitly close it?

According to the docs:

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.

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.

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

pdo connection wont close once statements have been executed

You can see a related note about this on the PHP docs here http://php.net/manual/en/pdo.connections.php#114822. In short, you need to set the statement and the connection to null.

$stmt = null;
$dbh = null; 

PDO let database stay open, or open and close when needed?

Usually, there is significant time spent/lost when connecting, and you want to do it only once. Do not go closing a connection you need later on, it will only slow things down. You may consider closing a connection sooner if you are reaching the maximum connections limit, but that's more a hint you should scale up then a permanent solution IMHO.

PDO Help - Closing the connection & Having a separate connection file

Your question about how to store the database name, username and password have nothing to do with the capabilities of PDO. This is an implementation choice. The way you use to work with procedural functions can also be applied to PDO, the difference is that with PDO you work with objects instead.

So for simplicity, store the PDO creation of an object, either in a function or class, in which you can create the PDO instance anytime, e.g.

function createPDO($cfg) {
  try {
    return new PDO("mysql:host=".$cfg['host'].",port:".($cfg['port']).";dbname=".($cfg['name']).";",$cfg['username'], $cfg['password']);
  } catch(PDOException $e) {
    // handle exceptions accordingly
  }
}

You can centralise these in whatever PHP file you like to include, just like you were used with the procedural functions.

You have two choices, either put all the relevant database information inside the createPDO, or use something like a config ($cfg) variable to store all this information.

$config = array();
$config['db'] = array(
  'host' => 'localhost',
  'name' => 'databse',
  'username' => 'userx',
  'password' => 'passy'
  /* .. etc */
)

Using the createPDO function would be as followed

$db_conn = createPDO($config['db']);

For connections closing, each connection made to the database automatically disconnects after PHP exits its execution. You can however, close the connection if you wish, by setting the variable of the PDO object you assigned it to, in this example (and in yours) $db_conn to null

$db_conn = null; // connection closed.

The PDO has a manual http://php.net/manual/en/book.pdo.php here, which is a good start getting to know PDO a bit better.

---

Related Topics