Blog

How to close the PDO connection from PHP

The connection to the database through PDO is usually my favorite, because I love being able to connect to different DBMS with the same methodology. But there is a little problem that is pending to be solved by the PHP developers: closing the connection to the database.

Content

  • Why is it complicated to close the PDO connection?
  • How to close PDO connection?
  • Function to close database connection
  • Extra: Create a method to close the encapsulated PDO connection
  • Related Posts:

Why is it complicated to close the PDO connection?

Whenever we make a connection to the database, we must close the open connection. This point is common to all connections in PHP (at least the non-persistent ones), and each of the ways to connect to the database in php usually has its own disconnect function. usually with a close() either disconnect().

But when you start using PDO and gets to work, the general documentation obviates the disconnect. So we live happily until one fine day our web application is saturated with unclosed connections. The solution? Difficult, or rather unorthodox, since there is no close() either disconnect() from PDO to close the connection.

How to close PDO connection?

To close the connection PDO what we will do is free up related system resources. As? Well, putting a null value (null) the variables involved in the query process: the object PDOStatement which treats and manipulates our queries and the object PDO.

So for a connection to the class PDO to a database on a local server, named Test and with username and password root like the following:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=Test, 'root', 'root');

And in which we perform a query preparing it with PDO and executing it with the execute() method of PDOStatement:

$stmt = $pdo->prepare('SELECT * FROM user');
$result = $stmt->execute();

We would close the connection with the following lines of code:

$stmt->closeCursor(); // opcional en MySQL, dependiendo del controlador de base de datos puede ser obligatorio
$stmt = null; // obligado para cerrar la conexión
$pdo = null;
?>

The essential lines to close the PDO connection are:

  • the line that frees the PDOStatement object: $stmt = null
  • release the PDO connection object: $pdo = null

Function to close database connection

As we have seen, closing the connection, even if it is not through the orthodox way with its own method, is not too complex. But this task can become monotonous or lead to errors if in each of our scripts we have to resort to the previous lines of code.

Thus, the ideal would be to use our own function that closes the connection for PDO and that avoids possible errors. A useful version if we always name the connections and the results of prepare() the same would be:

<?php
function disconnect () {
   global $pdo, $stmt;
   $stmt->closeCursor();
   $stmt = null;
   $pdo = null;
}
?>

This function could be one of several that encapsulate the different uses of your connections PDO. In the event that it is a utility, I recommend (if you don’t already do so) that you include it as a static method in a utility class. read in code PDOUtils::disconnect() it’s much more visual and clean than a function call disconnect().

Another different option is to perform the function with input parameters:

<?php
function disconnect ( $pdo, $stmt ) {
   global $pdo, $stmt;
   $stmt->closeCursor();
   $stmt = null;
   $pdo = null;
}
?>

Extra: Create a method to close the encapsulated PDO connection

In a previous post I presented a class to encapsulate the operation of PDO. In this encapsulation class created by Mr source code you can see a method disconnect working perfectly.

  • Wrap PDO with your own class and disconnect() method.

Related Posts:

  • Connection to MySQL in 3 different ways.
  • Secure connection to MySQL, avoid SQL Inject.
  • Wrap the PDO class with a custom class.

Leave a Reply