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, $use

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:

• Handling of multiple connections

• 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:

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:

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:

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

StackExchange Code Review Q#29555, answer score: 2