SHARE
TWEET

MySQLDatabase class for handling prepared statements easy!

a guest Mar 2nd, 2011 206 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. <?php
  2.  
  3. /**
  4.  * Master abstract class for a MySQLi Connection
  5.  * @author StormByte <stormbyte@gmail.com>
  6.  */
  7. class MySQLDatabase {
  8.     /**
  9.      * Database Resource
  10.      * @var mysqli
  11.      */
  12.     private $resource;
  13.  
  14.     /**
  15.      * Internal array for STMTs
  16.      * @var array Contains an array with ('stmt' => stmt, 'hasresults' => hasresults)
  17.      */
  18.     private $stmtarray;
  19.  
  20.     /**
  21.      * Array to save stmt's positions along with its names
  22.      * @var array
  23.      */
  24.     private $stmtnamearray;
  25.  
  26.     /**
  27.      * Autoincremental counter
  28.      * @var int
  29.      */
  30.     private $stmtcount;
  31.  
  32.     /**
  33.      * Constructor
  34.      */
  35.     protected function  __construct() {
  36.         $this->resource=new mysqli();
  37.         $this->resource->init();
  38.         $this->stmtarray=array();
  39.         $this->stmtnamearray=null;
  40.         $this->stmtcount=0;
  41.     }
  42.  
  43.     /**
  44.      * Destructor
  45.      */
  46.     public function  __destruct() {
  47.         if ($this->IsConnected()) {
  48.             $this->UnLockTables();
  49.             //borrar las STMT
  50.             $this->Disconnect();
  51.         }
  52.     }
  53.  
  54.     /*
  55.      * Connects to Database
  56.      */
  57.     final protected function Connect($server,$user,$pass, $db) {
  58.         $this->resource->real_connect($server, $user, $pass, $db);
  59.     }
  60.  
  61.     /**
  62.      * Deletes a STMT by its name
  63.      * @param string $name Name of STMT
  64.      */
  65.     final protected function DeleteSTMT($name) {
  66.         $stmt=$this->GetSTMT($name);
  67.         $stmt->close();
  68.         $id=$this->stmtnamearray[$name];
  69.         unset($this->stmtnamearray[$name]);
  70.         unset($this->stmtarray[$id]);
  71.     }
  72.  
  73.     /**
  74.      * Deletes all STMTs
  75.      */
  76.     final protected function DeleteAllSTMT() {
  77.         $stmt;$count=$this->stmtcount;
  78.         for ($i=0; $i<$count; $i++) {
  79.             $stmt=$this->stmtarray[$i]['stmt'];
  80.             if ($stmt) {
  81.                 $stmt->close();
  82.             }
  83.         }
  84.         $this->stmtcount=0;
  85.         unset($this->stmtarray);
  86.         unset($this->stmtnamearray);
  87.     }
  88.  
  89.     /*
  90.      * Disconnects from DB and clear all STMT
  91.      */
  92.     final protected function Disconnect() {
  93.         if ($this->IsConnected()) {
  94.             $this->UnLockTables(); /* Just in case */
  95.             $this->DeleteAllSTMT();
  96.             $this->resource->close();
  97.             unset($this->resource);
  98.         }
  99.     }
  100.  
  101.     /**
  102.      * Executes a SQL sentence
  103.      * @param string $query SQL query
  104.      * @return bool|mysqli_result TRUE or mysqli_result of the query
  105.      */
  106.     final protected function ExecuteQuery($query) {
  107.         if ($this->IsConnected()) {
  108.             return $this->resource->query($query);
  109.         }
  110.     }
  111.  
  112.     /**
  113.      * Prepares and stores one STMT
  114.      * @param string $string Name to store (or alias) that STMT
  115.      * @param string $query SQL query
  116.      */
  117.     final public function PrepareSTMT($stmtname, $query) {
  118.         $hasresults=true;
  119.         /* Remove leading spaces */
  120.         $query=preg_replace("/^\s+/","",$query);
  121.         /* I detect if query shows results or not */
  122.         $str=strtoupper(substr($query, 0, 4));
  123.         if ($str!="SELE" && $str!="SHOW" && $str!="DESC" && $str!="EXPL") {
  124.             $hasresults=false;
  125.         }
  126.         $stmt=$this->resource->prepare($query);
  127.         if ($stmt) {
  128.             array_push($this->stmtarray, array( 'stmt' => &$stmt, 'hasresults' => $hasresults ));
  129.             $this->stmtnamearray[$stmtname]=$this->stmtcount;
  130.             $this->stmtcount++;
  131.         }
  132.         else {
  133.             trigger_error("The sentence '".$stmtname."' could not be loaded!", E_USER_ERROR);
  134.         }
  135.     }
  136.  
  137.     /**
  138.      * Make a reference array
  139.      * @param array $arr Array for converting
  140.      * @return array The array converted to reference
  141.      */
  142.     private function refValues($arr){
  143.         if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
  144.         {
  145.             $refs = array();
  146.             foreach($arr as $key => $value)
  147.                 $refs[$key] = &$arr[$key];
  148.             return $refs;
  149.         }
  150.         return $arr;
  151.     }
  152.  
  153.     /**
  154.      * Obtain the STMT for its name
  155.      * @param string $name Name of STMT
  156.      * @return mysqli_stmt|bool STMT or false in case of error
  157.      */
  158.     private function GetSTMT($name) {
  159.         $stmt=null;
  160.         if (isset ($this->stmtarray[$this->stmtnamearray[$name]]['stmt'])) {
  161.             $stmt=$this->stmtarray[$this->stmtnamearray[$name]]['stmt'];
  162.         }
  163.         else {
  164.             $stmt=false;
  165.         }
  166.         return $stmt;
  167.     }
  168.  
  169.     /**
  170.      * Checks if a STMT returns results (it is SELECT, SHOW, DESCRIBE or EXPLAIN)
  171.      * @param string $name Name of STMT
  172.      * @return bool
  173.      */
  174.     private function GetProduceResultsSTMT($name) {
  175.         return $this->stmtarray[$this->stmtnamearray[$name]]['hasresults'];
  176.     }
  177.  
  178.     /**
  179.      * Executes and fetchs results from STMT
  180.      * @param string $name Name of STMT
  181.      * @param ... Every other parameters comma separated
  182.      * @return bool|array|int False if error, array if has results or integer if has affected_rows
  183.      * return array has 2 indexes, 1 for position, and the other is name of field. Example: $foo[5]['usermail'] will be foo@bar.com
  184.      */
  185.     final public function ExecuteSTMT($name) {
  186.         $stmt=$this->GetSTMT($name);
  187.         if ($stmt) {
  188.             $code='';
  189.  
  190.             /* I make parameters array and autodetect its types */
  191.             $newparams=array();
  192.             for ($i=1; $i<func_num_args(); $i++) {
  193.                 $newparams[$i-1]=func_get_arg($i);
  194.                 if (is_numeric(func_get_arg($i))) {
  195.                     $code.='i';
  196.                 }
  197.                 else {
  198.                     $code.='s';
  199.                 }
  200.             }
  201.             array_unshift($newparams, $code);
  202.             if (count($newparams)>1) { call_user_func_array(array($stmt, 'bind_param'), $this->refValues($newparams)); }
  203.             $stmt->execute();
  204.             $stmt->store_result();
  205.             $result=null;
  206.             if ($stmt->errno!=0) {
  207.                 trigger_error("An error ".$stmt->errno." happened executing STMT ".$name."<br />The error was: ".$stmt->error, E_USER_ERROR);
  208.             }
  209.             else {
  210.                 if(!$this->GetProduceResultsSTMT($name)) {
  211.                     $result = $stmt->affected_rows;
  212.                 }
  213.                 else {
  214.                     $meta = $stmt->result_metadata();
  215.  
  216.                     while ( $field = $meta->fetch_field() ) {
  217.                         $parameters[] = &$row[$field->name];
  218.                     }
  219.  
  220.                     call_user_func_array(array($stmt, 'bind_result'), $this->refValues($parameters));
  221.                     $results=null;
  222.                     while ( $stmt->fetch() ) {
  223.                         $x = array();
  224.                         foreach( $row as $key => $val ) {
  225.                             $x[$key] = $val;
  226.                         }
  227.                         $results[] = $x;
  228.                     }
  229.                     if ($results) {
  230.                         $result = $results;
  231.                     }
  232.                     $stmt->free_result();
  233.                 }
  234.             }
  235.             return  $result;
  236.         }
  237.         else {
  238.             trigger_error("STMT named '".$name."' has not been loaded before or has generated errors.", E_USER_ERROR);
  239.             return false;
  240.         }
  241.     }
  242.  
  243.     /**
  244.      * Obtain affected rows of a STMT previously executed
  245.      * @param string $name Name of STMT
  246.      * @return int
  247.      */
  248.     final public function GetAffectedRowsSTMT($name) {
  249.         return $this->GetSTMT($name)->affected_rows;
  250.     }
  251.  
  252.     /**
  253.      * Gets last Insert ID of an autoincremental key
  254.      * @param string $name Name of STMT
  255.      * @return int
  256.      */
  257.     final public function GetInsertIDSTMT($name) {
  258.         return $this->GetSTMT($name)->insert_id;
  259.     }
  260.  
  261.     /**
  262.      * Write lock tables
  263.      * @param ... $tbl Comma delimited table names
  264.      */
  265.     public function WriteLockTable($tbl) {
  266.         if ($this->IsConnected()) {
  267.             $escapedtbl=$this->resource->real_escape_string($tbl);
  268.             $query="LOCK TABLES ".$tbl;
  269.             for ($i=1; $i<func_num_args(); $i++) {
  270.                 $query.", ".$tbl;
  271.             }
  272.             $query." WRITE";
  273.             $this->resource->query($query);
  274.         }
  275.     }
  276.  
  277.     /**
  278.      * Read lock tables
  279.      * @param ... $tbl Comma delimited table names
  280.      */
  281.     public function ReadLockTable($tbl) {
  282.         if ($this->IsConnected()) {
  283.             $escapedtbl=$this->resource->real_escape_string($tbl);
  284.             $query="LOCK TABLES ".$tbl;
  285.             for ($i=1; $i<func_num_args(); $i++) {
  286.                 $query.", ".$tbl;
  287.             }
  288.             $query." READ";
  289.             $this->resource->query($query);
  290.         }
  291.     }
  292.  
  293.     /**
  294.      * Read/Write locks tables
  295.      * @param ... $tbl Comma delimited table names
  296.      */
  297.     public function RWLockTable($tbl) {
  298.         if ($this->IsConnected()) {
  299.             $escapedtbl=$this->resource->real_escape_string($tbl);
  300.             $query="LOCK TABLES ".$tbl;
  301.             for ($i=1; $i<func_num_args(); $i++) {
  302.                 $query.", ".$tbl;
  303.             }
  304.             $this->resource->query($query." WRITE");
  305.             $this->resource->query($query." READ");
  306.         }
  307.     }
  308.  
  309.     /**
  310.      * Unlock all tables
  311.      */
  312.     public function UnLockTables() {
  313.         if ($this->IsConnected()) {
  314.             $this->resource->query("UNLOCK TABLES");
  315.         }
  316.     }
  317.  
  318.     /**
  319.      * Obtain last server's error
  320.      * @return string
  321.      */
  322.     public function GetError() {
  323.         return mysqli_error($this->resource);
  324.     }
  325.  
  326.     /**
  327.      * Obtain last server's error number
  328.      * @return int
  329.      */
  330.     public function GetErrorNumber() {
  331.         return mysqli_errno($this->resource);
  332.     }
  333.  
  334.     /**
  335.      * Obtain error number from a STMT
  336.      * @param string $name Name of STMT
  337.      * @return int
  338.      */
  339.     public function GetErrorSTMT($name) {
  340.         return $this->GetSTMT($name)->errno;
  341.     }
  342.  
  343.     /**
  344.      * Obtain error from a STMT
  345.      * @param string $name Name of STMT
  346.      * @return int
  347.      */
  348.     public function GetErrorNumberSTMT($name) {
  349.         return $this->GetSTMT($name)->error;
  350.     }
  351.  
  352.     /**
  353.      * Checks if server is connected
  354.      * @return boolean
  355.      */
  356.     public function IsConnected() {
  357.         $result=false;
  358.         if (!is_null($this->resource)) {
  359.             if ($this->resource->connect_errno==0) {
  360.                 $result=true;
  361.             }
  362.         }
  363.         return $result;
  364.     }
  365.  
  366.     /**
  367.      * Filters a string to be passed to mysql
  368.      * @param string $str String to filter
  369.      * @return string Filtered string
  370.      */
  371.     public function FilterString($str) {
  372.         return $this->resource->real_escape_string($str);
  373.     }
  374. };
  375.  
  376. ?>
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!
 
Top