Mysql Query Class - PHP OOP Method

Versions of MySQL prior to 4.1 do not provide a separate interface for users to prepare queries prior to execution or allow bind SQL. For us, though, passing all the variable data into the process separately provides a convenient place to intercept the variables and escape them before they are inserted into the query. An interface to the new MySQL 4.1 functionality is provided through Georg Richter's mysqli extension.

To accomplish this, you need to modify DB_Mysql to include a prepare method and DB_MysqlStatement to include bind and execute methods:

class DB_Mysql {
  /* ... */
  public function prepare($query) {
    if(!$this->dbh) {
      $this->connect();
    }
    return new DB_MysqlStatement($this->dbh, $query); 
  }
}
class DB_MysqlStatement {
  public $result;
  public $binds;
  public $query;
  public $dbh;
  /* ... */
  public function execute() {
    $binds = func_get_args();
    foreach($binds as $index => $name) { 
      $this->binds[$index + 1] = $name;
    }
    $cnt = count($binds);
    $query = $this->query;
    foreach ($this->binds as $ph => $pv) {
      $query = str_replace(":$ph", "'".mysql_escape_string($pv)."'", $query); 
    }
    $this->result = mysql_query($query, $this->dbh);
    if(!$this->result) {
      throw new MysqlException;
    }
    return $this;
  }
  /* ... */
}

In this case, prepare() actually does almost nothing; it simply instantiates a new DB_MysqlStatement object with the query specified. The real work all happens in DB_MysqlStatement. If you have no bind parameters, you can just call this:

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

The real benefit of using this wrapper class rather than using the native procedural calls comes when you want to bind parameters into your query. To do this, you can embed placeholders in your query, starting with :, which you can bind into at execution time:

$dbh = new DB_Mysql("testuser", "testpass",
"localhost", "testdb");
$stmt = $dbh->prepare("SELECT * FROM users WHERE name = :1"); 
$stmt->execute($name);

The :1 in the query says that this is the location of the first bind variable. When you call the execute() method of $stmt, execute() parses its argument, assigns its first passed argument ($name) to be the first bind variable's value, escapes and quotes it, and then substitutes it for the first bind placeholder :1 in the query.

Even though this bind interface doesn't have the traditional performance benefits of a bind interface, it provides a convenient way to automatically escape all input to a query.