insert Null and not empty

[TwinMist]

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]

Sorry but this is not a problem for this class

…

On Thu, Nov 7, 2019, 7:24 AM ernexus ***@***.***> wrote: No there are no such possibility at the time. As a temporary workaround you could use additional hidden input like this, get the job done: And use js callback: var drEvent = $('.dropify').dropify(); drEvent.on('dropify.afterClear', function(event, element){ $("#picture_removed").val("1"); }); And finally check in your php: if($_POST['picture_removed'] == 1) { unlink('file.jpg'); } — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#840?email_source=notifications&email_token=AEVXRRJNEVSTKEVS2G5XVYTQSQCGLA5CNFSM4HZKDBW2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEDMHT5A#issuecomment-551057908>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AEVXRRI7ZVPGDP4V7RPWCH3QSQCGLANCNFSM4HZKDBWQ> .

[enrybisco]

// 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]

#954

[enrybisco]

solved