63

When I open a MySQL connection in PHP with just PHP's built-in MySQL functions, I do the following:

$link = mysql_connect($servername, $username, $password);
mysql_select_db($dbname);
//queries etcetera
mysql_close($link);

When I open a connection with PDO, it looks like this:

$link = new PDO("mysql:dbname=$dbname;host=$servername",$username,$password);
//prepare statements, perform queries

Do I have to explicitly close the connection like I do with mysql_connect() and mysql_close()? If not, how does PHP know when I'm done with my connection?

TIA.

UKB
  • 1,162
  • 1
  • 11
  • 15
benjy
  • 4,664
  • 7
  • 38
  • 43

6 Answers6

79

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.

Annika Backstrom
  • 13,937
  • 6
  • 46
  • 52
DreadPirateShawn
  • 8,164
  • 4
  • 49
  • 71
  • 1
    I wonder if PDO dtor explicitly closes the connection or does it leave it to PHP just like what happens when `mysql_close()` is not used at the end of the script (or in the `__destruct()` of your db class)? From the manual it sounds like when PDO object is destructed, the connection gets closed too. Important because of this: http://stackoverflow.com/a/2960656/372654. Seems so from the PDO source code but... – Halil Özgür Dec 17 '11 at 11:14
  • 5
    WOuldn't one `unset()` the PDO object instead of setting it to NULL? I really want some clarification on this! – Theodore R. Smith May 07 '12 at 14:46
  • 3
    It's worth noting that the official PHP manual (per the link above) advises setting to null, and doesn't mention "unset()". Are you having trouble using the official approach, or are you asking academically? If the former, please elaborate with details. If the latter, you may want to reach out to the PHP devs, they'd be best suited to address alternatives to their officially-documented solution. (For instance, even if unset() works in one version, it may not work in another, if the devs aren't explicitly ensuring that approach.) – DreadPirateShawn May 08 '12 at 03:25
  • 5
    IMHO it doesn't matter if you unset() or set to NULL. AFAIK, in either way the PDO::__destruct method gets called in the same way as if you let the variable last 'til the end of the script and the connection is closed in that method. – S22h May 09 '12 at 11:44
  • 1
    How about scope? If a connection object is instantiated and isn't returned at the end of a function/method, will the connection die? – Madara's Ghost Jan 01 '13 at 18:39
  • 1
    @MadaraUchiha : see JDelage answer: "You can also limit your connections to within local functions. That way the connection is closed as soon as the function is completed." :) – Erenor Paz Mar 11 '13 at 03:34
13

PDO does not offer such a function on its own. Connections via PDO are indirectly managed via the PDO objects refcount in PHP.

But sometimes you want to close the connection anyway, regardless of the refcount. Either because you can not control it, need it for testing purposes or similar.

You can close the Mysql connection with PDO by running a SQL query. Every user that is able to connect to the Mysql server is able to KILL at least its own thread:

/*
 * Close Mysql Connection (PDO)
 */

$pdo_mysql_close = function (PDO $connection) {

    $query = 'SHOW PROCESSLIST -- ' . uniqid('pdo_mysql_close ', 1);
    $list  = $connection->query($query)->fetchAll(PDO::FETCH_ASSOC);
    foreach ($list as $thread) {
        if ($thread['Info'] === $query) {
            return $connection->query('KILL ' . $thread['Id']);
        }
    }
    return false;
};

$pdo_mysql_close($conn);

Related Mysql Documentation:

Related Stackoverflow Questions:

Community
  • 1
  • 1
hakre
  • 193,403
  • 52
  • 435
  • 836
  • `$connection->query('KILL CONNECTION_ID();');` Tested against mysql 5.7 on debian from local mysql command line client and from workbench on a windows workstation. According to http://dev.mysql.com/doc/refman/5.5/en/show-processlist.html the SHOW PROCESSLIST column `Id` is the "...connection identifier. This is the same type of value displayed in the ID column of the INFORMATION_SCHEMA PROCESSLIST table and returned by the `CONNECTION_ID()` function." (note: i am commenting on an answer that is almost 6 years old) – Jonathan Oct 22 '18 at 10:00
11

When the PHP script finishes executing, all connections are closed. Also you don't have to explicitly close your connection with mysql_close().

aksu
  • 5,221
  • 5
  • 24
  • 39
Vexatus
  • 880
  • 1
  • 7
  • 11
5

You can also limit your connections to within local functions. That way the connection is closed as soon as the function is completed.

JDelage
  • 13,036
  • 23
  • 78
  • 112
1

Well seeing as the $link for the PDO is assigned an object, PHP would set that as null as soon as the script runs so that it's no longer an object. Therefore you could just do:

$link = new PDO("mysql:dbname=$dbname;host=$servername",$username,$password);

//prepare statements, perform queries

$link = null;
David Cain
  • 16,484
  • 14
  • 65
  • 75
Rob Holland
  • 11
  • 1
0

http://uk3.php.net/pdo

From what i gather i could not see anyway to close it in the php manual, and examples of scripts i quickly looked at never closed the connection in anyway from what i could see.

pjau
  • 915
  • 1
  • 8
  • 14