OOP php5 structure

Question

Hello.

I have been trying to make OOP PHP5 code. But I think my attempts are clumsy. These are my questions:

• Is their a better, more leaner way to include database config information?
• Can I somehow get around having to declare $db = new Db() in every function I make?
• Should I use PEAR as database abstraction layer instead of Mysqli_database.php?

Mysqli_database.php

<?php
class Db {
    private $connection;

    private function open_connection() {
     if (file_exists('config.inc.php')) {
      require('config.inc.php');
     } else {
      require('../config.inc.php');
     }
     try
     {
      $this->connection = mysqli_connect($dbhost,$dbuser,$dbpass,$dbname);
     }
     catch (Exception $e)
     {
      throw $e;
     }
    }
    private function close_connection() {
     try
     {
      mysqli_close($this->connection);
     }
     catch (Exception $e)
     {
      throw $e;
     }
    }
    public function query($query) {
     try
     {
      $this->open_connection();
      $result = mysqli_query($this->connection,$query);
      return $result;
     }
     catch (Exception $e)
     {
      throw $e;
     }
     $this->close_connection();
    }
    public function fetchArray($query) {
     $row = mysqli_fetch_assoc($query);
     return $row;
    }
    public function count_rows($query) {
     $row = mysqli_num_rows($query);
     return $row;
    }
    public function rows_affected() {
     $row = mysqli_affected_rows($this->connection);
     return $row;
    }
    public function created_id() {
     $row = mysqli_insert_id($this->connection);
     return $row;
    }
}
?>

Test_data.php

<?php
class Test_data {
    public function show_text() {
     $db = new Db();
     $sql = $db->query("SELECT * FROM test_table");
     $row = $db->fetchArray($sql);
     echo 'This is the output: '.$row['text'];
    }
}
?>

config.inc.php

<?php
$dbname  = 'database_name';
$dbhost  = 'localhost';
$dbuser  = 'database_user';
$dbpass  = 'database_password';
?>

includes.php

<?php
require_once('config.inc.php');
require_once('Mysqli_database.php');
$db = new Db();
$test_data = new Test_data();
?>

index.php

<?php
require_once('includes.php');
$test_data->show_text();
?>

Answer 1

The config information is a matter of taste. But it would be better stored in a config object, and retreived in a more OO way then including global variables from another file.

You can get around creating a new object by using the singleton pattern.

The more abstracted layer you choose, the easier it will be to move from one database to another. You can also have a look at PDO.

Answer 2

There are some really common patterns for establishing database connections: Singleton, Factory, and sometimes Registry.

Here's how one might look.

<?php

class DbConn
{
  const CONN_DEV_1  = 'dev.db1';
  const CONN_PROD_1 = 'prod.db1';
  const CONN_DEV_2  = 'dev.db2';
  const CONN_PROD_2 = 'prod.db2';

  protected static $instances = array();

  protected $conn;

  public static function factory( $database, $env )
  {
    $connectionName = "$env.$database";
    if ( !isset( self::$instances[$connectionName] ) )
    {
      switch ( $connectionName )
      {
        case self::CONN_DEV_1:
          $dbname = 'dev1';
          $dbhost = 'localhost';
          $dbuser = 'database_user';
          $dbpass = 'database_password';
          break;
        case self::CONN_PROD_1:
          $dbname = 'prod1';
          $dbhost = 'some.server';
          $dbuser = 'database_user';
          $dbpass = 'database_password';
          break;
        case self::CONN_DEV_2:
          $dbname = 'dev2';
          $dbhost = 'localhost';
          $dbuser = 'database_user';
          $dbpass = 'database_password';
          break;
        case self::CONN_PROD_2:
          $dbname = 'prod2';
          $dbhost = 'some.server';
          $dbuser = 'database_user';
          $dbpass = 'database_password';
          break;
        default:
          throw new Exception( 'Unrecognized database connection!' );
      }
      self::$instances[$connectionName] = new self( $dbhost,$dbuser,$dbpass,$dbname );
    }
    return self::$instances[$connectionName];
  }

  private function __construct( $dbhost, $dbuser, $dbpass, $dbname )
  {
    $this->conn = mysqli_connect( $dbhost, $dbuser, $dbpass, $dbname );
  }

  /* all your other methods here */
}

and in usage

$db1 = DbConn::factory( 'db1', 'dev' );

Obviously the point here is to pull the value for $env from the current application config, wherever that may come from.

now in terms of usage, generally you want to pass/hand functions/classes a database connection, and not make them responsible for establishing the connection themselves. this provides looser coupling. To use your example:

<?php
class Test_data
{
    protected $db;

    public function __construct( DbConn $db )
    {
        $this->db = $db;
    }

    public function show_text()
    {
        $sql = $this->db->query( "SELECT * FROM test_table" );
        $row = $this->db->fetchArray($sql);
        echo 'This is the output: '.$row['text'];
    }
}
?>

Answer 3

As for capturing an instance in each function, a DB object like this is the de-facto example given for the Singleton pattern. It really does fit nicely here.

Here's a rough example:

class DB
{

   // Private Constructor so external code cannot directly instantiate
   private function __construct() {  

   }

   public static function instance() {
     static $instance = false;

     if (!$instance)
       $instance = new DB();

     return $instance;

   }

}

And a small variation, if you want to have multiple DB Connections open (to different databases) would be an instance method like this:

public static function instance($dsn) {
   static $instances = array();

   if (!isset($instances[$dsn]))
       $instances[$dsn] = new DB($dsn);

   return $instances[$dsn];

}