In PHP you are more than often required to create a connection to a database (Usually MySQL). Too many times we find ourselves re-writting entire connection routines for creating these connections. Another problem is that we don't create it in a modular fashion which means it will work with one but not with the other.

In PHP5 (which I must say is becoming a favourite when compared to the lower PHP versions and any other server side scripting language) we have better support for creating an object orientated design. PHP5 allows us to create, throw and handle exceptions, set access levels on class and functions, methods and class properties. With this added functionality or should I say features, we can create really nice object models for our web applications. Please note that I am targeting this tutorials more at web application developers and as such will not be addressing performance issues associated with websites.

Let us begin. The first thing to understand is that PHP5 provides all the functionality needed to connect to and manipulate databases. The point of this exercise is to create a reusable wrapper that we can just include every time we need it. What is nice about doing it this way is that we can also create connection factories that allow us to connect to multiple database using the same wrappers.

First up we are going top need our database class. This class will form the foundation for all other classes related to the database. Let us begin by calling the class DB_MySQL. Our basic class definitaion will look like this

class DB_MySql{
  protected $user;  // the database user name
  protected $pass;  // the user password
  protected $dbhost; // the database host
  protected $dbname; // the database name
  protected $dbh;  // the database handle

  public function __construct(){}
}

Now we have or class definition, time to start adding functions. Obviously the first thing we need to do is establish a connection to the database. For the sake of simplicity and understanding we will call the function connect

protected function connect(){
  $this->dbh    = mysql_connect($this->dbhost,
                                $this->user,
                                $this->pass);
  if(!is_resource($this->dbh)){
    throw new MySQLException; // we will get to this later
  }
  if(!mysql_select_db($this->dbname,$this->dbh)) {
    throw new MySQLException;
  }
}

The mysql_connect routine assigns the database link to the class dbh property. If it fails it throws a MySQLException (a custom exception that we will get to later). If the connection succeeds we select the database we want to use by calling mysql_select_db. Right now that we have that out the way we can go about adding other functions.

The next function we are going to add is the execute function.

public function execute($query){
  if (!$this->dbh) {
    $this->connect();
  }
  $ret =
    mysql_query($query, $this->dbh);
  if(!$ret){
    throw new MySQLException;
  }elseif(!is_resource($ret)){
    return TRUE;
  }else{
    $stmt =
      new DB_MySQLStatement($this->dbh,$query);
    $stmt->result = $ret;
    return $stmt;
  }
}

Okay, the execute statement does just that, executes something. If we look at the code we will see that it executes the mysq_query function. If all goes well we create a new statement and assign the returned value to the statement result property. Don't worry about the statment class just yet, wel will get to it later.

Okay the third and final function is the prepare function. This function will help us prevent people from entering any harmful sql statements.

public function prepare($query){
  if(!$this->dbh){
    $this->connect();
  }
  $stmt =
    new DB_MySQLStatement($this->dbh, $query)
  return $stmt;
}

And that is that, now save this file and let us proceed to the statement and exception class. These are covered in the tutorials Database Statement and Database Statement and are required before being able to run the connection class.

Below is the complete class:

class DB_MySql{
  protected $user;
  protected $pass;
  protected $dbhost;
  protected $dbname;
  protected $dbh;

  public function __construct(){}

  protected function connect(){
    $this->dbh    = mysql_connect($this->dbhost,
                                  $this->user,
                                  $this->pass);
    if(!is_resource($this->dbh)){
      throw new MySQLException;
    }
    if(!mysql_select_db($this->dbname,$this->dbh)) {
      throw new MySQLException;
    }
  }

  public function execute($query){
    if (!$this->dbh) {
      $this->connect();
    }
    $ret =
      mysql_query($query, $this->dbh);
    if(!$ret){
      throw new MySQLException;
    }elseif(!is_resource($ret)){
      return TRUE;
    }else{
      $stmt =
        new DB_MySQLStatement($this->dbh,$query);
      $stmt->result = $ret;
      return $stmt;
    }
  }

  public function prepare($query){
    if(!$this->dbh){
      $this->connect();
    }
    $stmt =
      new DB_MySQLStatement($this->dbh, $query)
    return $stmt;
  }

}