· 11 years ago · Sep 30, 2015, 10:50 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
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.4
14 **/
15class MysqliDb
16{
17 /**
18 * Static instance of self
19 *
20 * @var MysqliDb
21 */
22 protected static $_instance;
23 /**
24 * Table prefix
25 *
26 * @var string
27 */
28 public static $prefix = '';
29 /**
30 * MySQLi instance
31 *
32 * @var mysqli
33 */
34 protected $_mysqli;
35 /**
36 * The SQL query to be prepared and executed
37 *
38 * @var string
39 */
40 protected $_query;
41 /**
42 * The previously executed SQL query
43 *
44 * @var string
45 */
46 protected $_lastQuery;
47 /**
48 * The SQL query options required after SELECT, INSERT, UPDATE or DELETE
49 *
50 * @var string
51 */
52 protected $_queryOptions = array();
53 /**
54 * An array that holds where joins
55 *
56 * @var array
57 */
58 protected $_join = array();
59 /**
60 * An array that holds where conditions 'fieldname' => 'value'
61 *
62 * @var array
63 */
64 protected $_where = array();
65 /**
66 * Dynamic type list for order by condition value
67 */
68 protected $_orderBy = array();
69 /**
70 * Dynamic type list for group by condition value
71 */
72 protected $_groupBy = array();
73 /**
74 * Dynamic array that holds a combination of where condition/table data value types and parameter references
75 *
76 * @var array
77 */
78 protected $_bindParams = array(''); // Create the empty 0 index
79 /**
80 * Variable which holds an amount of returned rows during get/getOne/select queries
81 *
82 * @var string
83 */
84 public $count = 0;
85 /**
86 * Variable which holds an amount of returned rows during get/getOne/select queries with withTotalCount()
87 *
88 * @var string
89 */
90 public $totalCount = 0;
91 /**
92 * Variable which holds last statement error
93 *
94 * @var string
95 */
96 protected $_stmtError;
97
98 /**
99 * Database credentials
100 *
101 * @var string
102 */
103 protected $host;
104 protected $username;
105 protected $password;
106 protected $db;
107 protected $port;
108 protected $charset;
109
110 /**
111 * Is Subquery object
112 *
113 */
114 protected $isSubQuery = false;
115
116 /**
117 * Name of the auto increment column
118 *
119 */
120 protected $_lastInsertId = null;
121
122 /**
123 * Column names for update when using onDuplicate method
124 *
125 */
126 protected $_updateColumns = null;
127
128 /**
129 * Return type: 'Array' to return results as array, 'Object' as object
130 * 'Json' as json string
131 *
132 * @var string
133 */
134 public $returnType = 'Array';
135
136 /**
137 * Should join() results be nested by table
138 * @var boolean
139 */
140 protected $_nestJoin = false;
141 private $_tableName = '';
142
143 /**
144 * FOR UPDATE flag
145 * @var boolean
146 */
147 protected $_forUpdate = false;
148
149 /**
150 * LOCK IN SHARE MODE flag
151 * @var boolean
152 */
153 protected $_lockInShareMode = false;
154
155 /**
156 * Variables for query execution tracing
157 *
158 */
159 protected $traceStartQ;
160 protected $traceEnabled;
161 protected $traceStripPrefix;
162 public $trace = array();
163
164 /**
165 * @param string $host
166 * @param string $username
167 * @param string $password
168 * @param string $db
169 * @param int $port
170 */
171 public function __construct($host = NULL, $username = NULL, $password = NULL, $db = NULL, $port = NULL, $charset = 'utf8',$prefix = NULL)
172 {
173 $isSubQuery = false;
174
175 // if params were passed as array
176 if (is_array ($host)) {
177 foreach ($host as $key => $val)
178 $$key = $val;
179 }
180 // if host were set as mysqli socket
181 if (is_object ($host))
182 $this->_mysqli = $host;
183 else
184 $this->host = $host;
185
186 $this->username = $username;
187 $this->password = $password;
188 $this->db = $db;
189 $this->port = $port;
190 $this->charset = $charset;
191 $this->prefix = $prefix;
192
193 if ($isSubQuery) {
194 $this->isSubQuery = true;
195 return;
196 }
197 if (isset ($prefix))
198 $this->setPrefix ($prefix);
199 self::$_instance = $this;
200 }
201
202 /**
203 * A method to connect to the database
204 *
205 */
206 public function connect()
207 {
208 if ($this->isSubQuery)
209 return;
210
211 if (empty ($this->host))
212 die ('Mysql host is not set');
213
214 $this->_mysqli = new mysqli ($this->host, $this->username, $this->password, $this->db, $this->port);
215 if ($this->_mysqli->connect_error)
216 throw new Exception ('Connect Error ' . $this->_mysqli->connect_errno . ': ' . $this->_mysqli->connect_error);
217
218 if ($this->charset)
219 $this->_mysqli->set_charset ($this->charset);
220 }
221
222 /**
223 * A method to get mysqli object or create it in case needed
224 */
225 public function mysqli ()
226 {
227 if (!$this->_mysqli)
228 $this->connect();
229 return $this->_mysqli;
230 }
231
232 /**
233 * A method of returning the static instance to allow access to the
234 * instantiated object from within another class.
235 * Inheriting this class would require reloading connection info.
236 *
237 * @uses $db = MySqliDb::getInstance();
238 *
239 * @return object Returns the current instance.
240 */
241 public static function getInstance()
242 {
243 return self::$_instance;
244 }
245
246 /**
247 * Reset states after an execution
248 *
249 * @return object Returns the current instance.
250 */
251 protected function reset()
252 {
253 if ($this->traceEnabled)
254 $this->trace[] = array ($this->_lastQuery, (microtime(true) - $this->traceStartQ) , $this->_traceGetCaller());
255
256 $this->_where = array();
257 $this->_join = array();
258 $this->_orderBy = array();
259 $this->_groupBy = array();
260 $this->_bindParams = array(''); // Create the empty 0 index
261 $this->_query = null;
262 $this->_queryOptions = array();
263 $this->returnType = 'Array';
264 $this->_nestJoin = false;
265 $this->_forUpdate = false;
266 $this->_lockInShareMode = false;
267 $this->_tableName = '';
268 $this->_lastInsertId = null;
269 $this->_updateColumns = null;
270 }
271
272 /**
273 * Helper function to create dbObject with Json return type
274 *
275 * @return dbObject
276 */
277 public function JsonBuilder () {
278 $this->returnType = 'Json';
279 return $this;
280 }
281
282 /**
283 * Helper function to create dbObject with Array return type
284 * Added for consistency as thats default output type
285 *
286 * @return dbObject
287 */
288 public function ArrayBuilder () {
289 $this->returnType = 'Array';
290 return $this;
291 }
292
293 /**
294 * Helper function to create dbObject with Object return type.
295 *
296 * @return dbObject
297 */
298 public function ObjectBuilder () {
299 $this->returnType = 'Object';
300 return $this;
301 }
302
303 /**
304 * Method to set a prefix
305 *
306 * @param string $prefix Contains a tableprefix
307 */
308 public function setPrefix($prefix = '')
309 {
310 self::$prefix = $prefix;
311 return $this;
312 }
313
314 /**
315 * Execute raw SQL query.
316 *
317 * @param string $query User-provided query to execute.
318 * @param array $bindParams Variables array to bind to the SQL statement.
319 *
320 * @return array Contains the returned rows from the query.
321 */
322 public function rawQuery ($query, $bindParams = null)
323 {
324 $params = array(''); // Create the empty 0 index
325 $this->_query = $query;
326 $stmt = $this->_prepareQuery();
327
328 if (is_array ($bindParams) === true) {
329 foreach ($bindParams as $prop => $val) {
330 $params[0] .= $this->_determineType($val);
331 array_push($params, $bindParams[$prop]);
332 }
333
334 call_user_func_array(array($stmt, 'bind_param'), $this->refValues($params));
335
336 }
337
338 $stmt->execute();
339 $this->count = $stmt->affected_rows;
340 $this->_stmtError = $stmt->error;
341 $this->_lastQuery = $this->replacePlaceHolders ($this->_query, $params);
342 $res = $this->_dynamicBindResults($stmt);
343 $this->reset();
344
345 return $res;
346 }
347
348 /**
349 * Helper function to execute raw SQL query and return only 1 row of results.
350 * Note that function do not add 'limit 1' to the query by itself
351 * Same idea as getOne()
352 *
353 * @param string $query User-provided query to execute.
354 * @param array $bindParams Variables array to bind to the SQL statement.
355 *
356 * @return array Contains the returned row from the query.
357 */
358 public function rawQueryOne ($query, $bindParams = null) {
359 $res = $this->rawQuery ($query, $bindParams);
360 if (is_array ($res) && isset ($res[0]))
361 return $res[0];
362
363 return null;
364 }
365
366 /**
367 * Helper function to execute raw SQL query and return only 1 column of results.
368 * If 'limit 1' will be found, then string will be returned instead of array
369 * Same idea as getValue()
370 *
371 * @param string $query User-provided query to execute.
372 * @param array $bindParams Variables array to bind to the SQL statement.
373 *
374 * @return mixed Contains the returned rows from the query.
375 */
376 public function rawQueryValue ($query, $bindParams = null) {
377 $res = $this->rawQuery ($query, $bindParams);
378 if (!$res)
379 return null;
380
381 $limit = preg_match ('/limit\s+1;?$/i', $query);
382 $key = key ($res[0]);
383 if (isset($res[0][$key]) && $limit == true)
384 return $res[0][$key];
385
386 $newRes = Array ();
387 for ($i = 0; $i < $this->count; $i++)
388 $newRes[] = $res[$i][$key];
389 return $newRes;
390 }
391 /**
392 *
393 * @param string $query Contains a user-provided select query.
394 * @param integer|array $numRows Array to define SQL limit in format Array ($count, $offset)
395 *
396 * @return array Contains the returned rows from the query.
397 */
398 public function query($query, $numRows = null)
399 {
400 $this->_query = $query;
401 $stmt = $this->_buildQuery($numRows);
402 $stmt->execute();
403 $this->_stmtError = $stmt->error;
404 $res = $this->_dynamicBindResults($stmt);
405 $this->reset();
406
407 return $res;
408 }
409
410 /**
411 * This method allows you to specify multiple (method chaining optional) options for SQL queries.
412 *
413 * @uses $MySqliDb->setQueryOption('name');
414 *
415 * @param string/array $options The optons name of the query.
416 *
417 * @return MysqliDb
418 */
419 public function setQueryOption ($options) {
420 $allowedOptions = Array ('ALL','DISTINCT','DISTINCTROW','HIGH_PRIORITY','STRAIGHT_JOIN','SQL_SMALL_RESULT',
421 'SQL_BIG_RESULT','SQL_BUFFER_RESULT','SQL_CACHE','SQL_NO_CACHE', 'SQL_CALC_FOUND_ROWS',
422 'LOW_PRIORITY','IGNORE','QUICK', 'MYSQLI_NESTJOIN', 'FOR UPDATE', 'LOCK IN SHARE MODE');
423 if (!is_array ($options))
424 $options = Array ($options);
425
426 foreach ($options as $option) {
427 $option = strtoupper ($option);
428 if (!in_array ($option, $allowedOptions))
429 die ('Wrong query option: '.$option);
430
431 if ($option == 'MYSQLI_NESTJOIN')
432 $this->_nestJoin = true;
433 else if ($option == 'FOR UPDATE')
434 $this->_forUpdate = true;
435 else if ($option == 'LOCK IN SHARE MODE')
436 $this->_lockInShareMode = true;
437 else
438 $this->_queryOptions[] = $option;
439 }
440
441 return $this;
442 }
443
444 /**
445 * Function to enable SQL_CALC_FOUND_ROWS in the get queries
446 *
447 * @return MysqliDb
448 */
449 public function withTotalCount () {
450 $this->setQueryOption ('SQL_CALC_FOUND_ROWS');
451 return $this;
452 }
453
454 /**
455 * A convenient SELECT * function.
456 *
457 * @param string $tableName The name of the database table to work with.
458 * @param integer|array $numRows Array to define SQL limit in format Array ($count, $offset)
459 * or only $count
460 *
461 * @return array Contains the returned rows from the select query.
462 */
463 public function get($tableName, $numRows = null, $columns = '*')
464 {
465 if (empty ($columns))
466 $columns = '*';
467
468 $column = is_array($columns) ? implode(', ', $columns) : $columns;
469 if (strpos ($tableName, '.') === false)
470 $this->_tableName = self::$prefix . $tableName;
471 else
472 $this->_tableName = $tableName;
473
474 $this->_query = 'SELECT ' . implode(' ', $this->_queryOptions) . ' ' .
475 $column . " FROM " . $this->_tableName;
476 $stmt = $this->_buildQuery($numRows);
477
478 if ($this->isSubQuery)
479 return $this;
480
481 $stmt->execute();
482 $this->_stmtError = $stmt->error;
483 $res = $this->_dynamicBindResults($stmt);
484 $this->reset();
485
486 return $res;
487 }
488
489 /**
490 * A convenient SELECT * function to get one record.
491 *
492 * @param string $tableName The name of the database table to work with.
493 *
494 * @return array Contains the returned rows from the select query.
495 */
496 public function getOne($tableName, $columns = '*')
497 {
498 $res = $this->get ($tableName, 1, $columns);
499
500 if ($res instanceof MysqliDb)
501 return $res;
502 else if (is_array ($res) && isset ($res[0]))
503 return $res[0];
504 else if ($res)
505 return $res;
506
507 return null;
508 }
509
510 /**
511 * A convenient SELECT COLUMN function to get a single column value from one row
512 *
513 * @param string $tableName The name of the database table to work with.
514 * @param int $limit Limit of rows to select. Use null for unlimited..1 by default
515 *
516 * @return mixed Contains the value of a returned column / array of values
517 */
518 public function getValue ($tableName, $column, $limit = 1)
519 {
520 $res = $this->ArrayBuilder()->get ($tableName, $limit, "{$column} AS retval");
521
522 if (!$res)
523 return null;
524
525 if ($limit == 1) {
526 if (isset ($res[0]["retval"]))
527 return $res[0]["retval"];
528 return null;
529 }
530
531 $newRes = Array ();
532 for ($i = 0; $i < $this->count; $i++)
533 $newRes[] = $res[$i]['retval'];
534 return $newRes;
535 }
536
537 /**
538 * Insert method to add new row
539 *
540 * @param <string $tableName The name of the table.
541 * @param array $insertData Data containing information for inserting into the DB.
542 *
543 * @return boolean Boolean indicating whether the insert query was completed succesfully.
544 */
545 public function insert ($tableName, $insertData) {
546 return $this->_buildInsert ($tableName, $insertData, 'INSERT');
547 }
548
549 /**
550 * Replace method to add new row
551 *
552 * @param <string $tableName The name of the table.
553 * @param array $insertData Data containing information for inserting into the DB.
554 *
555 * @return boolean Boolean indicating whether the insert query was completed succesfully.
556 */
557 public function replace ($tableName, $insertData) {
558 return $this->_buildInsert ($tableName, $insertData, 'REPLACE');
559 }
560
561 /**
562 * A convenient function that returns TRUE if exists at least an element that
563 * satisfy the where condition specified calling the "where" method before this one.
564 *
565 * @param string $tableName The name of the database table to work with.
566 *
567 * @return array Contains the returned rows from the select query.
568 */
569 public function has($tableName)
570 {
571 $this->getOne($tableName, '1');
572 return $this->count >= 1;
573 }
574
575 /**
576 * Update query. Be sure to first call the "where" method.
577 *
578 * @param string $tableName The name of the database table to work with.
579 * @param array $tableData Array of data to update the desired row.
580 *
581 * @return boolean
582 */
583 public function update($tableName, $tableData)
584 {
585 if ($this->isSubQuery)
586 return;
587
588 $this->_query = "UPDATE " . self::$prefix . $tableName;
589
590 $stmt = $this->_buildQuery (null, $tableData);
591 $status = $stmt->execute();
592 $this->reset();
593 $this->_stmtError = $stmt->error;
594 $this->count = $stmt->affected_rows;
595
596 return $status;
597 }
598
599 /**
600 * Delete query. Call the "where" method first.
601 *
602 * @param string $tableName The name of the database table to work with.
603 * @param integer|array $numRows Array to define SQL limit in format Array ($count, $offset)
604 * or only $count
605 *
606 * @return boolean Indicates success. 0 or 1.
607 */
608 public function delete($tableName, $numRows = null)
609 {
610 if ($this->isSubQuery)
611 return;
612
613 $table = self::$prefix . $tableName;
614 if (count ($this->_join))
615 $this->_query = "DELETE " . preg_replace ('/.* (.*)/', '$1', $table) . " FROM " . $table;
616 else
617 $this->_query = "DELETE FROM " . $table;
618
619 $stmt = $this->_buildQuery($numRows);
620 $stmt->execute();
621 $this->_stmtError = $stmt->error;
622 $this->reset();
623
624 return ($stmt->affected_rows > 0);
625 }
626
627 /**
628 * This method allows you to specify multiple (method chaining optional) AND WHERE statements for SQL queries.
629 *
630 * @uses $MySqliDb->where('id', 7)->where('title', 'MyTitle');
631 *
632 * @param string $whereProp The name of the database field.
633 * @param mixed $whereValue The value of the database field.
634 *
635 * @return MysqliDb
636 */
637 public function where($whereProp, $whereValue = 'DBNULL', $operator = '=', $cond = 'AND')
638 {
639 // forkaround for an old operation api
640 if (is_array ($whereValue) && ($key = key ($whereValue)) != "0") {
641 $operator = $key;
642 $whereValue = $whereValue[$key];
643 }
644 if (count ($this->_where) == 0)
645 $cond = '';
646 $this->_where[] = Array ($cond, $whereProp, $operator, $whereValue);
647 return $this;
648 }
649
650 /**
651 * This function store update column's name and column name of the
652 * autoincrement column
653 *
654 * @param Array Variable with values
655 * @param String Variable value
656 */
657 public function onDuplicate($_updateColumns, $_lastInsertId = null)
658 {
659 $this->_lastInsertId = $_lastInsertId;
660 $this->_updateColumns = $_updateColumns;
661 return $this;
662 }
663
664 /**
665 * This method allows you to specify multiple (method chaining optional) OR WHERE statements for SQL queries.
666 *
667 * @uses $MySqliDb->orWhere('id', 7)->orWhere('title', 'MyTitle');
668 *
669 * @param string $whereProp The name of the database field.
670 * @param mixed $whereValue The value of the database field.
671 *
672 * @return MysqliDb
673 */
674 public function orWhere($whereProp, $whereValue = 'DBNULL', $operator = '=')
675 {
676 return $this->where ($whereProp, $whereValue, $operator, 'OR');
677 }
678 /**
679 * This method allows you to concatenate joins for the final SQL statement.
680 *
681 * @uses $MySqliDb->join('table1', 'field1 <> field2', 'LEFT')
682 *
683 * @param string $joinTable The name of the table.
684 * @param string $joinCondition the condition.
685 * @param string $joinType 'LEFT', 'INNER' etc.
686 *
687 * @return MysqliDb
688 */
689 public function join($joinTable, $joinCondition, $joinType = '')
690 {
691 $allowedTypes = array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER');
692 $joinType = strtoupper (trim ($joinType));
693
694 if ($joinType && !in_array ($joinType, $allowedTypes))
695 die ('Wrong JOIN type: '.$joinType);
696
697 if (!is_object ($joinTable))
698 $joinTable = self::$prefix . $joinTable;
699
700 $this->_join[] = Array ($joinType, $joinTable, $joinCondition);
701
702 return $this;
703 }
704 /**
705 * This method allows you to specify multiple (method chaining optional) ORDER BY statements for SQL queries.
706 *
707 * @uses $MySqliDb->orderBy('id', 'desc')->orderBy('name', 'desc');
708 *
709 * @param string $orderByField The name of the database field.
710 * @param string $orderByDirection Order direction.
711 *
712 * @return MysqliDb
713 */
714 public function orderBy($orderByField, $orderbyDirection = "DESC", $customFields = null)
715 {
716 $allowedDirection = Array ("ASC", "DESC");
717 $orderbyDirection = strtoupper (trim ($orderbyDirection));
718 $orderByField = preg_replace ("/[^-a-z0-9\.\(\),_`\*]+/i",'', $orderByField);
719
720 // Add table prefix to orderByField if needed.
721 //FIXME: We are adding prefix only if table is enclosed into `` to distinguish aliases
722 // from table names
723 $orderByField = preg_replace('/(\`)([`a-zA-Z0-9_]*\.)/', '\1' . self::$prefix. '\2', $orderByField);
724
725
726 if (empty($orderbyDirection) || !in_array ($orderbyDirection, $allowedDirection))
727 die ('Wrong order direction: '.$orderbyDirection);
728
729 if (is_array ($customFields)) {
730 foreach ($customFields as $key => $value)
731 $customFields[$key] = preg_replace ("/[^-a-z0-9\.\(\),_`]+/i",'', $value);
732
733 $orderByField = 'FIELD (' . $orderByField . ', "' . implode('","', $customFields) . '")';
734 }
735
736 $this->_orderBy[$orderByField] = $orderbyDirection;
737 return $this;
738 }
739
740 /**
741 * This method allows you to specify multiple (method chaining optional) GROUP BY statements for SQL queries.
742 *
743 * @uses $MySqliDb->groupBy('name');
744 *
745 * @param string $groupByField The name of the database field.
746 *
747 * @return MysqliDb
748 */
749 public function groupBy($groupByField)
750 {
751 $groupByField = preg_replace ("/[^-a-z0-9\.\(\),_\*]+/i",'', $groupByField);
752
753 $this->_groupBy[] = $groupByField;
754 return $this;
755 }
756
757 /**
758 * This methods returns the ID of the last inserted item
759 *
760 * @return integer The last inserted item ID.
761 */
762 public function getInsertId()
763 {
764 return $this->mysqli()->insert_id;
765 }
766
767 /**
768 * Escape harmful characters which might affect a query.
769 *
770 * @param string $str The string to escape.
771 *
772 * @return string The escaped string.
773 */
774 public function escape($str)
775 {
776 return $this->mysqli()->real_escape_string($str);
777 }
778
779 /**
780 * Method to call mysqli->ping() to keep unused connections open on
781 * long-running scripts, or to reconnect timed out connections (if php.ini has
782 * global mysqli.reconnect set to true). Can't do this directly using object
783 * since _mysqli is protected.
784 *
785 * @return bool True if connection is up
786 */
787 public function ping() {
788 return $this->mysqli()->ping();
789 }
790
791 /**
792 * This method is needed for prepared statements. They require
793 * the data type of the field to be bound with "i" s", etc.
794 * This function takes the input, determines what type it is,
795 * and then updates the param_type.
796 *
797 * @param mixed $item Input to determine the type.
798 *
799 * @return string The joined parameter types.
800 */
801 protected function _determineType($item)
802 {
803 switch (gettype($item)) {
804 case 'NULL':
805 case 'string':
806 return 's';
807 break;
808
809 case 'boolean':
810 case 'integer':
811 return 'i';
812 break;
813
814 case 'blob':
815 return 'b';
816 break;
817
818 case 'double':
819 return 'd';
820 break;
821 }
822 return '';
823 }
824
825 /**
826 * Helper function to add variables into bind parameters array
827 *
828 * @param string Variable value
829 */
830 protected function _bindParam($value) {
831 $this->_bindParams[0] .= $this->_determineType ($value);
832 array_push ($this->_bindParams, $value);
833 }
834
835 /**
836 * Helper function to add variables into bind parameters array in bulk
837 *
838 * @param Array Variable with values
839 */
840 protected function _bindParams ($values) {
841 foreach ($values as $value)
842 $this->_bindParam ($value);
843 }
844
845 /**
846 * Helper function to add variables into bind parameters array and will return
847 * its SQL part of the query according to operator in ' $operator ?' or
848 * ' $operator ($subquery) ' formats
849 *
850 * @param Array Variable with values
851 */
852 protected function _buildPair ($operator, $value) {
853 if (!is_object($value)) {
854 $this->_bindParam ($value);
855 return ' ' . $operator. ' ? ';
856 }
857
858 $subQuery = $value->getSubQuery ();
859 $this->_bindParams ($subQuery['params']);
860
861 return " " . $operator . " (" . $subQuery['query'] . ") " . $subQuery['alias'];
862 }
863
864 /**
865 * Internal function to build and execute INSERT/REPLACE calls
866 *
867 * @param <string $tableName The name of the table.
868 * @param array $insertData Data containing information for inserting into the DB.
869 *
870 * @return boolean Boolean indicating whether the insert query was completed succesfully.
871 */
872 private function _buildInsert ($tableName, $insertData, $operation)
873 {
874 if ($this->isSubQuery)
875 return;
876
877 $this->_query = $operation . " " . implode (' ', $this->_queryOptions) ." INTO " .self::$prefix . $tableName;
878 $stmt = $this->_buildQuery (null, $insertData);
879 $stmt->execute();
880 $this->_stmtError = $stmt->error;
881 $this->reset();
882 $this->count = $stmt->affected_rows;
883
884 if ($stmt->affected_rows < 1)
885 return false;
886
887 if ($stmt->insert_id > 0)
888 return $stmt->insert_id;
889
890 return true;
891 }
892
893 /**
894 * Abstraction method that will compile the WHERE statement,
895 * any passed update data, and the desired rows.
896 * It then builds the SQL query.
897 *
898 * @param integer|array $numRows Array to define SQL limit in format Array ($count, $offset)
899 * or only $count
900 * @param array $tableData Should contain an array of data for updating the database.
901 *
902 * @return mysqli_stmt Returns the $stmt object.
903 */
904 protected function _buildQuery($numRows = null, $tableData = null)
905 {
906 $this->_buildJoin();
907 $this->_buildInsertQuery ($tableData);
908 $this->_buildWhere();
909 $this->_buildGroupBy();
910 $this->_buildOrderBy();
911 $this->_buildLimit ($numRows);
912 $this->_buildOnDuplicate($tableData);
913 if ($this->_forUpdate)
914 $this->_query .= ' FOR UPDATE';
915 if ($this->_lockInShareMode)
916 $this->_query .= ' LOCK IN SHARE MODE';
917
918 $this->_lastQuery = $this->replacePlaceHolders ($this->_query, $this->_bindParams);
919
920 if ($this->isSubQuery)
921 return;
922
923 // Prepare query
924 $stmt = $this->_prepareQuery();
925
926 // Bind parameters to statement if any
927 if (count ($this->_bindParams) > 1)
928 call_user_func_array(array($stmt, 'bind_param'), $this->refValues($this->_bindParams));
929
930 return $stmt;
931 }
932
933 /**
934 * This helper method takes care of prepared statements' "bind_result method
935 * , when the number of variables to pass is unknown.
936 *
937 * @param mysqli_stmt $stmt Equal to the prepared statement object.
938 *
939 * @return array The results of the SQL fetch.
940 */
941 protected function _dynamicBindResults(mysqli_stmt $stmt)
942 {
943 $parameters = array();
944 $results = array();
945 // See http://php.net/manual/en/mysqli-result.fetch-fields.php
946 $mysqlLongType = 252;
947 $shouldStoreResult = false;
948
949 $meta = $stmt->result_metadata();
950
951 // if $meta is false yet sqlstate is true, there's no sql error but the query is
952 // most likely an update/insert/delete which doesn't produce any results
953 if(!$meta && $stmt->sqlstate)
954 return array();
955
956 $row = array();
957 while ($field = $meta->fetch_field()) {
958 if ($field->type == $mysqlLongType)
959 $shouldStoreResult = true;
960
961 if ($this->_nestJoin && $field->table != $this->_tableName) {
962 $field->table = substr ($field->table, strlen (self::$prefix));
963 $row[$field->table][$field->name] = null;
964 $parameters[] = & $row[$field->table][$field->name];
965 } else {
966 $row[$field->name] = null;
967 $parameters[] = & $row[$field->name];
968 }
969 }
970
971 // avoid out of memory bug in php 5.2 and 5.3. Mysqli allocates lot of memory for long*
972 // and blob* types. So to avoid out of memory issues store_result is used
973 // https://github.com/joshcam/PHP-MySQLi-Database-Class/pull/119
974 if ($shouldStoreResult)
975 $stmt->store_result();
976
977 call_user_func_array(array($stmt, 'bind_result'), $parameters);
978
979 $this->totalCount = 0;
980 $this->count = 0;
981 while ($stmt->fetch()) {
982 if ($this->returnType == 'Object') {
983 $x = new stdClass ();
984 foreach ($row as $key => $val) {
985 if (is_array ($val)) {
986 $x->$key = new stdClass ();
987 foreach ($val as $k => $v)
988 $x->$key->$k = $v;
989 } else
990 $x->$key = $val;
991 }
992 } else {
993 $x = array();
994 foreach ($row as $key => $val) {
995 if (is_array($val)) {
996 foreach ($val as $k => $v)
997 $x[$key][$k] = $v;
998 } else
999 $x[$key] = $val;
1000 }
1001 }
1002 $this->count++;
1003 array_push ($results, $x);
1004 }
1005 if ($shouldStoreResult)
1006 $stmt->free_result();
1007 $stmt->close();
1008 // stored procedures sometimes can return more then 1 resultset
1009 if ($this->mysqli()->more_results())
1010 $this->mysqli()->next_result();
1011
1012 if (in_array ('SQL_CALC_FOUND_ROWS', $this->_queryOptions)) {
1013 $stmt = $this->mysqli()->query ('SELECT FOUND_ROWS()');
1014 $totalCount = $stmt->fetch_row();
1015 $this->totalCount = $totalCount[0];
1016 }
1017 if ($this->returnType == 'Json')
1018 return json_encode ($results);
1019
1020 return $results;
1021 }
1022
1023
1024 /**
1025 * Abstraction method that will build an JOIN part of the query
1026 */
1027 protected function _buildJoin () {
1028 if (empty ($this->_join))
1029 return;
1030
1031 foreach ($this->_join as $data) {
1032 list ($joinType, $joinTable, $joinCondition) = $data;
1033
1034 if (is_object ($joinTable))
1035 $joinStr = $this->_buildPair ("", $joinTable);
1036 else
1037 $joinStr = $joinTable;
1038
1039 $this->_query .= " " . $joinType. " JOIN " . $joinStr ." on " . $joinCondition;
1040 }
1041 }
1042
1043 public function _buildDataPairs ($tableData, $tableColumns, $isInsert) {
1044 foreach ($tableColumns as $column) {
1045 $value = $tableData[$column];
1046 if (!$isInsert)
1047 $this->_query .= "`" . $column . "` = ";
1048
1049 // Subquery value
1050 if ($value instanceof MysqliDb) {
1051 $this->_query .= $this->_buildPair ("", $value) . ", ";
1052 continue;
1053 }
1054
1055 // Simple value
1056 if (!is_array ($value)) {
1057 $this->_bindParam($value);
1058 $this->_query .= '?, ';
1059 continue;
1060 }
1061
1062 // Function value
1063 $key = key ($value);
1064 $val = $value[$key];
1065 switch ($key) {
1066 case '[I]':
1067 $this->_query .= $column . $val . ", ";
1068 break;
1069 case '[F]':
1070 $this->_query .= $val[0] . ", ";
1071 if (!empty ($val[1]))
1072 $this->_bindParams ($val[1]);
1073 break;
1074 case '[N]':
1075 if ($val == null)
1076 $this->_query .= "!" . $column . ", ";
1077 else
1078 $this->_query .= "!" . $val . ", ";
1079 break;
1080 default:
1081 die ("Wrong operation");
1082 }
1083 }
1084 $this->_query = rtrim($this->_query, ', ');
1085 }
1086
1087 /**
1088 * Helper function to add variables into the query statement
1089 *
1090 * @param Array Variable with values
1091 */
1092 protected function _buildOnDuplicate($tableData)
1093 {
1094 if (is_array($this->_updateColumns) && !empty($this->_updateColumns)) {
1095 $this->_query .= " on duplicate key update ";
1096 if ($this->_lastInsertId)
1097 $this->_query .= $this->_lastInsertId . "=LAST_INSERT_ID (".$this->_lastInsertId."), ";
1098
1099 $this->_buildDataPairs ($tableData, $this->_updateColumns, false);
1100 }
1101 }
1102
1103 /**
1104 * Abstraction method that will build an INSERT or UPDATE part of the query
1105 */
1106 protected function _buildInsertQuery ($tableData) {
1107 if (!is_array ($tableData))
1108 return;
1109
1110 $isInsert = preg_match ('/^[INSERT|REPLACE]/', $this->_query);
1111 $dataColumns = array_keys ($tableData);
1112 if ($isInsert)
1113 $this->_query .= ' (`' . implode ($dataColumns, '`, `') . '`) VALUES (';
1114 else
1115 $this->_query .= " SET ";
1116
1117 $this->_buildDataPairs ($tableData, $dataColumns, $isInsert);
1118
1119 if ($isInsert)
1120 $this->_query .= ')';
1121 }
1122
1123 /**
1124 * Abstraction method that will build the part of the WHERE conditions
1125 */
1126 protected function _buildWhere () {
1127 if (empty ($this->_where))
1128 return;
1129
1130 //Prepare the where portion of the query
1131 $this->_query .= ' WHERE';
1132
1133 foreach ($this->_where as $cond) {
1134 list ($concat, $varName, $operator, $val) = $cond;
1135 $this->_query .= " " . $concat ." " . $varName;
1136
1137 switch (strtolower ($operator)) {
1138 case 'not in':
1139 case 'in':
1140 $comparison = ' ' . $operator. ' (';
1141 if (is_object ($val)) {
1142 $comparison .= $this->_buildPair ("", $val);
1143 } else {
1144 foreach ($val as $v) {
1145 $comparison .= ' ?,';
1146 $this->_bindParam ($v);
1147 }
1148 }
1149 $this->_query .= rtrim($comparison, ',').' ) ';
1150 break;
1151 case 'not between':
1152 case 'between':
1153 $this->_query .= " $operator ? AND ? ";
1154 $this->_bindParams ($val);
1155 break;
1156 case 'not exists':
1157 case 'exists':
1158 $this->_query.= $operator . $this->_buildPair ("", $val);
1159 break;
1160 default:
1161 if (is_array ($val))
1162 $this->_bindParams ($val);
1163 else if ($val === null)
1164 $this->_query .= $operator . " NULL";
1165 else if ($val != 'DBNULL' || $val == '0')
1166 $this->_query .= $this->_buildPair ($operator, $val);
1167 }
1168 }
1169 }
1170
1171 /**
1172 * Abstraction method that will build the GROUP BY part of the WHERE statement
1173 *
1174 */
1175 protected function _buildGroupBy () {
1176 if (empty ($this->_groupBy))
1177 return;
1178
1179 $this->_query .= " GROUP BY ";
1180 foreach ($this->_groupBy as $key => $value)
1181 $this->_query .= $value . ", ";
1182
1183 $this->_query = rtrim($this->_query, ', ') . " ";
1184 }
1185
1186 /**
1187 * Abstraction method that will build the LIMIT part of the WHERE statement
1188 *
1189 */
1190 protected function _buildOrderBy () {
1191 if (empty ($this->_orderBy))
1192 return;
1193
1194 $this->_query .= " ORDER BY ";
1195 foreach ($this->_orderBy as $prop => $value) {
1196 if (strtolower (str_replace (" ", "", $prop)) == 'rand()')
1197 $this->_query .= "rand(), ";
1198 else
1199 $this->_query .= $prop . " " . $value . ", ";
1200 }
1201
1202 $this->_query = rtrim ($this->_query, ', ') . " ";
1203 }
1204
1205 /**
1206 * Abstraction method that will build the LIMIT part of the WHERE statement
1207 *
1208 * @param integer|array $numRows Array to define SQL limit in format Array ($count, $offset)
1209 * or only $count
1210 */
1211 protected function _buildLimit ($numRows) {
1212 if (!isset ($numRows))
1213 return;
1214
1215 if (is_array ($numRows))
1216 $this->_query .= ' LIMIT ' . (int)$numRows[0] . ', ' . (int)$numRows[1];
1217 else
1218 $this->_query .= ' LIMIT ' . (int)$numRows;
1219 }
1220
1221 /**
1222 * Method attempts to prepare the SQL query
1223 * and throws an error if there was a problem.
1224 *
1225 * @return mysqli_stmt
1226 */
1227 protected function _prepareQuery()
1228 {
1229 if (!$stmt = $this->mysqli()->prepare($this->_query))
1230 throw new Exception ("Problem preparing query ($this->_query) " . $this->mysqli()->error);
1231 if ($this->traceEnabled)
1232 $this->traceStartQ = microtime (true);
1233
1234 return $stmt;
1235 }
1236
1237 /**
1238 * Close connection
1239 */
1240 public function __destruct()
1241 {
1242 if ($this->isSubQuery)
1243 return;
1244 if ($this->_mysqli)
1245 $this->_mysqli->close();
1246 }
1247
1248 /**
1249 * @param array $arr
1250 *
1251 * @return array
1252 */
1253 protected function refValues(Array &$arr)
1254 {
1255 //Reference in the function arguments are required for HHVM to work
1256 //https://github.com/facebook/hhvm/issues/5155
1257 //Referenced data array is required by mysqli since PHP 5.3+
1258 if (strnatcmp (phpversion(), '5.3') >= 0) {
1259 $refs = array();
1260 foreach ($arr as $key => $value)
1261 $refs[$key] = & $arr[$key];
1262 return $refs;
1263 }
1264 return $arr;
1265 }
1266
1267 /**
1268 * Function to replace ? with variables from bind variable
1269 * @param string $str
1270 * @param Array $vals
1271 *
1272 * @return string
1273 */
1274 protected function replacePlaceHolders ($str, $vals) {
1275 $i = 1;
1276 $newStr = "";
1277
1278 while ($pos = strpos ($str, "?")) {
1279 $val = $vals[$i++];
1280 if (is_object ($val))
1281 $val = '[object]';
1282 if ($val === NULL)
1283 $val = 'NULL';
1284 $newStr .= substr ($str, 0, $pos) . "'". $val . "'";
1285 $str = substr ($str, $pos + 1);
1286 }
1287 $newStr .= $str;
1288 return $newStr;
1289 }
1290
1291 /**
1292 * Method returns last executed query
1293 *
1294 * @return string
1295 */
1296 public function getLastQuery () {
1297 return $this->_lastQuery;
1298 }
1299
1300 /**
1301 * Method returns mysql error
1302 *
1303 * @return string
1304 */
1305 public function getLastError () {
1306 if (!$this->_mysqli)
1307 return "mysqli is null";
1308 return trim ($this->_stmtError . " " . $this->mysqli()->error);
1309 }
1310
1311 /**
1312 * Mostly internal method to get query and its params out of subquery object
1313 * after get() and getAll()
1314 *
1315 * @return array
1316 */
1317 public function getSubQuery () {
1318 if (!$this->isSubQuery)
1319 return null;
1320
1321 array_shift ($this->_bindParams);
1322 $val = Array ('query' => $this->_query,
1323 'params' => $this->_bindParams,
1324 'alias' => $this->host
1325 );
1326 $this->reset();
1327 return $val;
1328 }
1329
1330 /* Helper functions */
1331 /**
1332 * Method returns generated interval function as a string
1333 *
1334 * @param string interval in the formats:
1335 * "1", "-1d" or "- 1 day" -- For interval - 1 day
1336 * Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear
1337 * Default null;
1338 * @param string Initial date
1339 *
1340 * @return string
1341 */
1342 public function interval ($diff, $func = "NOW()") {
1343 $types = Array ("s" => "second", "m" => "minute", "h" => "hour", "d" => "day", "M" => "month", "Y" => "year");
1344 $incr = '+';
1345 $items = '';
1346 $type = 'd';
1347
1348 if ($diff && preg_match('/([+-]?) ?([0-9]+) ?([a-zA-Z]?)/',$diff, $matches)) {
1349 if (!empty ($matches[1])) $incr = $matches[1];
1350 if (!empty ($matches[2])) $items = $matches[2];
1351 if (!empty ($matches[3])) $type = $matches[3];
1352 if (!in_array($type, array_keys($types)))
1353 throw new Exception("invalid interval type in '{$diff}'");
1354 $func .= " ".$incr ." interval ". $items ." ".$types[$type] . " ";
1355 }
1356 return $func;
1357
1358 }
1359 /**
1360 * Method returns generated interval function as an insert/update function
1361 *
1362 * @param string interval in the formats:
1363 * "1", "-1d" or "- 1 day" -- For interval - 1 day
1364 * Supported intervals [s]econd, [m]inute, [h]hour, [d]day, [M]onth, [Y]ear
1365 * Default null;
1366 * @param string Initial date
1367 *
1368 * @return array
1369 */
1370 public function now ($diff = null, $func = "NOW()") {
1371 return Array ("[F]" => Array($this->interval($diff, $func)));
1372 }
1373
1374 /**
1375 * Method generates incremental function call
1376 * @param int increment by int or float. 1 by default
1377 */
1378 public function inc($num = 1) {
1379 if(!is_numeric($num))
1380 throw new Exception ('Argument supplied to inc must be a number');
1381 return Array ("[I]" => "+" . $num);
1382 }
1383
1384 /**
1385 * Method generates decrimental function call
1386 * @param int increment by int or float. 1 by default
1387 */
1388 public function dec ($num = 1) {
1389 if(!is_numeric($num))
1390 throw new Exception ('Argument supplied to dec must be a number');
1391 return Array ("[I]" => "-" . $num);
1392 }
1393
1394 /**
1395 * Method generates change boolean function call
1396 * @param string column name. null by default
1397 */
1398 public function not ($col = null) {
1399 return Array ("[N]" => (string)$col);
1400 }
1401
1402 /**
1403 * Method generates user defined function call
1404 * @param string user function body
1405 */
1406 public function func ($expr, $bindParams = null) {
1407 return Array ("[F]" => Array($expr, $bindParams));
1408 }
1409
1410 /**
1411 * Method creates new mysqlidb object for a subquery generation
1412 */
1413 public static function subQuery($subQueryAlias = "")
1414 {
1415 return new MysqliDb (Array('host' => $subQueryAlias, 'isSubQuery' => true));
1416 }
1417
1418 /**
1419 * Method returns a copy of a mysqlidb subquery object
1420 *
1421 * @param object new mysqlidb object
1422 */
1423 public function copy ()
1424 {
1425 $copy = unserialize (serialize ($this));
1426 $copy->_mysqli = $this->_mysqli;
1427 return $copy;
1428 }
1429
1430 /**
1431 * Begin a transaction
1432 *
1433 * @uses mysqli->autocommit(false)
1434 * @uses register_shutdown_function(array($this, "_transaction_shutdown_check"))
1435 */
1436 public function startTransaction () {
1437 $this->mysqli()->autocommit (false);
1438 $this->_transaction_in_progress = true;
1439 register_shutdown_function (array ($this, "_transaction_status_check"));
1440 }
1441
1442 /**
1443 * Transaction commit
1444 *
1445 * @uses mysqli->commit();
1446 * @uses mysqli->autocommit(true);
1447 */
1448 public function commit () {
1449 $this->mysqli()->commit ();
1450 $this->_transaction_in_progress = false;
1451 $this->mysqli()->autocommit (true);
1452 }
1453
1454 /**
1455 * Transaction rollback function
1456 *
1457 * @uses mysqli->rollback();
1458 * @uses mysqli->autocommit(true);
1459 */
1460 public function rollback () {
1461 $this->mysqli()->rollback ();
1462 $this->_transaction_in_progress = false;
1463 $this->mysqli()->autocommit (true);
1464 }
1465
1466 /**
1467 * Shutdown handler to rollback uncommited operations in order to keep
1468 * atomic operations sane.
1469 *
1470 * @uses mysqli->rollback();
1471 */
1472 public function _transaction_status_check () {
1473 if (!$this->_transaction_in_progress)
1474 return;
1475 $this->rollback ();
1476 }
1477
1478 /**
1479 * Query exection time tracking switch
1480 *
1481 * @param bool $enabled Enable execution time tracking
1482 * @param string $stripPrefix Prefix to strip from the path in exec log
1483 **/
1484 public function setTrace ($enabled, $stripPrefix = null) {
1485 $this->traceEnabled = $enabled;
1486 $this->traceStripPrefix = $stripPrefix;
1487 return $this;
1488 }
1489 /**
1490 * Get where and what function was called for query stored in MysqliDB->trace
1491 *
1492 * @return string with information
1493 */
1494 private function _traceGetCaller () {
1495 $dd = debug_backtrace ();
1496 $caller = next ($dd);
1497 while (isset ($caller) && $caller["file"] == __FILE__ )
1498 $caller = next($dd);
1499
1500 return __CLASS__ . "->" . $caller["function"] . "() >> file \"" .
1501 str_replace ($this->traceStripPrefix, '', $caller["file"] ) . "\" line #" . $caller["line"] . " " ;
1502 }
1503
1504 /**
1505 * Method to check if needed table is created
1506 *
1507 * @param array $tables Table name or an Array of table names to check
1508 *
1509 * @returns boolean True if table exists
1510 */
1511 public function tableExists ($tables) {
1512 $tables = !is_array ($tables) ? Array ($tables) : $tables;
1513 $count = count ($tables);
1514 if ($count == 0)
1515 return false;
1516
1517 array_walk ($tables, function (&$value, $key) { $value = self::$prefix . $value; });
1518 $this->where ('table_schema', $this->db);
1519 $this->where ('table_name', $tables, 'IN');
1520 $this->get ('information_schema.tables', $count);
1521 return $this->count == $count;
1522 }
1523} // END class