Revisiting the Database Class

Several months ago I wrote a blog post describing how to create a database class that would wrap the native php mysqli connection. You can read that post here. Since that post was written I've learned a lot about proper php classes and realized that I made several mistakes in that blog post that I'd like to revisit.

Singleton

The database class that I wrote constructs a new read/write connection whenever it is instantiated. However, there is nothing unique about this connection - it remains the same no matter how many different instances you have of this class. There should be only one instance of this class in this case. If you have several different connections (say, a different user for each of your databases or schemas) then you'd have to look at passing connection information into the class through a factory class, but this case requires a singleton.

A singleton class is fairly simple to make. By making the __construct method private and maintaining a single instance saved within the class, you guarantee that there will be only one instance of the class. It can be very helpful to restrict some classes in a singleton manner. For this case we'll avoid creating multiple identical mysqli connections throughout the code, adding unnecessary overhead.

Property usage

Properties (or vars) should be defined at the top of the class and should contain the primary controls needed to control the behavior for the class. If you're using strings or integers throughout the class to affect the method behaviors throughout the class there's a good chance that you're doing something wrong. In this case I should have defined the connection parameters as properties, not in the __construct method.

Lazy logic

If you're worried about overhead in a class than you should wait until you're sure you need to do something before you do it. In this case I should have waited until I know a certain connection is needed before instantiating it. The way the class is currently used I create both a read and write connection without checking to see which connection I need to perform my query.

class Database
{

  private static $READ_CONNECTION = array(
    'database' => 'localhost',
    'user' => 'USER',
    'password' => 'PASSWORD');
  private static $WRITE_CONNECTION = array(
    'database' => 'localhost',
    'user' => 'USER',
    'password' => 'PASSWORD');

  private static $read;
  private static $write;
  private static $instance;

  private function __construct() {}

  public static function instance()
  {
    if(!self::$instance)
      self::$instance = new Database();
    return self::$instance;
  }

  private static function get_read_connection()
  {
    if(!self::$read)
    {
      self::$read = new mysqli(
        self::$READ_CONNECTION['database'],
        self::$READ_CONNECTION['user'],
        self::$READ_CONNECTION['password']);
    }
    return self::$read;
  }

  public static function select($query)
  {
    $result = $this->get_read_connection()->query($query);
    if(!$result)
      return false;
    
    $result_array = array();
    while($row = $result->fetch_object())
      $result_array[] = $row;
    $result->close();
    
    return $result_array();
  }

  public static function selectRow($query)
  {
    $result = $this->get_read_connection()->query($query);
    if(!$result || $result->num_rows > 1)
      return false;
    
    $result_row = $result->fetch_object();
    $result->close();
    
    return $result_row;
  }

  private static function get_write_connection()
  {
    if(!self::$write)
    {
      self::$write = new mysqli(
        self::$WRITE_CONNECTION['database'],
        self::$WRITE_CONNECTION['user'],
        self::$WRITE_CONNECTION['password']);
    }
    return self::$write;
  }

  public static function execute($query)
  {
    return $this->get_write_connection()->query($query);
  }

  public static function escape($string)
  {
    return $this->get_write_connection()->real_escape_string($string);
  }

}

Database::instance();

This new class has a number of improvements over the previous database class. It's a singleton, uses some lazy loading logic, and has better abstraction of logic and properties. I'll probably find more ways of improving it in the future, but for now it's a solid piece of my MVC and I'd recommend using it (or pieces of it) to any other developers out there looking for a good wrapper of the mysqli object.