PHP Classes

File: src/unreal4u/dbmysqli.php

Recommend this page to a friend!
  Classes of Camilo Sperberg  >  Extended MySQLi  >  src/unreal4u/dbmysqli.php  >  Download  
File: src/unreal4u/dbmysqli.php
Role: Class source
Content type: text/plain
Description: Class source
Class: Extended MySQLi
MySQL database access wrapper using MySQLi
Author: By
Last change: Merge branch 'master' of github.com:unreal4u/db_mysqli

Conflicts:
src/unreal4u/dbmysqli.php
Date: 8 years ago
Size: 25,385 bytes
 

Contents

Class file image Download
<?php namespace unreal4u; include(dirname(__FILE__).'/auxiliar_classes.php'); /** * Extended MySQLi Parametrized DB Class * * dbmysqli.php, a MySQLi database access wrapper * Original idea from Mertol Kasanan, http://www.phpclasses.org/browse/package/5191.html * Optimized, tuned and fixed by unreal4u (Camilo Sperberg) * * @package dbmysqli * @version 5.0.0 * @author Camilo Sperberg, http://unreal4u.com/ * @author Mertol Kasanan * @license BSD License * @copyright 2009 - 2014 Camilo Sperberg * * @method int num_rows() num_rows() Returns the number of results from the query * @method mixed[] insert_id() insert_id($query, $args) Returns the insert id of the query * @method mixed[] query() query($query, $args) Returns false if query could not be executed, resultset otherwise */ class dbmysqli { /** * The version of this class * @var string */ private $classVersion = '5.0.0'; /** * Contains the actual DB connection instance * @var object */ private $db = null; /** * Contains the prepared statement * @var object */ private $stmt = null; /** * Internal indicator indicating whether we are connected to the database or not. Defaults to false * @var boolean */ private $isConnected = false; /** * Internal statistics collector * @var array */ private $stats = array(); /** * Saves the last known error. Can be boolean false or string with error otherwise. Defaults to false * @var mixed[] */ private $error = false; /** * Internal indicator to know whether we are in a transaction or not. Defaults to false * @var boolean */ private $inTransaction = false; /** * Internal indicator to know whether we should rollback the current transaction or not. Defaults to false * @var boolean */ private $rollback = false; /** * Counter of failed connections to the database. Defaults to 0 * @var int */ private $failedConnectionsCount = 0; /** * Provides a flag for knowing if we are in our own custom handler or not. Defaults to false * @var boolean */ private $isWithinCustomErrorHandler = false; /** * Keep an informational array with all executed queries. Defaults to false * @var boolean */ public $keepLiveLog = false; /** * Maintains statistics of the executed queries, but only if $this->keepLiveLog is set to true * * @see $this->keepLiveLog * @var array */ public $dbLiveStats = array(); /** * Maintains statistics exclusively from the errors in SQL * @var array */ public $dbErrors = array(); /** * Whether to disable throwing exceptions. Defaults to false * @var boolean */ public $supressErrors = false; /** * Whether to throw errors on invalid queries. Defaults to false * @var boolean */ public $throwQueryExceptions = false; /** * The number of maximum failed attempts trying to connect to the database. Defaults to 10 * @var int */ public $failedConnectionsTreshold = 10; /** * Indicator for number of executed queries. Defaults to 0 * @var int */ public $executedQueries = 0; /** * The constructor, optionally (default off) enter immediatly into transaction mode * * @param boolean $inTransaction Whether to begin a transaction, defaults to false */ public function __construct($inTransaction=false) { if (version_compare(PHP_VERSION, '5.3.0', '<')) { $this->throwException('Sorry, class only valid for PHP &gt; 5.3.0, please consider upgrading to the latest version', __LINE__); } if ($inTransaction === true) { $this->begin_transaction(); } } /** * Ends a transaction if needed committing remaining changes */ public function __destruct() { if ($this->isConnected === true OR $this->inTransaction === true) { $this->end_transaction(); } } /** * Controls all the calls to the class * * @param string $method The method to call * @param array $arg_array The data, such as the query. Can also by empty */ public function __call($method, array $arg_array=null) { // Sets our own error handler (Defined in auxiliar_classes.php) $this->enableCustomErrorHandler(); $this->error = false; // Some custom statistics $this->stats = array( 'time' => microtime(true), 'memory' => memory_get_usage(), ); switch ($method) { case 'num_rows': case 'insert_id': case 'query': $this->executedQueries++; $this->execute_query($arg_array); if ($method == 'query') { $result = $this->execute_result_array($arg_array); } else { $resultInfo = $this->execute_result_info($arg_array); $result = $resultInfo[$method]; } break; default: $result = 'Method not supported!'; break; } $this->logStatistics($this->stats, $arg_array, $result, $this->error); // Restore whatever error handler we had before calling this class $this->restoreErrorHandler(); // Finally, return our result return $result; } /** * Magic get method. Will always return the number of rows * * @param string $v Any identifier supported by @link $this->execute_result_info() * @return array Returns an array with the requested index (supported by execute_result_info) */ public function __get($v='') { $resultInfo = $this->execute_result_info(); if (!isset($resultInfo[$v])) { $resultInfo[$v] = 'Method not supported!'; } return $resultInfo[$v]; } /** * Magic toString method. Will return current version of this class * * @return string */ public function __toString() { return basename(__FILE__).' v'.$this->classVersion.' by Camilo Sperberg - http://unreal4u.com/'; } /** * Will return MySQL version or client version * * @param boolean $clientInformation Set to true to return client information. Defaults to false * @return string Returns a string with the client version */ public function version($clientInformation=false) { $result = false; if (empty($clientInformation)) { $result = $this->query('SELECT VERSION()'); if (!empty($result)) { $result = $result[0]['VERSION()']; } } else { $this->registerConnection(); $temp = explode(' ', $this->db->client_info); $result = $temp[1]; } return $result; } /** * Begins a transaction, optionally with other credentials * * Note: This function will set throwQueryExceptions to true because without it we have no way of knowing that the * transaction actually succeeded or not. * * @param string $databaseName The database name * @param string $host The host of the MySQL server * @param string $username The username * @param string $passwd The password * @param int $port The port to which MySQL is listening to * * @return boolean Returns whether we are or not in a transaction */ public function begin_transaction($databaseName='', $host='', $username='', $passwd='', $port=0) { if ($this->inTransaction === false) { if ($this->registerConnection($databaseName, $host, $username, $passwd, $port)) { $this->inTransaction = true; $this->throwQueryExceptions = true; $this->db->autocommit(false); } } return $this->inTransaction; } /** * Ends a transaction * * @return boolean Returns whether we are or not in a transaction */ public function end_transaction() { if ($this->inTransaction === true) { if ($this->rollback === false) { $this->db->commit(); } else { $this->db->rollback(); $this->rollback = false; $result = false; } $this->db->autocommit(true); $this->inTransaction = false; } return $this->inTransaction; } /** * Opens a new connection to a MySQL database * * If you want to open another connection, use this method and provide the necesary credentials. Provided * credentials will overwrite default values. Note that database name is in first place! * This function will immediatly establish a connection to the database and won't wait for the first query to be * executed. * * @param string $databaseName The database name * @param string $host The host of the MySQL server * @param string $username The username * @param string $passwd The password * @param int $port The port to which MySQL is listening to * @return boolean Returns true if connection is established, false otherwise */ public function registerConnection($databaseName='', $host='', $username='', $passwd='', $port=0) { $return = false; if ($this->isConnected === false) { $this->enableCustomErrorHandler(); if (empty($host)) { $host = DB_MYSQLI_HOST; } if (empty($username)) { $username = DB_MYSQLI_USER; } if (empty($passwd)) { $passwd = DB_MYSQLI_PASS; } if (empty($databaseName)) { $databaseName = DB_MYSQLI_NAME; } if (empty($port)) { $port = DB_MYSQLI_PORT; } $this->connectToDatabase($host, $username, $passwd, $databaseName, $port); $this->restoreErrorHandler(); } return $this->isConnected; } /** * This method will open a connection to the database * * @return boolean Returns value indicating whether we are connected or not */ private function connectToDatabase($host, $username, $passwd, $database, $port) { if ($this->isConnected === false) { if ($this->failedConnectionsCount < $this->failedConnectionsTreshold) { try { // Always capture all errors from the singleton connection $db_connect = mysql_connect::getInstance($host, $username, $passwd, $database, $port); $this->db = $db_connect->db; $this->isConnected = true; } catch (databaseException $e) { // Log the error in our internal error collector and re-throw the exception $this->failedConnectionsCount++; $this->logError(null, 0, 'fatal', $e->getMessage()); $this->throwException($e->getMessage(), $e->getLine()); } } else { $this->throwException('Too many attempts to connect to database, not trying anymore', __LINE__); } } return $this->isConnected; } /** * Function that checks what type is the data we are trying to insert * * Supported bind types (http://php.net/manual/en/mysqli-stmt.bind-param.php): * i corresponding variable has type integer * d corresponding variable has type double * s corresponding variable has type string * b corresponding variable is a blob and will be sent in packets * * @TODO Support for blob type data (will now go through string type) * * @param array $arg_array All values that the query will be handling * @return array Returns an array with a string of types and another one with the corrected values */ protected function castValues(array $arg_array=null) { $types = ''; if (!empty($arg_array)) { foreach ($arg_array as $v) { switch ($v) { // @TODO Check the following condition very well! // Empty STRING case '': $types .= 's'; break; // All "integer" types case is_null($v): case is_bool($v): case is_int($v): $types .= 'i'; break; // Save a float type data case is_float($v): $types .= 'd'; break; // Save a string typed data case is_string($v): #default: // @FIXME Disabled until good testing of consequences $types .= 's'; break; } } } $returnArray = array( 'types' => $types, 'arg_array' => $arg_array, ); return $returnArray; } /** * Function that prepares and binds the query * * @param $arg_array array Contains the binded values * @return boolean Whether we could execute the query or not */ private function execute_query(array $arg_array=null) { $executeQuery = false; if ($this->registerConnection()) { $sqlQuery = array_shift($arg_array); $tempArray = $this->castValues($arg_array); $types = $tempArray['types']; $arg_array = $tempArray['arg_array']; unset($tempArray); if (isset($this->stmt)) { unset($this->stmt); } $this->stmt = $this->db->prepare($sqlQuery); if (!is_object($this->stmt)) { $this->logError($sqlQuery, $this->db->errno, 'fatal', $this->db->error); } if (!empty($arg_array)) { array_unshift($arg_array, $types); if (empty($this->error)) { if (!$executeQuery = @call_user_func_array(array($this->stmt, 'bind_param'), $this->makeValuesReferenced($arg_array))) { $this->logError($sqlQuery, $this->stmt->errno, 'fatal', 'Failed to bind. Do you have equal parameters for all the \'?\'?'); $executeQuery = false; } } } else { if (!empty($sqlQuery)) { $executeQuery = true; } } if ($executeQuery AND is_object($this->stmt)) { $this->stmt->execute(); $this->stmt->store_result(); } elseif (!$this->error) { $this->logError($sqlQuery, 0, 'non-fatal', 'General error: Bad query or no query at all'); } } return $executeQuery; } /** * Returns data like the number of rows and last insert id * * @param array $arg_array Contains the binded values * @return array Can return affected rows, number of rows or last id inserted. */ private function execute_result_info(array $arg_array=null) { $result = array(); if (!$this->error) { if ($this->db->affected_rows > 0) $num_rows = $this->db->affected_rows; else { if (isset($this->db->num_rows)) { $num_rows = $this->db->num_rows; } else { $num_rows = 0; } } $result['num_rows'] = $num_rows; $result['insert_id'] = $this->db->insert_id; } return $result; } /** * Establishes the $result array: the data itself * * @param array $arg_array * @return mixed Returns the array with data, false if there was an error present or int with errno if an error at this stage happens */ private function execute_result_array(array $arg_array) { $result = false; if (!$this->error) { if ($this->stmt->error) { $this->logError(null, $this->stmt->errno, 'fatal', $this->stmt->error); return false; } $result_metadata = $this->stmt->result_metadata(); if (is_object($result_metadata)) { $rows = array(); $fields = $result_metadata->fetch_fields(); foreach($fields AS $field) { $rows[$field->name] = null; $dataTypes[$field->name] = $field->type; $params[] =& $rows[$field->name]; } $result = array(); call_user_func_array(array( $this->stmt, 'bind_result' ), $params); while ($this->stmt->fetch()) { foreach ($rows as $key => $val) { $c[$key] = $val; // Fix for boolean data types: hard-detect these and set them explicitely as boolean // Complete list: http://www.php.net/manual/en/mysqli-result.fetch-fields.php#113949 switch ($dataTypes[$key]) { case 7: // timestamp case 10: // date case 11: // time case 12: // datetime if ($val !== null) { $c[$key] = new \DateTime($val); } break; case 16: // bit $c[$key] = (bool)$val; break; case 4: // float case 5: // double case 246: // decimal $c[$key] = floatval($val); break; // Following are just as quick reference for later (maybe) #case 1: // tinyint #case 2: // smallint #case 3: // int #case 8: // bigint #case 9: // mediumint #case 13: // year #case 252: // Text related field #case 253: // varchar #case 254: // char #default: # break; } } $result[] = $c; } $result = \SplFixedArray::fromArray($result); } elseif ($this->stmt->errno == 0) { $result = true; } else { $result = $this->stmt->errno; } } return $result; } /** * Enables our own intern error handler * * @link http://php.net/manual/en/function.set-error-handler.php Documentation on returned values * @link http://www.tyrael.hu/2011/06/26/performance-of-error-handling-in-php/ Benchmark on set_error_handler * * Conclusion: (...) the overhead of having a custom error handler is almost negligible if it isn’t called. (...) if * you have an error and a custom error handler which gets executed, that yields for a ~10X performance loss, * regardless of using the suppression operator or not. * * AKA: If your queries do have a lot of errors, then this will slow things down. Otherwise, you can capture them * and do whatever you want, such as logging them or mailing the faulty queries to yourself. * * @return mixed Returns whatever value set_error_handler returns or false if custom error handler is already set */ private function enableCustomErrorHandler() { $return = false; if ($this->isWithinCustomErrorHandler === false) { $this->isWithinCustomErrorHandler = true; $return = set_error_handler(array('\\'.__NAMESPACE__.'\\databaseErrorHandler', 'handleError')); } return $return; } /** * Restores the previous setted error handler * * @return boolean Returns true if error handler has been restored or false if no custom error handler had been previously set */ private function restoreErrorHandler() { $return = false; if ($this->isWithinCustomErrorHandler === true) { $this->isWithinCustomErrorHandler = false; $return = restore_error_handler(); } return $return; } /** * Throws an exception if these are enabled * * @param string $msg The string to print within the exception * @param int $line The line in which the exception ocurred * @throws databaseException * @return boolean Returns always false (only when supressErrors is active) */ protected function throwException($msg='', $line=0) { if (empty($this->supressErrors)) { throw new databaseException($msg, $line, __FILE__); } return false; } /** * Throws exception on query error * * @param string $query * @param string $mysqlErrorString * @param int $mysqlErrno * @throws queryException */ protected function throwQueryException($query='', $mysqlErrorString='', $mysqlErrno=0) { if (!empty($this->throwQueryExceptions)) { throw new queryException($query, $mysqlErrorString, $mysqlErrno); } return false; } /** * Function that logs all errors * * @param string $query The query to log * @param int $errno The error number to log * @param string $type Whether the error is fatal or non-fatal * @param string $error The error description * @return boolean Always returns true. */ private function logError($query, $errno, $type='non-fatal', $error=null) { if (empty($error)) { $complete_error = '(not specified)'; } else if ($type == 'non-fatal') { $complete_error = '[NOTICE] ' . $error; } else { $complete_error = '[ERROR] ' . $error; $this->rollback = true; } $this->dbErrors[] = array( 'query' => $query, 'query_number' => $this->executedQueries, 'errno' => $errno, 'type' => $type, 'error' => $complete_error ); if ($type == 'fatal') { $this->error = '[' . $errno . '] ' . $error; $this->throwQueryException($query, $error, $errno); } return true; } /** * Function that executes after each query * * @param array $stats * @param array $arg_array * @param array $result * @param mixed[] $error * @return boolean Returns true if logentry could be made, false otherwise */ private function logStatistics(array $stats, array $arg_array, $result, $error) { $return = false; if ($this->keepLiveLog === true) { $stats = array( 'memory' => memory_get_usage() - $stats['memory'], 'time' => number_format(microtime(true) - $stats['time'], 5, ',', '.'), ); if ($error == false) { $errorString = 'FALSE'; } else { $errorString = 'TRUE'; } $inTransaction = 'FALSE'; if ($this->inTransaction === true) { $inTransaction = 'TRUE'; } $resultInfo = $this->execute_result_info($arg_array); $query = reset($arg_array); if (!isset($resultInfo['num_rows'])) { $resultInfo['num_rows'] = 0; } $this->dbLiveStats[] = array( 'query' => $query, 'number_results' => $resultInfo['num_rows'], 'time' => $stats['time'] . ' (seg)', 'memory' => $stats['memory'] . ' (bytes)', 'error' => $error, 'within_transaction' => $inTransaction, ); $return = true; } return $return; } /** * Creates an referenced representation of an array * * @author Hugo Simon http://www.phpclasses.org/discuss/package/5812/thread/5/ * @param array $arr The array that creates a referenced copy * @return array A referenced copy of the original array */ private function makeValuesReferenced(array $arr=null) { $refs = null; if (!empty($arr)) { $refs = array(); foreach ($arr as $key => $value) { $refs[$key] = &$arr[$key]; } } return $refs; } }