Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL abstraction library
<?php
define('POSITIVE', 1);
define('NEGATIVE', -1);
define('DB_CONFIG_FILE', '/path/to/mysql.json');
/* MySQLi module does not have this function by default */
function mysqli_field_name($result, $field_offset) {
$properties = mysqli_fetch_field_direct($result, $field_offset);
return is_object($properties) ? $properties->name : null;
}
function mysqli_field_table($result, $field_offset) {
$properties = mysqli_fetch_field_direct($result, $field_offset);
return is_object($properties) ? $properties->table : null;
}
class mysqli_db {
var $Hostname = '';
var $Username = '';
var $Password = '';
var $Database = '';
var $error;
var $conn = null;
var $sql;
var $num_rows;
public $lastID;
var $_query_count;
var $_log_query;
var $Unbuffered = false;
var $total_time = 0;
var $resources = array();
var $query_log = array();
function __construct($hn = false, $un = false, $pw = false, $db = false) {
try {
// first variable might be a file path to json config.
if (file_exists(DB_CONFIG_FILE)) {
$config = @json_decode(file_get_contents(DB_CONFIG_FILE));
} else {
$config = false;
}
} catch (Exception $e) {
$config = false;
}
if ($config) {
if ($config->hostname) $this->Hostname = $config->hostname;
if ($config->username) $this->Username = $config->username;
if ($config->password) $this->Password = $config->password;
if ($config->database) $this->Database = $config->database;
} elseif (getenv('DB_HOSTNAME') && getenv('DB_USERNAME') && getenv('DB_PASSWORD') && getenv('DB_DATABASE')) {
$this->Hostname = getenv('DB_HOSTNAME');
$this->Username = getenv('DB_USERNAME');
$this->Password = getenv('DB_PASSWORD');
$this->Database = getenv('DB_DATABASE');
} elseif ($hn && $un && $pw && $db) {
$this->Hostname = $hn;
$this->Username = $un;
$this->Password = $pw;
$this->Database = $db;
}
}
function init_conn() {
if ($this->conn != null) {
return;
}
$this->conn = @mysqli_connect($this->Hostname, $this->Username, $this->Password);
if (!$this->conn) {
if (mysqli_connect_errno() == 1040) {
die('The server is currently overloaded. Please try again in 10 minutes.');
}
die('The server is currently not available. Please try again in 10 minutes. e'.mysqli_connect_errno());
return;
}
if (!mysqli_select_db($this->conn, $this->Database)) {
die('The server is currently not available.');
}
mysqli_set_charset($this->conn, "utf8mb4");
}
function disconnect(){
return mysqli_close($this->conn);
}
function escape($string) {
return mysqli_real_escape_string($this->conn, $string);
}
function & Query($sql = '', ...$args){
$this->init_conn();
global $_log_query;
$this->sql = $sql;
$_is_prepared = (func_num_args() > 1) == True;
if ($_is_prepared) {
// TODO: Refactor everything here to $args_types = get_args_types($args);
$arg_list = $args;
$args_types = '';
$type_map = Array(
"boolean" => "b",
"integer" => "i",
"double" => "d",
"string" => "s",
"array" => "s",
"object" => "s",
"resource" => "s",
"NULL" => "s",
"unknown type" => "s",
);
foreach ($arg_list as $arg) {
$type = gettype($arg);
$ctype = $type_map[$type];
$args_types .= $ctype;
}
}
# Measure query time
$query_start = microtime(true);
if (!$_is_prepared) {
$sql = mysqli_query($this->conn, $sql);
$this->num_rows = @mysqli_affected_rows($this->conn);
} else {
$p = $this->conn->prepare($sql);
$p->bind_param($args_types, ...$arg_list);
#call_user_func_array(array($p, 'bind_param'), $arg_list);
$p->execute();
$sql = $p->get_result();
$p->store_result();
$this->num_rows = $p->affected_rows;
$p->close();
}
$query_end = microtime(true);
$query_time = ($query_end - $query_start);
$this->total_time += $query_time;
$this->Unbuffered = false;
$this->_query_count++;
$this->resources[$this->_query_count] = &$sql;
// build backtrace
$backtrace = debug_backtrace(0,3);
$stacktrace = array();
foreach ($backtrace as $key => $arr) {
if ($arr['file'] == __FILE__) {
continue;
}
$stacktrace[] = array(
'file' => str_replace($_SERVER['DOCUMENT_ROOT'], '', $arr['file']),
'line' => $arr['line'],
'function' => $arr['function']);
}
$this->query_log[$this->_query_count] = array('query' => $this->sql, 'time'=>$query_time, 'stacktrace' => $stacktrace);
//$this->num_rows = @mysqli_affected_rows($this->conn);
$this->lastID = @mysqli_insert_id($this->conn);
$this->error = '';
# FIXME: Should not be recursive
if (mysqli_errno($this->conn) == 1205) { // Lock Timeout Exceeded
sleep(1);
$sql = $this->Query($this->sql);
} elseif (mysqli_errno($this->conn) == 1213) { // Deadlock found
sleep(1);
$sql = $this->Query($this->sql);
} elseif (mysqli_error($this->conn) && !$this->skipNextError) {
$this->error = mysqli_errno($this->conn) . ': ' . mysqli_error($this->conn);
trigger_error(htmlentities($this->sql).'<br /><br />'.htmlentities($this->error), E_USER_ERROR);
}
$this->skipNextError = false;
return $sql;
}
function & rQuery($sql, ...$args){
$result = $this->Query($sql, ...$args);
if ($this->num_rows > 1) trigger_error('Multiple row selection for rQuery call', E_USER_WARNING);
if (!$result) return false;
$return = mysqli_fetch_object($result);
mysqli_free_result($result);
return $return;
}
function & aQuery($sql, ...$args) {
$result = $this->Query($sql, ...$args);
if ($this->num_rows > 1) trigger_error('Multiple row selection for aQuery call', E_USER_WARNING);
if (!$result) return false;
$return = mysqli_fetch_assoc($result);
mysqli_free_result($result);
return $return;
}
function & oQuery($sql, ...$args){
$result = $this->Query($sql, ...$args);
if ($this->num_rows > 1) trigger_error('Multiple row selection for oQuery call', E_USER_WARNING);
if ($result) {
list($sql) = mysqli_fetch_row($result);
mysqli_free_result($result);
}
else
$sql = NULL;
return $sql;
}
function & cQuery($sql, ...$args){
$this->Unbuffered = 1;
$result = $sql = $this->Query($sql, ...$args);
$csql = array();
if ($result) {
while (list($_) = mysqli_fetch_row($sql)) $csql[] = $_;
mysqli_free_result($result);
}
return $csql;
}
function & keyQuery($sql, ...$args){
$this->Unbuffered = 1;
$result = $sql = $this->Query($sql, ...$args);
$csql = array();
if ($result) {
while (list($_) = mysqli_fetch_row($sql)) $csql[$_] = true;
mysqli_free_result($result);
}
return $csql;
}
function & kvQuery($sql, ...$args) {
$this->Unbuffered = 1;
$result = $sql = $this->Query($sql, ...$args);
$csql = array();
while ($q = mysqli_fetch_row($sql))
$csql[$q[0]] = $q[1];
mysqli_free_result($result);
return $csql;
}
function & rowsQuery($sql, ...$args) {
// Same as krQuery but does allows prepared statements and does not accept custom key
// The key will be the first column
$result = $sql = $this->Query($sql, ...$args);
if (!$result) return array();
$key = mysqli_field_name($result, 0);
$csql = array();
while ($q = mysqli_fetch_object($sql)) {
$csql[$q->$key] = $q;
}
mysqli_free_result($result);
return $csql;
}
function & krQuery($key, $sql = false) {
// Some code leverages the $key variable. Until that is removed this function can't be deprecated
// once $key is not in use - replace rowsQuery with krQuery()
if (!$sql) {
$sql = $key;
$key = false;
}
$result = $sql = $this->Query($sql);
if (!$result) return array();
if (!$key) {
$key = mysqli_field_name($result, 0);
}
$csql = array();
while ($q = mysqli_fetch_object($sql)) {
$csql[$q->$key] = $q;
}
mysqli_free_result($result);
return $csql;
}
}
$db = new mysqli_db('/path/to/mysql.json');
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment