PHP: using a database class

Question

I have a Database class and a User class. My problem is that I want to instantiate the database and use it in all classes.

The User class is just one of them. I want to get a user record from a table using a database object. I do not want to create a database variable inside each class, because I will use sessions. Is there a solution?

 class Database { /* * Edit the following variables */ private $db_host = 'localhost'; // Database Host private $db_user = 'root'; // Username private $db_pass = ''; // Password private $db_name = 'researchportal'; // Database /* * End edit */ private $con = false; // Checks to see if the connection is active private $result = array(); // Results that are returned from the query /* * Connects to the database, only one connection * allowed */ public function connect() { if(!$this->con) { $myconn = @mysql_connect($this->db_host,$this->db_user,$this->db_pass); if($myconn) { $seldb = @mysql_select_db($this->db_name,$myconn); if($seldb) { $this->con = true; return true; } else { return false; } } else { return false; } } else { return true; } } /* * Changes the new database, sets all current results * to null */ public function setDatabase($name) { if($this->con) { if(@mysql_close()) { $this->con = false; $this->results = null; $this->db_name = $name; $this->connect(); } } } /* * Checks to see if the table exists when performing * queries */ private function tableExists($table) { $tablesInDb = @mysql_query('SHOW TABLES FROM '.$this->db_name.' LIKE "'.$table.'"'); if($tablesInDb) { if(mysql_num_rows($tablesInDb)==1) { return true; } else { return false; } } } /* * Selects information from the database. * Required: table (the name of the table) * Optional: rows (the columns requested, separated by commas) * where (column = value as a string) * order (column DIRECTION as a string) */ public function select($table, $rows = '*', $where = null, $order = null) { $q = 'SELECT '.$rows.' FROM '.$table; if($where != null) $q .= ' WHERE '.$where; if($order != null) $q .= ' ORDER BY '.$order; $query = @mysql_query($q); if($query) { $this->numResults = mysql_num_rows($query); for($i = 0; $i < $this->numResults; $i++) { $r = mysql_fetch_array($query); $key = array_keys($r); for($x = 0; $x < count($key); $x++) { // Sanitizes keys so only alphavalues are allowed if(!is_int($key[$x])) { if(mysql_num_rows($query) > 1) $this->result[$i][$key[$x]] = $r[$key[$x]]; else if(mysql_num_rows($query) < 1) $this->result = null; else $this->result[$key[$x]] = $r[$key[$x]]; } } } return true; } else { return false; } } /* * Insert values into the table * Required: table (the name of the table) * values (the values to be inserted) * Optional: rows (if values don't match the number of rows) */ public function insert($table,$values,$rows = null) { if($this->tableExists($table)) { $insert = 'INSERT INTO '.$table; if($rows != null) { $insert .= ' ('.$rows.')'; } for($i = 0; $i < count($values); $i++) { if(is_string($values[$i])) $values[$i] = '"'.$values[$i].'"'; } $values = implode(',',$values); $insert .= ' VALUES ('.$values.')'; $ins = @mysql_query($insert); if($ins) { return true; } else { return false; } } } /* * Deletes table or records where condition is true * Required: table (the name of the table) * Optional: where (condition [column = value]) */ public function delete($table,$where = null) { if($this->tableExists($table)) { if($where == null) { $delete = 'DELETE '.$table; } else { $delete = 'DELETE FROM '.$table.' WHERE '.$where; } $del = @mysql_query($delete); if($del) { return true; } else { return false; } } else { return false; } } /* * Updates the database with the values sent * Required: table (the name of the table to be updated * rows (the rows/values in a key/value array * where (the row/condition in an array (row,condition) ) */ public function update($table,$rows,$where) { if($this->tableExists($table)) { // Parse the where values // even values (including 0) contain the where rows // odd values contain the clauses for the row for($i = 0; $i < count($where); $i++) { if($i%2 != 0) { if(is_string($where[$i])) { if(($i+1) != null) $where[$i] = '"'.$where[$i].'" AND '; else $where[$i] = '"'.$where[$i].'"'; } } } $where = implode('',$where); $update = 'UPDATE '.$table.' SET '; $keys = array_keys($rows); for($i = 0; $i < count($rows); $i++) { if(is_string($rows[$keys[$i]])) { $update .= $keys[$i].'="'.$rows[$keys[$i]].'"'; } else { $update .= $keys[$i].'='.$rows[$keys[$i]]; } // Parse to add commas if($i != count($rows)-1) { $update .= ','; } } $update .= ' WHERE '.$where; $query = @mysql_query($update); if($query) { return true; } else { return false; } } else { return false; } } /* * Returns the result set */ public function getResult() { return $this->result; } } 

Class User:

 <?php require_once 'class.database.php'; class User{ public $usr_id; public $usr_name; public $usr_level; public $last_access_login; public function __construct($id) { $this->usr_id = $id; //$this->usr_name = $usr_name; //$this->usr_level = $usr_level; $this->last_access_login = date("F d YH:i:s.",time()); } public function getUser() { // $db->select('login_users','*','user_id='.$this->usr_id.''); // $res = $db->getResult(); // print_r($res); $this->usr_name = $res['username']; $this->usr_level = $res['user_level']; $this->last_access_login = date("F d YH:i:s.",time()); } } $a = new User(3); $a->getUser(); ?> 

Answer 1

You need to either pass the $ database to your custom constructor, or use one singleton, for example MrJ.

Passing a database object as a parameter is quite simple, as you already know, but you make it clear to others:

 private $myDatabase = NULL; public function __construct(Database $myDatabase){ $this->myDatabase = $myDatabase; } 

And then in the class anywhere you can use your database using

 $this->myDatabase->select(...); 

Creating an object now becomes as simple as:

 $myUser = new User($database); 

Obviously adjust it as needed :)

---

Singlets are also easy to use; create a static method inside your database class:

 private static $staticInstance = NULL; public static function getInstance(){ if(Database::$staticInstance == NULL){ Database::$staticInstance = new Database(); Database::$staticInstance->connect(); } return Database::$staticInstance; } 

This creates a function that will automatically create your instance of your unique database class as soon as you need it, and return that single instance when you call getInstance again.

To use this inside your other classes, simply call:

 $mydb = Database::getInstance(); 

And then use your database object ...

Singletones are really useful in many such situations.

Answer 2

You probably want to use a singleton class, for example http://www.ricocheting.com/code/php/mysql-database-class-wrapper-v3 - this is the one that I personally use

Update

I'm not quite sure, but you can include what you did:

 $database = new Database(); $client = new User($id, $database); 

Then in class User and __construct()

 public function __construct($id, $database){ $this->db = $database; ... } 

Then you should be able to call it as $this->db->... inside the User class (although I'm not too sure how to explain better, but this is an alternative that may / should work)

Answer 3

You should take a look at the implementation of a singleton pattern . This way you can use your class / database connection anywhere.

Answer 4

Just pass the object to the User class as an argument to the constructor.

 new User(id, $db); 

Answer 5

Why not just extend the database class?

class.database.php

 class Database extends ReflectionClass { protected $db; protected $_dbhost = DB_HOST; protected $_dbname = DB_NAME; protected $_dbpass = DB_PASS; protected $_dbuser = DB_USER; protected $_dboptions = array(PDO::ATTR_PERSISTENT => false); public function __construct() { parent::__construct($this); $dsn = "mysql:host=$this->_dbhost;dbname=$this->_dbname"; try { $this->db = new PDO($dsn, $this->_dbuser, $this->_dbpass, $this->_dboptions); $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch(Exception $e) { error_log(print_r($e->getMessage(),1).' '.__FILE__.' '.__LINE__,0); exit('Database is currently unavailable.'); } } public function __get($name) { if ($this->hasProperty($name)) return $this->$name; return null; } public function __set($name, $value) { if ($this->hasProperty($name)) $this->$name = $value; return null; } public function __destruct(){ $this->db = NULL; } public function select($tbl,$rows='*',$where=null,$order=null) { $q = 'SELECT '.$rows.' FROM '.$tbl; if(isset($where)) { $q .= ' WHERE '.$where; } if(isset($order)) { $q .= ' ORDER BY '.$order; } $q .= ' LIMIT 1'; $r = $this->db->prepare($q); foreach($this->getProperties() as $i=>$p){ if ($p->class===$this->name) { $prop = $p->name; $r->bindColumn($i+1,$this->$prop); } } $r->execute(); $n = $r->rowCount(); if($n) { $r->fetch(); } return $n; } } // end 

class.users.php

 class Users extends Database { protected $_id; protected $_name; protected $_level; public function __construct() { parent::__construct(); } public function selectUser($id) { $tbl = 'Users'; $rows = 'user_id,user_name,user_level'; $where = "user_id=$id"; $this->select($tbl,$rows,$where); } } // end 

index.php

 // config $C['DB_HOST'] = 'localhost'; $C['DB_NAME'] = 'db'; $C['DB_PASS'] = 'root'; $C['DB_USER'] = 'root'; foreach($C as $k=>$v) { define($k,$v); } // include require_once 'class.database.php'; require_once 'class.users.php'; // select $u = new Users(); $u->selectUser(1); echo $u->_id.'<br>'; echo $u->_name.'<br>'; echo $u->_level.'<br>';