# PHP framework building: Database Control Classes

I am building a PHP framework and would like to get some feedback on a few different sections of the project so far. I consider myself still a neophyte in PHP so I would like to ask if I'm going about completing these different tasks in an efficient and or correct way.

This section is of the MySQL database interaction classes I have created for it. I thought is would be best to divide its processes into two different part. The Database class; which over sees the connection to the the database and the DBcontrol class; which over see the interaction with the database, the querying.

I am trying to create the classes so their methods and returned values and be easily worked on the on with PHP's MySQL API. My reason from creating these classes is to offer easy interaction with the database when working with the framework.

My question is if this is a good way to build this type of function/feature? I am open to any tip, tricks, suggestions and advice.

I have include the code for the Database and DBcontrol classes below.

Database class

class Database{

protected $connection = array(); protected$activeConnection = 0;
//Not in use
protected $lastactiveConnection; public function __construct(){ //Empty } /** * Change the select DB of the selected DB connection * @param Int$connection_id - Index of DB connection
* @param String $database - name of DB to select * @return Null */ public function ChangeSelectedDB($connection_id,$database){ if(array_key_exists($connection_id, $this->connection)){ if($this->connection[$connection_id]->select_db($database)){
}else{
trigger_error('Unable to change selected database', E_USER_ERROR);
}
}else{
trigger_error('Unable to find connection Index', E_USER_ERROR);
}
}
/**
* Create a new mysqli object using the parameters supplied
* @param String $host,$user, $pass,$batabase - Required DB connection information
* @return Index number of the current connection ID
*/
public function CreateConnection($host,$user,$pass,$batabase){
$this->connection[] = @new mysqli($host,$user,$pass,$batabase); //Connection index$currentconnect_id = count($this->connection)-1; if(mysqli_connect_errno()){ trigger_error('Class Error: Unable to connect to database'.$this->connection[$currentconnect_id]->error, E_USER_ERROR); } //Set activeConnection to most recently created connection //Using this method$this->activeConnection = $currentconnect_id; return$currentconnect_id;

}
/**
* Set DB connection currently being used
* @param Int $connection_id - Index number of DB connection to set to * @return Null */ public function SetActiveConnection($connection_id){
if(array_key_exists($connection_id,$this->connection) || is_integer($connection_id)){$this->activeConnection = $connection_id; }else{ trigger_error('Connection does not exists',E_USER_ERROR); } } /** * Get and return active db connection * @param Null * @return active connection */ public function GetActiveConnection(){ //Check using empty because$connection is set to 0
if(!empty($this->activeConnection)){ return$this->connection[$this->activeConnection]; }else{ trigger_error('A connection is not set',E_USER_ERROR); } } /** * Close selected DB connection * @param Null * @return Null */ public function CloseActiveConnection(){$this->connection[$this->activeConnection]->close(); } /** * Returns number of the current active * @param Null * @return Int index of the active DB connection */ public function ShowActiveConnection(){ echo "Current Connection index[".$this->activeConnection."]";
}

public function __destruct(){
foreach ($this->connection as$connection) {
$connection->close(); } } }  DBcontrol Class The DBcontrol class extends the Database Class include_once('database.object.php'); class DBControl extends Database{ //Array of cached queries private$cacheQuery = array();

public function __constuct(){
//Empty
}

/**
* Stores an query in cacheQuery array
* @param Query String $query - query sent to DB * @return Index number of the cached query */ public function CacheQuery($query){
if($result =$this->connection[$this->activeConnection]->query($query)){
$this->cacheQuery[] =$result;
//Number if it's index
$index = count($this->cacheQuery)-1;
return $this->cacheQuery[$index];
}else{
trigger_error('Unable to send or cache query',E_USER_ERROR);
return false;
}

}
/**
* Returns a query stored in the cacheQuery array
* @param Int $cache_id - Index number of cached query * @return The cached query */ public function GetCacheQuery($cache_id){
if(array_key_exists($cache_id,$this->cacheQuery)){
return $this->cacheQuery[$cache_id];
}else{
trigger_error('Cache ID does not exists',E_USER_ERROR);
return false;
}
}
/**
* Returns a cached query array as an associative, numeric array, or both
* @param Int $cache_id - Index number of cached query * @param Parameter$resulttype - Retruned result type default MYSQLI_BOTH
* @return Cached query in result type chosen
*/
public function GetCacheQueryArray($cache_id,$resulttype = MYSQLI_BOTH){
if(array_key_exists($cache_id,$this->cacheQuery)){
return $this->cacheQuery[$cache_id]->fetch_array($resulttype); }else{ trigger_error('Cache ID does not exists',E_USER_ERROR); return false; } } /** * Debugging method * @param Null * @return var_dum of the cachedQuery array */ public function ShowCache(){ echo '<pre>'; print_r($this->cacheQuery);
echo '</pre>';
}
/**
* Returns number of rows changed in DB when using RunnQuery() method
* Note: Only use after a query has been sent
* @param Null
* @return Number of row changed
*/
public function AffectedRows(){
return $this->connection[$this->activeConnection]->affected_rows;
}

public function __destruct(){
parent::__destruct();
}
}


Here is an example if using the classes methods

//Create Connection
$dbcontrol = new DBControl(); //Returns 0 for connection ID to be used for //methods SetActiveConnection and ChangeSelectedDB$connectionID1 = $dbcontrol->CreateConnection('localhost','root','password','splice_app'); //Send first cache query //$query1 holds results
$query1 =$dbcontrol->CacheQuery('SELECT * FROM app_state LIMIT 10');
//Change database but use same user connection cred
$dbcontrol->ChangeSelectedDB($connectionID1,'dummy');
//Send second cache query
//$query2 holds results$query2 = $dbcontrol->CacheQuery('SELECT * FROM post'); //Get cached queries$cachedquery1 = $dbcontrol->GetCacheQueryArray(0);$cachedquery2 = $dbcontrol->GetCacheQueryArray(1); //Combine$cachedquery1 and $cachedquery2 if needed //using the cached queries is the same as //using$query1 and $query2$combinequeries = array_merge($cachedquery1,$cachedquery2);
//Change DB connection
//automatically set connection to be used
$connectionID2 =$dbcontrol->CreateConnection('127.0.0.1','root','password','gencore');
//Send third cache query using new connection
//$query3 holds results$query3 = $dbcontrol->CacheQuery('SELECT * FROM logqueries'); //Switch back to first connection //Using localhost, root, password, splice_app$dbcontrol->SetActiveConnection($connectionID1);  My question is if this a good way to go about created a database interaction class. Thank you for taking the time to read my question. ## 1 Answer Always think this: everything you code must solve some kind of problem. If it doesn't solve a problem. Remove it. Now by looking at your code I think you are trying to solve two problems being: 1. Handling of multiple connections 2. Caching query results for later use Let's define each problem so that it is easy to use, easy to extend and easy to code. Also note that in every step of writing code you should be sure to not restrict anything. It will become clear what I mean here. 1. Handling of multiple databaseconnections We have a DatabaseConnectionHandler that handles DatabaseConnection's. Allready a vague word: 'handles'. So let's define it: A DatabaseConnectionHandler should be able to keep track of all current DatabaseConnection's. You can add, remove and get DatabaseConnection's from the DatabaseConnectionHandler. A simple DatabaseConnectionHandler would look something like this: <?php class DatabaseConnectionHandler { private$connections;

public function addConnection(String $connectionName, DatabaseConnection$connections) {
$this->connection[$connectionName] = $connections; } public function removeConnection(String$connectionName) {
unset($this->connection[$connectionName]);
}

public function getConnection($connectionName) { return$this->connection[$connectionName]; } }  We also mentioned a DatabaseConnection. This class obviously represents a DatabaseConnection. This is very vague, so we have to keep the methods 'vague'. Make sure we don't restrict anything. mysqli_* isn't the only way to connect to a database. Now, what should a DatabaseConnection be able to do? Obviously you need to be able to query it. A nice plus would be to be able to open and close it. so a DatabaseConnection would look something like this: <?php class DatabaseConnection { public function query($query) {}

public function close() {}

public function open() {}

}


But hmm, because this is such a Generic class and in fact it shouldn't really exist on its own. It's more an abstract class. So let's fix that:

abstract class DatabaseConnection {

public abstract function query($query); public abstract function close(); public abstract function open(); }  Ofcourse, our program needs to be able to communicate with a mysql connection. So we need to write a DatabaseConnection class that handles all that stuff. But, lucky us mysqli_* can do all the heavy lifting for us. We only have to write an adapter for it that extends the DatabaseConnection. this would look something like this: <?php class MysqliDatabaseConnection extends DatabaseConnection { private$host;

private $user; private$pass;

private $database; private$connection;

public function __costruct($host,$user,$pass,$batabase) {
$this->host =$host;
$this->user =$user;
$this->pass =$pass;
$this->database =$database;
}

public function query($query) {$this->connection->query($query); } public function close() { return$this->connection->close();
}

public function open() {
$this->connection = new mysqli($host,$user,$pass,$batabase); return !!$this->connection->connect_errno;
}

public function getError() {
return $this->connection->connect_errno } }  now we could start using our classes as follows: <?php //create a DBC handler$myDatabaseHandler = new DatabaseConnectionHandler();

$myDatabaseHandler->addConnection( 'foo', new MysqliDatabaseConnection('localhost','root','password','splice_app') );$myDatabaseHandler->addConnection(
'bar',
);

//lets open both connections
$myDatabaseHandler->getConnection('foo')->open();$myDatabaseHandler->getConnection('bar')->open();

//lets query the first one
$resultFirstQuery =$myDatabaseHandler->getConnection('foo')->query("SELECT * FROM app_state LIMIT 10");

//lets query the second one
$resultSecondQuery =$myDatabaseHandler->getConnection('bar')->query("SELECT * FROM logqueries");


Now this is neat, clean. And if I would like to change mysqli_ to PDO. I wouldn't have to change my entire application. I simply create PDODatabaseConnection class and insert that into the handler instead of mysqli variant.

Other application also don't have to know what the connectionID is. They only need a name (foo and bar in my example).

The next part would be to add caching of queries. This can be done at different levels in the application depending on your need.

But then again, if you really need caching of queries, maybe you shouldn't request the same thing all over your application. Caching wil then not be the answer, just a fix.

I hope you learned something out of my code. Good luck!

And as a last thing, enjoy this link: http://en.wikipedia.org/wiki/SOLID_%28object-oriented_design%29

Some notes: Is my code picture perfect? no. Not at all. Is it production ready? no, not at all. you probably would add some error handling. throw exceptions if the open() fails etc etc

• thank you for your helpful answer. If I understand correctly it's best to keep the db control/handling class(es) abstract and only add the functionality in which the application will need to complete it's different tasks and that the mysqli_ library does not offer. – andrewnite Aug 9 '13 at 17:02
• A classname should always represent what it does. If it says DatabaseConnectionHandler it should handle DatabaseConnection. This is very vague so the class should be very abstract. You then create child classes that add functionality and limit the scope it can be used. This is the Open/closed principle of Solid: en.wikipedia.org/wiki/Open/closed_principle – Pinoniq Aug 9 '13 at 21:48
• There is an error with the first code block. There is no String type-hint in PHP. Now PHP assumes a string class exists and expects the arguments to of such type. Only classes, abstract classes, interfaces and arrays can be type-hinted. – AnotherGuy Feb 4 '15 at 0:07