PHP Mysql Class

The Adaptor Pattern

The Adaptor pattern is used to provide access to an object via a specific interface. In a purely OO language, the Adaptor pattern specifically addresses providing an alternative API to an object; but in PHP we most often see this pattern as providing an alternative interface to a set of procedural routines.

Providing the ability to interface with a class via a specific API can be helpful for two main reasons:

• If multiple classes providing similar services implement the same API, you can switch between them at runtime. This is known as polymorphism. This is derived from Greek: Poly means "many," and morph means "form."

• A predefined framework for acting on a set of objects may be difficult to change. When incorporating a third-party class that does not comply with the API used by the framework, it is often easiest to use an Adaptor to provide access via the expected API.

The most common use of adaptors in PHP is not for providing an alternative interface to one class via another (because there is a limited amount of commercial PHP code, and open code can have its interface changed directly). PHP has its roots in being a procedural language; therefore, most of the built-in PHP functions are procedural in nature. When functions need to be accessed sequentially (for example, when you're making a database query, you need to use mysql_pconnect(), mysql_select_db(), mysql_query(), and mysql_fetch()), a resource is commonly used to hold the connection data, and you pass that into all your functions. Wrapping this entire process in a class can help hide much of the repetitive work and error handling that need to be done.

The idea is to wrap an object interface around the two principal MySQL extension resources: the connection resource and the result resource. The goal is not to write a true abstraction but to simply provide enough wrapper code that you can access all the MySQL extension functions in an OO way and add a bit of additional convenience. Here is a first attempt at such a wrapper class:

class DB_Mysql {
  protected $user;
  protected $pass;
  protected $dbhost;
  protected $dbname;
  protected $dbh;    // Database connection handle 

  public function _ _construct($user, $pass, $dbhost, $dbname) {
    $this->user = $user;
    $this->pass = $pass;
    $this->dbhost = $dbhost;
    $this->dbname = $dbname;
  }
  protected function connect() { 
    $this->dbh = mysql_pconnect($this->dbhost, $this->user, $this->pass);
    if(!is_resource($this->dbh)) {
      throw new Exception;
    }
    if(!mysql_select_db($this->dbname, $this->dbh)) { 
      throw new Exception;
    }
  }
  public function execute($query) {
    if(!$this->dbh) {
      $this->connect();
    }
    $ret = mysql_query($query, $this->dbh);
    if(!$ret) { 
      throw new Exception;
    }
    else if(!is_resource($ret)) {
      return TRUE;
    } else {
      $stmt = new DB_MysqlStatement($this->dbh, $query);
      $stmt->result = $ret;
      return $stmt; 
    }
  }
}

To use this interface, you just create a new DB_Mysql object and instantiate it with the login credentials for the MySQL database you are logging in to (username, password, hostname, and database name):

$dbh = new DB_Mysql("testuser", "testpass", "localhost", "testdb");
$query = "SELECT * FROM users WHERE name = '".mysql_escape_string($name)."'"; 
$stmt = $dbh->execute($query);

This code returns a DB_MysqlStatement object, which is a wrapper you implement around the MySQL return value resource:

class DB_MysqlStatement {
  protected $result;
  public $query;
  protected $dbh;
  public function _ _construct($dbh, $query) {
    $this->query = $query; 
    $this->dbh = $dbh;
    if(!is_resource($dbh)) {
      throw new Exception("Not a valid database connection");
    }
  }
  public function fetch_row() {
    if(!$this->result) {
       throw new Exception("Query not executed");
    }
    return mysql_fetch_row($this->result);
  }
  public function fetch_assoc() {
    return mysql_fetch_assoc($this->result);
  }
   public function fetchall_assoc() {
    $retval = array();
    while($row = $this->fetch_assoc()) {
      $retval[] = $row;
    }
    return $retval;
  }
}

To then extract rows from the query as you would by using mysql_fetch_assoc(), you can use this:

while($row = $stmt->fetch_assoc()) {
  // process row
}