MySQLDatabase class for handling prepared statements easy!

<?php

/**
 * Master abstract class for a MySQLi Connection
 * @author StormByte <stormbyte@gmail.com>
 */
class MySQLDatabase {
    /**
     * Database Resource
     * @var mysqli
     */
    private $resource;

    /**
     * Internal array for STMTs
     * @var array Contains an array with ('stmt' => stmt, 'hasresults' => hasresults)
     */
    private $stmtarray;

    /**
     * Array to save stmt's positions along with its names
     * @var array
     */
    private $stmtnamearray;

    /**
     * Autoincremental counter
     * @var int
     */
    private $stmtcount;

    /**
     * Constructor
     */
    protected function  __construct() {
        $this->resource=new mysqli();
        $this->resource->init();
        $this->stmtarray=array();
        $this->stmtnamearray=null;
        $this->stmtcount=0;
    }

    /**
     * Destructor
     */
    public function  __destruct() {
        if ($this->IsConnected()) {
            $this->UnLockTables();
            //borrar las STMT
            $this->Disconnect();
        }
    }

    /*
     * Connects to Database
     */
    final protected function Connect($server,$user,$pass, $db) {
        $this->resource->real_connect($server, $user, $pass, $db);
    }

    /**
     * Deletes a STMT by its name
     * @param string $name Name of STMT
     */
    final protected function DeleteSTMT($name) {
        $stmt=$this->GetSTMT($name);
        $stmt->close();
        $id=$this->stmtnamearray[$name];
        unset($this->stmtnamearray[$name]);
        unset($this->stmtarray[$id]);
    }

    /**
     * Deletes all STMTs
     */
    final protected function DeleteAllSTMT() {
        $stmt;$count=$this->stmtcount;
        for ($i=0; $i<$count; $i++) {
            $stmt=$this->stmtarray[$i]['stmt'];
            if ($stmt) {
                $stmt->close();
            }
        }
        $this->stmtcount=0;
        unset($this->stmtarray);
        unset($this->stmtnamearray);
    }

    /*
     * Disconnects from DB and clear all STMT
     */
    final protected function Disconnect() {
        if ($this->IsConnected()) {
            $this->UnLockTables(); /* Just in case */
            $this->DeleteAllSTMT();
            $this->resource->close();
            unset($this->resource);
        }
    }

    /**
     * Executes a SQL sentence
     * @param string $query SQL query
     * @return bool|mysqli_result TRUE or mysqli_result of the query
     */
    final protected function ExecuteQuery($query) {
        if ($this->IsConnected()) {
            return $this->resource->query($query);
        }
    }

    /**
     * Prepares and stores one STMT
     * @param string $string Name to store (or alias) that STMT
     * @param string $query SQL query
     */
    final public function PrepareSTMT($stmtname, $query) {
        $hasresults=true;
        /* Remove leading spaces */
        $query=preg_replace("/^\s+/","",$query);
        /* I detect if query shows results or not */
        $str=strtoupper(substr($query, 0, 4));
        if ($str!="SELE" && $str!="SHOW" && $str!="DESC" && $str!="EXPL") {
            $hasresults=false;
        }
        $stmt=$this->resource->prepare($query);
        if ($stmt) {
            array_push($this->stmtarray, array( 'stmt' => &$stmt, 'hasresults' => $hasresults ));
            $this->stmtnamearray[$stmtname]=$this->stmtcount;
            $this->stmtcount++;
        }
        else {
            trigger_error("The sentence '".$stmtname."' could not be loaded!", E_USER_ERROR);
        }
    }

    /**
     * Make a reference array
     * @param array $arr Array for converting
     * @return array The array converted to reference
     */
    private function refValues($arr){
        if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
        {
            $refs = array();
            foreach($arr as $key => $value)
                $refs[$key] = &$arr[$key];
            return $refs;
        }
        return $arr;
    }

    /**
     * Obtain the STMT for its name
     * @param string $name Name of STMT
     * @return mysqli_stmt|bool STMT or false in case of error
     */
    private function GetSTMT($name) {
        $stmt=null;
        if (isset ($this->stmtarray[$this->stmtnamearray[$name]]['stmt'])) {
            $stmt=$this->stmtarray[$this->stmtnamearray[$name]]['stmt'];
        }
        else {
            $stmt=false;
        }
        return $stmt;
    }

    /**
     * Checks if a STMT returns results (it is SELECT, SHOW, DESCRIBE or EXPLAIN)
     * @param string $name Name of STMT
     * @return bool
     */
    private function GetProduceResultsSTMT($name) {
        return $this->stmtarray[$this->stmtnamearray[$name]]['hasresults'];
    }

    /**
     * Executes and fetchs results from STMT
     * @param string $name Name of STMT
     * @param ... Every other parameters comma separated
     * @return bool|array|int False if error, array if has results or integer if has affected_rows
     * return array has 2 indexes, 1 for position, and the other is name of field. Example: $foo[5]['usermail'] will be foo@bar.com
     */
    final public function ExecuteSTMT($name) {
        $stmt=$this->GetSTMT($name);
        if ($stmt) {
            $code='';

            /* I make parameters array and autodetect its types */
            $newparams=array();
            for ($i=1; $i<func_num_args(); $i++) {
                $newparams[$i-1]=func_get_arg($i);
                if (is_numeric(func_get_arg($i))) {
                    $code.='i';
                }
                else {
                    $code.='s';
                }
            }
            array_unshift($newparams, $code);
            if (count($newparams)>1) { call_user_func_array(array($stmt, 'bind_param'), $this->refValues($newparams)); }
            $stmt->execute();
            $stmt->store_result();
            $result=null;
            if ($stmt->errno!=0) {
                trigger_error("An error ".$stmt->errno." happened executing STMT ".$name."<br />The error was: ".$stmt->error, E_USER_ERROR);
            }
            else {
                if(!$this->GetProduceResultsSTMT($name)) {
                    $result = $stmt->affected_rows;
                }
                else {
                    $meta = $stmt->result_metadata();

                    while ( $field = $meta->fetch_field() ) {
                        $parameters[] = &$row[$field->name];
                    }

                    call_user_func_array(array($stmt, 'bind_result'), $this->refValues($parameters));
                    $results=null;
                    while ( $stmt->fetch() ) {
                        $x = array();
                        foreach( $row as $key => $val ) {
                            $x[$key] = $val;
                        }
                        $results[] = $x;
                    }
                    if ($results) {
                        $result = $results;
                    }
                    $stmt->free_result();
                }
            }
            return  $result;
        }
        else {
            trigger_error("STMT named '".$name."' has not been loaded before or has generated errors.", E_USER_ERROR);
            return false;
        }
    }

    /**
     * Obtain affected rows of a STMT previously executed
     * @param string $name Name of STMT
     * @return int
     */
    final public function GetAffectedRowsSTMT($name) {
        return $this->GetSTMT($name)->affected_rows;
    }

    /**
     * Gets last Insert ID of an autoincremental key
     * @param string $name Name of STMT
     * @return int
     */
    final public function GetInsertIDSTMT($name) {
        return $this->GetSTMT($name)->insert_id;
    }

    /**
     * Write lock tables
     * @param ... $tbl Comma delimited table names
     */
    public function WriteLockTable($tbl) {
        if ($this->IsConnected()) {
            $escapedtbl=$this->resource->real_escape_string($tbl);
            $query="LOCK TABLES ".$tbl;
            for ($i=1; $i<func_num_args(); $i++) {
                $query.", ".$tbl;
            }
            $query." WRITE";
            $this->resource->query($query);
        }
    }

    /**
     * Read lock tables
     * @param ... $tbl Comma delimited table names
     */
    public function ReadLockTable($tbl) {
        if ($this->IsConnected()) {
            $escapedtbl=$this->resource->real_escape_string($tbl);
            $query="LOCK TABLES ".$tbl;
            for ($i=1; $i<func_num_args(); $i++) {
                $query.", ".$tbl;
            }
            $query." READ";
            $this->resource->query($query);
        }
    }

    /**
     * Read/Write locks tables
     * @param ... $tbl Comma delimited table names
     */
    public function RWLockTable($tbl) {
        if ($this->IsConnected()) {
            $escapedtbl=$this->resource->real_escape_string($tbl);
            $query="LOCK TABLES ".$tbl;
            for ($i=1; $i<func_num_args(); $i++) {
                $query.", ".$tbl;
            }
            $this->resource->query($query." WRITE");
            $this->resource->query($query." READ");
        }
    }

    /**
     * Unlock all tables
     */
    public function UnLockTables() {
        if ($this->IsConnected()) {
            $this->resource->query("UNLOCK TABLES");
        }
    }

    /**
     * Obtain last server's error
     * @return string
     */
    public function GetError() {
        return mysqli_error($this->resource);
    }

    /**
     * Obtain last server's error number
     * @return int
     */
    public function GetErrorNumber() {
        return mysqli_errno($this->resource);
    }

    /**
     * Obtain error number from a STMT
     * @param string $name Name of STMT
     * @return int
     */
    public function GetErrorSTMT($name) {
        return $this->GetSTMT($name)->errno;
    }

    /**
     * Obtain error from a STMT
     * @param string $name Name of STMT
     * @return int
     */
    public function GetErrorNumberSTMT($name) {
        return $this->GetSTMT($name)->error;
    }

    /**
     * Checks if server is connected
     * @return boolean
     */
    public function IsConnected() {
        $result=false;
        if (!is_null($this->resource)) {
            if ($this->resource->connect_errno==0) {
                $result=true;
            }
        }
        return $result;
    }

    /**
     * Filters a string to be passed to mysql
     * @param string $str String to filter
     * @return string Filtered string
     */
    public function FilterString($str) {
        return $this->resource->real_escape_string($str);
    }
};

?>