CODEWITHSUNDEEP
phpmysqlpdo
Hoda Kh
Hoda Kh

Reputation: 355

Why does a persistent PDO connection makes a new connection with each request?

This is my database class to connect MySQL via PDO persistent connection:

class database{
    private $_db;
    public function conn()
    {
        $user = 'xxx';
        $pass = 'xxxxxxx';
        try
        {
           $this->_db = new PDO('mysql:dbname=x;host=x.x.x.x',$user,$pass,array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',PDO::ATTR_PERSISTENT => true));
           $this->_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
           $this->_db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
           return $this->_db;
        }

        catch(PDOException $e)
        {
           return "-1";
        }
    }

}

I call this class in another function to query database. Each time I call the function a new connection is created in database. Why persistent pdo connection make a new connection per request?

Upvotes: 1

Views: 889

Answers (2)

WillardSolutions
WillardSolutions

Reputation: 2314

You need to check if the connection is already established before you connect again. This can be accomplished by adding a quick method to your class that you invoke inside the conn() method - 

class database{
    private $_db;

    private function isConnected()
    {
        return ($this->_db) ? TRUE : FALSE;
    } 

    public function conn()
    {

        if ($this->isConnected()) 
        {
            return $this->_db;
        } else {

            $user = 'xxx';
            $pass = 'xxxxxxx';
            try
            {
               $this->_db = new PDO('mysql:dbname=x;host=x.x.x.x',$user,$pass,array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',PDO::ATTR_PERSISTENT => true));
               $this->_db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
               $this->_db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
               return $this->_db;
            }

            catch(PDOException $e)
            {
               return "-1";
            }
        }
    }

}

This code will return the existing connection if it exists, or a new one if it does not.

Upvotes: 1

N. Hamelink
N. Hamelink

Reputation: 603

I am using a static class that I am defining at the start of my project, the only bad part on this is that you are not able to create multiple databases.

<?php

/**
 *
 ************************************************
 *                 PlainFramework               *
 ************************************************
 *
 *   Created by Niels Hamelink
 *   File: class.database.php
 *   Created at: 14-11-2016 02:08
 **/
class Database
{
    private static $_db;

    public static function connect($dsn, $username, $password, $debug)
    {
        try
        {
            $_db = new PDO($dsn, $username, $password);
        } catch(PDOException $e)
        {
            if($debug)
                Application::throw_error($e->getMessage());
        }
    }

    public static function close()
    {
        $_db = null;
    }

    public static function isConnected()
    {
        return isset($_db)
            && $_db != null;
    }

    public static function getPDO()
    {
        if(!isset($_db))
            $_db = null;

        return $_db;
    }

    public static function executeQuery(Query $query)
    {
        try
        {
            return Database::getPDO()->query($query->toString());
        } catch(PDOException $ex)
        {
            Application::throw_error($ex->getMessage());
        }
    }

}

Upvotes: 1

Related Questions