1

I'm creating a web app with various classes for things like the user, Smarty template control, etc.

I already have a database class which is all well and good, but I'm concerned about the performance of it.

Currently, in another class, I'm doing $this->db = new DB() to create a local database instance, however the database class's __construct() function creates a new connection to the MySQL server every time I make a new DB() instance, which is obviously less than sensible. This means that each instance of all my different classes that uses the database class makes a connection to the server. I don't have a vast amount of classes, but I only want one per page load.

This is a stripped down sample of what I have at the moment:

// Database class used by multiple other classes class DB { private $dbh; function __construct() { $this->dbh = // PDO connection here } public function query($str) { // Do a query } } // Example class User class User { private $db; // Stores local instance of DB class. function __construct() { $this->db = new DB(); // Makes a new connection in DB::__construct() } public function login() { $this->db->query('SELECT * FROM users'); } }

I'm looking for the "best" or most common practice of doing this. I don't want to make 10-ish separate connections for each page load.

I want to know what the best way of using and managing a DB class in my application. My four thoughts are these:

  1. Would using a persistent connection to the MySQL server solve this multiple connection issue for me?
  2. Should I use a static factory class and return a DB instance instead of using new DB()?
  3. Is the proper solution to use an entirely static class and just do DB::query() (for example) every time I reference it?
  4. I often use multiple classes in another (so we might have class Folders which requires classes User, DB and Smarty). Is it general practice to extend each class somehow?
Improve this question
6
  • 2
    You're on the right track to keep the DB as a class property... This is often done either by creating a singleton for the database whose instance is retrieved in each class needing it, or (perhaps better) by dependency injection, whereby you instantiate the DB and then pass it into the constructor as a parameter. Commented Mar 4, 2012 at 17:02
  • There are a couple of good illustrations around here somewhere. If I find one I'll link it. Commented Mar 4, 2012 at 17:04
  • Here's one with a good accepted answer. There are others with multiple good answers.. Commented Mar 4, 2012 at 17:06
  • @Michael Thank you for your input. I'd completely forgotten about singletons. I've always thought having $this->db for each class is a little silly, but with singletons it makes sense; there are no scope issues and only once instance is ever created. Commented Mar 4, 2012 at 17:06
  • 1
    Please consider extending PDO instead of wrapping it. Commented Mar 4, 2012 at 17:14

4 Answers 4

Reset to default
2

If you make the variable holding the connection static, then you can check if you already established a connection. Static variables are the same across all instances of the class, so you can create 100 instances that all use the same connection. You just need to reference it statically: self::$dbh instead of $this->dbh.

class DB { private static $dbh = null; function __construct() { if ( is_null(self::$dbh) ) { self::$dbh = // PDO connection here } } }
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you. I've made my $dbh static and now my MySQL log only lists one open connection, then the page's queries. It's nice to be able to use such a quick and easy solution.
1

I would suggest you to check the $this -> db at first and then only create it.

function __construct() { if(!isset($this -> db) || !is_a("DB", $this -> db)) { $this->db = new DB(); // Makes a new connection in DB::__construct() } }
Improve this answer

Comments

0

You need to inject db connection to your class instead of creating a new connection.

// In a bootstrap file $db = new DB(); // User.php class User { private $db; function __construct($db=null) { if (!is_null($db)) { $this->setConnection($db); } } function setConnection($db) { $this->db = $db; } public function login() { $this->db->query('SELECT * FROM users'); } }

BTW, Zend_Registry is a good solution if you prefer it http://framework.zend.com/manual/en/zend.registry.using.html

Improve this answer

4 Comments

I should have mentioned this in my question; I don't want to have to pass anything into the constructor function. I'd need to have some classes where I pass in four or five different classes (as instances), which is ungainly. Frameworks are handy, but I don't want to use one in this case.
@JamWaffles What about passing in an array of objects (which happens by reference) to the constructors? Then unpack which objects are needed inside the class from the array.
Edited. You can use setConnection function. The last solution IK think is to use global $db; to each function
BTW you can use Zend framework as a library, only the components you need. I use it in custom projects
-1 
<?php class DBLayer { public $prefix; public $link_id; public $query_result; public $saved_queries = array(); public $num_queries = 0; public function DBLayer() { $db_prefix = ''; $this->prefix = $db_prefix; if (isset($this->link_id)) { return $this->link_id; } $this->link_id = @mysql_connect(DATABASE_HOST, DATABASE_USER, DATABASE_PASSWORD, true); if ($this->link_id) { if (@mysql_select_db(DATABASE_NAME, $this->link_id)) { return $this->link_id; } else { $this->wplog("Unable to select database. Host:". DATABASE_HOST. "Database:" . DATABASE_NAME . " Error: " . mysql_error(), 'ERROR', __FILE__, __LINE__); } } else { $this->wplog("Unable to connect to MySQL server. Host: " . DATABASE_HOST . " Error: " . mysql_error(), 'ERROR', __FILE__, __LINE__); } } public function query($sql, $unbuffered = false) { if(LOG){echo "<hr>$sql";} $this->query_result = @mysql_query($sql, $this->link_id); if ($this->query_result) { return $this->query_result; } else { $msg= $sql . "<br /> Error: (" . mysql_errno() . ") " . mysql_error(); $this->wplog($msg); } } public function result($query_id = 0, $row = 0) { return ($query_id) ? @mysql_result($query_id, $row) : false; } public function fetch_assoc($query_id = 0) { return ($query_id) ? @mysql_fetch_assoc($query_id) : false; } public function fetch_row($query_id = 0) { return ($query_id) ? @mysql_fetch_row($query_id) : false; } public function num_rows($query_id = 0) { return ($query_id) ? @mysql_num_rows($query_id) : false; } public function affected_rows() { return ($this->link_id) ? @mysql_affected_rows($this->link_id) : false; } public function insert_id() { return ($this->link_id) ? @mysql_insert_id($this->link_id) : false; } public function get_num_queries() { return $this->num_queries; } public function get_saved_queries() { return $this->saved_queries; } public function free_result($query_id = false) { return ($query_id) ? @mysql_free_result($query_id) : false; } public function escape($str) { if (function_exists('mysql_real_escape_string')) return mysql_real_escape_string($str, $this->link_id); else return mysql_escape_string($str); } public function get_select($q, $onlyone=false) { $results = array(); $r = $this->query($q); if ($onlyone) { return $this->fetch_assoc($r); } while ($l = $this->fetch_assoc($r)) { $results[] = $l; } return $results; } public function get_error() { return mysql_error(); } public function close() { if ($this->link_id) { if ($this->query_result) @mysql_free_result($this->query_result); return @mysql_close($this->link_id); } else return false; } public function auto_execute($table, $data, $type, $criteria='') { $result = $this->get_select("desc " . $table); if ($type == "INSERT") $start = "insert into " . $table . " set "; elseif ($type == "UPDATE") $start = "update " . $table . " set "; $sql = $start; foreach ($result as $rst) { foreach ($data as $key => $value) { if ($key == $rst['Field'] and $key !== 0) { if ((@ereg('date', $rst['Type'])) && $value == '') { $sql = $sql . "`".$key."`" . "=NULL, "; } elseif ((!@ereg('int', $rst['Type']))) { $sql = $sql . "`".$key."`" . "='" . $value . "', "; } else { if (trim($value) != "") { $sql = $sql . "`".$key."`" . "=" . $value . ", "; } } } } } if ($sql == $start) return 0; else { $sql = substr($sql, 0, strlen($sql) - 2); if ($type == "UPDATE" and !empty($criteria)) $sql = $sql . " where " . $criteria; } //echo $sql;exit; if ($this->query($sql)) { $return = $this->insert_id(); } else { $return = 0; } return $return; } private function wplog($message) { if(LOG==true){ $lineBreak = "\n"; // this function will NOT work on a windows server without further modification $contents = date('Y-m-d H:i:s') . ' ' . $message. $lineBreak; $myFile = SERVER_PATH.'/log.txt'; $fh = fopen($myFile, 'a') ; fwrite($fh, $contents); fclose($fh); //SetFileContents(SERVER_PATH.'/log.txt',$contents,'a'); } } }
Improve this answer

1 Comment

I hope you're not using this in production.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.