Mehmet Daşdemir Mehmet Daşdemir - 7 months ago 27
PHP Question

pdo too many connection, suggestion

this is my php code. problem is db connection and db close. i have too many connection error. i used $db = null; but i have again error. what can i do?
thank you...

db.php

public function getConnection(){

$this->conn = null;

try{
$this->conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name .";charset=utf8", $this->username, $this->password);
}

catch(PDOException $exception){
echo "Connection error: " . $exception->getMessage();
}

return $this->conn;
}
}


$database = new Config();
$db = $database->getConnection();


data.inc.php

include 'db.php';

private $table_name = "contents";

public function __construct($db){
$this->conn = $db;
}

function dbclose() {

$this->conn = null;

return true;
}

function readContents($page, $from_record_num, $records_per_page,$category){


$query = "SELECT
*
FROM
" . $this->table_name . " where cat_id = ?
ORDER BY
date desc
LIMIT
{$from_record_num}, {$records_per_page}";

$stmt = $this->conn->prepare( $query );
$stmt->bindParam(1, $cat_id);
$stmt->execute();

return $stmt;
}


index.php

$stmt1 = $product->readContents(1, 0, 20, 1); //page,0,20 list contents,category 1
$stmt2 = $product->readContents(1, 0, 20, 5); //page,0,20 list contents,category 5
$stmt3 = $product->readContents(1, 0, 20, 13); //page,0,20 list contents,category 13

while ($row1 = $stmt1->fetch(PDO::FETCH_ASSOC)){ //20 contents in category 1 }
while ($row2 = $stmt2->fetch(PDO::FETCH_ASSOC)){ //20 contents in category 5 }
while ($row3 = $stmt3->fetch(PDO::FETCH_ASSOC)){ //20 contents in category 13 }

$product->dbclose();


i used $db null; but, i have again too many connection error. What can i do ?

Answer

Probably you should modify your Config class to use a singleton pattern so that it only creates one instance of a db connection:

class Config {
    static $conn = false;

    public function getConnection(){

      if(static::$conn === false) {
        // no db connection established: create one
        try{
            static::$conn = new PDO("mysql:host=" . $this->host . ";dbname=" . $this->db_name .";charset=utf8", $this->username, $this->password); 
        }

        catch(PDOException $exception){
            echo "Connection error: " . $exception->getMessage();
        }
      }

      return static::$conn;
    }
}

This enforces the use of a single db connection no matter how often getConnection() gets called.

Comments