How to use persistent PDO connection?

Question

I have the following code and updated this webpage in Firefox 5 times, then MySQL showed me 5 connections. According to the PDO Guide,

Persistent connections do not close at the end of the script, but are cached and reused when another script requests a connection using the same permissions. A persistent cache connection avoids the overhead of establishing a new connection every time a script needs to talk to the database, which leads to the application.

I used the same credentials, but the number of MYSQL connections continues to grow. Even trying to close the connection with $db = null could not close the connections. What happened to my code?

 <?php try { $dbh = new PDO('mysql:host=127.0.0.1;dbname=lingtong', 'root', 'xxxxxx', array(PDO::ATTR_PERSISTENT => true)); foreach ($dbh->query('SELECT * from agent') as $row) print_r($row); $dbh = null; } catch (PDOException $e) { print "Error! : " . $e->getMessage() . "<br/>"; die(); } 

Answer 1

From what I know from a persistent connection, you probably don't need this:

• You are on a local host, so the connection is very fast, and you will not be able to save a lot of time caching your connection.
• because of the main Apache principal, you have many threads that respond to the client’s request, and because of this multi-threaded connection, the connection remains constant in one thread, not all of them, so you will see that your connection number in mysql, until it reaches ThreadLimit apache
• there is a risk that a persistent connection will cause a problem for your application as dbLock or tableLock

Now you can do a bit more research on persistent relationships if you still think you really need it.

Answer 2

This question is very old, but everything will be fine if I contributed. I think you need to implement a singleton class to handle database connections. I will write a sample class below.

 <?php class DB{ //set the connection property to private to prevent direct access private static $conn; //now since we dont want to reinstiate the class anytime we need it, lets also set the constructor to private private function __construct(){} //now lets create our method for connecting to the database public static function connect(){ //now lets check if a connection exists already in our $conn property, then we should return it instead of recreating a new connection if(!empty(self::$conn)){ return self::$conn; }//end if //upon reaching here means the $conn property is empty so lets create a new connection try { $dbh = new PDO('mysql:host=127.0.0.1;dbname=lingtong', 'root', 'xxxxxx', array(PDO::ATTR_PERSISTENT => true)); //lets now assign the database connection to our $conn property self::$conn = $dbh; //return the connection return $dbh; } catch (PDOException $e) { print "Error! : " . $e->getMessage() . "<br/>"; die(); } }//end method }//end class ?> 

Our singleton class can only make one connection and reuse it, see how we can use our class

 <?php $dbh = DB::connect(); foreach ($dbh->query('SELECT * from agent') as $row){ print_r($row); } ?> 

Answer 3

It seems you need to close the cursor and release (set to null) the last pdo subroutine, as well as close the connection.

In addition, what is important to understand about persistent connections is that they persist, but you cannot guarantee that you cannot:

• Give your last connection handler in the following script executions
• You cannot reuse the previous connection if it is still busy. Busy may mean in the script, time, etc ... The installation of connections can go on and on ... See Fully Understanding PDO ATTR_PERSISTENT