Skip to content

Instantly share code, notes, and snippets.

@muthu32

muthu32/mysqli.php

Last active May 1, 2019 07:34
Show Gist options
  • Save muthu32/4cf93619643432578b0d278ba7da6c23 to your computer and use it in GitHub Desktop.
Save muthu32/4cf93619643432578b0d278ba7da6c23 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
mysqli.php
<?php
namespace db;
class mysqli
{
private $conn;
private $temp;
private $data;
protected static $_instance;
function __construct(
$database = "wo",
$server = "root",
$username = "localhost",
$password = ""
) {
$this->conn = mysqli_connect($server, $username, $password, $database);
if (!$this->conn) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
exit();
}
mysqli_set_charset($this->conn, 'utf8');
}
public static function getInstance()
{
if (self::$_instance == null)
{
self::$_instance = new mysqli();
}
return self::$_instance;
}
function __destruct()
{
if ($this->conn && mysqli_connect_errno($this->conn)) {
mysqli_close($this->conn);
$this->conn = null;
}
}
public function GetAffected()
{
return (mysqli_affected_rows($this->conn));
}
public function escape($str)
{
return $this->conn->real_escape_string($str);
}
public function Free(&$stmt = null)
{
if ($stmt) {
mysqli_free_result($stmt);
unset($stmt);
return;
} else if ($this->temp) {
$this->data = null;
mysqli_free_result($this->temp);
$this->temp = null;
}
}
public function Query($query, $array = false)
{
// Execute query
$stmt = $this->conn->query($query);
// Failed?
if ($stmt !== false) {
if ($this->temp)
self::Free();
$this->temp = $stmt;
if ($array) {
return $this->NextRow();
}
return $this->temp;
}
throw new Exception(sprintf('Unprepared Query Failed, ERRNO: %u (%s)', $this->conn->errno, $this->conn->error), $this->conn->errno);
}
public function NextRow($res = null)
{
if ($this->temp == null && $res == null)
return 0;
return $res == null ? ($this->temp ? mysqli_fetch_assoc($this->temp) : null) : ($res ? mysqli_fetch_assoc($res) : null);
}
public function FetchRow($res = null)
{
if ($this->temp == null && $res == null)
return 0;
return $res == null ? ($this->temp ? mysqli_fetch_row($this->temp) : null) : ($res ? mysqli_fetch_row($res) : null);
}
public function GetData($identifier)
{
if ($this->data == null)
$this->data = $this->NextRow();
return isset($this->data["$identifier"]) ? $this->data["$identifier"] : NULL;
}
public function GetValue($identifier = 0)
{
if ($this->data == null)
$this->data = $this->FetchRow();
return isset($this->data["$identifier"]) ? $this->data["$identifier"] : NULL;
}
public function getInsertId()
{
return $this->conn->insert_id;
}
public function arrayToUpdate($table, $data, $where = NULL, $limit = 1) {
if (is_array(reset($data))) {
$cols = array();
foreach (array_keys($data[0]) as $c) {
$cols[] = "`{$c}` = VALUES(`{$c}`)";
}
return $this->arrayToInsert($table, $data, TRUE, implode(', ', $cols));
}
$fields = array();
foreach ($data as $key => $val) {
if (in_array(strtolower($val), $this->reserved)) {
$fields[] = "`{$key}` = " . strtoupper($val);
} elseif (preg_match('/^' . preg_quote($this->statementStart) . '/i', $val)) {
$fields[] = "`{$key}` = " . preg_replace('/^' . preg_quote($this->statementStart) . '/i', NULL, $val);
} else {
$fields[] = "`{$key}` = '{$this->escape($val)}'";
}
}
if($where==null)
exit("Please put where for arrayupdate query");
$query = "UPDATE `{$table}` SET " . implode(', ', $fields) ." WHERE {$where} LIMIT {$limit};";
exit($query);
return (!empty($fields)) ? $this->Query($query) ? $this->affected : TRUE : FALSE;
}
public function arrayToInsert($table, $data, $ignore = FALSE, $duplicateupdate = NULL) {
$multirow = is_array(reset($data));
if ($multirow) {
$c = implode('`, `', array_keys($data[0]));
$dat = array();
foreach ($data as &$val) {
foreach ($val as &$v) {
if (in_array(strtolower($v), $this->reserved)) {
$v = strtoupper($v);
} elseif (preg_match('/^' . preg_quote($this->statementStart) . '/i', $v)) {
$v = preg_replace('/^' . preg_quote($this->statementStart) . '/i', NULL, $v);
} else {
$v = "'{$this->escape($v)}'";
}
}
$dat[] = "( " . implode(', ', $val) . " )";
}
$v = implode(', ', $dat);
} else {
$c = implode('`, `', array_keys($data));
foreach ($data as &$val) {
if (in_array(strtolower($val), $this->reserved)) {
$val = strtoupper($val);
} elseif (preg_match('/^' . preg_quote($this->statementStart) . '/i', $val)) {
$val = preg_replace('/^' . preg_quote($this->statementStart) . '/i', NULL, $val);
} else {
$val = "'{$this->escape($val)}'";
}
}
$v = "( " . implode(', ', $data) . " )";
}
$query = "INSERT" . ($ignore ? " IGNORE" : NULL) . " INTO `{$table}` ( `{$c}` ) VALUES {$v}" . ($duplicateupdate ? " ON DUPLICATE KEY UPDATE {$duplicateupdate}" : NULL) . ";";
exit($query);
return (!empty($data)) ? $this->query($query) ? ($multirow ? TRUE : $this->insertID()) : FALSE : FALSE;
}
}
Raw
wrapper.php
<?php
/**
* MysqliDb Class
*
* @category Database Access
* @package MysqliDb
* @author Jeffery Way <[email protected]>
* @author Josh Campbell <[email protected]>
* @author Alexander V. Butenko <[email protected]>
* @copyright Copyright (c) 2010-2017
* @license http://opensource.org/licenses/gpl-3.0.html GNU Public License
* @link http://github.com/joshcam/PHP-MySQLi-Database-Class
* @version 2.9.2
*/
namespace db;
use traits\Exception;
class wrapper extends mysqli
{
public static $prefix = '';
protected $_mysqli = array();
protected $_query;
protected $_lastQuery;
protected $_queryOptions = array();
protected $_join = array();
protected $_where = array();
protected $_joinAnd = array();
protected $_having = array();
protected $_orderBy = array();
protected $_groupBy = array();
protected $_bindParams = array(''); // Create the empty 0 index
public $count = 0;
public $totalCount = 0;
protected $_stmtError;
protected $_stmtErrno;
protected $isSubQuery = false;
protected $_lastInsertId = null;
protected $_updateColumns = null;
public $returnType = 'array';
protected $_nestJoin = false;
private $_tableName = '';
protected $_forUpdate = false;
protected $_lockInShareMode = false;
protected $_mapKey = null;
protected $traceStartQ;
protected $traceEnabled;
protected $traceStripPrefix;
public $trace = array();
public $pageLimit = 20;
public $totalPages = 0;
private function __clone() {
}
private function __wakeup() {
// ... void
}
public function insert($tableName, $insertData)
{
return $this->_buildInsert($tableName, $insertData, 'INSERT');
}
private function _buildInsert($tableName, $insertData, $operation)
{
if ($this->isSubQuery) {
return;
}
$this->_query = $operation . " " . implode(' ', $this->_queryOptions) . " INTO " . self::$prefix . $tableName;
$stmt = $this->_buildQuery(null, $insertData);
$status = $stmt->execute();
$this->_stmtError = $stmt->error;
$this->_stmtErrno = $stmt->errno;
$haveOnDuplicate = !empty ($this->_updateColumns);
$this->reset();
$this->count = $stmt->affected_rows;
if ($stmt->affected_rows < 1) {
// in case of onDuplicate() usage, if no rows were inserted
if ($status && $haveOnDuplicate) {
return true;
}
return false;
}
if ($stmt->insert_id > 0) {
return $stmt->insert_id;
}
return true;
}
protected function _buildQuery($numRows = null, $tableData = null)
{
$this->buildQUery($numRows = null, $tableData = null);
if ($this->_forUpdate) {
$this->_query .= ' FOR UPDATE';
}
if ($this->_lockInShareMode) {
$this->_query .= ' LOCK IN SHARE MODE';
}
//echo "<br />",$this->_lastQuery;
if ($this->isSubQuery) {
return;
}
// Prepare query
$stmt = $this->_prepareQuery();
// Bind parameters to statement if any
if (count($this->_bindParams) > 1) {
call_user_func_array(array($stmt, 'bind_param'), $this->refValues($this->_bindParams));
}
return $stmt;
}
public function buildQUery($numRows = null, $tableData = null)
{
$this->_buildJoin();
if($tableData!==null)
$this->_buildInsertQuery($tableData);
$this->_buildCondition('WHERE', $this->_where);
$this->_buildGroupBy();
$this->_buildCondition('HAVING', $this->_having);
$this->_buildOrderBy();
if($numRows!==null)
$this->_buildLimit($numRows);
if($tableData!==null)
$this->_buildOnDuplicate($tableData);
$this->_lastQuery = $this->replacePlaceHolders($this->_query, $this->_bindParams);
return $this->_lastQuery;
}
public function buildWhere()
{
$this->_buildCondition('WHERE', $this->_where);
$this->_buildOrderBy();
$this->_lastQuery = $this->replacePlaceHolders($this->_query, $this->_bindParams);
$this->resetWhere();
return $this->_lastQuery;
}
public function resetWhere()
{
$this->_query=null;
$this->_where=$this->_orderBy=array();
}
protected function reset()
{
if ($this->traceEnabled) {
$this->trace[] = array($this->_lastQuery, (microtime(true) - $this->traceStartQ), $this->_traceGetCaller());
}
$this->_where = array();
$this->_having = array();
$this->_join = array();
$this->_joinAnd = array();
$this->_orderBy = array();
$this->_groupBy = array();
$this->_bindParams = array(''); // Create the empty 0 index
$this->_query = null;
$this->_queryOptions = array();
$this->returnType = 'array';
$this->_nestJoin = false;
$this->_forUpdate = false;
$this->_lockInShareMode = false;
$this->_tableName = '';
$this->_lastInsertId = null;
$this->_updateColumns = null;
$this->_mapKey = null;
return $this;
}
public function jsonBuilder()
{
$this->returnType = 'json';
return $this;
}
public function arrayBuilder()
{
$this->returnType = 'array';
return $this;
}
public function objectBuilder()
{
$this->returnType = 'object';
return $this;
}
public function setPrefix($prefix = '')
{
self::$prefix = $prefix;
return $this;
}
public function SafeQuery($query, $bindParams = null)
{
$params = array(''); // Create the empty 0 index
$this->_query = $query;
$stmt = $this->_prepareQuery();
if (is_array($bindParams) === true) {
foreach ($bindParams as $prop => $val) {
$params[0] .= $this->_determineType($val);
array_push($params, $bindParams[$prop]);
}
call_user_func_array(array($stmt, 'bind_param'), $this->refValues($params));
}
$stmt->execute();
$this->count = $stmt->affected_rows;
$this->_stmtError = $stmt->error;
$this->_stmtErrno = $stmt->errno;
$this->_lastQuery = $this->replacePlaceHolders($this->_query, $params);
$res = $this->_dynamicBindResults($stmt);
$this->reset();
return $res;
}
public function SafeQueryOne($query, $bindParams = null)
{
$res = $this->SafeQuery($query, $bindParams);
if (is_array($res) && isset($res[0])) {
return $res[0];
}
return null;
}
public function SafeQueryValue($query, $bindParams = null)
{
$res = $this->SafeQuery($query, $bindParams);
if (!$res) {
return null;
}
$limit = preg_match('/limit\s+1;?$/i', $query);
$key = key($res[0]);
if (isset($res[0][$key]) && $limit == true) {
return $res[0][$key];
}
$newRes = Array();
for ($i = 0; $i < $this->count; $i++) {
$newRes[] = $res[$i][$key];
}
return $newRes;
}
public function setQueryOption($options)
{
$allowedOptions = Array('ALL', 'DISTINCT', 'DISTINCTROW', 'HIGH_PRIORITY', 'STRAIGHT_JOIN', 'SQL_SMALL_RESULT',
'SQL_BIG_RESULT', 'SQL_BUFFER_RESULT', 'SQL_CACHE', 'SQL_NO_CACHE', 'SQL_CALC_FOUND_ROWS',
'LOW_PRIORITY', 'IGNORE', 'QUICK', 'MYSQLI_NESTJOIN', 'FOR UPDATE', 'LOCK IN SHARE MODE');
if (!is_array($options)) {
$options = Array($options);
}
foreach ($options as $option) {
$option = strtoupper($option);
if (!in_array($option, $allowedOptions)) {
throw new Exception('Wrong query option: ' . $option);
}
if ($option == 'MYSQLI_NESTJOIN') {
$this->_nestJoin = true;
} elseif ($option == 'FOR UPDATE') {
$this->_forUpdate = true;
} elseif ($option == 'LOCK IN SHARE MODE') {
$this->_lockInShareMode = true;
} else {
$this->_queryOptions[] = $option;
}
}
return $this;
}
public function withTotalCount()
{
$this->setQueryOption('SQL_CALC_FOUND_ROWS');
return $this;
}
public function get($tableName, $numRows = 1, $columns = '*')
{
if (empty($columns)) {
$columns = '*';
}
if($numRows<1)
{
throw new Exception('Please select with limit');
}
$column = is_array($columns) ? implode(', ', $columns) : $columns;
if (strpos($tableName, '.') === false) {
$this->_tableName = self::$prefix . $tableName;
} else {
$this->_tableName = $tableName;
}
$this->_query = 'SELECT ' . implode(' ', $this->_queryOptions) . ' ' .
$column . " FROM " . $this->_tableName;
$stmt = $this->_buildQuery($numRows);
if ($this->isSubQuery) {
return $this;
}
$stmt->execute();
$this->_stmtError = $stmt->error;
$this->_stmtErrno = $stmt->errno;
$res = $this->_dynamicBindResults($stmt);
$this->reset();
return $res;
}
public function getOne($tableName, $columns = '*')
{
$res = $this->get($tableName, 1, $columns);
if ($res instanceof MysqliDb) {
return $res;
} elseif (is_array($res) && isset($res[0])) {
return $res[0];
} elseif ($res) {
return $res;
}
return null;
}
public function getVal($tableName, $column, $limit = 1)
{
$limit = intval($limit);
$res = $this->ArrayBuilder()->get($tableName, $limit, "{$column} AS retval");
if (!$res) {
return null;
}
if ($limit == 1) {
if (isset($res[0]["retval"])) {
return $res[0]["retval"];
}
return null;
}
$newRes = Array();
for ($i = 0; $i < $this->count; $i++) {
$newRes[] = $res[$i]['retval'];
}
return $newRes;
}
public function insertMulti($tableName, array $multiInsertData, array $dataKeys = null)
{
$ids = array();
foreach ($multiInsertData as $insertData) {
if($dataKeys !== null) {
// apply column-names if given, else assume they're already given in the data
$insertData = array_combine($dataKeys, $insertData);
}
$id = $this->insert($tableName, $insertData);
if(!$id) {
return false;
}
$ids[] = $id;
}
return $ids;
}
public function replace($tableName, $insertData)
{
return $this->_buildInsert($tableName, $insertData, 'REPLACE');
}
public function has($tableName)
{
$this->getOne($tableName, '1');
return $this->count >= 1;
}
public function update($tableName, $tableData, $numRows = null)
{
if ($this->isSubQuery) {
return;
}
$this->_query = "UPDATE " . self::$prefix . $tableName;
$stmt = $this->_buildQuery($numRows, $tableData);
if(count($this->_where)<1)
throw new Exception('PLEASE PUT WHERE CONDITION FOR UPDATE');
$status = $stmt->execute();
$this->reset();
$this->_stmtError = $stmt->error;
$this->_stmtErrno = $stmt->errno;
$this->count = $stmt->affected_rows;
return $status;
}
public function where($whereProp, $whereValue = NULL, $operator = '=', $cond = 'AND')
{
/*
// forkaround for an old operation api
if (is_array($whereValue) && ($key = key($whereValue)) != "0") {
$operator = $key;
$whereValue = $whereValue[$key];
}
*/
if (count($this->_where) == 0) {
$cond = '';
}
if($whereValue!==NULL && !empty($whereProp) && !empty($operator))
$this->_where[] = array($cond, $whereProp, $operator, $whereValue);
return $this;
}
public function onDuplicate($updateColumns, $lastInsertId = null)
{
$this->_lastInsertId = $lastInsertId;
$this->_updateColumns = $updateColumns;
return $this;
}
public function orWhere($whereProp, $whereValue = 'DBNULL', $operator = '=')
{
return $this->where($whereProp, $whereValue, $operator, 'OR');
}
public function having($havingProp, $havingValue = 'DBNULL', $operator = '=', $cond = 'AND')
{
if (count($this->_having) == 0) {
$cond = '';
}
$this->_having[] = array($cond, $havingProp, $operator, $havingValue);
return $this;
}
public function orHaving($havingProp, $havingValue = null, $operator = null)
{
return $this->having($havingProp, $havingValue, $operator, 'OR');
}
public function join($joinTable, $joinCondition, $joinType = 'INNER')
{
$allowedTypes = array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER', 'NATURAL');
$joinType = strtoupper(trim($joinType));
if ($joinType && !in_array($joinType, $allowedTypes)) {
throw new Exception('Wrong JOIN type: ' . $joinType);
}
if (!is_object($joinTable)) {
$joinTable = self::$prefix . $joinTable;
}
$this->_join[] = Array($joinType, $joinTable, $joinCondition);
return $this;
}
public function orderBy($orderByField, $orderbyDirection = "DESC", $customFieldsOrRegExp = null)
{
$allowedDirection = Array("ASC", "DESC");
$orderbyDirection = strtoupper(trim($orderbyDirection));
$orderByField = preg_replace("/[^ -a-z0-9\.\(\),_`\*\'\"]+/i", '', $orderByField);
// Add table prefix to orderByField if needed.
//FIXME: We are adding prefix only if table is enclosed into `` to distinguish aliases
// from table names
$orderByField = preg_replace('/(\`)([`a-zA-Z0-9_]*\.)/', '\1' . self::$prefix . '\2', $orderByField);
if (empty($orderbyDirection) || !in_array($orderbyDirection, $allowedDirection)) {
throw new Exception('Wrong order direction: ' . $orderbyDirection);
}
if (is_array($customFieldsOrRegExp)) {
foreach ($customFieldsOrRegExp as $key => $value) {
$customFieldsOrRegExp[$key] = preg_replace("/[^\x80-\xff-a-z0-9\.\(\),_` ]+/i", '', $value);
}
$orderByField = 'FIELD (' . $orderByField . ', "' . implode('","', $customFieldsOrRegExp) . '")';
}elseif(is_string($customFieldsOrRegExp)){
$orderByField = $orderByField . " REGEXP '" . $customFieldsOrRegExp . "'";
}elseif($customFieldsOrRegExp !== null){
throw new Exception('Wrong custom field or Regular Expression: ' . $customFieldsOrRegExp);
}
$this->_orderBy[$orderByField] = $orderbyDirection;
return $this;
}
public function groupBy($groupByField)
{
$groupByField = preg_replace("/[^-a-z0-9\.\(\),_\* <>=!]+/i", '', $groupByField);
$this->_groupBy[] = $groupByField;
return $this;
}
protected function _determineType($item)
{
switch (gettype($item)) {
case 'NULL':
case 'string':
return 's';
break;
case 'boolean':
case 'integer':
return 'i';
break;
case 'blob':
return 'b';
break;
case 'double':
return 'd';
break;
}
return '';
}
protected function _bindParam($value)
{
$this->_bindParams[0] .= $this->_determineType($value);
array_push($this->_bindParams, $value);
}
protected function _bindParams($values)
{
foreach ($values as $value) {
$this->_bindParam($value);
}
}
protected function _buildPair($operator, $value)
{
if (!is_object($value)) {
$this->_bindParam($value);
return ' ' . $operator . ' ? ';
}
$subQuery = $value->getSubQuery();
$this->_bindParams($subQuery['params']);
return " " . $operator . " (" . $subQuery['query'] . ") " . $subQuery['alias'];
}
protected function _dynamicBindResults($stmt)
{
$parameters = array();
$results = array();
/**
* @see http://php.net/manual/en/mysqli-result.fetch-fields.php
*/
$mysqlLongType = 252;
$shouldStoreResult = false;
$meta = $stmt->result_metadata();
// if $meta is false yet sqlstate is true, there's no sql error but the query is
// most likely an update/insert/delete which doesn't produce any results
if (!$meta && $stmt->sqlstate)
return array();
$row = array();
while ($field = $meta->fetch_field()) {
if ($field->type == $mysqlLongType) {
$shouldStoreResult = true;
}
if ($this->_nestJoin && $field->table != $this->_tableName) {
$field->table = substr($field->table, strlen(self::$prefix));
$row[$field->table][$field->name] = null;
$parameters[] = & $row[$field->table][$field->name];
} else {
$row[$field->name] = null;
$parameters[] = & $row[$field->name];
}
}
// avoid out of memory bug in php 5.2 and 5.3. Mysqli allocates lot of memory for long*
// and blob* types. So to avoid out of memory issues store_result is used
// https://github.com/joshcam/PHP-MySQLi-Database-Class/pull/119
if ($shouldStoreResult) {
$stmt->store_result();
}
call_user_func_array(array($stmt, 'bind_result'), $parameters);
$this->totalCount = 0;
$this->count = 0;
while ($stmt->fetch()) {
if ($this->returnType == 'object') {
$result = new \stdClass ();
foreach ($row as $key => $val) {
if (is_array($val)) {
$result->$key = new \stdClass ();
foreach ($val as $k => $v) {
$result->$key->$k = $v;
}
} else {
$result->$key = $val;
}
}
} else {
$result = array();
foreach ($row as $key => $val) {
if (is_array($val)) {
foreach ($val as $k => $v) {
$result[$key][$k] = $v;
}
} else {
$result[$key] = $val;
}
}
}
$this->count++;
if ($this->_mapKey) {
$results[$row[$this->_mapKey]] = count($row) > 2 ? $result : end($result);
} else {
array_push($results, $result);
}
}
if ($shouldStoreResult) {
$stmt->free_result();
}
$stmt->close();
// stored procedures sometimes can return more then 1 resultset
if ($this->conn->more_results()) {
$this->conn->next_result();
}
if (in_array('SQL_CALC_FOUND_ROWS', $this->_queryOptions)) {
$stmt = $this->conn->query('SELECT FOUND_ROWS()');
$totalCount = $stmt->fetch_row();
$this->totalCount = $totalCount[0];
}
if ($this->returnType == 'json') {
return json_encode($results);
}
return $results;
}
public function _buildDataPairs($tableData, $tableColumns, $isInsert)
{
foreach ($tableColumns as $column) {
$value = $tableData[$column];
if (!$isInsert) {
if(strpos($column,'.')===false) {
$this->_query .= "`" . $column . "` = ";
} else {
$this->_query .= str_replace('.','.`',$column) . "` = ";
}
}
// Subquery value
if ($value instanceof MysqliDb) {
$this->_query .= $this->_buildPair("", $value) . ", ";
continue;
}
// Simple value
if (!is_array($value)) {
$this->_bindParam($value);
$this->_query .= '?, ';
continue;
}
// Function value
$key = key($value);
$val = $value[$key];
switch ($key) {
case '[I]':
$this->_query .= $column . $val . ", ";
break;
case '[F]':
$this->_query .= $val[0] . ", ";
if (!empty($val[1])) {
$this->_bindParams($val[1]);
}
break;
case '[N]':
if ($val == null) {
$this->_query .= "!" . $column . ", ";
} else {
$this->_query .= "!" . $val . ", ";
}
break;
default:
throw new Exception("Wrong operation");
}
}
$this->_query = rtrim($this->_query, ', ');
}
protected function _buildOnDuplicate($tableData)
{
if (is_array($this->_updateColumns) && !empty($this->_updateColumns)) {
$this->_query .= " ON DUPLICATE KEY UPDATE ";
if ($this->_lastInsertId) {
$this->_query .= $this->_lastInsertId . "=LAST_INSERT_ID (" . $this->_lastInsertId . "), ";
}
foreach ($this->_updateColumns as $key => $val) {
// skip all params without a value
if (is_numeric($key)) {
$this->_updateColumns[$val] = '';
unset($this->_updateColumns[$key]);
} else {
$tableData[$key] = $val;
}
}
$this->_buildDataPairs($tableData, array_keys($this->_updateColumns), false);
}
}
protected function _buildInsertQuery($tableData)
{
if (!is_array($tableData)) {
return;
}
$isInsert = preg_match('/^[INSERT|REPLACE]/', $this->_query);
$dataColumns = array_keys($tableData);
if ($isInsert) {
if (isset ($dataColumns[0]))
$this->_query .= ' (`' . implode($dataColumns, '`, `') . '`) ';
$this->_query .= ' VALUES (';
} else {
$this->_query .= " SET ";
}
$this->_buildDataPairs($tableData, $dataColumns, $isInsert);
if ($isInsert) {
$this->_query .= ')';
}
}
protected function _buildCondition($operator, &$conditions)
{
if (empty($conditions)) {
return;
}
//Prepare the where portion of the query
$this->_query .= ' ' . $operator;
foreach ($conditions as $cond) {
list ($concat, $varName, $operator, $val) = $cond;
$this->_query .= " " . $concat . " " . $varName;
switch (strtolower($operator)) {
case 'not in':
case 'in':
$comparison = ' ' . $operator . ' (';
if (is_object($val)) {
$comparison .= $this->_buildPair("", $val);
} else {
foreach ($val as $v) {
$comparison .= ' ?,';
$this->_bindParam($v);
}
}
$this->_query .= rtrim($comparison, ',') . ' ) ';
break;
case 'not between':
case 'between':
$this->_query .= " $operator ? AND ? ";
$this->_bindParams($val);
break;
case 'not exists':
case 'exists':
$this->_query.= $operator . $this->_buildPair("", $val);
break;
default:
if (is_array($val)) {
$this->_bindParams($val);
} elseif ($val === null) {
$this->_query .= ' ' . $operator . " NULL";
} elseif ($val != 'DBNULL' || $val == '0') {
$this->_query .= $this->_buildPair($operator, $val);
}
}
}
}
protected function _buildGroupBy()
{
if (empty($this->_groupBy)) {
return;
}
$this->_query .= " GROUP BY ";
foreach ($this->_groupBy as $key => $value) {
$this->_query .= $value . ", ";
}
$this->_query = rtrim($this->_query, ', ') . " ";
}
protected function _buildOrderBy()
{
if (empty($this->_orderBy)) {
return;
}
$this->_query .= " ORDER BY ";
foreach ($this->_orderBy as $prop => $value) {
if (strtolower(str_replace(" ", "", $prop)) == 'rand()') {
$this->_query .= "rand(), ";
} else {
$this->_query .= $prop . " " . $value . ", ";
}
}
$this->_query = rtrim($this->_query, ', ') . " ";
}
protected function _buildLimit($numRows)
{
if (!isset($numRows)) {
return;
}
if (is_array($numRows)) {
$this->_query .= ' LIMIT ' . (int) $numRows[0] . ', ' . (int) $numRows[1];
} else {
$this->_query .= ' LIMIT ' . (int) $numRows;
}
}
protected function _prepareQuery()
{
$stmt = $this->conn->prepare($this->_query);
if ($stmt !== false) {
if ($this->traceEnabled)
$this->traceStartQ = microtime(true);
return $stmt;
}
$error = $this->conn->error;
$query = $this->_query;
$errno = $this->conn->errno;
$this->reset();
throw new Exception(sprintf('%s query: %s', $error, $query), $errno);
}
protected function refValues(array &$arr)
{
$refs = array();
foreach ($arr as $key => $value) {
$refs[$key] = & $arr[$key];
}
return $refs;
}
protected function replacePlaceHolders($str, $vals)
{
$i = 1;
$newStr = "";
if (empty($vals)) {
return $str;
}
while ($pos = strpos($str, "?")) {
$val = $vals[$i++];
if (is_object($val)) {
$val = '[object]';
}
if ($val === null) {
$val = 'NULL';
}
$newStr .= substr($str, 0, $pos) . "'" . $val . "'";
$str = substr($str, $pos + 1);
}
$newStr .= $str;
return $newStr;
}
public function getLastQuery()
{
return $this->_lastQuery;
}
public function getLastError()
{
return trim($this->_stmtError . " " . $this->conn->error);
}
public function getLastErrno () {
return $this->_stmtErrno;
}
public function getSubQuery()
{
if (!$this->isSubQuery) {
return null;
}
array_shift($this->_bindParams);
$val = Array('query' => $this->_query,
'params' => $this->_bindParams,
'alias' => null
);
$this->reset();
return $val;
}
public function interval($diff, $func = "NOW()")
{
$types = Array("s" => "second", "m" => "minute", "h" => "hour", "d" => "day", "M" => "month", "Y" => "year");
$incr = '+';
$items = '';
$type = 'd';
if ($diff && preg_match('/([+-]?) ?([0-9]+) ?([a-zA-Z]?)/', $diff, $matches)) {
if (!empty($matches[1])) {
$incr = $matches[1];
}
if (!empty($matches[2])) {
$items = $matches[2];
}
if (!empty($matches[3])) {
$type = $matches[3];
}
if (!in_array($type, array_keys($types))) {
throw new Exception("invalid interval type in '{$diff}'");
}
$func .= " " . $incr . " interval " . $items . " " . $types[$type] . " ";
}
return $func;
}
public function now($diff = null, $func = "NOW()")
{
return array("[F]" => Array($this->interval($diff, $func)));
}
public function inc($num = 1)
{
if (!is_numeric($num)) {
throw new Exception('Argument supplied to inc must be a number');
}
return array("[I]" => "+" . $num);
}
public function dec($num = 1)
{
if (!is_numeric($num)) {
throw new Exception('Argument supplied to dec must be a number');
}
return array("[I]" => "-" . $num);
}
public function not($col = null)
{
return array("[N]" => (string)$col);
}
public function func($expr, $bindParams = null)
{
return array("[F]" => array($expr, $bindParams));
}
public static function subQuery($subQueryAlias = "")
{
return new self(array('host' => $subQueryAlias, 'isSubQuery' => true));
}
public function copy()
{
$copy = unserialize(serialize($this));
$copy->_mysqli = array();
return $copy;
}
public function setTrace($enabled, $stripPrefix = null)
{
$this->traceEnabled = $enabled;
$this->traceStripPrefix = $stripPrefix;
return $this;
}
private function _traceGetCaller()
{
$dd = debug_backtrace();
$caller = next($dd);
while (isset($caller) && $caller["file"] == __FILE__) {
$caller = next($dd);
}
return __CLASS__ . "->" . $caller["function"] . "() >> file \"" .
str_replace($this->traceStripPrefix, '', $caller["file"]) . "\" line #" . $caller["line"] . " ";
}
public function map($idField)
{
$this->_mapKey = $idField;
return $this;
}
public function paginate ($table, $page, $fields = null) {
$offset = $this->pageLimit * ($page - 1);
$res = $this->withTotalCount()->get ($table, Array ($offset, $this->pageLimit), $fields);
$this->totalPages = ceil($this->totalCount / $this->pageLimit);
return $res;
}
public function joinWhere($whereJoin, $whereProp, $whereValue = 'DBNULL', $operator = '=', $cond = 'AND')
{
$this->_joinAnd[self::$prefix . $whereJoin][] = Array ($cond, $whereProp, $operator, $whereValue);
return $this;
}
public function joinOrWhere($whereJoin, $whereProp, $whereValue = 'DBNULL', $operator = '=', $cond = 'AND')
{
return $this->joinWhere($whereJoin, $whereProp, $whereValue, $operator, 'OR');
}
protected function _buildJoin () {
if (empty ($this->_join))
return;
foreach ($this->_join as $data) {
list ($joinType, $joinTable, $joinCondition) = $data;
if (is_object ($joinTable))
$joinStr = $this->_buildPair ("", $joinTable);
else
$joinStr = $joinTable;
$this->_query .= " " . $joinType. " JOIN " . $joinStr .
(false !== stripos($joinCondition, 'using') ? " " : " on ")
. $joinCondition;
// Add join and query
if (!empty($this->_joinAnd) && isset($this->_joinAnd[$joinStr])) {
foreach($this->_joinAnd[$joinStr] as $join_and_cond) {
list ($concat, $varName, $operator, $val) = $join_and_cond;
$this->_query .= " " . $concat ." " . $varName;
$this->conditionToSql($operator, $val);
}
}
}
}
private function conditionToSql($operator, $val) {
switch (strtolower ($operator)) {
case 'not in':
case 'in':
$comparison = ' ' . $operator. ' (';
if (is_object ($val)) {
$comparison .= $this->_buildPair ("", $val);
} else {
foreach ($val as $v) {
$comparison .= ' ?,';
$this->_bindParam ($v);
}
}
$this->_query .= rtrim($comparison, ',').' ) ';
break;
case 'not between':
case 'between':
$this->_query .= " $operator ? AND ? ";
$this->_bindParams ($val);
break;
case 'not exists':
case 'exists':
$this->_query.= $operator . $this->_buildPair ("", $val);
break;
default:
if (is_array ($val))
$this->_bindParams ($val);
else if ($val === null)
$this->_query .= $operator . " NULL";
else if ($val != 'DBNULL' || $val == '0')
$this->_query .= $this->_buildPair ($operator, $val);
}
}
}
// END class
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment