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