build a mysqli db connection class and other classes use it

Question

Sorry for the stupid question am a newbie to oop in php...i have a proble wiriting a mysqli class thet returns a connection handler that enable me the connection handler in other classes/methods. i run a small procedural blog n wanted to convert to oop. heres a my working code example. please refactor for me where necessary. Thank in advance.

$db_link = mysqli_connect(DB,HOST,DB_USER,DB,PASS,DB_NAME) or die(mysql_error());
class myDB extends mysqli
{
  public function getConfig($id, $db_link) // thanks cbuckley
 {
   $db_link = $this->db_link;
   $res = mysqli_query($this->db_link,"SELECT * from config where id='1'");
   $row = mysqli_fetch_array($res);
   return $row['option'];
  }    
}

i have defined the constants already and the connection is successful but the select is not working. on the index page this goes

include('inc/dbc.php'); 
$db = new MyDB(); 
echo $db->getConfig(1, $db_link);  

any help is appreciated

Answer 1

Your question is not very specific, and I have the feeling you're not really aware what your concrete problems are. You've already accept an answer that gives you some code but puts you in the wrong direction as it does not solve your underlying problem and wastes code.

The first thing you should know if you want to make use and benefit of the object oriented interface of PHP's mysqli extension is, that the Mysqli class represents the database connection (the "link" as it was named for a procedural approach) already:

require('inc/dbc.php');

$dbConnection = new Mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

That's already it. Sure you might want to use some error handling here:

if ($dbConnection->connect_error)
{
    throw new Exception(
       sprintf('(#%d) %s', $dbConnection->connect_errorno,
                $dbConnection->connect_error)
    );
}

Throwing exception instead of let's say die('message') is done, because you can easily create an exception handler so that you can display a more useful response to the user from within a central place instead of handling each exceptional error-case where it appears (actually at the place where die would be).

You can also log and mail the backtrace so you can fix things more easily. Naturally you do not need to use exceptions, however the rule of thumb is to use die only in scripts you throw away in let's say a week and it does not work well with object oriented design.

As you will need this code anyway in all places where you will need your database connection, you can create your own connection object to bring those parts together as they belong together:

class DatabaseException extends Exception
{
}

class DatabaseConnection extends Mysqli
{
    public function __construct($host, $user, $password, $database = "", $port = NULL, $socket = NULL) {

        parent::__construct($host, $user, $password, $database, $port, $socket);

        $this->throwConnectionExceptionOnConnectionError();
    }

    private function throwConnectionExceptionOnConnectionError() {

        if (!$this->connect_error) return;

        $message = sprintf('(%s) %s', $this->connect_errno, $this->connect_error);

        throw new DatabaseException($message);
    }
}

The usage is actually pretty straight forward and very much the same, only the name of the class varies and it's definition needs to be loaded:

require('inc/dbc.php');
require('inc/database.php');

$dbConnection = new DatabaseConnection(DB_HOST, DB_USER, DB_PASS, DB_NAME);

As written, the connection object already represents your database connection. So every part in your application that needs it, has to ask for it. Let's review your example function:

function getOption($id, $db_link)
{
   // $db_link = $this->db_link;
   $res = mysqli_query($this->db_link,"SELECT * from config where id='1'");
   $row = mysqli_fetch_array($res);
   return $row['option'];
}

I renamed the function and I commented the first line, even this might be what you want. Actually, if that function would be part of the DatabaseConnection object, it could work like this:

class DatabaseConnection extends Mysqli
{
    ...

    public function getOption($id) {
        $statement = $this->prepare('SELECT `option` FROM config WHERE id=?');
        $statement->bind_param('i', $id);
        $statement->execute();
        $statement->bind_result($option);
        $statement->fetch();
        $statement->close();
        return $option;
    }

As this example demonstrates, the database connection is already there. However, this is not advisable. Imagine you not only have options but this and that and such and what not more. You would create one function after the other all in one class. Well for a little application that might even so work right, but imagine more and more. You would get one very large class that is responsible for many things. So it would be bad to do this, even if you can use $this already to prepare the statement.

Also take note that you should prepare statements. This has been answered here numerous times, if you're not used to it, read about it, it's worth the lines. There are better ways to not repeat code (DRY: Don't repeat yourself) while stepping into object oriented (you should even already do this with procedural).

So as to have this all in one class would be a problem, you instead put it in a class of it's own:

class DatabaseModelBase
{
    protected $connection;

    public function __construct(Connection $connection) {
        $this->connection = $connection;
    }

    protected function prepare($query) {
        $connection = $this->connection;
        $statement = $connection->prepare($query);
        if (!$statement) {
            throw new DatabaseException(
                sprintf('(%s) %s', $connection->error, $connection->errno)
            );
        }
        return $statement;
    }
}

class Option extends DatabaseModelBase
{
    public function find($id) {
        $statement = $this->prepare('SELECT `option` FROM config WHERE id=?');
        $statement->bind_param('i', $id);
        $statement->execute();
        $statement->bind_result($option);
        $statement->fetch();
        $statement->close();

        return $option;
    }
}

This has some extended error handling again, because most often mistakes are made in the SQL query. And as you can see the individual function to fetch some specific data is placed in it's own class. You can use such classes to group the fetching and updating for specific datatypes.

Usage in full:

$dbConnection = new Connection(DB_HOST, DB_USER, DB_PASS, DB_NAME);

$option = new Option($dbConnection);

$optionValue = $option->find(1);

echo $optionValue; # value for option with ID1

The names of the Option object probably is not well, I tried to keep the example lightweight but also offer some separation. For other scenarious you might want to prefer some different kind how to access the db connection, because it is injected into Option via it's constructor, Option does not deal any longer with the details how the database connection is being created.

For example you can make the database connection object more smart only to connect to the database the first time prepare or query is actually used with it. So that requests to your website that do not need a database connection would not needlessly connect to the database.

You find more examples in other questions and you might want to learn about dependency injection. Also you always want to keep things apart from each other, so you have some objects that are only lightly connected to each other.

• How to successfully rewrite old mysql-php code with deprecated mysql_* functions?
• Dependency Injection simple implementation

Answer 2

I would suggest moving the connect to the database also inside the class:

class myDB extends mysqli
{
  private $link;

  public function Connect()
  {
    $this->link = mysqli_connect(DB,HOST,DB_USER,DB,PASS,DB_NAME) or die(mysql_error());
  }

  public function getConfig($id) // thanks cbuckley
  {
     $res = mysqli_query($this->link,"SELECT * from config where id='$id'");
     $row = mysqli_fetch_array($res);
     return $row['option'];
   }    
}

And use it like this:

include('inc/dbc.php'); 
$db = new MyDB();
$db->Connect(); 
echo $db->getConfig(1);

Answer 3

I recommend you to Wrap the mysqli class in a new Class instead extending it: For those Classes, where you need the Configuration, just include the Configuration with Dependency Injection. This brings you the Benefit, that the Code, where you need the Configuration, don't need to know about the Way, Configuration get its values. If you later decide to parse Configuration from a iniFile, you only need to change the Configuration-Class or even better, write a new Class which implements ConfigurationInterface and inject the new ConfigurationClass.

class Configuration implements ConfigurationInterface{
        private $mysqli;

        public function construct(mysqli $mysqli) {
            $this->mysqli = $mysqli;
        }

        public function get($key) 
        {
            // You should escape this query to prevent SQL-injection
            $res = $this->mysqli->query("SELECT * from config where id='$key'");
            $row = $res>fetch_array();
            return $row['option'];
        }
    }

interface ConfigurationInterface {
    public get($key);
}

Use it like this:

$mysqli = new mysqli('localhost', 'root', 'pw', 'my_db');
$config = new Configuration($mysqli);

var_dump($config->get(0));