Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

insert Null and not empty #840

Open
TwinMist opened this issue Jun 19, 2019 · 4 comments
Open

insert Null and not empty #840

TwinMist opened this issue Jun 19, 2019 · 4 comments

Comments

@TwinMist
Copy link

Hi
On a insert or update if the variable i am user for the value is empty is it possible to insert NULL instead?

many thanks
@adrianlaraperez88
Copy link

adrianlaraperez88 commented Nov 7, 2019 via email

@enrybisco
Copy link

// Modified by Enrico Bisco
// IF Inserted a value 'null' or NULL is insert a 'NULL' value not an empty string

* @author Josh Campbell * @author Alexander V. Butenko * @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.3 */ class MysqliDb { /** * Static instance of self * * @var MysqliDb */ protected static $_instance; /** * Table prefix * * @var string */ public static $prefix = ''; /** * MySQLi instances * * @var mysqli[] */ protected $_mysqli = array(); /** * The SQL query to be prepared and executed * * @var string */ protected $_query; /** * The previously executed SQL query * * @var string */ protected $_lastQuery; /** * The SQL query options required after SELECT, INSERT, UPDATE or DELETE * * @var array */ protected $_queryOptions = array(); /** * An array that holds where joins * * @var array */ protected $_join = array(); /** * An array that holds where conditions * * @var array */ protected $_where = array(); /** * An array that holds where join ands * * @var array */ protected $_joinAnd = array(); /** * An array that holds having conditions * * @var array */ protected $_having = array(); /** * Dynamic type list for order by condition value * * @var array */ protected $_orderBy = array(); /** * Dynamic type list for group by condition value * * @var array */ protected $_groupBy = array(); /** * Dynamic type list for temporary locking tables. * * @var array */ protected $_tableLocks = array(); /** * Variable which holds the current table lock method. * * @var string */ protected $_tableLockMethod = "READ"; /** * Dynamic array that holds a combination of where condition/table data value types and parameter references * * @var array */ protected $_bindParams = array(''); // Create the empty 0 index /** * Variable which holds an amount of returned rows during get/getOne/select queries * * @var string */ public $count = 0; /** * Variable which holds an amount of returned rows during get/getOne/select queries with withTotalCount() * * @var string */ public $totalCount = 0; /** * Variable which holds last statement error * * @var string */ protected $_stmtError; /** * Variable which holds last statement error code * * @var int */ protected $_stmtErrno; /** * Is Subquery object * * @var bool */ protected $isSubQuery = false; /** * Name of the auto increment column * * @var int */ protected $_lastInsertId = null; /** * Column names for update when using onDuplicate method * * @var array */ protected $_updateColumns = null; /** * Return type: 'array' to return results as array, 'object' as object * 'json' as json string * * @var string */ public $returnType = 'array'; /** * Should join() results be nested by table * * @var bool */ protected $_nestJoin = false; /** * Table name (with prefix, if used) * * @var string */ private $_tableName = ''; /** * FOR UPDATE flag * * @var bool */ protected $_forUpdate = false; /** * LOCK IN SHARE MODE flag * * @var bool */ protected $_lockInShareMode = false; /** * Key field for Map()'ed result array * * @var string */ protected $_mapKey = null; /** * Variables for query execution tracing */ protected $traceStartQ; protected $traceEnabled; protected $traceStripPrefix; public $trace = array(); /** * Per page limit for pagination * * @var int */ public $pageLimit = 20; /** * Variable that holds total pages count of last paginate() query * * @var int */ public $totalPages = 0; /** * @var array connections settings [profile_name=>[same_as_contruct_args]] */ protected $connectionsSettings = array(); /** * @var string the name of a default (main) mysqli connection */ public $defConnectionName = 'default'; public $autoReconnect = true; protected $autoReconnectCount = 0; /** * @var bool Operations in transaction indicator */ protected $_transaction_in_progress = false; /** * @param string $host * @param string $username * @param string $password * @param string $db * @param int $port * @param string $charset * @param string $socket */ public function __construct($host = null, $username = null, $password = null, $db = null, $port = null, $charset = 'utf8', $socket = null) { $isSubQuery = false; // if params were passed as array if (is_array($host)) { foreach ($host as $key => $val) { $$key = $val; } } $this->addConnection('default', array( 'host' => $host, 'username' => $username, 'password' => $password, 'db' => $db, 'port' => $port, 'socket' => $socket, 'charset' => $charset )); if ($isSubQuery) { $this->isSubQuery = true; return; } if (isset($prefix)) { $this->setPrefix($prefix); } self::$_instance = $this; } /** * A method to connect to the database * * @param null|string $connectionName * * @throws Exception * @return void */ public function connect($connectionName = 'default') { if(!isset($this->connectionsSettings[$connectionName])) throw new Exception('Connection profile not set'); $pro = $this->connectionsSettings[$connectionName]; $params = array_values($pro); $charset = array_pop($params); if ($this->isSubQuery) { return; } if (empty($pro['host']) && empty($pro['socket'])) { throw new Exception('MySQL host or socket is not set'); } $mysqlic = new ReflectionClass('mysqli'); $mysqli = $mysqlic->newInstanceArgs($params); if ($mysqli->connect_error) { throw new Exception('Connect Error ' . $mysqli->connect_errno . ': ' . $mysqli->connect_error, $mysqli->connect_errno); } if (!empty($charset)) { $mysqli->set_charset($charset); } $this->_mysqli[$connectionName] = $mysqli; } /** * @throws Exception */ public function disconnectAll() { foreach (array_keys($this->_mysqli) as $k) { $this->disconnect($k); } } /** * Set the connection name to use in the next query * * @param string $name * * @return $this * @throws Exception */ public function connection($name) { if (!isset($this->connectionsSettings[$name])) throw new Exception('Connection ' . $name . ' was not added.'); $this->defConnectionName = $name; return $this; } /** * A method to disconnect from the database * * @params string $connection connection name to disconnect * * @param string $connection * * @return void */ public function disconnect($connection = 'default') { if (!isset($this->_mysqli[$connection])) return; $this->_mysqli[$connection]->close(); unset($this->_mysqli[$connection]); } /** * Create & store at _mysqli new mysqli instance * * @param string $name * @param array $params * * @return $this */ public function addConnection($name, array $params) { $this->connectionsSettings[$name] = array(); foreach (array('host', 'username', 'password', 'db', 'port', 'socket', 'charset') as $k) { $prm = isset($params[$k]) ? $params[$k] : null; if ($k == 'host') { if (is_object($prm)) $this->_mysqli[$name] = $prm; if (!is_string($prm)) $prm = null; } $this->connectionsSettings[$name][$k] = $prm; } return $this; } /** * A method to get mysqli object or create it in case needed * * @return mysqli * @throws Exception */ public function mysqli() { if (!isset($this->_mysqli[$this->defConnectionName])) { $this->connect($this->defConnectionName); } return $this->_mysqli[$this->defConnectionName]; } /** * A method of returning the static instance to allow access to the * instantiated object from within another class. * Inheriting this class would require reloading connection info. * * @uses $db = MySqliDb::getInstance(); * * @return MysqliDb Returns the current instance. */ public static function getInstance() { return self::$_instance; } /** * Reset states after an execution * * @return MysqliDb Returns the current instance. */ 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; if(!$this->_transaction_in_progress ) { $this->defConnectionName = 'default'; } $this->autoReconnectCount = 0; return $this; } /** * Helper function to create dbObject with JSON return type * * @return MysqliDb */ public function jsonBuilder() { $this->returnType = 'json'; return $this; } /** * Helper function to create dbObject with array return type * Added for consistency as that's default output type * * @return MysqliDb */ public function arrayBuilder() { $this->returnType = 'array'; return $this; } /** * Helper function to create dbObject with object return type. * * @return MysqliDb */ public function objectBuilder() { $this->returnType = 'object'; return $this; } /** * Method to set a prefix * * @param string $prefix Contains a table prefix * * @return MysqliDb */ public function setPrefix($prefix = '') { self::$prefix = $prefix; return $this; } /** * Pushes a unprepared statement to the mysqli stack. * WARNING: Use with caution. * This method does not escape strings by default so make sure you'll never use it in production. * * @author Jonas Barascu * * @param [[Type]] $query [[Description]] * * @return bool|mysqli_result * @throws Exception */ private function queryUnprepared($query) { // Execute query $stmt = $this->mysqli()->query($query); // Failed? if ($stmt !== false) return $stmt; if ($this->mysqli()->errno === 2006 && $this->autoReconnect === true && $this->autoReconnectCount === 0) { $this->connect($this->defConnectionName); $this->autoReconnectCount++; return $this->queryUnprepared($query); } throw new Exception(sprintf('Unprepared Query Failed, ERRNO: %u (%s)', $this->mysqli()->errno, $this->mysqli()->error), $this->mysqli()->errno); } /** * Execute raw SQL query. * * @param string $query User-provided query to execute. * @param array $bindParams Variables array to bind to the SQL statement. * * @return array Contains the returned rows from the query. * @throws Exception */ public function rawQuery($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) { //HENRY MOD 01 if ($val === null || $val == 'null' || $val == 'NULL' || is_null($val) ) { $val = 'NULL'; } $params[0] .= $this->_determineType($val); if ($val === null || $val == 'null' || $val == 'NULL' || is_null($val) ) { array_push($params, NULL); }else{ array_push($params, $bindParams[$prop]); } //END: HENRY MOD 01 } 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; } /** * Helper function to execute raw SQL query and return only 1 row of results. * Note that function do not add 'limit 1' to the query by itself * Same idea as getOne() * * @param string $query User-provided query to execute. * @param array $bindParams Variables array to bind to the SQL statement. * * @return array|null Contains the returned row from the query. * @throws Exception */ public function rawQueryOne($query, $bindParams = null) { $res = $this->rawQuery($query, $bindParams); if (is_array($res) && isset($res[0])) { return $res[0]; } return null; } /** * Helper function to execute raw SQL query and return only 1 column of results. * If 'limit 1' will be found, then string will be returned instead of array * Same idea as getValue() * * @param string $query User-provided query to execute. * @param array $bindParams Variables array to bind to the SQL statement. * * @return mixed Contains the returned rows from the query. * @throws Exception */ public function rawQueryValue($query, $bindParams = null) { $res = $this->rawQuery($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; } /** * A method to perform select query * * @param string $query Contains a user-provided select query. * @param int|array $numRows Array to define SQL limit in format Array ($offset, $count) * * @return array Contains the returned rows from the query. * @throws Exception */ public function query($query, $numRows = null) { $this->_query = $query; $stmt = $this->_buildQuery($numRows); $stmt->execute(); $this->_stmtError = $stmt->error; $this->_stmtErrno = $stmt->errno; $res = $this->_dynamicBindResults($stmt); $this->reset(); return $res; } /** * This method allows you to specify multiple (method chaining optional) options for SQL queries. * * @uses $MySqliDb->setQueryOption('name'); * * @param string|array $options The options name of the query.'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' * * @throws Exception * @return MysqliDb */ 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; } /** * Function to enable SQL_CALC_FOUND_ROWS in the get queries * * @return MysqliDb * @throws Exception */ public function withTotalCount() { $this->setQueryOption('SQL_CALC_FOUND_ROWS'); return $this; } /** * A convenient SELECT * function. * * @param string $tableName The name of the database table to work with. * @param int|array $numRows Array to define SQL limit in format Array ($offset, $count) * or only $count * @param string $columns Desired columns * * @return array|MysqliDb Contains the returned rows from the select query. * @throws Exception */ public function get($tableName, $numRows = null, $columns = '*') { if (empty($columns)) { $columns = '*'; } $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; } /** * A convenient SELECT * function to get one record. * * @param string $tableName The name of the database table to work with. * @param string $columns Desired columns * * @return array Contains the returned rows from the select query. * @throws Exception */ 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; } /** * A convenient SELECT COLUMN function to get a single column value from one row * * @param string $tableName The name of the database table to work with. * @param string $column The desired column * @param int $limit Limit of rows to select. Use null for unlimited..1 by default * * @return mixed Contains the value of a returned column / array of values * @throws Exception */ public function getValue($tableName, $column, $limit = 1) { $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; } /** * Insert method to add new row * * @param string $tableName The name of the table. * @param array $insertData Data containing information for inserting into the DB. * * @return bool Boolean indicating whether the insert query was completed successfully. * @throws Exception */ public function insert($tableName, $insertData) { return $this->_buildInsert($tableName, $insertData, 'INSERT'); } /** * Insert method to add several rows at once * * @param string $tableName The name of the table. * @param array $multiInsertData Two-dimensional Data-array containing information for inserting into the DB. * @param array $dataKeys Optional Table Key names, if not set in insertDataSet. * * @return bool|array Boolean indicating the insertion failed (false), else return id-array ([int]) * @throws Exception */ public function insertMulti($tableName, array $multiInsertData, array $dataKeys = null) { // only auto-commit our inserts, if no transaction is currently running $autoCommit = (isset($this->_transaction_in_progress) ? !$this->_transaction_in_progress : true); $ids = array(); if($autoCommit) { $this->startTransaction(); } 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) { if($autoCommit) { $this->rollback(); } return false; } $ids[] = $id; } if($autoCommit) { $this->commit(); } return $ids; } /** * Replace method to add new row * * @param string $tableName The name of the table. * @param array $insertData Data containing information for inserting into the DB. * * @return bool Boolean indicating whether the insert query was completed successfully. * @throws Exception */ public function replace($tableName, $insertData) { return $this->_buildInsert($tableName, $insertData, 'REPLACE'); } /** * A convenient function that returns TRUE if exists at least an element that * satisfy the where condition specified calling the "where" method before this one. * * @param string $tableName The name of the database table to work with. * * @return bool * @throws Exception */ public function has($tableName) { $this->getOne($tableName, '1'); return $this->count >= 1; } /** * Update query. Be sure to first call the "where" method. * * @param string $tableName The name of the database table to work with. * @param array $tableData Array of data to update the desired row. * @param int $numRows Limit on the number of rows that can be updated. * * @return bool * @throws Exception */ public function update($tableName, $tableData, $numRows = null) { if ($this->isSubQuery) { return; } $this->_query = "UPDATE " . self::$prefix . $tableName; $stmt = $this->_buildQuery($numRows, $tableData); $status = $stmt->execute(); $this->reset(); $this->_stmtError = $stmt->error; $this->_stmtErrno = $stmt->errno; $this->count = $stmt->affected_rows; return $status; } /** * Delete query. Call the "where" method first. * * @param string $tableName The name of the database table to work with. * @param int|array $numRows Array to define SQL limit in format Array ($offset, $count) * or only $count * * @return bool Indicates success. 0 or 1. * @throws Exception */ public function delete($tableName, $numRows = null) { if ($this->isSubQuery) { return; } $table = self::$prefix . $tableName; if (count($this->_join)) { $this->_query = "DELETE " . preg_replace('/.* (.*)/', '$1', $table) . " FROM " . $table; } else { $this->_query = "DELETE FROM " . $table; } $stmt = $this->_buildQuery($numRows); $stmt->execute(); $this->_stmtError = $stmt->error; $this->_stmtErrno = $stmt->errno; $this->count = $stmt->affected_rows; $this->reset(); return ($stmt->affected_rows > -1); // -1 indicates that the query returned an error } /** * This method allows you to specify multiple (method chaining optional) AND WHERE statements for SQL queries. * * @uses $MySqliDb->where('id', 7)->where('title', 'MyTitle'); * * @param string $whereProp The name of the database field. * @param mixed $whereValue The value of the database field. * @param string $operator Comparison operator. Default is = * @param string $cond Condition of where statement (OR, AND) * * @return MysqliDb */ public function where($whereProp, $whereValue = 'DBNULL', $operator = '=', $cond = 'AND') { if (count($this->_where) == 0) { $cond = ''; } $this->_where[] = array($cond, $whereProp, $operator, $whereValue); return $this; } /** * This function store update column's name and column name of the * autoincrement column * * @param array $updateColumns Variable with values * @param string $lastInsertId Variable value * * @return MysqliDb */ public function onDuplicate($updateColumns, $lastInsertId = null) { $this->_lastInsertId = $lastInsertId; $this->_updateColumns = $updateColumns; return $this; } /** * This method allows you to specify multiple (method chaining optional) OR WHERE statements for SQL queries. * * @uses $MySqliDb->orWhere('id', 7)->orWhere('title', 'MyTitle'); * * @param string $whereProp The name of the database field. * @param mixed $whereValue The value of the database field. * @param string $operator Comparison operator. Default is = * * @return MysqliDb */ public function orWhere($whereProp, $whereValue = 'DBNULL', $operator = '=') { return $this->where($whereProp, $whereValue, $operator, 'OR'); } /** * This method allows you to specify multiple (method chaining optional) AND HAVING statements for SQL queries. * * @uses $MySqliDb->having('SUM(tags) > 10') * * @param string $havingProp The name of the database field. * @param mixed $havingValue The value of the database field. * @param string $operator Comparison operator. Default is = * * @param string $cond * * @return MysqliDb */ public function having($havingProp, $havingValue = 'DBNULL', $operator = '=', $cond = 'AND') { // forkaround for an old operation api if (is_array($havingValue) && ($key = key($havingValue)) != "0") { $operator = $key; $havingValue = $havingValue[$key]; } if (count($this->_having) == 0) { $cond = ''; } $this->_having[] = array($cond, $havingProp, $operator, $havingValue); return $this; } /** * This method allows you to specify multiple (method chaining optional) OR HAVING statements for SQL queries. * * @uses $MySqliDb->orHaving('SUM(tags) > 10') * * @param string $havingProp The name of the database field. * @param mixed $havingValue The value of the database field. * @param string $operator Comparison operator. Default is = * * @return MysqliDb */ public function orHaving($havingProp, $havingValue = null, $operator = null) { return $this->having($havingProp, $havingValue, $operator, 'OR'); } /** * This method allows you to concatenate joins for the final SQL statement. * * @uses $MySqliDb->join('table1', 'field1 <> field2', 'LEFT') * * @param string $joinTable The name of the table. * @param string $joinCondition the condition. * @param string $joinType 'LEFT', 'INNER' etc. * * @throws Exception * @return MysqliDb */ public function join($joinTable, $joinCondition, $joinType = '') { $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; } /** * This is a basic method which allows you to import raw .CSV data into a table * Please check out http://dev.mysql.com/doc/refman/5.7/en/load-data.html for a valid .csv file. * * @author Jonas Barascu (Noneatme) * * @param string $importTable The database table where the data will be imported into. * @param string $importFile The file to be imported. Please use double backslashes \\ and make sure you * @param string $importSettings An Array defining the import settings as described in the README.md * * @return boolean * @throws Exception */ public function loadData($importTable, $importFile, $importSettings = null) { // We have to check if the file exists if (!file_exists($importFile)) { // Throw an exception throw new Exception("importCSV -> importFile " . $importFile . " does not exists!"); } // Define the default values // We will merge it later $settings = Array("fieldChar" => ';', "lineChar" => PHP_EOL, "linesToIgnore" => 1); // Check the import settings if (gettype($importSettings) == "array") { // Merge the default array with the custom one $settings = array_merge($settings, $importSettings); } // Add the prefix to the import table $table = self::$prefix . $importTable; // Add 1 more slash to every slash so maria will interpret it as a path $importFile = str_replace("\\", "\\\\", $importFile); // Switch between LOAD DATA and LOAD DATA LOCAL $loadDataLocal = isset($settings["loadDataLocal"]) ? 'LOCAL' : ''; // Build SQL Syntax $sqlSyntax = sprintf('LOAD DATA %s INFILE \'%s\' INTO TABLE %s', $loadDataLocal, $importFile, $table); // FIELDS $sqlSyntax .= sprintf(' FIELDS TERMINATED BY \'%s\'', $settings["fieldChar"]); if (isset($settings["fieldEnclosure"])) { $sqlSyntax .= sprintf(' ENCLOSED BY \'%s\'', $settings["fieldEnclosure"]); } // LINES $sqlSyntax .= sprintf(' LINES TERMINATED BY \'%s\'', $settings["lineChar"]); if (isset($settings["lineStarting"])) { $sqlSyntax .= sprintf(' STARTING BY \'%s\'', $settings["lineStarting"]); } // IGNORE LINES $sqlSyntax .= sprintf(' IGNORE %d LINES', $settings["linesToIgnore"]); // Execute the query unprepared because LOAD DATA only works with unprepared statements. $result = $this->queryUnprepared($sqlSyntax); // Are there rows modified? // Let the user know if the import failed / succeeded return (bool) $result; } /** * This method is useful for importing XML files into a specific table. * Check out the LOAD XML syntax for your MySQL server. * * @author Jonas Barascu * * @param string $importTable The table in which the data will be imported to. * @param string $importFile The file which contains the .XML data. * @param string $importSettings An Array defining the import settings as described in the README.md * * @return boolean Returns true if the import succeeded, false if it failed. * @throws Exception */ public function loadXml($importTable, $importFile, $importSettings = null) { // We have to check if the file exists if(!file_exists($importFile)) { // Does not exists throw new Exception("loadXml: Import file does not exists"); return; } // Create default values $settings = Array("linesToIgnore" => 0); // Check the import settings if(gettype($importSettings) == "array") { $settings = array_merge($settings, $importSettings); } // Add the prefix to the import table $table = self::$prefix . $importTable; // Add 1 more slash to every slash so maria will interpret it as a path $importFile = str_replace("\\", "\\\\", $importFile); // Build SQL Syntax $sqlSyntax = sprintf('LOAD XML INFILE \'%s\' INTO TABLE %s', $importFile, $table); // FIELDS if(isset($settings["rowTag"])) { $sqlSyntax .= sprintf(' ROWS IDENTIFIED BY \'%s\'', $settings["rowTag"]); } // IGNORE LINES $sqlSyntax .= sprintf(' IGNORE %d LINES', $settings["linesToIgnore"]); // Exceute the query unprepared because LOAD XML only works with unprepared statements. $result = $this->queryUnprepared($sqlSyntax); // Are there rows modified? // Let the user know if the import failed / succeeded return (bool) $result; } /** * This method allows you to specify multiple (method chaining optional) ORDER BY statements for SQL queries. * * @uses $MySqliDb->orderBy('id', 'desc')->orderBy('name', 'desc', '^[a-z]')->orderBy('name', 'desc'); * * @param string $orderByField The name of the database field. * @param string $orderbyDirection * @param mixed $customFieldsOrRegExp Array with fieldset for ORDER BY FIELD() ordering or string with regular expression for ORDER BY REGEXP ordering * * @return MysqliDb * @throws Exception */ 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; } /** * This method allows you to specify multiple (method chaining optional) GROUP BY statements for SQL queries. * * @uses $MySqliDb->groupBy('name'); * * @param string $groupByField The name of the database field. * * @return MysqliDb */ public function groupBy($groupByField) { $groupByField = preg_replace("/[^-a-z0-9\.\(\),_\* <>=!]+/i", '', $groupByField); $this->_groupBy[] = $groupByField; return $this; } /** * This method sets the current table lock method. * * @author Jonas Barascu * * @param string $method The table lock method. Can be READ or WRITE. * * @throws Exception * @return MysqliDb */ public function setLockMethod($method) { // Switch the uppercase string switch(strtoupper($method)) { // Is it READ or WRITE? case "READ" || "WRITE": // Succeed $this->_tableLockMethod = $method; break; default: // Else throw an exception throw new Exception("Bad lock type: Can be either READ or WRITE"); break; } return $this; } /** * Locks a table for R/W action. * * @author Jonas Barascu * * @param string|array $table The table to be locked. Can be a table or a view. * * @return bool if succeeded; * @throws Exception */ public function lock($table) { // Main Query $this->_query = "LOCK TABLES"; // Is the table an array? if(gettype($table) == "array") { // Loop trough it and attach it to the query foreach($table as $key => $value) { if(gettype($value) == "string") { if($key > 0) { $this->_query .= ","; } $this->_query .= " ".self::$prefix.$value." ".$this->_tableLockMethod; } } } else{ // Build the table prefix $table = self::$prefix . $table; // Build the query $this->_query = "LOCK TABLES ".$table." ".$this->_tableLockMethod; } // Execute the query unprepared because LOCK only works with unprepared statements. $result = $this->queryUnprepared($this->_query); $errno = $this->mysqli()->errno; // Reset the query $this->reset(); // Are there rows modified? if($result) { // Return true // We can't return ourself because if one table gets locked, all other ones get unlocked! return true; } // Something went wrong else { throw new Exception("Locking of table ".$table." failed", $errno); } // Return the success value return false; } /** * Unlocks all tables in a database. * Also commits transactions. * * @author Jonas Barascu * @return MysqliDb * @throws Exception */ public function unlock() { // Build the query $this->_query = "UNLOCK TABLES"; // Execute the query unprepared because UNLOCK and LOCK only works with unprepared statements. $result = $this->queryUnprepared($this->_query); $errno = $this->mysqli()->errno; // Reset the query $this->reset(); // Are there rows modified? if($result) { // return self return $this; } // Something went wrong else { throw new Exception("Unlocking of tables failed", $errno); } // Return self return $this; } /** * This methods returns the ID of the last inserted item * * @return int The last inserted item ID. * @throws Exception */ public function getInsertId() { return $this->mysqli()->insert_id; } /** * Escape harmful characters which might affect a query. * * @param string $str The string to escape. * * @return string The escaped string. * @throws Exception */ public function escape($str) { return $this->mysqli()->real_escape_string($str); } /** * Method to call mysqli->ping() to keep unused connections open on * long-running scripts, or to reconnect timed out connections (if php.ini has * global mysqli.reconnect set to true). Can't do this directly using object * since _mysqli is protected. * * @return bool True if connection is up * @throws Exception */ public function ping() { return $this->mysqli()->ping(); } /** * This method is needed for prepared statements. They require * the data type of the field to be bound with "i" s", etc. * This function takes the input, determines what type it is, * and then updates the param_type. * * @param mixed $item Input to determine the type. * * @return string The joined parameter types. */ protected function _determineType($item) { switch (gettype($item)) { case 'string': return 's'; break; //HENRY MOD 02 case 'NULL': case 'boolean': case 'integer': return 'i'; break; case 'blob': return 'b'; break; case 'double': return 'd'; break; } return ''; } /** * Helper function to add variables into bind parameters array * * @param string Variable value */ protected function _bindParam($value) { $this->_bindParams[0] .= $this->_determineType($value); array_push($this->_bindParams, $value); } /** * Helper function to add variables into bind parameters array in bulk * * @param array $values Variable with values */ protected function _bindParams($values) { foreach ($values as $value) { $this->_bindParam($value); } } /** * Helper function to add variables into bind parameters array and will return * its SQL part of the query according to operator in ' $operator ?' or * ' $operator ($subquery) ' formats * * @param string $operator * @param mixed $value Variable with values * * @return string */ 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']; } /** * Internal function to build and execute INSERT/REPLACE calls * * @param string $tableName The name of the table. * @param array $insertData Data containing information for inserting into the DB. * @param string $operation Type of operation (INSERT, REPLACE) * * @return bool Boolean indicating whether the insert query was completed successfully. * @throws Exception */ 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; } /** * Abstraction method that will compile the WHERE statement, * any passed update data, and the desired rows. * It then builds the SQL query. * * @param int|array $numRows Array to define SQL limit in format Array ($offset, $count) * or only $count * @param array $tableData Should contain an array of data for updating the database. * * @return mysqli_stmt|bool Returns the $stmt object. * @throws Exception */ protected function _buildQuery($numRows = null, $tableData = null) { // $this->_buildJoinOld(); $this->_buildJoin(); $this->_buildInsertQuery($tableData); $this->_buildCondition('WHERE', $this->_where); $this->_buildGroupBy(); $this->_buildCondition('HAVING', $this->_having); $this->_buildOrderBy(); $this->_buildLimit($numRows); $this->_buildOnDuplicate($tableData); if ($this->_forUpdate) { $this->_query .= ' FOR UPDATE'; } if ($this->_lockInShareMode) { $this->_query .= ' LOCK IN SHARE MODE'; } $this->_lastQuery = $this->replacePlaceHolders($this->_query, $this->_bindParams); 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; } /** * This helper method takes care of prepared statements' "bind_result method * , when the number of variables to pass is unknown. * * @param mysqli_stmt $stmt Equal to the prepared statement object. * * @return array|string The results of the SQL fetch. * @throws Exception */ protected function _dynamicBindResults(mysqli_stmt $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//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->mysqli()->more_results()) { $this->mysqli()->next_result(); } if (in_array('SQL_CALC_FOUND_ROWS', $this->_queryOptions)) { $stmt = $this->mysqli()->query('SELECT FOUND_ROWS()'); $totalCount = $stmt->fetch_row(); $this->totalCount = $totalCount[0]; } if ($this->returnType == 'json') { return json_encode($results); } return $results; } /** * Abstraction method that will build an JOIN part of the query * * @return void */ protected function _buildJoinOld() { 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; } } /** * Insert/Update query helper * * @param array $tableData * @param array $tableColumns * @param bool $isInsert INSERT operation flag * * @throws Exception */ 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, ', '); } /** * Helper function to add variables into the query statement * * @param array $tableData Variable with values * * @throws Exception */ 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); } } /** * Abstraction method that will build an INSERT or UPDATE part of the query * * @param array $tableData * * @throws Exception */ 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 .= ')'; } } /** * Abstraction method that will build the part of the WHERE conditions * * @param string $operator * @param array $conditions */ 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); } } } } /** * Abstraction method that will build the GROUP BY part of the WHERE statement * * @return void */ 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, ', ') . " "; } /** * Abstraction method that will build the LIMIT part of the WHERE statement * * @return void */ 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, ', ') . " "; } /** * Abstraction method that will build the LIMIT part of the WHERE statement * * @param int|array $numRows Array to define SQL limit in format Array ($offset, $count) * or only $count * * @return void */ 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; } } /** * Method attempts to prepare the SQL query * and throws an error if there was a problem. * * @return mysqli_stmt * @throws Exception */ protected function _prepareQuery() { $stmt = $this->mysqli()->prepare($this->_query); if ($stmt !== false) { if ($this->traceEnabled) $this->traceStartQ = microtime(true); return $stmt; } if ($this->mysqli()->errno === 2006 && $this->autoReconnect === true && $this->autoReconnectCount === 0) { $this->connect($this->defConnectionName); $this->autoReconnectCount++; return $this->_prepareQuery(); } $error = $this->mysqli()->error; $query = $this->_query; $errno = $this->mysqli()->errno; $this->reset(); throw new Exception(sprintf('%s query: %s', $error, $query), $errno); } /** * Referenced data array is required by mysqli since PHP 5.3+ * * @param array $arr * * @return array */ protected function refValues(array &$arr) { //Reference in the function arguments are required for HHVM to work //https://github.com/facebook/hhvm/issues/5155 //Referenced data array is required by mysqli since PHP 5.3+ if (strnatcmp(phpversion(), '5.3') >= 0) { $refs = array(); foreach ($arr as $key => $value) { $refs[$key] = & $arr[$key]; } return $refs; } return $arr; } /** * Function to replace ? with variables from bind variable * * @param string $str * @param array $vals * * @return string */ 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' || $val == 'NULL' || is_null($val) ) { $val = "NULL"; $newStr .= substr($str, 0, $pos) . $val ; }else{ $newStr .= substr($str, 0, $pos) . "'" . $val . "'"; } $str = substr($str, $pos + 1); } $newStr .= $str; return $newStr; } /** * Method returns last executed query * * @return string */ public function getLastQuery() { return $this->_lastQuery; } /** * Method returns mysql error * * @return string * @throws Exception */ public function getLastError() { if (!isset($this->_mysqli[$this->defConnectionName])) { return "mysqli is null"; } return trim($this->_stmtError . " " . $this->mysqli()->error); } /** * Method returns mysql error code * * @return int */ public function getLastErrno () { return $this->_stmtErrno; } /** * Mostly internal method to get query and its params out of subquery object * after get() and getAll() * * @return array */ public function getSubQuery() { if (!$this->isSubQuery) { return null; } array_shift($this->_bindParams); $val = Array('query' => $this->_query, 'params' => $this->_bindParams, 'alias' => isset($this->connectionsSettings[$this->defConnectionName]) ? $this->connectionsSettings[$this->defConnectionName]['host'] : null ); $this->reset(); return $val; } /* Helper functions */ /** * Method returns generated interval function as a string * * @param string $diff interval in the formats: * "1", "-1d" or "- 1 day" -- For interval - 1 day * Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear * Default null; * @param string $func Initial date * * @return string * @throws Exception */ 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; } /** * Method returns generated interval function as an insert/update function * * @param string $diff interval in the formats: * "1", "-1d" or "- 1 day" -- For interval - 1 day * Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear * Default null; * @param string $func Initial date * * @return array * @throws Exception */ public function now($diff = null, $func = "NOW()") { return array("[F]" => Array($this->interval($diff, $func))); } /** * Method generates incremental function call * * @param int $num increment by int or float. 1 by default * * @throws Exception * @return array */ public function inc($num = 1) { if (!is_numeric($num)) { throw new Exception('Argument supplied to inc must be a number'); } return array("[I]" => "+" . $num); } /** * Method generates decremental function call * * @param int $num increment by int or float. 1 by default * * @return array * @throws Exception */ public function dec($num = 1) { if (!is_numeric($num)) { throw new Exception('Argument supplied to dec must be a number'); } return array("[I]" => "-" . $num); } /** * Method generates change boolean function call * * @param string $col column name. null by default * * @return array */ public function not($col = null) { return array("[N]" => (string)$col); } /** * Method generates user defined function call * * @param string $expr user function body * @param array $bindParams * * @return array */ public function func($expr, $bindParams = null) { return array("[F]" => array($expr, $bindParams)); } /** * Method creates new mysqlidb object for a subquery generation * * @param string $subQueryAlias * * @return MysqliDb */ public static function subQuery($subQueryAlias = "") { return new self(array('host' => $subQueryAlias, 'isSubQuery' => true)); } /** * Method returns a copy of a mysqlidb subquery object * * @return MysqliDb new mysqlidb object */ public function copy() { $copy = unserialize(serialize($this)); $copy->_mysqli = array(); return $copy; } /** * Begin a transaction * * @uses mysqli->autocommit(false) * @uses register_shutdown_function(array($this, "_transaction_shutdown_check")) * @throws Exception */ public function startTransaction() { $this->mysqli()->autocommit(false); $this->_transaction_in_progress = true; register_shutdown_function(array($this, "_transaction_status_check")); } /** * Transaction commit * * @uses mysqli->commit(); * @uses mysqli->autocommit(true); * @throws Exception */ public function commit() { $result = $this->mysqli()->commit(); $this->_transaction_in_progress = false; $this->mysqli()->autocommit(true); return $result; } /** * Transaction rollback function * * @uses mysqli->rollback(); * @uses mysqli->autocommit(true); * @throws Exception */ public function rollback() { $result = $this->mysqli()->rollback(); $this->_transaction_in_progress = false; $this->mysqli()->autocommit(true); return $result; } /** * Shutdown handler to rollback uncommited operations in order to keep * atomic operations sane. * * @uses mysqli->rollback(); * @throws Exception */ public function _transaction_status_check() { if (!$this->_transaction_in_progress) { return; } $this->rollback(); } /** * Query execution time tracking switch * * @param bool $enabled Enable execution time tracking * @param string $stripPrefix Prefix to strip from the path in exec log * * @return MysqliDb */ public function setTrace($enabled, $stripPrefix = null) { $this->traceEnabled = $enabled; $this->traceStripPrefix = $stripPrefix; return $this; } /** * Get where and what function was called for query stored in MysqliDB->trace * * @return string with information */ 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"] . " "; } /** * Method to check if needed table is created * * @param array $tables Table name or an Array of table names to check * * @return bool True if table exists * @throws Exception */ public function tableExists($tables) { $tables = !is_array($tables) ? Array($tables) : $tables; $count = count($tables); if ($count == 0) { return false; } foreach ($tables as $i => $value) $tables[$i] = self::$prefix . $value; $db = isset($this->connectionsSettings[$this->defConnectionName]) ? $this->connectionsSettings[$this->defConnectionName]['db'] : null; $this->where('table_schema', $db); $this->where('table_name', $tables, 'in'); $this->get('information_schema.tables', $count); return $this->count == $count; } /** * Return result as an associative array with $idField field value used as a record key * * Array Returns an array($k => $v) if get(.."param1, param2"), array ($k => array ($v, $v)) otherwise * * @param string $idField field name to use for a mapped element key * * @return MysqliDb */ public function map($idField) { $this->_mapKey = $idField; return $this; } /** * Pagination wrapper to get() * * @access public * * @param string $table The name of the database table to work with * @param int $page Page number * @param array|string $fields Array or coma separated list of fields to fetch * * @return array * @throws Exception */ 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; } /** * This method allows you to specify multiple (method chaining optional) AND WHERE statements for the join table on part of the SQL query. * * @uses $dbWrapper->joinWhere('user u', 'u.id', 7)->where('user u', 'u.title', 'MyTitle'); * * @param string $whereJoin The name of the table followed by its prefix. * @param string $whereProp The name of the database field. * @param mixed $whereValue The value of the database field. * * @param string $operator * @param string $cond * * @return $this */ public function joinWhere($whereJoin, $whereProp, $whereValue = 'DBNULL', $operator = '=', $cond = 'AND') { $this->_joinAnd[self::$prefix . $whereJoin][] = Array ($cond, $whereProp, $operator, $whereValue); return $this; } /** * This method allows you to specify multiple (method chaining optional) OR WHERE statements for the join table on part of the SQL query. * * @uses $dbWrapper->joinWhere('user u', 'u.id', 7)->where('user u', 'u.title', 'MyTitle'); * * @param string $whereJoin The name of the table followed by its prefix. * @param string $whereProp The name of the database field. * @param mixed $whereValue The value of the database field. * @param string $operator * * @return $this */ public function joinOrWhere($whereJoin, $whereProp, $whereValue = 'DBNULL', $operator = '=', $cond = 'AND') { return $this->joinWhere($whereJoin, $whereProp, $whereValue, $operator, 'OR'); } /** * Abstraction method that will build an JOIN part of the query */ 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); } } } } /** * Convert a condition and value into the sql string * * @param String $operator The where constraint operator * @param String|array $val The where constraint value */ 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

@enrybisco
Copy link

#954

@enrybisco
Copy link

solved

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@TwinMist @adrianlaraperez88 @enrybisco