Database class design

Question

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:

Would using a persistent connection to the MySQL server solve this multiple connection issue for me?
Should I use a static factory class and return a DB instance instead of using new DB()?
Is the proper solution to use an entirely static class and just do DB::query() (for example) every time I reference it?
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?

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. — Michael Berkowski
– Michael Berkowski, 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. — Michael Berkowski
– Michael Berkowski, Commented Mar 4, 2012 at 17:04
Here's one with a good accepted answer. There are others with multiple good answers.. — Michael Berkowski
– Michael Berkowski, 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. — Bojangles
– Bojangles, Commented Mar 4, 2012 at 17:06

Brent Baisley · Accepted Answer · 2012-03-04 17:10:59Z

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
        }
    }
 }

answered Mar 4, 2012 at 17:10

Brent Baisley

12.7k2 gold badges28 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bojangles Over a year ago

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.

Starx · Accepted Answer · 2012-03-04 17:07:18Z

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()
    }
}

answered Mar 4, 2012 at 17:07

Starx

79.3k50 gold badges187 silver badges266 bronze badges

Comments

dotoree · Accepted Answer · 2012-03-04 17:05:23Z

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

answered Mar 4, 2012 at 17:05

dotoree

2,9911 gold badge26 silver badges29 bronze badges

4 Comments

Bojangles Over a year ago

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.

Michael Berkowski Over a year ago

@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.

dotoree Over a year ago

Edited. You can use setConnection function. The last solution IK think is to use global $db; to each function

dotoree Over a year ago

BTW you can use Zend framework as a library, only the components you need. I use it in custom projects

Qousain Qureshi · Accepted Answer · 2015-04-18 05:39:02Z

<?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');
        }
    }

}

Collectives™ on Stack Overflow

Database class design

4 Answers 4

1 Comment

Comments

4 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

4 Comments

1 Comment

Linked

Related