Advertisement
Guest User

MysqliDb.php

a guest
Nov 17th, 2018
291
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 74.26 KB | None | 0 0
  1. <?php
  2.  
  3. class MysqliDb
  4. {
  5.  
  6. /**
  7. * Static instance of self
  8. *
  9. * @var MysqliDb
  10. */
  11. protected static $_instance;
  12.  
  13. /**
  14. * Table prefix
  15. *
  16. * @var string
  17. */
  18. public static $prefix = '';
  19.  
  20. /**
  21. * MySQLi instances
  22. *
  23. * @var mysqli[]
  24. */
  25. protected $_mysqli = array();
  26.  
  27. /**
  28. * The SQL query to be prepared and executed
  29. *
  30. * @var string
  31. */
  32. protected $_query;
  33.  
  34. /**
  35. * The previously executed SQL query
  36. *
  37. * @var string
  38. */
  39. protected $_lastQuery;
  40.  
  41. /**
  42. * The SQL query options required after SELECT, INSERT, UPDATE or DELETE
  43. *
  44. * @var array
  45. */
  46. protected $_queryOptions = array();
  47.  
  48. /**
  49. * An array that holds where joins
  50. *
  51. * @var array
  52. */
  53. protected $_join = array();
  54.  
  55. /**
  56. * An array that holds where conditions
  57. *
  58. * @var array
  59. */
  60. protected $_where = array();
  61.  
  62. /**
  63. * An array that holds where join ands
  64. *
  65. * @var array
  66. */
  67. protected $_joinAnd = array();
  68.  
  69. /**
  70. * An array that holds having conditions
  71. *
  72. * @var array
  73. */
  74. protected $_having = array();
  75.  
  76. /**
  77. * Dynamic type list for order by condition value
  78. *
  79. * @var array
  80. */
  81. protected $_orderBy = array();
  82.  
  83. /**
  84. * Dynamic type list for group by condition value
  85. *
  86. * @var array
  87. */
  88. protected $_groupBy = array();
  89.  
  90. /**
  91. * Dynamic type list for temporary locking tables.
  92. *
  93. * @var array
  94. */
  95. protected $_tableLocks = array();
  96.  
  97. /**
  98. * Variable which holds the current table lock method.
  99. *
  100. * @var string
  101. */
  102. protected $_tableLockMethod = "READ";
  103.  
  104. /**
  105. * Dynamic array that holds a combination of where condition/table data value types and parameter references
  106. *
  107. * @var array
  108. */
  109. protected $_bindParams = array(''); // Create the empty 0 index
  110.  
  111. /**
  112. * Variable which holds an amount of returned rows during get/getOne/select queries
  113. *
  114. * @var string
  115. */
  116. public $count = 0;
  117.  
  118. /**
  119. * Variable which holds an amount of returned rows during get/getOne/select queries with withTotalCount()
  120. *
  121. * @var string
  122. */
  123. public $totalCount = 0;
  124.  
  125. /**
  126. * Variable which holds last statement error
  127. *
  128. * @var string
  129. */
  130. protected $_stmtError;
  131.  
  132. /**
  133. * Variable which holds last statement error code
  134. *
  135. * @var int
  136. */
  137. protected $_stmtErrno;
  138.  
  139. /**
  140. * Is Subquery object
  141. *
  142. * @var bool
  143. */
  144. protected $isSubQuery = false;
  145.  
  146. /**
  147. * Name of the auto increment column
  148. *
  149. * @var int
  150. */
  151. protected $_lastInsertId = null;
  152.  
  153. /**
  154. * Column names for update when using onDuplicate method
  155. *
  156. * @var array
  157. */
  158. protected $_updateColumns = null;
  159.  
  160. /**
  161. * Return type: 'array' to return results as array, 'object' as object
  162. * 'json' as json string
  163. *
  164. * @var string
  165. */
  166. public $returnType = 'array';
  167.  
  168. /**
  169. * Should join() results be nested by table
  170. *
  171. * @var bool
  172. */
  173. protected $_nestJoin = false;
  174.  
  175. /**
  176. * Table name (with prefix, if used)
  177. *
  178. * @var string
  179. */
  180. private $_tableName = '';
  181.  
  182. /**
  183. * FOR UPDATE flag
  184. *
  185. * @var bool
  186. */
  187. protected $_forUpdate = false;
  188.  
  189. /**
  190. * LOCK IN SHARE MODE flag
  191. *
  192. * @var bool
  193. */
  194. protected $_lockInShareMode = false;
  195.  
  196. /**
  197. * Key field for Map()'ed result array
  198. *
  199. * @var string
  200. */
  201. protected $_mapKey = null;
  202.  
  203. /**
  204. * Variables for query execution tracing
  205. */
  206. protected $traceStartQ;
  207. protected $traceEnabled;
  208. protected $traceStripPrefix;
  209. public $trace = array();
  210.  
  211. /**
  212. * Per page limit for pagination
  213. *
  214. * @var int
  215. */
  216.  
  217. public $pageLimit = 20;
  218. /**
  219. * Variable that holds total pages count of last paginate() query
  220. *
  221. * @var int
  222. */
  223. public $totalPages = 0;
  224.  
  225. /**
  226. * @var array connections settings [profile_name=>[same_as_contruct_args]]
  227. */
  228. protected $connectionsSettings = array();
  229. /**
  230. * @var string the name of a default (main) mysqli connection
  231. */
  232. public $defConnectionName = 'default';
  233.  
  234. public $autoReconnect = true;
  235. protected $autoReconnectCount = 0;
  236.  
  237. /**
  238. * @var bool Operations in transaction indicator
  239. */
  240. protected $_transaction_in_progress = false;
  241.  
  242. /**
  243. * @param string $host
  244. * @param string $username
  245. * @param string $password
  246. * @param string $db
  247. * @param int $port
  248. * @param string $charset
  249. * @param string $socket
  250. */
  251. public function __construct($host = null, $username = null, $password = null, $db = null, $port = null, $charset = 'utf8', $socket = null)
  252. {
  253. $isSubQuery = false;
  254.  
  255. // if params were passed as array
  256. if (is_array($host)) {
  257. foreach ($host as $key => $val) {
  258. $$key = $val;
  259. }
  260. }
  261.  
  262. $this->addConnection('default', array(
  263. 'host' => $host,
  264. 'username' => $username,
  265. 'password' => $password,
  266. 'db' => $db,
  267. 'port' => $port,
  268. 'socket' => $socket,
  269. 'charset' => $charset
  270. ));
  271.  
  272. if ($isSubQuery) {
  273. $this->isSubQuery = true;
  274. return;
  275. }
  276.  
  277. if (isset($prefix)) {
  278. $this->setPrefix($prefix);
  279. }
  280.  
  281. self::$_instance = $this;
  282. }
  283.  
  284. /**
  285. * A method to connect to the database
  286. *
  287. * @param null|string $connectionName
  288. *
  289. * @throws Exception
  290. * @return void
  291. */
  292. public function connect($connectionName = 'default')
  293. {
  294. if(!isset($this->connectionsSettings[$connectionName]))
  295. throw new Exception('Connection profile not set');
  296.  
  297. $pro = $this->connectionsSettings[$connectionName];
  298. $params = array_values($pro);
  299. $charset = array_pop($params);
  300.  
  301. if ($this->isSubQuery) {
  302. return;
  303. }
  304.  
  305. if (empty($pro['host']) && empty($pro['socket'])) {
  306. throw new Exception('MySQL host or socket is not set');
  307. }
  308.  
  309. $mysqlic = new ReflectionClass('mysqli');
  310. $mysqli = $mysqlic->newInstanceArgs($params);
  311.  
  312. if ($mysqli->connect_error) {
  313. throw new Exception('Connect Error ' . $mysqli->connect_errno . ': ' . $mysqli->connect_error, $mysqli->connect_errno);
  314. }
  315.  
  316. if (!empty($charset)) {
  317. $mysqli->set_charset($charset);
  318. }
  319. $this->_mysqli[$connectionName] = $mysqli;
  320. }
  321.  
  322. /**
  323. * @throws Exception
  324. */
  325. public function disconnectAll()
  326. {
  327. foreach (array_keys($this->_mysqli) as $k) {
  328. $this->disconnect($k);
  329. }
  330. }
  331.  
  332. /**
  333. * Set the connection name to use in the next query
  334. *
  335. * @param string $name
  336. *
  337. * @return $this
  338. * @throws Exception
  339. */
  340. public function connection($name)
  341. {
  342. if (!isset($this->connectionsSettings[$name]))
  343. throw new Exception('Connection ' . $name . ' was not added.');
  344.  
  345. $this->defConnectionName = $name;
  346. return $this;
  347. }
  348.  
  349. /**
  350. * A method to disconnect from the database
  351. *
  352. * @params string $connection connection name to disconnect
  353. *
  354. * @param string $connection
  355. *
  356. * @return void
  357. */
  358. public function disconnect($connection = 'default')
  359. {
  360. if (!isset($this->_mysqli[$connection]))
  361. return;
  362.  
  363. $this->_mysqli[$connection]->close();
  364. unset($this->_mysqli[$connection]);
  365. }
  366.  
  367. /**
  368. * Create & store at _mysqli new mysqli instance
  369. *
  370. * @param string $name
  371. * @param array $params
  372. *
  373. * @return $this
  374. */
  375. public function addConnection($name, array $params)
  376. {
  377. $this->connectionsSettings[$name] = array();
  378. foreach (array('host', 'username', 'password', 'db', 'port', 'socket', 'charset') as $k) {
  379. $prm = isset($params[$k]) ? $params[$k] : null;
  380.  
  381. if ($k == 'host') {
  382. if (is_object($prm))
  383. $this->_mysqli[$name] = $prm;
  384.  
  385. if (!is_string($prm))
  386. $prm = null;
  387. }
  388. $this->connectionsSettings[$name][$k] = $prm;
  389. }
  390. return $this;
  391. }
  392.  
  393. /**
  394. * A method to get mysqli object or create it in case needed
  395. *
  396. * @return mysqli
  397. * @throws Exception
  398. */
  399. public function mysqli()
  400. {
  401. if (!isset($this->_mysqli[$this->defConnectionName])) {
  402. $this->connect($this->defConnectionName);
  403. }
  404. return $this->_mysqli[$this->defConnectionName];
  405. }
  406.  
  407. /**
  408. * A method of returning the static instance to allow access to the
  409. * instantiated object from within another class.
  410. * Inheriting this class would require reloading connection info.
  411. *
  412. * @uses $db = MySqliDb::getInstance();
  413. *
  414. * @return MysqliDb Returns the current instance.
  415. */
  416. public static function getInstance()
  417. {
  418. if(isset(self::$_instance)) {
  419. return self::$_instance;
  420. } else {
  421. return new MysqliDb();
  422. }
  423. }
  424.  
  425. /**
  426. * Reset states after an execution
  427. *
  428. * @return MysqliDb Returns the current instance.
  429. */
  430. protected function reset()
  431. {
  432. if ($this->traceEnabled) {
  433. $this->trace[] = array($this->_lastQuery, (microtime(true) - $this->traceStartQ), $this->_traceGetCaller());
  434. }
  435.  
  436. $this->_where = array();
  437. $this->_having = array();
  438. $this->_join = array();
  439. $this->_joinAnd = array();
  440. $this->_orderBy = array();
  441. $this->_groupBy = array();
  442. $this->_bindParams = array(''); // Create the empty 0 index
  443. $this->_query = null;
  444. $this->_queryOptions = array();
  445. $this->returnType = 'array';
  446. $this->_nestJoin = false;
  447. $this->_forUpdate = false;
  448. $this->_lockInShareMode = false;
  449. $this->_tableName = '';
  450. $this->_lastInsertId = null;
  451. $this->_updateColumns = null;
  452. $this->_mapKey = null;
  453. if(!$this->_transaction_in_progress ) {
  454. $this->defConnectionName = 'default';
  455. }
  456. $this->autoReconnectCount = 0;
  457. return $this;
  458. }
  459.  
  460. /**
  461. * Helper function to create dbObject with JSON return type
  462. *
  463. * @return MysqliDb
  464. */
  465. public function jsonBuilder()
  466. {
  467. $this->returnType = 'json';
  468. return $this;
  469. }
  470.  
  471. /**
  472. * Helper function to create dbObject with array return type
  473. * Added for consistency as that's default output type
  474. *
  475. * @return MysqliDb
  476. */
  477. public function arrayBuilder()
  478. {
  479. $this->returnType = 'array';
  480. return $this;
  481. }
  482.  
  483. /**
  484. * Helper function to create dbObject with object return type.
  485. *
  486. * @return MysqliDb
  487. */
  488. public function objectBuilder()
  489. {
  490. $this->returnType = 'object';
  491. return $this;
  492. }
  493.  
  494. /**
  495. * Method to set a prefix
  496. *
  497. * @param string $prefix Contains a table prefix
  498. *
  499. * @return MysqliDb
  500. */
  501. public function setPrefix($prefix = '')
  502. {
  503. self::$prefix = $prefix;
  504. return $this;
  505. }
  506.  
  507. /**
  508. * Pushes a unprepared statement to the mysqli stack.
  509. * WARNING: Use with caution.
  510. * This method does not escape strings by default so make sure you'll never use it in production.
  511. *
  512. * @author Jonas Barascu
  513. *
  514. * @param [[Type]] $query [[Description]]
  515. *
  516. * @return bool|mysqli_result
  517. * @throws Exception
  518. */
  519. private function queryUnprepared($query)
  520. {
  521. // Execute query
  522. $stmt = $this->mysqli()->query($query);
  523.  
  524. // Failed?
  525. if ($stmt !== false)
  526. return $stmt;
  527.  
  528. if ($this->mysqli()->errno === 2006 && $this->autoReconnect === true && $this->autoReconnectCount === 0) {
  529. $this->connect($this->defConnectionName);
  530. $this->autoReconnectCount++;
  531. return $this->queryUnprepared($query);
  532. }
  533.  
  534. throw new Exception(sprintf('Unprepared Query Failed, ERRNO: %u (%s)', $this->mysqli()->errno, $this->mysqli()->error), $this->mysqli()->errno);
  535. }
  536.  
  537. /**
  538. * Execute raw SQL query.
  539. *
  540. * @param string $query User-provided query to execute.
  541. * @param array $bindParams Variables array to bind to the SQL statement.
  542. *
  543. * @return array Contains the returned rows from the query.
  544. * @throws Exception
  545. */
  546. public function rawQuery($query, $bindParams = null)
  547. {
  548. $params = array(''); // Create the empty 0 index
  549. $this->_query = $query;
  550. $stmt = $this->_prepareQuery();
  551.  
  552. if (is_array($bindParams) === true) {
  553. foreach ($bindParams as $prop => $val) {
  554. $params[0] .= $this->_determineType($val);
  555. array_push($params, $bindParams[$prop]);
  556. }
  557.  
  558. call_user_func_array(array($stmt, 'bind_param'), $this->refValues($params));
  559. }
  560.  
  561. $stmt->execute();
  562. $this->count = $stmt->affected_rows;
  563. $this->_stmtError = $stmt->error;
  564. $this->_stmtErrno = $stmt->errno;
  565. $this->_lastQuery = $this->replacePlaceHolders($this->_query, $params);
  566. $res = $this->_dynamicBindResults($stmt);
  567. $this->reset();
  568.  
  569. return $res;
  570. }
  571.  
  572. /**
  573. * Helper function to execute raw SQL query and return only 1 row of results.
  574. * Note that function do not add 'limit 1' to the query by itself
  575. * Same idea as getOne()
  576. *
  577. * @param string $query User-provided query to execute.
  578. * @param array $bindParams Variables array to bind to the SQL statement.
  579. *
  580. * @return array|null Contains the returned row from the query.
  581. * @throws Exception
  582. */
  583. public function rawQueryOne($query, $bindParams = null)
  584. {
  585. $res = $this->rawQuery($query, $bindParams);
  586. if (is_array($res) && isset($res[0])) {
  587. return $res[0];
  588. }
  589.  
  590. return null;
  591. }
  592.  
  593. /**
  594. * Helper function to execute raw SQL query and return only 1 column of results.
  595. * If 'limit 1' will be found, then string will be returned instead of array
  596. * Same idea as getValue()
  597. *
  598. * @param string $query User-provided query to execute.
  599. * @param array $bindParams Variables array to bind to the SQL statement.
  600. *
  601. * @return mixed Contains the returned rows from the query.
  602. * @throws Exception
  603. */
  604. public function rawQueryValue($query, $bindParams = null)
  605. {
  606. $res = $this->rawQuery($query, $bindParams);
  607. if (!$res) {
  608. return null;
  609. }
  610.  
  611. $limit = preg_match('/limit\s+1;?$/i', $query);
  612. $key = key($res[0]);
  613. if (isset($res[0][$key]) && $limit == true) {
  614. return $res[0][$key];
  615. }
  616.  
  617. $newRes = Array();
  618. for ($i = 0; $i < $this->count; $i++) {
  619. $newRes[] = $res[$i][$key];
  620. }
  621. return $newRes;
  622. }
  623.  
  624. /**
  625. * A method to perform select query
  626. *
  627. * @param string $query Contains a user-provided select query.
  628. * @param int|array $numRows Array to define SQL limit in format Array ($offset, $count)
  629. *
  630. * @return array Contains the returned rows from the query.
  631. * @throws Exception
  632. */
  633. public function query($query, $numRows = null)
  634. {
  635. $this->_query = $query;
  636. $stmt = $this->_buildQuery($numRows);
  637. $stmt->execute();
  638. $this->_stmtError = $stmt->error;
  639. $this->_stmtErrno = $stmt->errno;
  640. $res = $this->_dynamicBindResults($stmt);
  641. $this->reset();
  642.  
  643. return $res;
  644. }
  645.  
  646. /**
  647. * This method allows you to specify multiple (method chaining optional) options for SQL queries.
  648. *
  649. * @uses $MySqliDb->setQueryOption('name');
  650. *
  651. * @param string|array $options The options name of the query.
  652. *
  653. * @throws Exception
  654. * @return MysqliDb
  655. */
  656. public function setQueryOption($options)
  657. {
  658. $allowedOptions = Array('ALL', 'DISTINCT', 'DISTINCTROW', 'HIGH_PRIORITY', 'STRAIGHT_JOIN', 'SQL_SMALL_RESULT',
  659. 'SQL_BIG_RESULT', 'SQL_BUFFER_RESULT', 'SQL_CACHE', 'SQL_NO_CACHE', 'SQL_CALC_FOUND_ROWS',
  660. 'LOW_PRIORITY', 'IGNORE', 'QUICK', 'MYSQLI_NESTJOIN', 'FOR UPDATE', 'LOCK IN SHARE MODE');
  661.  
  662. if (!is_array($options)) {
  663. $options = Array($options);
  664. }
  665.  
  666. foreach ($options as $option) {
  667. $option = strtoupper($option);
  668. if (!in_array($option, $allowedOptions)) {
  669. throw new Exception('Wrong query option: ' . $option);
  670. }
  671.  
  672. if ($option == 'MYSQLI_NESTJOIN') {
  673. $this->_nestJoin = true;
  674. } elseif ($option == 'FOR UPDATE') {
  675. $this->_forUpdate = true;
  676. } elseif ($option == 'LOCK IN SHARE MODE') {
  677. $this->_lockInShareMode = true;
  678. } else {
  679. $this->_queryOptions[] = $option;
  680. }
  681. }
  682.  
  683. return $this;
  684. }
  685.  
  686. /**
  687. * Function to enable SQL_CALC_FOUND_ROWS in the get queries
  688. *
  689. * @return MysqliDb
  690. * @throws Exception
  691. */
  692. public function withTotalCount()
  693. {
  694. $this->setQueryOption('SQL_CALC_FOUND_ROWS');
  695. return $this;
  696. }
  697.  
  698. /**
  699. * A convenient SELECT * function.
  700. *
  701. * @param string $tableName The name of the database table to work with.
  702. * @param int|array $numRows Array to define SQL limit in format Array ($offset, $count)
  703. * or only $count
  704. * @param string $columns Desired columns
  705. *
  706. * @return array|MysqliDb Contains the returned rows from the select query.
  707. * @throws Exception
  708. */
  709. public function get($tableName, $numRows = null, $columns = '*')
  710. {
  711. if (empty($columns)) {
  712. $columns = '*';
  713. }
  714.  
  715. $column = is_array($columns) ? implode(', ', $columns) : $columns;
  716.  
  717. if (strpos($tableName, '.') === false) {
  718. $this->_tableName = self::$prefix . $tableName;
  719. } else {
  720. $this->_tableName = $tableName;
  721. }
  722.  
  723. $this->_query = 'SELECT ' . implode(' ', $this->_queryOptions) . ' ' .
  724. $column . " FROM " . $this->_tableName;
  725. $stmt = $this->_buildQuery($numRows);
  726.  
  727. if ($this->isSubQuery) {
  728. return $this;
  729. }
  730.  
  731. $stmt->execute();
  732. $this->_stmtError = $stmt->error;
  733. $this->_stmtErrno = $stmt->errno;
  734. $res = $this->_dynamicBindResults($stmt);
  735. $this->reset();
  736.  
  737. return $res;
  738. }
  739.  
  740. /**
  741. * A convenient SELECT * function to get one record.
  742. *
  743. * @param string $tableName The name of the database table to work with.
  744. * @param string $columns Desired columns
  745. *
  746. * @return array Contains the returned rows from the select query.
  747. * @throws Exception
  748. */
  749. public function getOne($tableName, $columns = '*')
  750. {
  751. $res = $this->get($tableName, 1, $columns);
  752.  
  753. if ($res instanceof MysqliDb) {
  754. return $res;
  755. } elseif (is_array($res) && isset($res[0])) {
  756. return $res[0];
  757. } elseif ($res) {
  758. return $res;
  759. }
  760.  
  761. return null;
  762. }
  763.  
  764. /**
  765. * A convenient SELECT COLUMN function to get a single column value from one row
  766. *
  767. * @param string $tableName The name of the database table to work with.
  768. * @param string $column The desired column
  769. * @param int $limit Limit of rows to select. Use null for unlimited..1 by default
  770. *
  771. * @return mixed Contains the value of a returned column / array of values
  772. * @throws Exception
  773. */
  774. public function getValue($tableName, $column, $limit = 1)
  775. {
  776. $res = $this->ArrayBuilder()->get($tableName, $limit, "{$column} AS retval");
  777.  
  778. if (!$res) {
  779. return null;
  780. }
  781.  
  782. if ($limit == 1) {
  783. if (isset($res[0]["retval"])) {
  784. return $res[0]["retval"];
  785. }
  786. return null;
  787. }
  788.  
  789. $newRes = Array();
  790. for ($i = 0; $i < $this->count; $i++) {
  791. $newRes[] = $res[$i]['retval'];
  792. }
  793. return $newRes;
  794. }
  795.  
  796. /**
  797. * Insert method to add new row
  798. *
  799. * @param string $tableName The name of the table.
  800. * @param array $insertData Data containing information for inserting into the DB.
  801. *
  802. * @return bool Boolean indicating whether the insert query was completed successfully.
  803. * @throws Exception
  804. */
  805. public function insert($tableName, $insertData)
  806. {
  807. return $this->_buildInsert($tableName, $insertData, 'INSERT');
  808. }
  809.  
  810. /**
  811. * Insert method to add several rows at once
  812. *
  813. * @param string $tableName The name of the table.
  814. * @param array $multiInsertData Two-dimensional Data-array containing information for inserting into the DB.
  815. * @param array $dataKeys Optional Table Key names, if not set in insertDataSet.
  816. *
  817. * @return bool|array Boolean indicating the insertion failed (false), else return id-array ([int])
  818. * @throws Exception
  819. */
  820. public function insertMulti($tableName, array $multiInsertData, array $dataKeys = null)
  821. {
  822. // only auto-commit our inserts, if no transaction is currently running
  823. $autoCommit = (isset($this->_transaction_in_progress) ? !$this->_transaction_in_progress : true);
  824. $ids = array();
  825.  
  826. if($autoCommit) {
  827. $this->startTransaction();
  828. }
  829.  
  830. foreach ($multiInsertData as $insertData) {
  831. if($dataKeys !== null) {
  832. // apply column-names if given, else assume they're already given in the data
  833. $insertData = array_combine($dataKeys, $insertData);
  834. }
  835.  
  836. $id = $this->insert($tableName, $insertData);
  837. if(!$id) {
  838. if($autoCommit) {
  839. $this->rollback();
  840. }
  841. return false;
  842. }
  843. $ids[] = $id;
  844. }
  845.  
  846. if($autoCommit) {
  847. $this->commit();
  848. }
  849.  
  850. return $ids;
  851. }
  852.  
  853. /**
  854. * Replace method to add new row
  855. *
  856. * @param string $tableName The name of the table.
  857. * @param array $insertData Data containing information for inserting into the DB.
  858. *
  859. * @return bool Boolean indicating whether the insert query was completed successfully.
  860. * @throws Exception
  861. */
  862. public function replace($tableName, $insertData)
  863. {
  864. return $this->_buildInsert($tableName, $insertData, 'REPLACE');
  865. }
  866.  
  867. /**
  868. * A convenient function that returns TRUE if exists at least an element that
  869. * satisfy the where condition specified calling the "where" method before this one.
  870. *
  871. * @param string $tableName The name of the database table to work with.
  872. *
  873. * @return bool
  874. * @throws Exception
  875. */
  876. public function has($tableName)
  877. {
  878. $this->getOne($tableName, '1');
  879. return $this->count >= 1;
  880. }
  881.  
  882. /**
  883. * Update query. Be sure to first call the "where" method.
  884. *
  885. * @param string $tableName The name of the database table to work with.
  886. * @param array $tableData Array of data to update the desired row.
  887. * @param int $numRows Limit on the number of rows that can be updated.
  888. *
  889. * @return bool
  890. * @throws Exception
  891. */
  892. public function update($tableName, $tableData, $numRows = null)
  893. {
  894. if ($this->isSubQuery) {
  895. return;
  896. }
  897.  
  898. $this->_query = "UPDATE " . self::$prefix . $tableName;
  899.  
  900. $stmt = $this->_buildQuery($numRows, $tableData);
  901. $status = $stmt->execute();
  902. $this->reset();
  903. $this->_stmtError = $stmt->error;
  904. $this->_stmtErrno = $stmt->errno;
  905. $this->count = $stmt->affected_rows;
  906.  
  907. return $status;
  908. }
  909.  
  910. /**
  911. * Delete query. Call the "where" method first.
  912. *
  913. * @param string $tableName The name of the database table to work with.
  914. * @param int|array $numRows Array to define SQL limit in format Array ($offset, $count)
  915. * or only $count
  916. *
  917. * @return bool Indicates success. 0 or 1.
  918. * @throws Exception
  919. */
  920. public function delete($tableName, $numRows = null)
  921. {
  922. if ($this->isSubQuery) {
  923. return;
  924. }
  925.  
  926. $table = self::$prefix . $tableName;
  927.  
  928. if (count($this->_join)) {
  929. $this->_query = "DELETE " . preg_replace('/.* (.*)/', '$1', $table) . " FROM " . $table;
  930. } else {
  931. $this->_query = "DELETE FROM " . $table;
  932. }
  933.  
  934. $stmt = $this->_buildQuery($numRows);
  935. $stmt->execute();
  936. $this->_stmtError = $stmt->error;
  937. $this->_stmtErrno = $stmt->errno;
  938. $this->count = $stmt->affected_rows;
  939. $this->reset();
  940.  
  941. return ($stmt->affected_rows > -1); // -1 indicates that the query returned an error
  942. }
  943.  
  944. /**
  945. * This method allows you to specify multiple (method chaining optional) AND WHERE statements for SQL queries.
  946. *
  947. * @uses $MySqliDb->where('id', 7)->where('title', 'MyTitle');
  948. *
  949. * @param string $whereProp The name of the database field.
  950. * @param mixed $whereValue The value of the database field.
  951. * @param string $operator Comparison operator. Default is =
  952. * @param string $cond Condition of where statement (OR, AND)
  953. *
  954. * @return MysqliDb
  955. */
  956. public function where($whereProp, $whereValue = 'DBNULL', $operator = '=', $cond = 'AND')
  957. {
  958. // forkaround for an old operation api
  959. if (is_array($whereValue) && ($key = key($whereValue)) != "0") {
  960. $operator = $key;
  961. $whereValue = $whereValue[$key];
  962. }
  963.  
  964. if (count($this->_where) == 0) {
  965. $cond = '';
  966. }
  967.  
  968. $this->_where[] = array($cond, $whereProp, $operator, $whereValue);
  969. return $this;
  970. }
  971.  
  972. /**
  973. * This function store update column's name and column name of the
  974. * autoincrement column
  975. *
  976. * @param array $updateColumns Variable with values
  977. * @param string $lastInsertId Variable value
  978. *
  979. * @return MysqliDb
  980. */
  981. public function onDuplicate($updateColumns, $lastInsertId = null)
  982. {
  983. $this->_lastInsertId = $lastInsertId;
  984. $this->_updateColumns = $updateColumns;
  985. return $this;
  986. }
  987.  
  988. /**
  989. * This method allows you to specify multiple (method chaining optional) OR WHERE statements for SQL queries.
  990. *
  991. * @uses $MySqliDb->orWhere('id', 7)->orWhere('title', 'MyTitle');
  992. *
  993. * @param string $whereProp The name of the database field.
  994. * @param mixed $whereValue The value of the database field.
  995. * @param string $operator Comparison operator. Default is =
  996. *
  997. * @return MysqliDb
  998. */
  999. public function orWhere($whereProp, $whereValue = 'DBNULL', $operator = '=')
  1000. {
  1001. return $this->where($whereProp, $whereValue, $operator, 'OR');
  1002. }
  1003.  
  1004. /**
  1005. * This method allows you to specify multiple (method chaining optional) AND HAVING statements for SQL queries.
  1006. *
  1007. * @uses $MySqliDb->having('SUM(tags) > 10')
  1008. *
  1009. * @param string $havingProp The name of the database field.
  1010. * @param mixed $havingValue The value of the database field.
  1011. * @param string $operator Comparison operator. Default is =
  1012. *
  1013. * @param string $cond
  1014. *
  1015. * @return MysqliDb
  1016. */
  1017.  
  1018. public function having($havingProp, $havingValue = 'DBNULL', $operator = '=', $cond = 'AND')
  1019. {
  1020. // forkaround for an old operation api
  1021. if (is_array($havingValue) && ($key = key($havingValue)) != "0") {
  1022. $operator = $key;
  1023. $havingValue = $havingValue[$key];
  1024. }
  1025.  
  1026. if (count($this->_having) == 0) {
  1027. $cond = '';
  1028. }
  1029.  
  1030. $this->_having[] = array($cond, $havingProp, $operator, $havingValue);
  1031. return $this;
  1032. }
  1033.  
  1034. /**
  1035. * This method allows you to specify multiple (method chaining optional) OR HAVING statements for SQL queries.
  1036. *
  1037. * @uses $MySqliDb->orHaving('SUM(tags) > 10')
  1038. *
  1039. * @param string $havingProp The name of the database field.
  1040. * @param mixed $havingValue The value of the database field.
  1041. * @param string $operator Comparison operator. Default is =
  1042. *
  1043. * @return MysqliDb
  1044. */
  1045. public function orHaving($havingProp, $havingValue = null, $operator = null)
  1046. {
  1047. return $this->having($havingProp, $havingValue, $operator, 'OR');
  1048. }
  1049.  
  1050. /**
  1051. * This method allows you to concatenate joins for the final SQL statement.
  1052. *
  1053. * @uses $MySqliDb->join('table1', 'field1 <> field2', 'LEFT')
  1054. *
  1055. * @param string $joinTable The name of the table.
  1056. * @param string $joinCondition the condition.
  1057. * @param string $joinType 'LEFT', 'INNER' etc.
  1058. *
  1059. * @throws Exception
  1060. * @return MysqliDb
  1061. */
  1062. public function join($joinTable, $joinCondition, $joinType = '')
  1063. {
  1064. $allowedTypes = array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER', 'NATURAL');
  1065. $joinType = strtoupper(trim($joinType));
  1066.  
  1067. if ($joinType && !in_array($joinType, $allowedTypes)) {
  1068. throw new Exception('Wrong JOIN type: ' . $joinType);
  1069. }
  1070.  
  1071. if (!is_object($joinTable)) {
  1072. $joinTable = self::$prefix . $joinTable;
  1073. }
  1074.  
  1075. $this->_join[] = Array($joinType, $joinTable, $joinCondition);
  1076.  
  1077. return $this;
  1078. }
  1079.  
  1080.  
  1081. /**
  1082. * This is a basic method which allows you to import raw .CSV data into a table
  1083. * Please check out http://dev.mysql.com/doc/refman/5.7/en/load-data.html for a valid .csv file.
  1084. *
  1085. * @author Jonas Barascu (Noneatme)
  1086. *
  1087. * @param string $importTable The database table where the data will be imported into.
  1088. * @param string $importFile The file to be imported. Please use double backslashes \\ and make sure you
  1089. * @param string $importSettings An Array defining the import settings as described in the README.md
  1090. *
  1091. * @return boolean
  1092. * @throws Exception
  1093. */
  1094. public function loadData($importTable, $importFile, $importSettings = null)
  1095. {
  1096. // We have to check if the file exists
  1097. if (!file_exists($importFile)) {
  1098. // Throw an exception
  1099. throw new Exception("importCSV -> importFile " . $importFile . " does not exists!");
  1100. }
  1101.  
  1102. // Define the default values
  1103. // We will merge it later
  1104. $settings = Array("fieldChar" => ';', "lineChar" => PHP_EOL, "linesToIgnore" => 1);
  1105.  
  1106. // Check the import settings
  1107. if (gettype($importSettings) == "array") {
  1108. // Merge the default array with the custom one
  1109. $settings = array_merge($settings, $importSettings);
  1110. }
  1111.  
  1112. // Add the prefix to the import table
  1113. $table = self::$prefix . $importTable;
  1114.  
  1115. // Add 1 more slash to every slash so maria will interpret it as a path
  1116. $importFile = str_replace("\\", "\\\\", $importFile);
  1117.  
  1118. // Switch between LOAD DATA and LOAD DATA LOCAL
  1119. $loadDataLocal = isset($settings["loadDataLocal"]) ? 'LOCAL' : '';
  1120.  
  1121. // Build SQL Syntax
  1122. $sqlSyntax = sprintf('LOAD DATA %s INFILE \'%s\' INTO TABLE %s',
  1123. $loadDataLocal, $importFile, $table);
  1124.  
  1125. // FIELDS
  1126. $sqlSyntax .= sprintf(' FIELDS TERMINATED BY \'%s\'', $settings["fieldChar"]);
  1127. if (isset($settings["fieldEnclosure"])) {
  1128. $sqlSyntax .= sprintf(' ENCLOSED BY \'%s\'', $settings["fieldEnclosure"]);
  1129. }
  1130.  
  1131. // LINES
  1132. $sqlSyntax .= sprintf(' LINES TERMINATED BY \'%s\'', $settings["lineChar"]);
  1133. if (isset($settings["lineStarting"])) {
  1134. $sqlSyntax .= sprintf(' STARTING BY \'%s\'', $settings["lineStarting"]);
  1135. }
  1136.  
  1137. // IGNORE LINES
  1138. $sqlSyntax .= sprintf(' IGNORE %d LINES', $settings["linesToIgnore"]);
  1139.  
  1140. // Execute the query unprepared because LOAD DATA only works with unprepared statements.
  1141. $result = $this->queryUnprepared($sqlSyntax);
  1142.  
  1143. // Are there rows modified?
  1144. // Let the user know if the import failed / succeeded
  1145. return (bool) $result;
  1146. }
  1147.  
  1148. /**
  1149. * This method is useful for importing XML files into a specific table.
  1150. * Check out the LOAD XML syntax for your MySQL server.
  1151. *
  1152. * @author Jonas Barascu
  1153. *
  1154. * @param string $importTable The table in which the data will be imported to.
  1155. * @param string $importFile The file which contains the .XML data.
  1156. * @param string $importSettings An Array defining the import settings as described in the README.md
  1157. *
  1158. * @return boolean Returns true if the import succeeded, false if it failed.
  1159. * @throws Exception
  1160. */
  1161. public function loadXml($importTable, $importFile, $importSettings = null)
  1162. {
  1163. // We have to check if the file exists
  1164. if(!file_exists($importFile)) {
  1165. // Does not exists
  1166. throw new Exception("loadXml: Import file does not exists");
  1167. return;
  1168. }
  1169.  
  1170. // Create default values
  1171. $settings = Array("linesToIgnore" => 0);
  1172.  
  1173. // Check the import settings
  1174. if(gettype($importSettings) == "array") {
  1175. $settings = array_merge($settings, $importSettings);
  1176. }
  1177.  
  1178. // Add the prefix to the import table
  1179. $table = self::$prefix . $importTable;
  1180.  
  1181. // Add 1 more slash to every slash so maria will interpret it as a path
  1182. $importFile = str_replace("\\", "\\\\", $importFile);
  1183.  
  1184. // Build SQL Syntax
  1185. $sqlSyntax = sprintf('LOAD XML INFILE \'%s\' INTO TABLE %s',
  1186. $importFile, $table);
  1187.  
  1188. // FIELDS
  1189. if(isset($settings["rowTag"])) {
  1190. $sqlSyntax .= sprintf(' ROWS IDENTIFIED BY \'%s\'', $settings["rowTag"]);
  1191. }
  1192.  
  1193. // IGNORE LINES
  1194. $sqlSyntax .= sprintf(' IGNORE %d LINES', $settings["linesToIgnore"]);
  1195.  
  1196. // Exceute the query unprepared because LOAD XML only works with unprepared statements.
  1197. $result = $this->queryUnprepared($sqlSyntax);
  1198.  
  1199. // Are there rows modified?
  1200. // Let the user know if the import failed / succeeded
  1201. return (bool) $result;
  1202. }
  1203.  
  1204. /**
  1205. * This method allows you to specify multiple (method chaining optional) ORDER BY statements for SQL queries.
  1206. *
  1207. * @uses $MySqliDb->orderBy('id', 'desc')->orderBy('name', 'desc', '^[a-z]')->orderBy('name', 'desc');
  1208. *
  1209. * @param string $orderByField The name of the database field.
  1210. * @param string $orderbyDirection
  1211. * @param mixed $customFieldsOrRegExp Array with fieldset for ORDER BY FIELD() ordering or string with regular expression for ORDER BY REGEXP ordering
  1212. *
  1213. * @return MysqliDb
  1214. * @throws Exception
  1215. */
  1216. public function orderBy($orderByField, $orderbyDirection = "DESC", $customFieldsOrRegExp = null)
  1217. {
  1218. $allowedDirection = Array("ASC", "DESC");
  1219. $orderbyDirection = strtoupper(trim($orderbyDirection));
  1220. $orderByField = preg_replace("/[^ -a-z0-9\.\(\),_`\*\'\"]+/i", '', $orderByField);
  1221.  
  1222. // Add table prefix to orderByField if needed.
  1223. //FIXME: We are adding prefix only if table is enclosed into `` to distinguish aliases
  1224. // from table names
  1225. $orderByField = preg_replace('/(\`)([`a-zA-Z0-9_]*\.)/', '\1' . self::$prefix . '\2', $orderByField);
  1226.  
  1227.  
  1228. if (empty($orderbyDirection) || !in_array($orderbyDirection, $allowedDirection)) {
  1229. throw new Exception('Wrong order direction: ' . $orderbyDirection);
  1230. }
  1231.  
  1232. if (is_array($customFieldsOrRegExp)) {
  1233. foreach ($customFieldsOrRegExp as $key => $value) {
  1234. $customFieldsOrRegExp[$key] = preg_replace("/[^\x80-\xff-a-z0-9\.\(\),_` ]+/i", '', $value);
  1235. }
  1236. $orderByField = 'FIELD (' . $orderByField . ', "' . implode('","', $customFieldsOrRegExp) . '")';
  1237. }elseif(is_string($customFieldsOrRegExp)){
  1238. $orderByField = $orderByField . " REGEXP '" . $customFieldsOrRegExp . "'";
  1239. }elseif($customFieldsOrRegExp !== null){
  1240. throw new Exception('Wrong custom field or Regular Expression: ' . $customFieldsOrRegExp);
  1241. }
  1242.  
  1243. $this->_orderBy[$orderByField] = $orderbyDirection;
  1244. return $this;
  1245. }
  1246.  
  1247. /**
  1248. * This method allows you to specify multiple (method chaining optional) GROUP BY statements for SQL queries.
  1249. *
  1250. * @uses $MySqliDb->groupBy('name');
  1251. *
  1252. * @param string $groupByField The name of the database field.
  1253. *
  1254. * @return MysqliDb
  1255. */
  1256. public function groupBy($groupByField)
  1257. {
  1258. $groupByField = preg_replace("/[^-a-z0-9\.\(\),_\* <>=!]+/i", '', $groupByField);
  1259.  
  1260. $this->_groupBy[] = $groupByField;
  1261. return $this;
  1262. }
  1263.  
  1264.  
  1265. /**
  1266. * This method sets the current table lock method.
  1267. *
  1268. * @author Jonas Barascu
  1269. *
  1270. * @param string $method The table lock method. Can be READ or WRITE.
  1271. *
  1272. * @throws Exception
  1273. * @return MysqliDb
  1274. */
  1275. public function setLockMethod($method)
  1276. {
  1277. // Switch the uppercase string
  1278. switch(strtoupper($method)) {
  1279. // Is it READ or WRITE?
  1280. case "READ" || "WRITE":
  1281. // Succeed
  1282. $this->_tableLockMethod = $method;
  1283. break;
  1284. default:
  1285. // Else throw an exception
  1286. throw new Exception("Bad lock type: Can be either READ or WRITE");
  1287. break;
  1288. }
  1289. return $this;
  1290. }
  1291.  
  1292. /**
  1293. * Locks a table for R/W action.
  1294. *
  1295. * @author Jonas Barascu
  1296. *
  1297. * @param string|array $table The table to be locked. Can be a table or a view.
  1298. *
  1299. * @return bool if succeeded;
  1300. * @throws Exception
  1301. */
  1302. public function lock($table)
  1303. {
  1304. // Main Query
  1305. $this->_query = "LOCK TABLES";
  1306.  
  1307. // Is the table an array?
  1308. if(gettype($table) == "array") {
  1309. // Loop trough it and attach it to the query
  1310. foreach($table as $key => $value) {
  1311. if(gettype($value) == "string") {
  1312. if($key > 0) {
  1313. $this->_query .= ",";
  1314. }
  1315. $this->_query .= " ".self::$prefix.$value." ".$this->_tableLockMethod;
  1316. }
  1317. }
  1318. }
  1319. else{
  1320. // Build the table prefix
  1321. $table = self::$prefix . $table;
  1322.  
  1323. // Build the query
  1324. $this->_query = "LOCK TABLES ".$table." ".$this->_tableLockMethod;
  1325. }
  1326.  
  1327. // Execute the query unprepared because LOCK only works with unprepared statements.
  1328. $result = $this->queryUnprepared($this->_query);
  1329. $errno = $this->mysqli()->errno;
  1330.  
  1331. // Reset the query
  1332. $this->reset();
  1333.  
  1334. // Are there rows modified?
  1335. if($result) {
  1336. // Return true
  1337. // We can't return ourself because if one table gets locked, all other ones get unlocked!
  1338. return true;
  1339. }
  1340. // Something went wrong
  1341. else {
  1342. throw new Exception("Locking of table ".$table." failed", $errno);
  1343. }
  1344.  
  1345. // Return the success value
  1346. return false;
  1347. }
  1348.  
  1349. /**
  1350. * Unlocks all tables in a database.
  1351. * Also commits transactions.
  1352. *
  1353. * @author Jonas Barascu
  1354. * @return MysqliDb
  1355. * @throws Exception
  1356. */
  1357. public function unlock()
  1358. {
  1359. // Build the query
  1360. $this->_query = "UNLOCK TABLES";
  1361.  
  1362. // Execute the query unprepared because UNLOCK and LOCK only works with unprepared statements.
  1363. $result = $this->queryUnprepared($this->_query);
  1364. $errno = $this->mysqli()->errno;
  1365.  
  1366. // Reset the query
  1367. $this->reset();
  1368.  
  1369. // Are there rows modified?
  1370. if($result) {
  1371. // return self
  1372. return $this;
  1373. }
  1374. // Something went wrong
  1375. else {
  1376. throw new Exception("Unlocking of tables failed", $errno);
  1377. }
  1378.  
  1379.  
  1380. // Return self
  1381. return $this;
  1382. }
  1383.  
  1384.  
  1385. /**
  1386. * This methods returns the ID of the last inserted item
  1387. *
  1388. * @return int The last inserted item ID.
  1389. * @throws Exception
  1390. */
  1391. public function getInsertId()
  1392. {
  1393. return $this->mysqli()->insert_id;
  1394. }
  1395.  
  1396. /**
  1397. * Escape harmful characters which might affect a query.
  1398. *
  1399. * @param string $str The string to escape.
  1400. *
  1401. * @return string The escaped string.
  1402. * @throws Exception
  1403. */
  1404. public function escape($str)
  1405. {
  1406. return $this->mysqli()->real_escape_string($str);
  1407. }
  1408.  
  1409. /**
  1410. * Method to call mysqli->ping() to keep unused connections open on
  1411. * long-running scripts, or to reconnect timed out connections (if php.ini has
  1412. * global mysqli.reconnect set to true). Can't do this directly using object
  1413. * since _mysqli is protected.
  1414. *
  1415. * @return bool True if connection is up
  1416. * @throws Exception
  1417. */
  1418. public function ping()
  1419. {
  1420. return $this->mysqli()->ping();
  1421. }
  1422.  
  1423. /**
  1424. * This method is needed for prepared statements. They require
  1425. * the data type of the field to be bound with "i" s", etc.
  1426. * This function takes the input, determines what type it is,
  1427. * and then updates the param_type.
  1428. *
  1429. * @param mixed $item Input to determine the type.
  1430. *
  1431. * @return string The joined parameter types.
  1432. */
  1433. protected function _determineType($item)
  1434. {
  1435. switch (gettype($item)) {
  1436. case 'NULL':
  1437. case 'string':
  1438. return 's';
  1439. break;
  1440.  
  1441. case 'boolean':
  1442. case 'integer':
  1443. return 'i';
  1444. break;
  1445.  
  1446. case 'blob':
  1447. return 'b';
  1448. break;
  1449.  
  1450. case 'double':
  1451. return 'd';
  1452. break;
  1453. }
  1454. return '';
  1455. }
  1456.  
  1457. /**
  1458. * Helper function to add variables into bind parameters array
  1459. *
  1460. * @param string Variable value
  1461. */
  1462. protected function _bindParam($value)
  1463. {
  1464. $this->_bindParams[0] .= $this->_determineType($value);
  1465. array_push($this->_bindParams, $value);
  1466. }
  1467.  
  1468. /**
  1469. * Helper function to add variables into bind parameters array in bulk
  1470. *
  1471. * @param array $values Variable with values
  1472. */
  1473. protected function _bindParams($values)
  1474. {
  1475. foreach ($values as $value) {
  1476. $this->_bindParam($value);
  1477. }
  1478. }
  1479.  
  1480. /**
  1481. * Helper function to add variables into bind parameters array and will return
  1482. * its SQL part of the query according to operator in ' $operator ?' or
  1483. * ' $operator ($subquery) ' formats
  1484. *
  1485. * @param string $operator
  1486. * @param mixed $value Variable with values
  1487. *
  1488. * @return string
  1489. */
  1490. protected function _buildPair($operator, $value)
  1491. {
  1492. if (!is_object($value)) {
  1493. $this->_bindParam($value);
  1494. return ' ' . $operator . ' ? ';
  1495. }
  1496.  
  1497. $subQuery = $value->getSubQuery();
  1498. $this->_bindParams($subQuery['params']);
  1499.  
  1500. return " " . $operator . " (" . $subQuery['query'] . ") " . $subQuery['alias'];
  1501. }
  1502.  
  1503. /**
  1504. * Internal function to build and execute INSERT/REPLACE calls
  1505. *
  1506. * @param string $tableName The name of the table.
  1507. * @param array $insertData Data containing information for inserting into the DB.
  1508. * @param string $operation Type of operation (INSERT, REPLACE)
  1509. *
  1510. * @return bool Boolean indicating whether the insert query was completed successfully.
  1511. * @throws Exception
  1512. */
  1513. private function _buildInsert($tableName, $insertData, $operation)
  1514. {
  1515. if ($this->isSubQuery) {
  1516. return;
  1517. }
  1518.  
  1519. $this->_query = $operation . " " . implode(' ', $this->_queryOptions) . " INTO " . self::$prefix . $tableName;
  1520. $stmt = $this->_buildQuery(null, $insertData);
  1521. $status = $stmt->execute();
  1522. $this->_stmtError = $stmt->error;
  1523. $this->_stmtErrno = $stmt->errno;
  1524. $haveOnDuplicate = !empty ($this->_updateColumns);
  1525. $this->reset();
  1526. $this->count = $stmt->affected_rows;
  1527.  
  1528. if ($stmt->affected_rows < 1) {
  1529. // in case of onDuplicate() usage, if no rows were inserted
  1530. if ($status && $haveOnDuplicate) {
  1531. return true;
  1532. }
  1533. return false;
  1534. }
  1535.  
  1536. if ($stmt->insert_id > 0) {
  1537. return $stmt->insert_id;
  1538. }
  1539.  
  1540. return true;
  1541. }
  1542.  
  1543. /**
  1544. * Abstraction method that will compile the WHERE statement,
  1545. * any passed update data, and the desired rows.
  1546. * It then builds the SQL query.
  1547. *
  1548. * @param int|array $numRows Array to define SQL limit in format Array ($offset, $count)
  1549. * or only $count
  1550. * @param array $tableData Should contain an array of data for updating the database.
  1551. *
  1552. * @return mysqli_stmt|bool Returns the $stmt object.
  1553. * @throws Exception
  1554. */
  1555. protected function _buildQuery($numRows = null, $tableData = null)
  1556. {
  1557. // $this->_buildJoinOld();
  1558. $this->_buildJoin();
  1559. $this->_buildInsertQuery($tableData);
  1560. $this->_buildCondition('WHERE', $this->_where);
  1561. $this->_buildGroupBy();
  1562. $this->_buildCondition('HAVING', $this->_having);
  1563. $this->_buildOrderBy();
  1564. $this->_buildLimit($numRows);
  1565. $this->_buildOnDuplicate($tableData);
  1566.  
  1567. if ($this->_forUpdate) {
  1568. $this->_query .= ' FOR UPDATE';
  1569. }
  1570. if ($this->_lockInShareMode) {
  1571. $this->_query .= ' LOCK IN SHARE MODE';
  1572. }
  1573.  
  1574. $this->_lastQuery = $this->replacePlaceHolders($this->_query, $this->_bindParams);
  1575.  
  1576. if ($this->isSubQuery) {
  1577. return;
  1578. }
  1579.  
  1580. // Prepare query
  1581. $stmt = $this->_prepareQuery();
  1582.  
  1583. // Bind parameters to statement if any
  1584. if (count($this->_bindParams) > 1) {
  1585. call_user_func_array(array($stmt, 'bind_param'), $this->refValues($this->_bindParams));
  1586. }
  1587.  
  1588. return $stmt;
  1589. }
  1590.  
  1591. /**
  1592. * This helper method takes care of prepared statements' "bind_result method
  1593. * , when the number of variables to pass is unknown.
  1594. *
  1595. * @param mysqli_stmt $stmt Equal to the prepared statement object.
  1596. *
  1597. * @return array|string The results of the SQL fetch.
  1598. * @throws Exception
  1599. */
  1600. protected function _dynamicBindResults(mysqli_stmt $stmt)
  1601. {
  1602. $parameters = array();
  1603. $results = array();
  1604. /**
  1605. * @see http://php.net/manual/en/mysqli-result.fetch-fields.php
  1606. */
  1607. $mysqlLongType = 252;
  1608. $shouldStoreResult = false;
  1609.  
  1610. $meta = $stmt->result_metadata();
  1611.  
  1612. // if $meta is false yet sqlstate is true, there's no sql error but the query is
  1613. // most likely an update/insert/delete which doesn't produce any results
  1614. if (!$meta && $stmt->sqlstate)
  1615. return array();
  1616.  
  1617. $row = array();
  1618. while ($field = $meta->fetch_field()) {
  1619. if ($field->type == $mysqlLongType) {
  1620. $shouldStoreResult = true;
  1621. }
  1622.  
  1623. if ($this->_nestJoin && $field->table != $this->_tableName) {
  1624. $field->table = substr($field->table, strlen(self::$prefix));
  1625. $row[$field->table][$field->name] = null;
  1626. $parameters[] = & $row[$field->table][$field->name];
  1627. } else {
  1628. $row[$field->name] = null;
  1629. $parameters[] = & $row[$field->name];
  1630. }
  1631. }
  1632.  
  1633. // avoid out of memory bug in php 5.2 and 5.3. Mysqli allocates lot of memory for long*
  1634. // and blob* types. So to avoid out of memory issues store_result is used
  1635. // https://github.com/joshcam/PHP-MySQLi-Database-Class/pull/119
  1636. if ($shouldStoreResult) {
  1637. $stmt->store_result();
  1638. }
  1639.  
  1640. call_user_func_array(array($stmt, 'bind_result'), $parameters);
  1641.  
  1642. $this->totalCount = 0;
  1643. $this->count = 0;
  1644.  
  1645. while ($stmt->fetch()) {
  1646. if ($this->returnType == 'object') {
  1647. $result = new stdClass ();
  1648. foreach ($row as $key => $val) {
  1649. if (is_array($val)) {
  1650. $result->$key = new stdClass ();
  1651. foreach ($val as $k => $v) {
  1652. $result->$key->$k = $v;
  1653. }
  1654. } else {
  1655. $result->$key = $val;
  1656. }
  1657. }
  1658. } else {
  1659. $result = array();
  1660. foreach ($row as $key => $val) {
  1661. if (is_array($val)) {
  1662. foreach ($val as $k => $v) {
  1663. $result[$key][$k] = $v;
  1664. }
  1665. } else {
  1666. $result[$key] = $val;
  1667. }
  1668. }
  1669. }
  1670. $this->count++;
  1671. if ($this->_mapKey) {
  1672. $results[$row[$this->_mapKey]] = count($row) > 2 ? $result : end($result);
  1673. } else {
  1674. array_push($results, $result);
  1675. }
  1676. }
  1677.  
  1678. if ($shouldStoreResult) {
  1679. $stmt->free_result();
  1680. }
  1681.  
  1682. $stmt->close();
  1683.  
  1684. // stored procedures sometimes can return more then 1 resultset
  1685. if ($this->mysqli()->more_results()) {
  1686. $this->mysqli()->next_result();
  1687. }
  1688.  
  1689. if (in_array('SQL_CALC_FOUND_ROWS', $this->_queryOptions)) {
  1690. $stmt = $this->mysqli()->query('SELECT FOUND_ROWS()');
  1691. $totalCount = $stmt->fetch_row();
  1692. $this->totalCount = $totalCount[0];
  1693. }
  1694.  
  1695. if ($this->returnType == 'json') {
  1696. return json_encode($results);
  1697. }
  1698.  
  1699. return $results;
  1700. }
  1701.  
  1702. /**
  1703. * Abstraction method that will build an JOIN part of the query
  1704. *
  1705. * @return void
  1706. */
  1707. protected function _buildJoinOld()
  1708. {
  1709. if (empty($this->_join)) {
  1710. return;
  1711. }
  1712.  
  1713. foreach ($this->_join as $data) {
  1714. list ($joinType, $joinTable, $joinCondition) = $data;
  1715.  
  1716. if (is_object($joinTable)) {
  1717. $joinStr = $this->_buildPair("", $joinTable);
  1718. } else {
  1719. $joinStr = $joinTable;
  1720. }
  1721.  
  1722. $this->_query .= " " . $joinType . " JOIN " . $joinStr .
  1723. (false !== stripos($joinCondition, 'using') ? " " : " on ")
  1724. . $joinCondition;
  1725. }
  1726. }
  1727.  
  1728. /**
  1729. * Insert/Update query helper
  1730. *
  1731. * @param array $tableData
  1732. * @param array $tableColumns
  1733. * @param bool $isInsert INSERT operation flag
  1734. *
  1735. * @throws Exception
  1736. */
  1737. public function _buildDataPairs($tableData, $tableColumns, $isInsert)
  1738. {
  1739. foreach ($tableColumns as $column) {
  1740. $value = $tableData[$column];
  1741.  
  1742. if (!$isInsert) {
  1743. if(strpos($column,'.')===false) {
  1744. $this->_query .= "`" . $column . "` = ";
  1745. } else {
  1746. $this->_query .= str_replace('.','.`',$column) . "` = ";
  1747. }
  1748. }
  1749.  
  1750. // Subquery value
  1751. if ($value instanceof MysqliDb) {
  1752. $this->_query .= $this->_buildPair("", $value) . ", ";
  1753. continue;
  1754. }
  1755.  
  1756. // Simple value
  1757. if (!is_array($value)) {
  1758. $this->_bindParam($value);
  1759. $this->_query .= '?, ';
  1760. continue;
  1761. }
  1762.  
  1763. // Function value
  1764. $key = key($value);
  1765. $val = $value[$key];
  1766. switch ($key) {
  1767. case '[I]':
  1768. $this->_query .= $column . $val . ", ";
  1769. break;
  1770. case '[F]':
  1771. $this->_query .= $val[0] . ", ";
  1772. if (!empty($val[1])) {
  1773. $this->_bindParams($val[1]);
  1774. }
  1775. break;
  1776. case '[N]':
  1777. if ($val == null) {
  1778. $this->_query .= "!" . $column . ", ";
  1779. } else {
  1780. $this->_query .= "!" . $val . ", ";
  1781. }
  1782. break;
  1783. default:
  1784. throw new Exception("Wrong operation");
  1785. }
  1786. }
  1787. $this->_query = rtrim($this->_query, ', ');
  1788. }
  1789.  
  1790. /**
  1791. * Helper function to add variables into the query statement
  1792. *
  1793. * @param array $tableData Variable with values
  1794. *
  1795. * @throws Exception
  1796. */
  1797. protected function _buildOnDuplicate($tableData)
  1798. {
  1799. if (is_array($this->_updateColumns) && !empty($this->_updateColumns)) {
  1800. $this->_query .= " ON DUPLICATE KEY UPDATE ";
  1801. if ($this->_lastInsertId) {
  1802. $this->_query .= $this->_lastInsertId . "=LAST_INSERT_ID (" . $this->_lastInsertId . "), ";
  1803. }
  1804.  
  1805. foreach ($this->_updateColumns as $key => $val) {
  1806. // skip all params without a value
  1807. if (is_numeric($key)) {
  1808. $this->_updateColumns[$val] = '';
  1809. unset($this->_updateColumns[$key]);
  1810. } else {
  1811. $tableData[$key] = $val;
  1812. }
  1813. }
  1814. $this->_buildDataPairs($tableData, array_keys($this->_updateColumns), false);
  1815. }
  1816. }
  1817.  
  1818. /**
  1819. * Abstraction method that will build an INSERT or UPDATE part of the query
  1820. *
  1821. * @param array $tableData
  1822. *
  1823. * @throws Exception
  1824. */
  1825. protected function _buildInsertQuery($tableData)
  1826. {
  1827. if (!is_array($tableData)) {
  1828. return;
  1829. }
  1830.  
  1831. $isInsert = preg_match('/^[INSERT|REPLACE]/', $this->_query);
  1832. $dataColumns = array_keys($tableData);
  1833. if ($isInsert) {
  1834. if (isset ($dataColumns[0]))
  1835. $this->_query .= ' (`' . implode($dataColumns, '`, `') . '`) ';
  1836. $this->_query .= ' VALUES (';
  1837. } else {
  1838. $this->_query .= " SET ";
  1839. }
  1840.  
  1841. $this->_buildDataPairs($tableData, $dataColumns, $isInsert);
  1842.  
  1843. if ($isInsert) {
  1844. $this->_query .= ')';
  1845. }
  1846. }
  1847.  
  1848. /**
  1849. * Abstraction method that will build the part of the WHERE conditions
  1850. *
  1851. * @param string $operator
  1852. * @param array $conditions
  1853. */
  1854. protected function _buildCondition($operator, &$conditions)
  1855. {
  1856. if (empty($conditions)) {
  1857. return;
  1858. }
  1859.  
  1860. //Prepare the where portion of the query
  1861. $this->_query .= ' ' . $operator;
  1862.  
  1863. foreach ($conditions as $cond) {
  1864. list ($concat, $varName, $operator, $val) = $cond;
  1865. $this->_query .= " " . $concat . " " . $varName;
  1866.  
  1867. switch (strtolower($operator)) {
  1868. case 'not in':
  1869. case 'in':
  1870. $comparison = ' ' . $operator . ' (';
  1871. if (is_object($val)) {
  1872. $comparison .= $this->_buildPair("", $val);
  1873. } else {
  1874. foreach ($val as $v) {
  1875. $comparison .= ' ?,';
  1876. $this->_bindParam($v);
  1877. }
  1878. }
  1879. $this->_query .= rtrim($comparison, ',') . ' ) ';
  1880. break;
  1881. case 'not between':
  1882. case 'between':
  1883. $this->_query .= " $operator ? AND ? ";
  1884. $this->_bindParams($val);
  1885. break;
  1886. case 'not exists':
  1887. case 'exists':
  1888. $this->_query.= $operator . $this->_buildPair("", $val);
  1889. break;
  1890. default:
  1891. if (is_array($val)) {
  1892. $this->_bindParams($val);
  1893. } elseif ($val === null) {
  1894. $this->_query .= ' ' . $operator . " NULL";
  1895. } elseif ($val != 'DBNULL' || $val == '0') {
  1896. $this->_query .= $this->_buildPair($operator, $val);
  1897. }
  1898. }
  1899. }
  1900. }
  1901.  
  1902. /**
  1903. * Abstraction method that will build the GROUP BY part of the WHERE statement
  1904. *
  1905. * @return void
  1906. */
  1907. protected function _buildGroupBy()
  1908. {
  1909. if (empty($this->_groupBy)) {
  1910. return;
  1911. }
  1912.  
  1913. $this->_query .= " GROUP BY ";
  1914.  
  1915. foreach ($this->_groupBy as $key => $value) {
  1916. $this->_query .= $value . ", ";
  1917. }
  1918.  
  1919. $this->_query = rtrim($this->_query, ', ') . " ";
  1920. }
  1921.  
  1922. /**
  1923. * Abstraction method that will build the LIMIT part of the WHERE statement
  1924. *
  1925. * @return void
  1926. */
  1927. protected function _buildOrderBy()
  1928. {
  1929. if (empty($this->_orderBy)) {
  1930. return;
  1931. }
  1932.  
  1933. $this->_query .= " ORDER BY ";
  1934. foreach ($this->_orderBy as $prop => $value) {
  1935. if (strtolower(str_replace(" ", "", $prop)) == 'rand()') {
  1936. $this->_query .= "rand(), ";
  1937. } else {
  1938. $this->_query .= $prop . " " . $value . ", ";
  1939. }
  1940. }
  1941.  
  1942. $this->_query = rtrim($this->_query, ', ') . " ";
  1943. }
  1944.  
  1945. /**
  1946. * Abstraction method that will build the LIMIT part of the WHERE statement
  1947. *
  1948. * @param int|array $numRows Array to define SQL limit in format Array ($offset, $count)
  1949. * or only $count
  1950. *
  1951. * @return void
  1952. */
  1953. protected function _buildLimit($numRows)
  1954. {
  1955. if (!isset($numRows)) {
  1956. return;
  1957. }
  1958.  
  1959. if (is_array($numRows)) {
  1960. $this->_query .= ' LIMIT ' . (int) $numRows[0] . ', ' . (int) $numRows[1];
  1961. } else {
  1962. $this->_query .= ' LIMIT ' . (int) $numRows;
  1963. }
  1964. }
  1965.  
  1966. /**
  1967. * Method attempts to prepare the SQL query
  1968. * and throws an error if there was a problem.
  1969. *
  1970. * @return mysqli_stmt
  1971. * @throws Exception
  1972. */
  1973. protected function _prepareQuery()
  1974. {
  1975. $stmt = $this->mysqli()->prepare($this->_query);
  1976.  
  1977. if ($stmt !== false) {
  1978. if ($this->traceEnabled)
  1979. $this->traceStartQ = microtime(true);
  1980. return $stmt;
  1981. }
  1982.  
  1983. if ($this->mysqli()->errno === 2006 && $this->autoReconnect === true && $this->autoReconnectCount === 0) {
  1984. $this->connect($this->defConnectionName);
  1985. $this->autoReconnectCount++;
  1986. return $this->_prepareQuery();
  1987. }
  1988.  
  1989. $error = $this->mysqli()->error;
  1990. $query = $this->_query;
  1991. $errno = $this->mysqli()->errno;
  1992. $this->reset();
  1993. throw new Exception(sprintf('%s query: %s', $error, $query), $errno);
  1994. }
  1995.  
  1996. /**
  1997. * Referenced data array is required by mysqli since PHP 5.3+
  1998. *
  1999. * @param array $arr
  2000. *
  2001. * @return array
  2002. */
  2003. protected function refValues(array &$arr)
  2004. {
  2005. //Reference in the function arguments are required for HHVM to work
  2006. //https://github.com/facebook/hhvm/issues/5155
  2007. //Referenced data array is required by mysqli since PHP 5.3+
  2008. if (strnatcmp(phpversion(), '5.3') >= 0) {
  2009. $refs = array();
  2010. foreach ($arr as $key => $value) {
  2011. $refs[$key] = & $arr[$key];
  2012. }
  2013. return $refs;
  2014. }
  2015. return $arr;
  2016. }
  2017.  
  2018. /**
  2019. * Function to replace ? with variables from bind variable
  2020. *
  2021. * @param string $str
  2022. * @param array $vals
  2023. *
  2024. * @return string
  2025. */
  2026. protected function replacePlaceHolders($str, $vals)
  2027. {
  2028. $i = 1;
  2029. $newStr = "";
  2030.  
  2031. if (empty($vals)) {
  2032. return $str;
  2033. }
  2034.  
  2035. while ($pos = strpos($str, "?")) {
  2036. $val = $vals[$i++];
  2037. if (is_object($val)) {
  2038. $val = '[object]';
  2039. }
  2040. if ($val === null) {
  2041. $val = 'NULL';
  2042. }
  2043. $newStr .= substr($str, 0, $pos) . "'" . $val . "'";
  2044. $str = substr($str, $pos + 1);
  2045. }
  2046. $newStr .= $str;
  2047. return $newStr;
  2048. }
  2049.  
  2050. /**
  2051. * Method returns last executed query
  2052. *
  2053. * @return string
  2054. */
  2055. public function getLastQuery()
  2056. {
  2057. return $this->_lastQuery;
  2058. }
  2059.  
  2060. /**
  2061. * Method returns mysql error
  2062. *
  2063. * @return string
  2064. * @throws Exception
  2065. */
  2066. public function getLastError()
  2067. {
  2068. if (!isset($this->_mysqli[$this->defConnectionName])) {
  2069. return "mysqli is null";
  2070. }
  2071. return trim($this->_stmtError . " " . $this->mysqli()->error);
  2072. }
  2073.  
  2074. /**
  2075. * Method returns mysql error code
  2076. *
  2077. * @return int
  2078. */
  2079. public function getLastErrno () {
  2080. return $this->_stmtErrno;
  2081. }
  2082.  
  2083. /**
  2084. * Mostly internal method to get query and its params out of subquery object
  2085. * after get() and getAll()
  2086. *
  2087. * @return array
  2088. */
  2089. public function getSubQuery()
  2090. {
  2091. if (!$this->isSubQuery) {
  2092. return null;
  2093. }
  2094.  
  2095. array_shift($this->_bindParams);
  2096. $val = Array('query' => $this->_query,
  2097. 'params' => $this->_bindParams,
  2098. 'alias' => isset($this->connectionsSettings[$this->defConnectionName]) ? $this->connectionsSettings[$this->defConnectionName]['host'] : null
  2099. );
  2100. $this->reset();
  2101. return $val;
  2102. }
  2103.  
  2104. /* Helper functions */
  2105.  
  2106. /**
  2107. * Method returns generated interval function as a string
  2108. *
  2109. * @param string $diff interval in the formats:
  2110. * "1", "-1d" or "- 1 day" -- For interval - 1 day
  2111. * Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear
  2112. * Default null;
  2113. * @param string $func Initial date
  2114. *
  2115. * @return string
  2116. * @throws Exception
  2117. */
  2118. public function interval($diff, $func = "NOW()")
  2119. {
  2120. $types = Array("s" => "second", "m" => "minute", "h" => "hour", "d" => "day", "M" => "month", "Y" => "year");
  2121. $incr = '+';
  2122. $items = '';
  2123. $type = 'd';
  2124.  
  2125. if ($diff && preg_match('/([+-]?) ?([0-9]+) ?([a-zA-Z]?)/', $diff, $matches)) {
  2126. if (!empty($matches[1])) {
  2127. $incr = $matches[1];
  2128. }
  2129.  
  2130. if (!empty($matches[2])) {
  2131. $items = $matches[2];
  2132. }
  2133.  
  2134. if (!empty($matches[3])) {
  2135. $type = $matches[3];
  2136. }
  2137.  
  2138. if (!in_array($type, array_keys($types))) {
  2139. throw new Exception("invalid interval type in '{$diff}'");
  2140. }
  2141.  
  2142. $func .= " " . $incr . " interval " . $items . " " . $types[$type] . " ";
  2143. }
  2144. return $func;
  2145. }
  2146.  
  2147. /**
  2148. * Method returns generated interval function as an insert/update function
  2149. *
  2150. * @param string $diff interval in the formats:
  2151. * "1", "-1d" or "- 1 day" -- For interval - 1 day
  2152. * Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear
  2153. * Default null;
  2154. * @param string $func Initial date
  2155. *
  2156. * @return array
  2157. * @throws Exception
  2158. */
  2159. public function now($diff = null, $func = "NOW()")
  2160. {
  2161. return array("[F]" => Array($this->interval($diff, $func)));
  2162. }
  2163.  
  2164. /**
  2165. * Method generates incremental function call
  2166. *
  2167. * @param int $num increment by int or float. 1 by default
  2168. *
  2169. * @throws Exception
  2170. * @return array
  2171. */
  2172. public function inc($num = 1)
  2173. {
  2174. if (!is_numeric($num)) {
  2175. throw new Exception('Argument supplied to inc must be a number');
  2176. }
  2177. return array("[I]" => "+" . $num);
  2178. }
  2179.  
  2180. /**
  2181. * Method generates decremental function call
  2182. *
  2183. * @param int $num increment by int or float. 1 by default
  2184. *
  2185. * @return array
  2186. * @throws Exception
  2187. */
  2188. public function dec($num = 1)
  2189. {
  2190. if (!is_numeric($num)) {
  2191. throw new Exception('Argument supplied to dec must be a number');
  2192. }
  2193. return array("[I]" => "-" . $num);
  2194. }
  2195.  
  2196. /**
  2197. * Method generates change boolean function call
  2198. *
  2199. * @param string $col column name. null by default
  2200. *
  2201. * @return array
  2202. */
  2203. public function not($col = null)
  2204. {
  2205. return array("[N]" => (string)$col);
  2206. }
  2207.  
  2208. /**
  2209. * Method generates user defined function call
  2210. *
  2211. * @param string $expr user function body
  2212. * @param array $bindParams
  2213. *
  2214. * @return array
  2215. */
  2216. public function func($expr, $bindParams = null)
  2217. {
  2218. return array("[F]" => array($expr, $bindParams));
  2219. }
  2220.  
  2221. /**
  2222. * Method creates new mysqlidb object for a subquery generation
  2223. *
  2224. * @param string $subQueryAlias
  2225. *
  2226. * @return MysqliDb
  2227. */
  2228. public static function subQuery($subQueryAlias = "")
  2229. {
  2230. return new self(array('host' => $subQueryAlias, 'isSubQuery' => true));
  2231. }
  2232.  
  2233. /**
  2234. * Method returns a copy of a mysqlidb subquery object
  2235. *
  2236. * @return MysqliDb new mysqlidb object
  2237. */
  2238. public function copy()
  2239. {
  2240. $copy = unserialize(serialize($this));
  2241. $copy->_mysqli = array();
  2242. return $copy;
  2243. }
  2244.  
  2245. /**
  2246. * Begin a transaction
  2247. *
  2248. * @uses mysqli->autocommit(false)
  2249. * @uses register_shutdown_function(array($this, "_transaction_shutdown_check"))
  2250. * @throws Exception
  2251. */
  2252. public function startTransaction()
  2253. {
  2254. $this->mysqli()->autocommit(false);
  2255. $this->_transaction_in_progress = true;
  2256. register_shutdown_function(array($this, "_transaction_status_check"));
  2257. }
  2258.  
  2259. /**
  2260. * Transaction commit
  2261. *
  2262. * @uses mysqli->commit();
  2263. * @uses mysqli->autocommit(true);
  2264. * @throws Exception
  2265. */
  2266. public function commit()
  2267. {
  2268. $result = $this->mysqli()->commit();
  2269. $this->_transaction_in_progress = false;
  2270. $this->mysqli()->autocommit(true);
  2271. return $result;
  2272. }
  2273.  
  2274. /**
  2275. * Transaction rollback function
  2276. *
  2277. * @uses mysqli->rollback();
  2278. * @uses mysqli->autocommit(true);
  2279. * @throws Exception
  2280. */
  2281. public function rollback()
  2282. {
  2283. $result = $this->mysqli()->rollback();
  2284. $this->_transaction_in_progress = false;
  2285. $this->mysqli()->autocommit(true);
  2286. return $result;
  2287. }
  2288.  
  2289. /**
  2290. * Shutdown handler to rollback uncommited operations in order to keep
  2291. * atomic operations sane.
  2292. *
  2293. * @uses mysqli->rollback();
  2294. * @throws Exception
  2295. */
  2296. public function _transaction_status_check()
  2297. {
  2298. if (!$this->_transaction_in_progress) {
  2299. return;
  2300. }
  2301. $this->rollback();
  2302. }
  2303.  
  2304. /**
  2305. * Query execution time tracking switch
  2306. *
  2307. * @param bool $enabled Enable execution time tracking
  2308. * @param string $stripPrefix Prefix to strip from the path in exec log
  2309. *
  2310. * @return MysqliDb
  2311. */
  2312. public function setTrace($enabled, $stripPrefix = null)
  2313. {
  2314. $this->traceEnabled = $enabled;
  2315. $this->traceStripPrefix = $stripPrefix;
  2316. return $this;
  2317. }
  2318.  
  2319. /**
  2320. * Get where and what function was called for query stored in MysqliDB->trace
  2321. *
  2322. * @return string with information
  2323. */
  2324. private function _traceGetCaller()
  2325. {
  2326. $dd = debug_backtrace();
  2327. $caller = next($dd);
  2328. while (isset($caller) && $caller["file"] == __FILE__) {
  2329. $caller = next($dd);
  2330. }
  2331.  
  2332. return __CLASS__ . "->" . $caller["function"] . "() >> file \"" .
  2333. str_replace($this->traceStripPrefix, '', $caller["file"]) . "\" line #" . $caller["line"] . " ";
  2334. }
  2335.  
  2336. /**
  2337. * Method to check if needed table is created
  2338. *
  2339. * @param array $tables Table name or an Array of table names to check
  2340. *
  2341. * @return bool True if table exists
  2342. * @throws Exception
  2343. */
  2344. public function tableExists($tables)
  2345. {
  2346. $tables = !is_array($tables) ? Array($tables) : $tables;
  2347. $count = count($tables);
  2348. if ($count == 0) {
  2349. return false;
  2350. }
  2351.  
  2352. foreach ($tables as $i => $value)
  2353. $tables[$i] = self::$prefix . $value;
  2354. $db = isset($this->connectionsSettings[$this->defConnectionName]) ? $this->connectionsSettings[$this->defConnectionName]['db'] : null;
  2355. $this->where('table_schema', $db);
  2356. $this->where('table_name', $tables, 'in');
  2357. $this->get('information_schema.tables', $count);
  2358. return $this->count == $count;
  2359. }
  2360.  
  2361. /**
  2362. * Return result as an associative array with $idField field value used as a record key
  2363. *
  2364. * Array Returns an array($k => $v) if get(.."param1, param2"), array ($k => array ($v, $v)) otherwise
  2365. *
  2366. * @param string $idField field name to use for a mapped element key
  2367. *
  2368. * @return MysqliDb
  2369. */
  2370. public function map($idField)
  2371. {
  2372. $this->_mapKey = $idField;
  2373. return $this;
  2374. }
  2375.  
  2376. /**
  2377. * Pagination wrapper to get()
  2378. *
  2379. * @access public
  2380. *
  2381. * @param string $table The name of the database table to work with
  2382. * @param int $page Page number
  2383. * @param array|string $fields Array or coma separated list of fields to fetch
  2384. *
  2385. * @return array
  2386. * @throws Exception
  2387. */
  2388. public function paginate ($table, $page, $fields = null) {
  2389. $offset = $this->pageLimit * ($page - 1);
  2390. $res = $this->withTotalCount()->get ($table, Array ($offset, $this->pageLimit), $fields);
  2391. $this->totalPages = ceil($this->totalCount / $this->pageLimit);
  2392. return $res;
  2393. }
  2394.  
  2395. /**
  2396. * This method allows you to specify multiple (method chaining optional) AND WHERE statements for the join table on part of the SQL query.
  2397. *
  2398. * @uses $dbWrapper->joinWhere('user u', 'u.id', 7)->where('user u', 'u.title', 'MyTitle');
  2399. *
  2400. * @param string $whereJoin The name of the table followed by its prefix.
  2401. * @param string $whereProp The name of the database field.
  2402. * @param mixed $whereValue The value of the database field.
  2403. *
  2404. * @param string $operator
  2405. * @param string $cond
  2406. *
  2407. * @return $this
  2408. */
  2409. public function joinWhere($whereJoin, $whereProp, $whereValue = 'DBNULL', $operator = '=', $cond = 'AND')
  2410. {
  2411. $this->_joinAnd[$whereJoin][] = Array ($cond, $whereProp, $operator, $whereValue);
  2412. return $this;
  2413. }
  2414.  
  2415. /**
  2416. * This method allows you to specify multiple (method chaining optional) OR WHERE statements for the join table on part of the SQL query.
  2417. *
  2418. * @uses $dbWrapper->joinWhere('user u', 'u.id', 7)->where('user u', 'u.title', 'MyTitle');
  2419. *
  2420. * @param string $whereJoin The name of the table followed by its prefix.
  2421. * @param string $whereProp The name of the database field.
  2422. * @param mixed $whereValue The value of the database field.
  2423. * @param string $operator
  2424. *
  2425. * @return $this
  2426. */
  2427. public function joinOrWhere($whereJoin, $whereProp, $whereValue = 'DBNULL', $operator = '=', $cond = 'AND')
  2428. {
  2429. return $this->joinWhere($whereJoin, $whereProp, $whereValue, $operator, 'OR');
  2430. }
  2431.  
  2432. /**
  2433. * Abstraction method that will build an JOIN part of the query
  2434. */
  2435. protected function _buildJoin () {
  2436. if (empty ($this->_join))
  2437. return;
  2438.  
  2439. foreach ($this->_join as $data) {
  2440. list ($joinType, $joinTable, $joinCondition) = $data;
  2441.  
  2442. if (is_object ($joinTable))
  2443. $joinStr = $this->_buildPair ("", $joinTable);
  2444. else
  2445. $joinStr = $joinTable;
  2446.  
  2447. $this->_query .= " " . $joinType. " JOIN " . $joinStr .
  2448. (false !== stripos($joinCondition, 'using') ? " " : " on ")
  2449. . $joinCondition;
  2450.  
  2451. // Add join and query
  2452. if (!empty($this->_joinAnd) && isset($this->_joinAnd[$joinStr])) {
  2453. foreach($this->_joinAnd[$joinStr] as $join_and_cond) {
  2454. list ($concat, $varName, $operator, $val) = $join_and_cond;
  2455. $this->_query .= " " . $concat ." " . $varName;
  2456. $this->conditionToSql($operator, $val);
  2457. }
  2458. }
  2459. }
  2460. }
  2461.  
  2462. /**
  2463. * Convert a condition and value into the sql string
  2464. *
  2465. * @param String $operator The where constraint operator
  2466. * @param String|array $val The where constraint value
  2467. */
  2468. private function conditionToSql($operator, $val) {
  2469. switch (strtolower ($operator)) {
  2470. case 'not in':
  2471. case 'in':
  2472. $comparison = ' ' . $operator. ' (';
  2473. if (is_object ($val)) {
  2474. $comparison .= $this->_buildPair ("", $val);
  2475. } else {
  2476. foreach ($val as $v) {
  2477. $comparison .= ' ?,';
  2478. $this->_bindParam ($v);
  2479. }
  2480. }
  2481. $this->_query .= rtrim($comparison, ',').' ) ';
  2482. break;
  2483. case 'not between':
  2484. case 'between':
  2485. $this->_query .= " $operator ? AND ? ";
  2486. $this->_bindParams ($val);
  2487. break;
  2488. case 'not exists':
  2489. case 'exists':
  2490. $this->_query.= $operator . $this->_buildPair ("", $val);
  2491. break;
  2492. default:
  2493. if (is_array ($val))
  2494. $this->_bindParams ($val);
  2495. else if ($val === null)
  2496. $this->_query .= $operator . " NULL";
  2497. else if ($val != 'DBNULL' || $val == '0')
  2498. $this->_query .= $this->_buildPair ($operator, $val);
  2499. }
  2500. }
  2501. }
  2502.  
  2503. // END class
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement