lib/Database/ez_mysqli.php
<?php
declare(strict_types=1);
namespace ezsql\Database;
use Exception;
use ezsql\Db;
use ezsql\ezsqlModel;
use ezsql\ConfigInterface;
use ezsql\DatabaseInterface;
class ez_mysqli extends ezsqlModel implements DatabaseInterface
{
private $return_val = 0;
private $is_insert = false;
private $isTransactional = false;
/**
* Database connection handle
* @var \mysqli
*/
private $dbh;
/**
* Query result
* @var mixed
*/
private $result;
/**
* Database configuration setting
* @var ConfigInterface
*/
private $database;
protected $shortcutUsed = false;
public function __construct(ConfigInterface $settings = null)
{
if (empty($settings)) {
throw new Exception(\MISSING_CONFIGURATION);
}
parent::__construct();
$this->database = $settings;
if (!Db::has('ez' . \MYSQLI))
Db::set('ez' . \MYSQLI, $this);
Db::set('global', $this);
} // __construct
public function settings()
{
return $this->database;
}
/**
* Short hand way to connect to mysql database server and select a mysql
* database at the same time
*
* @param string $user The database user name
* @param string $password The database users password
* @param string $name The name of the database
* @param string $host The host name or IP address of the database server.
* Default is localhost
* @param string $charset Encoding of the database
* @return boolean
*/
public function quick_connect(
string $user = '',
string $password = '',
string $name = '',
string $host = '',
$port = '',
string $charset = ''
) {
$user = empty($user) ? $this->database->getUser() : $user;
$password = empty($password) ? $this->database->getPassword() : $password;
$name = empty($name) ? $this->database->getName() : $name;
$host = empty($host) ? $this->database->getHost() : $host;
$port = empty($port) ? $this->database->getPort() : $port;
$charset = empty($charset) ? $this->database->getCharset() : $charset;
if (!$this->connect($user, $password, $host, (int) $port, $charset));
else if (!$this->dbSelect($name, $charset));
return $this->_connected;
} // quick_connect
/**
* Try to connect to mySQLi database server
*
* @param string $user The database user name
* @param string $password The database users password
* @param string $host The host name or IP address of the database server.
* Default is localhost
* @param string $charset The database charset
* Default is empty string
* @return boolean
*/
public function connect(
string $user = '',
string $password = '',
string $host = '',
$port = '',
string $charset = ''
) {
$this->_connected = false;
$user = empty($user) ? $this->database->getUser() : $user;
$password = empty($password) ? $this->database->getPassword() : $password;
$host = empty($host) ? $this->database->getHost() : $host;
$port = empty($port) ? $this->database->getPort() : $port;
$charset = empty($charset) ? $this->database->getCharset() : $charset;
// Try to establish the server database handle
if (!$this->dbh = \mysqli_connect($host, $user, $password, $this->database->getName(), (int) $port)) {
$this->register_error(\FAILED_CONNECTION . ' in ' . __FILE__ . ' on line ' . __LINE__);
} else {
\mysqli_set_charset($this->dbh, $charset);
$this->_connected = true;
}
return $this->_connected;
} // connect
/**
* Try to select the default database for mySQL
*
* @param string $name The name of the database
* @param string $charset Encoding of the database
* @return boolean
*/
public function dbSelect($name = '', $charset = '')
{
$name = empty($name) ? $this->database->getName() : $name;
try {
// Try to connect to the database
if (($this->dbh === null) || ($this->_connected === false) || !\mysqli_select_db($this->dbh, $name)) {
throw new Exception("Error Processing Request", 1);
}
$this->database->setName($name);
if ($charset == '') {
$charset = $this->database->getCharset();
}
if ($charset != '') {
$encoding = \strtolower(\str_replace('-', '', $charset));
$charsetArray = array();
$recordSet = \mysqli_query($this->dbh, 'SHOW CHARACTER SET');
while ($row = \mysqli_fetch_array($recordSet, \MYSQLI_ASSOC)) {
$charsetArray[] = $row['Charset'];
}
if (\in_array($charset, $charsetArray)) {
\mysqli_query($this->dbh, 'SET NAMES \'' . $encoding . '\'');
}
}
return true;
} catch (\Throwable $e) {
$str = \FAILED_CONNECTION;
// Must have an active database connection
if ($this->dbh && $this->_connected) {
// Try to get error supplied by mysql if not use our own
if (!$str = \mysqli_error($this->dbh)) {
$str = 'Unexpected error while trying to select database';
}
}
$this->register_error($str . ' in ' . __FILE__ . ' on line ' . __LINE__);
return false;
}
} // select
/**
* Format a mySQL string correctly for safe mySQL insert
* (no matter if magic quotes are on or not)
*
* @param string $str
* @return string
*/
public function escape(string $str)
{
return \mysqli_real_escape_string($this->dbh, \stripslashes($str));
} // escape
/**
* Return mySQLi specific system date syntax
* i.e. Oracle: SYSDATE Mysql: NOW()
*
* @return string
*/
public function sysDate()
{
return 'NOW()';
}
/**
* Helper fetches rows from a prepared result set
* @param \mysqli_stmt $stmt
* @param string $query
* @return bool|\mysqli_result
*/
private function fetch_prepared_result(&$stmt, $query)
{
if ($stmt instanceof \mysqli_stmt) {
$stmt->store_result();
$variables = array();
$is_insert = false;
$col_info = array();
if (\preg_match("/^(insert|delete|update|replace)\s+/i", $query)) {
$this->_affectedRows = \mysqli_stmt_affected_rows($stmt);
// Take note of the insert id
if (\preg_match("/^(insert|replace)\s+/i", $query)) {
$this->insertId = $stmt->insert_id;
}
} else {
$this->_affectedRows = $stmt->num_rows;
$meta = $stmt->result_metadata();
$x = 0;
// Take note of column info
while ($field = $meta->fetch_field()) {
$col_info[$field->name] = "";
$variables[$field->name] = &$col_info[$field->name];
$this->colInfo[$x] = $field;
$x++;
}
// Binds variables to a prepared statement for result storage
\call_user_func_array([$stmt, 'bind_result'], \array_values($variables));
$i = 0;
// Store Query Results
while ($stmt->fetch()) {
// Store results as an objects within main array
$resultObject = new \stdClass();
foreach ($variables as $key => $value) {
$resultObject->$key = $value;
}
$this->lastResult[$i] = $resultObject;
$i++;
}
}
// If there is an error then take note of it..
if ($str = $stmt->error) {
$is_insert = true;
$this->register_error($str);
// If debug ALL queries
$this->trace || $this->debugAll ? $this->debug() : null;
return false;
}
// Return number of rows affected
$return_val = $this->_affectedRows;
// disk caching of queries
$this->store_cache($query, $is_insert);
// If debug ALL queries
$this->trace || $this->debugAll ? $this->debug() : null;
return $return_val;
}
return false;
}
/**
* Creates a prepared query, binds the given parameters and returns the result of the executed
* {@link mysqli_stmt}.
* @param string $query
* @param array $param
* @return bool|\mysqli_result
*/
public function query_prepared(string $query, array $param = null)
{
$stmt = $this->dbh->prepare($query);
if (!$stmt instanceof \mysqli_stmt) {
if ($this->isTransactional)
throw new \Exception($this->lastError);
return false;
}
$params = [];
$types = \array_reduce(
$param,
function ($string, &$arg) use (&$params) {
$params[] = &$arg;
if (\is_float($arg))
$string .= 'd';
elseif (\is_integer($arg))
$string .= 'i';
elseif (\is_string($arg))
$string .= 's';
else
$string .= 'b';
return $string;
},
''
);
\array_unshift($params, $types);
\call_user_func_array([$stmt, 'bind_param'], $params);
$result = ($stmt->execute()) ? $this->fetch_prepared_result($stmt, $query) : false;
// free and closes a prepared statement
$stmt->free_result();
$stmt->close();
return $result;
}
/**
* Perform post processing on SQL query call
*
* @param string $query
* @param mixed $result
* @return bool|void
*/
private function processQueryResult(string $query, $result = null)
{
$this->shortcutUsed = false;
if (!empty($result))
$this->result = $result;
// If there is an error then take note of it..
if ($str = \mysqli_error($this->dbh)) {
$this->register_error($str);
// If debug ALL queries
$this->trace || $this->debugAll ? $this->debug() : null;
return false;
}
// Query was an insert, delete, update, replace
$this->is_insert = false;
if (\preg_match("/^(insert|delete|update|replace)\s+/i", $query)) {
$this->is_insert = true;
$this->_affectedRows = \mysqli_affected_rows($this->dbh);
// Take note of the insert_id
if (\preg_match("/^(insert|replace)\s+/i", $query)) {
$this->insertId = \mysqli_insert_id($this->dbh);
}
// Return number of rows affected
$this->return_val = $this->_affectedRows;
} else {
// Query was a select
if (!\is_numeric($this->result) && !\is_bool($this->result)) {
// Take note of column info
$i = 0;
while ($i < \mysqli_num_fields($this->result)) {
$this->colInfo[$i] = \mysqli_fetch_field($this->result);
$i++;
}
// Store Query Results
$num_rows = 0;
while ($row = \mysqli_fetch_object($this->result)) {
// Store results as an objects within main array
$this->lastResult[$num_rows] = $row;
$num_rows++;
}
\mysqli_free_result($this->result);
// Log number of rows the query returned
$this->numRows = $num_rows;
// Return number of rows selected
$this->return_val = $this->numRows;
}
}
}
/**
* Perform mySQL query and try to determine result value
*
* @param string $query
* @param bool $use_prepare
* @return bool|mixed
*/
public function query(string $query, bool $use_prepare = false)
{
$param = [];
if ($use_prepare)
$param = $this->prepareValues();
// check for ezQuery placeholder tag and replace tags with proper prepare tag
$query = \str_replace(\_TAG, '?', $query);
// Initialize return
$this->return_val = 0;
// Flush cached values..
$this->flush();
// For reg expressions
$query = \trim($query);
// Log how the function was called
$this->log_query("\$db->query(\"$query\")");
// Keep track of the last query for debug..
$this->lastQuery = $query;
// Count how many queries there have been
$this->numQueries++;
// Use core file cache function
if ($cache = $this->get_cache($query)) {
return $cache;
}
// If there is no existing database connection then try to connect
if (!isset($this->dbh) || !$this->dbh) {
$this->connect($this->database->getUser(), $this->database->getPassword(), $this->database->getHost());
$this->dbSelect($this->database->getName());
}
// Perform the query via std mysql_query function..
if (!empty($param) && \is_array($param) && ($this->isPrepareOn())) {
$this->shortcutUsed = true;
return $this->query_prepared($query, $param);
}
$this->result = \mysqli_query($this->dbh, $query);
if ($this->processQueryResult($query) === false) {
if ($this->isTransactional)
throw new \Exception($this->lastError);
return false;
}
// disk caching of queries
$this->store_cache($query, $this->is_insert);
// If debug ALL queries
$this->trace || $this->debugAll ? $this->debug() : null;
return $this->return_val;
} // query
/**
* Close the database connection
*/
public function disconnect()
{
if ($this->dbh) {
\mysqli_close($this->dbh);
$this->_connected = false;
}
$this->_connected = false;
}
/**
* Reset database handle
*/
public function reset()
{
$this->dbh = null;
}
/**
* Get connection handle
*/
public function handle()
{
return $this->dbh;
}
/**
* Returns the current database server host
*
* @return string
*/
public function getHost()
{
return $this->database->getHost();
}
/**
* Returns the current database server port
*
* @return string
*/
public function getPort()
{
return $this->database->getPort();
}
/**
* Returns the current connection charset
*
* @return string
*/
public function getCharset()
{
return $this->database->getCharset();
}
/**
* Returns the last inserted Id - auto generated
*
* @return int
*/
public function getInsertId()
{
return \mysqli_insert_id($this->dbh);
} // getInsertId
/**
* Begin Mysql Transaction
*/
public function beginTransaction()
{
/* turn autocommit off */
$this->dbh->autocommit(false);
$this->dbh->begin_transaction(\MYSQLI_TRANS_START_READ_WRITE);
$this->isTransactional = true;
}
public function commit()
{
$this->dbh->commit();
$this->dbh->autocommit(true);
$this->isTransactional = false;
}
public function rollback()
{
$this->dbh->rollBack();
$this->dbh->autocommit(true);
$this->isTransactional = false;
}
} // ez_mysqli