Skip to content

Instantly share code, notes, and snippets.

@brendo
Created February 9, 2010 23:58
Show Gist options
  • Save brendo/299840 to your computer and use it in GitHub Desktop.
Save brendo/299840 to your computer and use it in GitHub Desktop.
<?php
Class DatabaseException extends Exception{
/*
Array
(
[query] =>
[msg] => Access denied for user 'rdoot'@'localhost' (using password: YES)
[num] => 1045
)
*/
private $_error;
public function __construct($message, array $error=NULL){
parent::__construct($message);
$this->_error = $error;
}
public function getQuery(){
return $this->_error['query'];
}
public function getDatabaseErrorMessage(){
return $this->_error['msg'];
}
public function getDatabaseErrorCode(){
return $this->_error['num'];
}
}
Class MySQLi {
const __WRITE_OPERATION__ = 0;
const __READ_OPERATION__ = 1;
private $_connection = array();
private $_log;
private $_result;
private $_lastResult = array();
private $_lastQuery;
private $_affectedRows;
private $_insertID;
private $_dumpTables = array();
private $_client_info;
private $_client_encoding;
private $_query_count;
private $_cache;
private $_logEverything;
function __construct(){
$this->_query_count = 0;
$this->_cache = NULL;
$this->_logEverything = NULL;
$this->flushLog();
}
function __destruct(){
$this->flush();
$this->close();
}
public function toggleCaching(){
$this->_cache = !$this->_cache;
}
public function enableCaching(){
$this->_cache = true;
}
public function disableCaching(){
$this->_cache = false;
}
public function isCachingEnabled(){
return $this->_cache;
}
public function toggleLogging(){
$this->_logEverything = !$this->_logEverything;
}
public function enableLogging(){
$this->_logEverything = true;
}
public function disableLogging(){
$this->_logEverything = false;
}
public function isLogging(){
return $this->_logEverything;
}
public function setPrefix($prefix){
$this->_connection['tbl_prefix'] = $prefix;
}
public function isConnected(){
return ($this->_connection['id'] instanceof mysqli && !mysqli_connect_error());
}
public function getSelected(){
return $this->_connection['database'];
}
public function getConnectionResource(){
return $this->_connection['id'];
}
public function connect($host=NULL, $user=NULL, $password=NULL, $port ='3306'){
$this->_connection['id'] = NULL;
if($host) $this->_connection['host'] = $host;
if($user) $this->_connection['user'] = $user;
if($password) $this->_connection['pass'] = $password;
if($port) $this->_connection['port'] = $port;
$this->_connection['id'] = @new mysqli($this->_connection['host'], $this->_connection['user'], $this->_connection['pass'], null, $this->_connection['port']);
if(!$this->isConnected()){
$this->__error();
return false;
}
$this->_client_info = mysqli_get_client_info();
$this->_client_encoding = mysqli_character_set_name($this->_connection['id']);
return true;
}
public function setCharacterSet($set='utf8'){
$this->_connection['id']->set_charset($set);
}
## Depreciated..
public function setCharacterEncoding($set='utf8'){
self::setCharacterSet($set);
//$this->query("SET NAMES '$set'");
}
public function select($db=NULL){
if($db) {
$this->_connection['database'] = $db;
} else {
return false;
}
if(!$this->_connection['id']->select_db($this->_connection['database'])) {
$this->__error();
$this->_connection['database'] = null;
return false;
}
return true;
}
public function cleanValue($value) {
if (method_exists('mysqli', 'real_escape_string')) {
return $this->_connection['id']->real_escape_string($value);
} else {
return addslashes($value);
}
}
public function cleanFields(array &$array){
foreach($array as $key => $val){
// Handle arrays with more than 1 level
if(is_array($val)){
self::cleanFields($val);
continue;
}
elseif(strlen($val) == 0){
$array[$key] = 'NULL';
}
else{
$array[$key] = "'" . self::cleanValue($val) . "'";
}
}
}
public function insert(array $fields, $table, $updateOnDuplicate=false){
// Multiple Insert
if(is_array(current($fields))){
$sql = "INSERT INTO `$table` (`".implode('`, `', array_keys(current($fields))).'`) VALUES ';
foreach($fields as $key => $array){
// Sanity check: Make sure we dont end up with ',()' in the SQL.
if(!is_array($array)) continue;
self::cleanFields($array);
$rows[] = '('.implode(', ', $array).')';
}
$sql .= implode(", ", $rows);
}
// Single Insert
else{
self::cleanFields($fields);
$sql = "INSERT INTO `$table` (`".implode('`, `', array_keys($fields)).'`) VALUES ('.implode(', ', $fields).')';
if($updateOnDuplicate){
$sql .= ' ON DUPLICATE KEY UPDATE ';
foreach($fields as $key => $value) $sql .= " `$key` = $value,";
$sql = trim($sql, ',');
}
}
return $this->query($sql);
}
public function update($fields, $table, $where=NULL){
self::cleanFields($fields);
$sql = "UPDATE $table SET ";
foreach($fields as $key => $val)
$rows[] = " `$key` = $val";
$sql .= implode(', ', $rows) . ($where != NULL ? ' WHERE ' . $where : NULL);
return $this->query($sql);
}
public function delete($table, $where){
$this->query("DELETE FROM $table WHERE $where");
}
public function close(){
if($this->isConnected()) return $this->_connection['id']->close();
}
public function determineQueryType($query){
return (preg_match('/^(insert|replace|delete|update|optimize|truncate)/i', $query) ? self::__WRITE_OPERATION__ : self::__READ_OPERATION__);
}
public function query($query){
if(empty($query)) return false;
$query = trim($query);
$query_type = $this->determineQueryType($query);
if($query_type == self::__READ_OPERATION__ && $this->isCachingEnabled() !== NULL && !preg_match('/^SELECT\s+SQL(_NO)?_CACHE/i', $query)){
if($this->isCachingEnabled() === false) $query = preg_replace('/^SELECT\s+/i', 'SELECT SQL_NO_CACHE ', $query);
elseif($this->isCachingEnabled() === true) $query = preg_replace('/^SELECT\s+/i', 'SELECT SQL_CACHE ', $query);
}
if($this->_connection['tbl_prefix'] != 'tbl_'){
$query = preg_replace('/tbl_(\S+?)([\s\.,]|$)/', $this->_connection['tbl_prefix'].'\\1\\2', $query);
}
$query_hash = md5($query.microtime());
$this->_log['query'][$query_hash] = array('query' => $query, 'start' => precision_timer());
$this->flush();
$this->_lastQuery = $query;
$this->_result = $this->_connection['id']->query($query);
$this->_query_count++;
if(!$this->_result) {
$this->__error();
return false;
}
if($this->_result instanceof mysqli_result){
while ($row = $this->_result->fetch_object()){
array_push($this->_lastResult, $row);
}
} else if($this->_result && self::__WRITE_OPERATION__) {
array_push($this->_lastResult, $this->_connection['id']->info);
}
$this->_log['query'][$query_hash]['time'] = precision_timer('stop', $this->_log['query'][$query_hash]['start']);
if($this->_logEverything) $this->_log['query'][$query_hash]['lastResult'] = $this->_lastResult;
return true;
}
public function extractTargetTablesFromQuery($query){
if(!preg_match('/\\s+FROM\\s+(([\\w\\d\\-`_]+(,(\\s+)?)?)+)/i', $query, $matches)) return 'DUAL';
return $matches[1];
}
public function numOfRows(){
return $this->_result->num_rows;
}
public function getInsertID(){
return $this->_connection['id']->insert_id;
}
public function queryCount(){
return $this->_query_count;
}
public function fetch($query=NULL, $index_by_field=NULL){
if($query) $this->query($query);
elseif($this->_lastResult == NULL){
return array();
}
$newArray = array();
foreach ($this->_lastResult as $row){
$newArray[] = get_object_vars($row);
}
if($index_by_field && isset($newArray[0][$index_by_field])){
$n = array();
foreach($newArray as $ii)
$n[$ii[$index_by_field]] = $ii;
$newArray = $n;
}
return $newArray;
}
public function fetchRow($offset=0, $query=NULL){
$arr = $this->fetch($query);
return (empty($arr) ? array() : $arr[$offset]);
}
public function fetchCol ($name, $query = NULL){
$arr = $this->fetch($query);
if(empty($arr)) return array();
foreach ($arr as $row){
$result[] = $row[$name];
}
return $result;
}
public function fetchVar ($varName, $offset = 0, $query = NULL){
$arr = $this->fetch($query);
return (empty($arr) ? NULL : $arr[$offset][$varName]);
}
public function flush(){
$this->_result = NULL;
$this->_lastResult = array();
$this->_lastQuery = NULL;
}
public function flushLog(){
$this->_log = array('error' => array(), 'query' => array());
}
private function __error($msg = NULL){
if(!$this->isConnected()){
$msg = mysqli_connect_error();
$errornum = mysqli_connect_errno();
}
if(!$msg){
$msg = $this->_connection['id']->error();
$errornum = $this->_connection['id']->errno();
}
$this->_log['error'][] = array('query' => $this->_lastQuery,
'msg' => $msg,
'num' => $errornum);
throw new DatabaseException(__('MySQL Error (%1$s): %2$s in query "%3$s"', array($errornum, $msg, $this->_lastQuery)), end($this->_log['error']));
}
public function debug($section=NULL){
if(!$section) return $this->_log;
return ($section == 'error' ? $this->_log['error'] : $this->_log['query']);
}
public function getLastError(){
return current($this->_log['error']);
}
public function getStatistics(){
$stats = array();
$query_log = $this->debug('query');
$query_timer = 0.0;
$slow_queries = array();
foreach($query_log as $key => $val) {
$query_timer += floatval($val['time']);
if($val['time'] > 0.0999) $slow_queries[] = $val;
}
return array('queries' => $this->queryCount(),
'slow-queries' => $slow_queries,
'total-query-time' => number_format($query_timer, 4, '.', ''));
}
public function import($sql){
$queries = preg_split('/;[\\r\\n]+/', $sql, -1, PREG_SPLIT_NO_EMPTY);
if(is_array($queries) && !empty($queries)){
foreach($queries as $sql){
if(trim($sql) != '') $result = $this->query($sql);
if(!$result) return false;
}
}
return true;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment