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