Location: PHPKode > scripts > MySQL Done Right > mysql-done-right/dbconnect.inc
<?php
/*
  Class         : mysqli_ext
  Requires      : mysqli (PHP 5.0+)
  Purpose       : PHP class to simplify database usage, and still do it the right way
  Version       : 0.24
  Filename      : dbconnect.inc 
  Author        : Daren Schwenke and others.
  Date released : 2009-10-19 
  License       : http://www.gnu.org/licenses/lgpl.txt
  Notes         : This class is the result of many google searches, 
                  and a little coding.  Thank you in advance to the other 
                  people with useful bits.  Just wanted to get it out there.  
                  Suggestions for improvements are welcome!

*/
class mysqli_ext extends mysqli {
  protected $cacheHandles = false;
  protected $prepareHandle = array();
  protected $result;
  protected $stmt;
  public function __construct($dbHost, $dbUsername, $dbPassword, $dbDatabase, $cachePrepare = 0) {
    if ( $cachePrepare ) { $this->cacheHandles = true; }
    parent::__construct($dbHost, $dbUsername, $dbPassword, $dbDatabase);
  }
  public function prepare($sql) {
    if ( $this->cacheHandles ) {
      if ( ! is_object( $this->prepareHandle[$query] ) ) {
        $this->prepareHandle[$sql] = new stmt_ext($this, $sql, true);
      }
      $stmt = $this->prepareHandle[$sql];
    } else {
      $stmt = new stmt_ext($this, $sql, false);
    }
    if(mysqli_error($this)) throw new exception($sql . ':' . mysqli_error($this), mysqli_errno($this));
    return $stmt;
  }
  public function psingle ($sql, $paramtypes = null) {
    # Execute query, return cloned object of first row.
    $query = $this->prepare($sql);
    if (isset($paramtypes)) {
      $params = array(0 => $paramtypes);
      $paramcount = func_num_args();
      for ($i = 2; $i < $paramcount; $i++) $params[] = func_get_arg($i);
      if (!call_user_func_array(array($query, 'bind_param'), $params)) throw new exception(mysqli_error($this), mysqli_errno($this));
    }
    $query->execute();
    if(mysqli_error($this)) throw new exception(mysqli_error($this), mysqli_errno($this));
    $query->store_result();
    if ( $query->num_rows() ) {
      $obj = $query->fetch_object();
      # Clone object, as original disappears when calling query->close.
      $result = new StdClass();
      foreach ($obj as $key => $val) {
        $result->$key = $val;
      }
    } else {
      $result = null;
    }
    $query->close();
    return $result;
  }
  public function pbind ($sql, $paramtypes = null) {
    # Generic binding for greater control.  Allows usage of all methods available such as num_rows, affected_rows, insert_id, fetch_object, fetch_assoc
    $query = $this->prepare($sql);
    if (isset($paramtypes)) {
      $params = array(0 => $paramtypes);
      $paramcount = func_num_args();
      for ($i = 2; $i < $paramcount; $i++) $params[] = func_get_arg($i);
      if (!call_user_func_array(array($query, 'bind_param'), $params)) throw new exception(mysqli_error($this), mysqli_errno($this));
    }
    $query->execute();
    if(mysqli_error($this)) throw new exception(mysqli_error($this), mysqli_errno($this));
    $query->store_result();
    return $query;
  }

  public function prows ($sql, $paramtypes = null) {
    # Just interested in the number of rows returned.  Useful for user exists queries and such without needing to store results.
    $query = $this->prepare($sql);
    if (isset($paramtypes)) {
      $params = array(0 => $paramtypes);
      $paramcount = func_num_args();
      for ($i = 2; $i < $paramcount; $i++) $params[] = func_get_arg($i);
      if (!call_user_func_array(array($query, 'bind_param'), $params)) throw new exception(mysqli_error($this), mysqli_errno($this));
    }
    $query->execute();
    if(mysqli_error($this)) throw new exception(mysqli_error($this), mysqli_errno($this));
    $query->store_result();
    $result = $query->num_rows;
    $query->close();
    return $result;
  }
  public function pexecute ($sql, $paramtypes = null) {
    # Execute query, return number of affected rows.
    $query = $this->prepare($sql);
    if (isset($paramtypes)) {
      $params = array(0 => $paramtypes);
      $paramcount = func_num_args();
      for ($i = 2; $i < $paramcount; $i++) $params[] = func_get_arg($i);
      if (!call_user_func_array(array($query, 'bind_param'), $params)) throw new exception(mysqli_error($this), mysqli_errno($this));
    }
    $query->execute();
    if(mysqli_error($this)) throw new exception(mysqli_error($this), mysqli_errno($this));
    $query->store_result();
    $result = $query->affected_rows;
    $query->close();
    return $result;
  }
  public function pinsert ($sql, $paramtypes = null) {
    # Execute query, return last insert id.
    $query = $this->prepare($sql);
    if (isset($paramtypes)) {
      $params = array(0 => $paramtypes);
      $paramcount = func_num_args();
      for ($i = 2; $i < $paramcount; $i++) $params[] = func_get_arg($i);
      if (!call_user_func_array(array($query, 'bind_param'), $params)) throw new exception(mysqli_error($this), mysqli_errno($this));
    }
    $query->execute();
    if(mysqli_error($this)) throw new exception(mysqli_error($this), mysqli_errno($this));
    $query->store_result();
    $result = ($query->insert_id > 0)?$query->insert_id:null;
    $query->close();
    return $result;
  }
}

class stmt_ext extends mysqli_stmt {
  protected $varsBound = false;
  protected $cacheHandles = false;
  protected $results;
  public function __construct($link, $query, $cachePrepare = false ) {
    # Have to propogate caching specification so we can hijack close().
    if ( $cachePrepare ) { $this->cacheHandles = true; }
    parent::__construct($link, $query);
  }
  public function __destruct() {
    if ( $this->cacheHandles ) { parent::close(); }
  }
  public function close() {
    # Hijack close function.  Keeps prepared queries around for reuse if DB_CACHE is specified.
    if ( $this->cacheHandles ) { $this->free_result(); } else { parent::close(); }
  }
  public function fetch_object() {
    # Replacment for fetch_object replicating it's functionality for bound parameter results.
    if (!$this->varsBound) {
      $meta = $this->result_metadata();
      while ($column = $meta->fetch_field()) {
        $columnName = str_replace(' ', '_', $column->name);
        $bindVarArray[] = &$this->results[$columnName];
      }
      call_user_func_array(array($this, 'bind_result'), $bindVarArray);
      $this->varsBound = true;
    }
    if ($this->fetch() != null) {
      $results = new StdClass();
      foreach ($this->results as $k => $v) {
        $results->$k = $v;
      }
      return $results;
    } else {
      return null;
    }
  }
  public function fetch_assoc() {
    # Replacment for fetch_assoc replicating it's functionality for bound parameter results.
    if (!$this->varsBound) {
      $meta = $this->result_metadata();
      while ($column = $meta->fetch_field()) {
        $columnName = str_replace(' ', '_', $column->name);
        $bindVarArray[] = &$this->results[$columnName];
      }
      call_user_func_array(array($this, 'bind_result'), $bindVarArray);
      $this->varsBound = true;
    }
    if ($this->fetch() != null) {
      foreach ($this->results as $k => $v) {
        $results[$k] = $v;
      }
      return $results;
    } else {
      return null;
    }
  }
}
# Initiate database connection.
$db = new mysqli_ext(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME, DB_CACHE) or die(mysqli_error($db)); 
Return current item: MySQL Done Right