在DB类的多个实例之间共享1个mysql连接

Question

I'm a little bit stuck with the following problem: I would like all my classes to just use 1 single mysqli connection. I don't particulary like dependency injection (it seems unelegant to me to pass the connection around via the constructor) so I implemented a singleton. This works well except for the following problem:

class Admin {
  private $DB;

  public function __construct() {
    $this->DB = new DB::get_instance();
  }

  public function get_all_users() {
    $this->DB->query('SELECT `email` FROM `users`');
    while ($row = $this->DB->result->fetch_row()) { $users[] = new User($row[0]); }
    return $users;
  }
}

class User {
  private $DB;

  public function __construct($email = FALSE){
    $this->DB = new DB;
    if ($email) {
      $this->load($email);
    }
  }

  public function load($email){
    $this->DB->query('SELECT * FROM `users` WHERE email = "'.$this->email.'";'); // Problem!
    // etc.
}

This doesn't work as expected (=Returns just one user instead of all of them) as the query() in User overwrites the "mysqli_result var" from Admin with the new query (which obviously makes sense as there is only one instance of DB since it's a singleton). So due to this nesting of queries a singleton won't work.

What I would like to do now is to store the connection in a separate singleton class and create new DB classes for querying etc. on the go (which would use the connection from the singleton class). Basically something like this:

class DB extendes Connection { .... } // Called as $DB = new DB in other classes
class Connection extends mysqli { .... } // This is a singleton

But I just can't figure it out. If I call parent::__construct() from DB it will create a new Connection instance which obviously isn't what I'm looking for. Cloning Connection obviously won't work either. How can I tell DB to use the mysqli link from Connection without actually creating a new Connection object?

I hope I layed out my problem more or less clearly :) As mentioned above I'm somewhat stuck and I didn't find any helpfull hints so far

Answer 1

<?php 
//Create a registry class to hold objects that traverse all the classes
Class Registry {
    private $vars = array();
    public function __set($index, $value){$this->vars[$index] = $value;}
    public function __get($index){return $this->vars[$index];}
}

//Your singleton db class
class db{
    private static $instance = NULL;
    private function __construct() {
    }
    public static function getInstance($DBHOST,$DBDB,$DBUSER,$DBPASS) {
        if (!self::$instance){
            self::$instance = new PDO("mysql:host=".$DBHOST.";dbname=".$DBDB, $DBUSER, $DBPASS);
            self::$instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }
        return self::$instance;
    }
    private function __clone(){}
}


//assign the database object to the registry, within your initialization
$registry = new registry;
$registry->db = db::getInstance(DBHOST,DBDB,DBUSER,DBPASS);
$registry->router = new router($registry);
$registry->users = new users($registry);


//Then add to your class constructors to have that connection available
protected $registry;
function __construct($registry){
    $this->registry=$registry;
}

//.....
$result = $this->registry->db->query('SELECT ...')->fetchAll(PDO::FETCH_ASSOC);
foreach($result as $key=>$val){
    $this->registry->$val['setting']=$val['value'];
}

?>