PHP framework building: Database Control Classes

Question

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.

Answer 1

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

//add some db connections
$myDatabaseHandler->addConnection(
    'foo',
    new MysqliDatabaseConnection('localhost','root','password','splice_app')
);
$myDatabaseHandler->addConnection(
    'bar',
    new MysqliDatabaseConnection('127.0.0.1','root','password','gencore')
);

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