1<?php 2/** 3 * CodeIgniter 4 * 5 * An open source application development framework for PHP 6 * 7 * This content is released under the MIT License (MIT) 8 * 9 * Copyright (c) 2014 - 2019, British Columbia Institute of Technology 10 * 11 * Permission is hereby granted, free of charge, to any person obtaining a copy 12 * of this software and associated documentation files (the "Software"), to deal 13 * in the Software without restriction, including without limitation the rights 14 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell 15 * copies of the Software, and to permit persons to whom the Software is 16 * furnished to do so, subject to the following conditions: 17 * 18 * The above copyright notice and this permission notice shall be included in 19 * all copies or substantial portions of the Software. 20 * 21 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR 22 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, 23 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE 24 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER 25 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, 26 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN 27 * THE SOFTWARE. 28 * 29 * @package CodeIgniter 30 * @author EllisLab Dev Team 31 * @copyright Copyright (c) 2008 - 2014, EllisLab, Inc. (https://ellislab.com/) 32 * @copyright Copyright (c) 2014 - 2019, British Columbia Institute of Technology (https://bcit.ca/) 33 * @license https://opensource.org/licenses/MIT MIT License 34 * @link https://codeigniter.com 35 * @since Version 1.0.0 36 * @filesource 37 */ 38defined('BASEPATH') OR exit('No direct script access allowed'); 39 40/** 41 * Query Builder Class 42 * 43 * This is the platform-independent base Query Builder implementation class. 44 * 45 * @package CodeIgniter 46 * @subpackage Drivers 47 * @category Database 48 * @author EllisLab Dev Team 49 * @link https://codeigniter.com/user_guide/database/ 50 */ 51 52abstract class CI_DB_query_builder extends CI_DB_driver { 53 54 /** 55 * Return DELETE SQL flag 56 * 57 * @var bool 58 */ 59 protected $return_delete_sql = FALSE; 60 61 /** 62 * Reset DELETE data flag 63 * 64 * @var bool 65 */ 66 protected $reset_delete_data = FALSE; 67 68 /** 69 * QB SELECT data 70 * 71 * @var array 72 */ 73 protected $qb_select = array(); 74 75 /** 76 * QB DISTINCT flag 77 * 78 * @var bool 79 */ 80 protected $qb_distinct = FALSE; 81 82 /** 83 * QB FROM data 84 * 85 * @var array 86 */ 87 protected $qb_from = array(); 88 89 /** 90 * QB JOIN data 91 * 92 * @var array 93 */ 94 protected $qb_join = array(); 95 96 /** 97 * QB WHERE data 98 * 99 * @var array 100 */ 101 protected $qb_where = array(); 102 103 /** 104 * QB GROUP BY data 105 * 106 * @var array 107 */ 108 protected $qb_groupby = array(); 109 110 /** 111 * QB HAVING data 112 * 113 * @var array 114 */ 115 protected $qb_having = array(); 116 117 /** 118 * QB keys 119 * 120 * @var array 121 */ 122 protected $qb_keys = array(); 123 124 /** 125 * QB LIMIT data 126 * 127 * @var int 128 */ 129 protected $qb_limit = FALSE; 130 131 /** 132 * QB OFFSET data 133 * 134 * @var int 135 */ 136 protected $qb_offset = FALSE; 137 138 /** 139 * QB ORDER BY data 140 * 141 * @var array 142 */ 143 protected $qb_orderby = array(); 144 145 /** 146 * QB data sets 147 * 148 * @var array 149 */ 150 protected $qb_set = array(); 151 152 /** 153 * QB data set for update_batch() 154 * 155 * @var array 156 */ 157 protected $qb_set_ub = array(); 158 159 /** 160 * QB aliased tables list 161 * 162 * @var array 163 */ 164 protected $qb_aliased_tables = array(); 165 166 /** 167 * QB WHERE group started flag 168 * 169 * @var bool 170 */ 171 protected $qb_where_group_started = FALSE; 172 173 /** 174 * QB WHERE group count 175 * 176 * @var int 177 */ 178 protected $qb_where_group_count = 0; 179 180 // Query Builder Caching variables 181 182 /** 183 * QB Caching flag 184 * 185 * @var bool 186 */ 187 protected $qb_caching = FALSE; 188 189 /** 190 * QB Cache exists list 191 * 192 * @var array 193 */ 194 protected $qb_cache_exists = array(); 195 196 /** 197 * QB Cache SELECT data 198 * 199 * @var array 200 */ 201 protected $qb_cache_select = array(); 202 203 /** 204 * QB Cache FROM data 205 * 206 * @var array 207 */ 208 protected $qb_cache_from = array(); 209 210 /** 211 * QB Cache JOIN data 212 * 213 * @var array 214 */ 215 protected $qb_cache_join = array(); 216 217 /** 218 * QB Cache aliased tables list 219 * 220 * @var array 221 */ 222 protected $qb_cache_aliased_tables = array(); 223 224 /** 225 * QB Cache WHERE data 226 * 227 * @var array 228 */ 229 protected $qb_cache_where = array(); 230 231 /** 232 * QB Cache GROUP BY data 233 * 234 * @var array 235 */ 236 protected $qb_cache_groupby = array(); 237 238 /** 239 * QB Cache HAVING data 240 * 241 * @var array 242 */ 243 protected $qb_cache_having = array(); 244 245 /** 246 * QB Cache ORDER BY data 247 * 248 * @var array 249 */ 250 protected $qb_cache_orderby = array(); 251 252 /** 253 * QB Cache data sets 254 * 255 * @var array 256 */ 257 protected $qb_cache_set = array(); 258 259 /** 260 * QB No Escape data 261 * 262 * @var array 263 */ 264 protected $qb_no_escape = array(); 265 266 /** 267 * QB Cache No Escape data 268 * 269 * @var array 270 */ 271 protected $qb_cache_no_escape = array(); 272 273 // -------------------------------------------------------------------- 274 275 /** 276 * Select 277 * 278 * Generates the SELECT portion of the query 279 * 280 * @param string 281 * @param mixed 282 * @return CI_DB_query_builder 283 */ 284 public function select($select = '*', $escape = NULL) 285 { 286 if (is_string($select)) 287 { 288 $select = explode(',', $select); 289 } 290 291 // If the escape value was not set, we will base it on the global setting 292 is_bool($escape) OR $escape = $this->_protect_identifiers; 293 294 foreach ($select as $val) 295 { 296 $val = trim($val); 297 298 if ($val !== '') 299 { 300 $this->qb_select[] = $val; 301 $this->qb_no_escape[] = $escape; 302 303 if ($this->qb_caching === TRUE) 304 { 305 $this->qb_cache_select[] = $val; 306 $this->qb_cache_exists[] = 'select'; 307 $this->qb_cache_no_escape[] = $escape; 308 } 309 } 310 } 311 312 return $this; 313 } 314 315 // -------------------------------------------------------------------- 316 317 /** 318 * Select Max 319 * 320 * Generates a SELECT MAX(field) portion of a query 321 * 322 * @param string the field 323 * @param string an alias 324 * @return CI_DB_query_builder 325 */ 326 public function select_max($select = '', $alias = '') 327 { 328 return $this->_max_min_avg_sum($select, $alias, 'MAX'); 329 } 330 331 // -------------------------------------------------------------------- 332 333 /** 334 * Select Min 335 * 336 * Generates a SELECT MIN(field) portion of a query 337 * 338 * @param string the field 339 * @param string an alias 340 * @return CI_DB_query_builder 341 */ 342 public function select_min($select = '', $alias = '') 343 { 344 return $this->_max_min_avg_sum($select, $alias, 'MIN'); 345 } 346 347 // -------------------------------------------------------------------- 348 349 /** 350 * Select Average 351 * 352 * Generates a SELECT AVG(field) portion of a query 353 * 354 * @param string the field 355 * @param string an alias 356 * @return CI_DB_query_builder 357 */ 358 public function select_avg($select = '', $alias = '') 359 { 360 return $this->_max_min_avg_sum($select, $alias, 'AVG'); 361 } 362 363 // -------------------------------------------------------------------- 364 365 /** 366 * Select Sum 367 * 368 * Generates a SELECT SUM(field) portion of a query 369 * 370 * @param string the field 371 * @param string an alias 372 * @return CI_DB_query_builder 373 */ 374 public function select_sum($select = '', $alias = '') 375 { 376 return $this->_max_min_avg_sum($select, $alias, 'SUM'); 377 } 378 379 // -------------------------------------------------------------------- 380 381 /** 382 * SELECT [MAX|MIN|AVG|SUM]() 383 * 384 * @used-by select_max() 385 * @used-by select_min() 386 * @used-by select_avg() 387 * @used-by select_sum() 388 * 389 * @param string $select Field name 390 * @param string $alias 391 * @param string $type 392 * @return CI_DB_query_builder 393 */ 394 protected function _max_min_avg_sum($select = '', $alias = '', $type = 'MAX') 395 { 396 if ( ! is_string($select) OR $select === '') 397 { 398 $this->display_error('db_invalid_query'); 399 } 400 401 $type = strtoupper($type); 402 403 if ( ! in_array($type, array('MAX', 'MIN', 'AVG', 'SUM'))) 404 { 405 show_error('Invalid function type: '.$type); 406 } 407 408 if ($alias === '') 409 { 410 $alias = $this->_create_alias_from_table(trim($select)); 411 } 412 413 $sql = $type.'('.$this->protect_identifiers(trim($select)).') AS '.$this->escape_identifiers(trim($alias)); 414 415 $this->qb_select[] = $sql; 416 $this->qb_no_escape[] = NULL; 417 418 if ($this->qb_caching === TRUE) 419 { 420 $this->qb_cache_select[] = $sql; 421 $this->qb_cache_exists[] = 'select'; 422 } 423 424 return $this; 425 } 426 427 // -------------------------------------------------------------------- 428 429 /** 430 * Determines the alias name based on the table 431 * 432 * @param string $item 433 * @return string 434 */ 435 protected function _create_alias_from_table($item) 436 { 437 if (strpos($item, '.') !== FALSE) 438 { 439 $item = explode('.', $item); 440 return end($item); 441 } 442 443 return $item; 444 } 445 446 // -------------------------------------------------------------------- 447 448 /** 449 * DISTINCT 450 * 451 * Sets a flag which tells the query string compiler to add DISTINCT 452 * 453 * @param bool $val 454 * @return CI_DB_query_builder 455 */ 456 public function distinct($val = TRUE) 457 { 458 $this->qb_distinct = is_bool($val) ? $val : TRUE; 459 return $this; 460 } 461 462 // -------------------------------------------------------------------- 463 464 /** 465 * From 466 * 467 * Generates the FROM portion of the query 468 * 469 * @param mixed $from can be a string or array 470 * @return CI_DB_query_builder 471 */ 472 public function from($from) 473 { 474 foreach ((array) $from as $val) 475 { 476 if (strpos($val, ',') !== FALSE) 477 { 478 foreach (explode(',', $val) as $v) 479 { 480 $v = trim($v); 481 $this->_track_aliases($v); 482 483 $this->qb_from[] = $v = $this->protect_identifiers($v, TRUE, NULL, FALSE); 484 485 if ($this->qb_caching === TRUE) 486 { 487 $this->qb_cache_from[] = $v; 488 $this->qb_cache_exists[] = 'from'; 489 } 490 } 491 } 492 else 493 { 494 $val = trim($val); 495 496 // Extract any aliases that might exist. We use this information 497 // in the protect_identifiers to know whether to add a table prefix 498 $this->_track_aliases($val); 499 500 $this->qb_from[] = $val = $this->protect_identifiers($val, TRUE, NULL, FALSE); 501 502 if ($this->qb_caching === TRUE) 503 { 504 $this->qb_cache_from[] = $val; 505 $this->qb_cache_exists[] = 'from'; 506 } 507 } 508 } 509 510 return $this; 511 } 512 513 // -------------------------------------------------------------------- 514 515 /** 516 * JOIN 517 * 518 * Generates the JOIN portion of the query 519 * 520 * @param string 521 * @param string the join condition 522 * @param string the type of join 523 * @param string whether not to try to escape identifiers 524 * @return CI_DB_query_builder 525 */ 526 public function join($table, $cond, $type = '', $escape = NULL) 527 { 528 if ($type !== '') 529 { 530 $type = strtoupper(trim($type)); 531 532 if ( ! in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER'), TRUE)) 533 { 534 $type = ''; 535 } 536 else 537 { 538 $type .= ' '; 539 } 540 } 541 542 // Extract any aliases that might exist. We use this information 543 // in the protect_identifiers to know whether to add a table prefix 544 $this->_track_aliases($table); 545 546 is_bool($escape) OR $escape = $this->_protect_identifiers; 547 548 if ( ! $this->_has_operator($cond)) 549 { 550 $cond = ' USING ('.($escape ? $this->escape_identifiers($cond) : $cond).')'; 551 } 552 elseif ($escape === FALSE) 553 { 554 $cond = ' ON '.$cond; 555 } 556 else 557 { 558 // Split multiple conditions 559 if (preg_match_all('/\sAND\s|\sOR\s/i', $cond, $joints, PREG_OFFSET_CAPTURE)) 560 { 561 $conditions = array(); 562 $joints = $joints[0]; 563 array_unshift($joints, array('', 0)); 564 565 for ($i = count($joints) - 1, $pos = strlen($cond); $i >= 0; $i--) 566 { 567 $joints[$i][1] += strlen($joints[$i][0]); // offset 568 $conditions[$i] = substr($cond, $joints[$i][1], $pos - $joints[$i][1]); 569 $pos = $joints[$i][1] - strlen($joints[$i][0]); 570 $joints[$i] = $joints[$i][0]; 571 } 572 } 573 else 574 { 575 $conditions = array($cond); 576 $joints = array(''); 577 } 578 579 $cond = ' ON '; 580 for ($i = 0, $c = count($conditions); $i < $c; $i++) 581 { 582 $operator = $this->_get_operator($conditions[$i]); 583 $cond .= $joints[$i]; 584 $cond .= preg_match("/(\(*)?([\[\]\w\.'-]+)".preg_quote($operator)."(.*)/i", $conditions[$i], $match) 585 ? $match[1].$this->protect_identifiers($match[2]).$operator.$this->protect_identifiers($match[3]) 586 : $conditions[$i]; 587 } 588 } 589 590 // Do we want to escape the table name? 591 if ($escape === TRUE) 592 { 593 $table = $this->protect_identifiers($table, TRUE, NULL, FALSE); 594 } 595 596 // Assemble the JOIN statement 597 $this->qb_join[] = $join = $type.'JOIN '.$table.$cond; 598 599 if ($this->qb_caching === TRUE) 600 { 601 $this->qb_cache_join[] = $join; 602 $this->qb_cache_exists[] = 'join'; 603 } 604 605 return $this; 606 } 607 608 // -------------------------------------------------------------------- 609 610 /** 611 * WHERE 612 * 613 * Generates the WHERE portion of the query. 614 * Separates multiple calls with 'AND'. 615 * 616 * @param mixed 617 * @param mixed 618 * @param bool 619 * @return CI_DB_query_builder 620 */ 621 public function where($key, $value = NULL, $escape = NULL) 622 { 623 return $this->_wh('qb_where', $key, $value, 'AND ', $escape); 624 } 625 626 // -------------------------------------------------------------------- 627 628 /** 629 * OR WHERE 630 * 631 * Generates the WHERE portion of the query. 632 * Separates multiple calls with 'OR'. 633 * 634 * @param mixed 635 * @param mixed 636 * @param bool 637 * @return CI_DB_query_builder 638 */ 639 public function or_where($key, $value = NULL, $escape = NULL) 640 { 641 return $this->_wh('qb_where', $key, $value, 'OR ', $escape); 642 } 643 644 // -------------------------------------------------------------------- 645 646 /** 647 * WHERE, HAVING 648 * 649 * @used-by where() 650 * @used-by or_where() 651 * @used-by having() 652 * @used-by or_having() 653 * 654 * @param string $qb_key 'qb_where' or 'qb_having' 655 * @param mixed $key 656 * @param mixed $value 657 * @param string $type 658 * @param bool $escape 659 * @return CI_DB_query_builder 660 */ 661 protected function _wh($qb_key, $key, $value = NULL, $type = 'AND ', $escape = NULL) 662 { 663 $qb_cache_key = ($qb_key === 'qb_having') ? 'qb_cache_having' : 'qb_cache_where'; 664 665 if ( ! is_array($key)) 666 { 667 $key = array($key => $value); 668 } 669 670 // If the escape value was not set will base it on the global setting 671 is_bool($escape) OR $escape = $this->_protect_identifiers; 672 673 foreach ($key as $k => $v) 674 { 675 $prefix = (count($this->$qb_key) === 0 && count($this->$qb_cache_key) === 0) 676 ? $this->_group_get_type('') 677 : $this->_group_get_type($type); 678 679 if ($v !== NULL) 680 { 681 if ($escape === TRUE) 682 { 683 $v = $this->escape($v); 684 } 685 686 if ( ! $this->_has_operator($k)) 687 { 688 $k .= ' = '; 689 } 690 } 691 elseif ( ! $this->_has_operator($k)) 692 { 693 // value appears not to have been set, assign the test to IS NULL 694 $k .= ' IS NULL'; 695 } 696 elseif (preg_match('/\s*(!?=|<>|\sIS(?:\s+NOT)?\s)\s*$/i', $k, $match, PREG_OFFSET_CAPTURE)) 697 { 698 $k = substr($k, 0, $match[0][1]).($match[1][0] === '=' ? ' IS NULL' : ' IS NOT NULL'); 699 } 700 701 ${$qb_key} = array('condition' => $prefix.$k, 'value' => $v, 'escape' => $escape); 702 $this->{$qb_key}[] = ${$qb_key}; 703 if ($this->qb_caching === TRUE) 704 { 705 $this->{$qb_cache_key}[] = ${$qb_key}; 706 $this->qb_cache_exists[] = substr($qb_key, 3); 707 } 708 709 } 710 711 return $this; 712 } 713 714 // -------------------------------------------------------------------- 715 716 /** 717 * WHERE IN 718 * 719 * Generates a WHERE field IN('item', 'item') SQL query, 720 * joined with 'AND' if appropriate. 721 * 722 * @param string $key The field to search 723 * @param array $values The values searched on 724 * @param bool $escape 725 * @return CI_DB_query_builder 726 */ 727 public function where_in($key = NULL, $values = NULL, $escape = NULL) 728 { 729 return $this->_where_in($key, $values, FALSE, 'AND ', $escape); 730 } 731 732 // -------------------------------------------------------------------- 733 734 /** 735 * OR WHERE IN 736 * 737 * Generates a WHERE field IN('item', 'item') SQL query, 738 * joined with 'OR' if appropriate. 739 * 740 * @param string $key The field to search 741 * @param array $values The values searched on 742 * @param bool $escape 743 * @return CI_DB_query_builder 744 */ 745 public function or_where_in($key = NULL, $values = NULL, $escape = NULL) 746 { 747 return $this->_where_in($key, $values, FALSE, 'OR ', $escape); 748 } 749 750 // -------------------------------------------------------------------- 751 752 /** 753 * WHERE NOT IN 754 * 755 * Generates a WHERE field NOT IN('item', 'item') SQL query, 756 * joined with 'AND' if appropriate. 757 * 758 * @param string $key The field to search 759 * @param array $values The values searched on 760 * @param bool $escape 761 * @return CI_DB_query_builder 762 */ 763 public function where_not_in($key = NULL, $values = NULL, $escape = NULL) 764 { 765 return $this->_where_in($key, $values, TRUE, 'AND ', $escape); 766 } 767 768 // -------------------------------------------------------------------- 769 770 /** 771 * OR WHERE NOT IN 772 * 773 * Generates a WHERE field NOT IN('item', 'item') SQL query, 774 * joined with 'OR' if appropriate. 775 * 776 * @param string $key The field to search 777 * @param array $values The values searched on 778 * @param bool $escape 779 * @return CI_DB_query_builder 780 */ 781 public function or_where_not_in($key = NULL, $values = NULL, $escape = NULL) 782 { 783 return $this->_where_in($key, $values, TRUE, 'OR ', $escape); 784 } 785 786 // -------------------------------------------------------------------- 787 788 /** 789 * Internal WHERE IN 790 * 791 * @used-by where_in() 792 * @used-by or_where_in() 793 * @used-by where_not_in() 794 * @used-by or_where_not_in() 795 * 796 * @param string $key The field to search 797 * @param array $values The values searched on 798 * @param bool $not If the statement would be IN or NOT IN 799 * @param string $type 800 * @param bool $escape 801 * @return CI_DB_query_builder 802 */ 803 protected function _where_in($key = NULL, $values = NULL, $not = FALSE, $type = 'AND ', $escape = NULL) 804 { 805 if ($key === NULL OR $values === NULL) 806 { 807 return $this; 808 } 809 810 if ( ! is_array($values)) 811 { 812 $values = array($values); 813 } 814 815 is_bool($escape) OR $escape = $this->_protect_identifiers; 816 817 $not = ($not) ? ' NOT' : ''; 818 819 if ($escape === TRUE) 820 { 821 $where_in = array(); 822 foreach ($values as $value) 823 { 824 $where_in[] = $this->escape($value); 825 } 826 } 827 else 828 { 829 $where_in = array_values($values); 830 } 831 832 $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) 833 ? $this->_group_get_type('') 834 : $this->_group_get_type($type); 835 836 $where_in = array( 837 'condition' => $prefix.$key.$not.' IN('.implode(', ', $where_in).')', 838 'value' => NULL, 839 'escape' => $escape 840 ); 841 842 $this->qb_where[] = $where_in; 843 if ($this->qb_caching === TRUE) 844 { 845 $this->qb_cache_where[] = $where_in; 846 $this->qb_cache_exists[] = 'where'; 847 } 848 849 return $this; 850 } 851 852 // -------------------------------------------------------------------- 853 854 /** 855 * LIKE 856 * 857 * Generates a %LIKE% portion of the query. 858 * Separates multiple calls with 'AND'. 859 * 860 * @param mixed $field 861 * @param string $match 862 * @param string $side 863 * @param bool $escape 864 * @return CI_DB_query_builder 865 */ 866 public function like($field, $match = '', $side = 'both', $escape = NULL) 867 { 868 return $this->_like($field, $match, 'AND ', $side, '', $escape); 869 } 870 871 // -------------------------------------------------------------------- 872 873 /** 874 * NOT LIKE 875 * 876 * Generates a NOT LIKE portion of the query. 877 * Separates multiple calls with 'AND'. 878 * 879 * @param mixed $field 880 * @param string $match 881 * @param string $side 882 * @param bool $escape 883 * @return CI_DB_query_builder 884 */ 885 public function not_like($field, $match = '', $side = 'both', $escape = NULL) 886 { 887 return $this->_like($field, $match, 'AND ', $side, 'NOT', $escape); 888 } 889 890 // -------------------------------------------------------------------- 891 892 /** 893 * OR LIKE 894 * 895 * Generates a %LIKE% portion of the query. 896 * Separates multiple calls with 'OR'. 897 * 898 * @param mixed $field 899 * @param string $match 900 * @param string $side 901 * @param bool $escape 902 * @return CI_DB_query_builder 903 */ 904 public function or_like($field, $match = '', $side = 'both', $escape = NULL) 905 { 906 return $this->_like($field, $match, 'OR ', $side, '', $escape); 907 } 908 909 // -------------------------------------------------------------------- 910 911 /** 912 * OR NOT LIKE 913 * 914 * Generates a NOT LIKE portion of the query. 915 * Separates multiple calls with 'OR'. 916 * 917 * @param mixed $field 918 * @param string $match 919 * @param string $side 920 * @param bool $escape 921 * @return CI_DB_query_builder 922 */ 923 public function or_not_like($field, $match = '', $side = 'both', $escape = NULL) 924 { 925 return $this->_like($field, $match, 'OR ', $side, 'NOT', $escape); 926 } 927 928 // -------------------------------------------------------------------- 929 930 /** 931 * Internal LIKE 932 * 933 * @used-by like() 934 * @used-by or_like() 935 * @used-by not_like() 936 * @used-by or_not_like() 937 * 938 * @param mixed $field 939 * @param string $match 940 * @param string $type 941 * @param string $side 942 * @param string $not 943 * @param bool $escape 944 * @return CI_DB_query_builder 945 */ 946 protected function _like($field, $match = '', $type = 'AND ', $side = 'both', $not = '', $escape = NULL) 947 { 948 if ( ! is_array($field)) 949 { 950 $field = array($field => $match); 951 } 952 953 is_bool($escape) OR $escape = $this->_protect_identifiers; 954 // lowercase $side in case somebody writes e.g. 'BEFORE' instead of 'before' (doh) 955 $side = strtolower($side); 956 957 foreach ($field as $k => $v) 958 { 959 $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) 960 ? $this->_group_get_type('') : $this->_group_get_type($type); 961 962 if ($escape === TRUE) 963 { 964 $v = $this->escape_like_str($v); 965 } 966 967 switch ($side) 968 { 969 case 'none': 970 $v = "'{$v}'"; 971 break; 972 case 'before': 973 $v = "'%{$v}'"; 974 break; 975 case 'after': 976 $v = "'{$v}%'"; 977 break; 978 case 'both': 979 default: 980 $v = "'%{$v}%'"; 981 break; 982 } 983 984 // some platforms require an escape sequence definition for LIKE wildcards 985 if ($escape === TRUE && $this->_like_escape_str !== '') 986 { 987 $v .= sprintf($this->_like_escape_str, $this->_like_escape_chr); 988 } 989 990 $qb_where = array('condition' => "{$prefix} {$k} {$not} LIKE {$v}", 'value' => NULL, 'escape' => $escape); 991 $this->qb_where[] = $qb_where; 992 if ($this->qb_caching === TRUE) 993 { 994 $this->qb_cache_where[] = $qb_where; 995 $this->qb_cache_exists[] = 'where'; 996 } 997 } 998 999 return $this; 1000 } 1001 1002 // -------------------------------------------------------------------- 1003 1004 /** 1005 * Starts a query group. 1006 * 1007 * @param string $not (Internal use only) 1008 * @param string $type (Internal use only) 1009 * @return CI_DB_query_builder 1010 */ 1011 public function group_start($not = '', $type = 'AND ') 1012 { 1013 $type = $this->_group_get_type($type); 1014 1015 $this->qb_where_group_started = TRUE; 1016 $prefix = (count($this->qb_where) === 0 && count($this->qb_cache_where) === 0) ? '' : $type; 1017 $where = array( 1018 'condition' => $prefix.$not.str_repeat(' ', ++$this->qb_where_group_count).' (', 1019 'value' => NULL, 1020 'escape' => FALSE 1021 ); 1022 1023 $this->qb_where[] = $where; 1024 if ($this->qb_caching) 1025 { 1026 $this->qb_cache_where[] = $where; 1027 } 1028 1029 return $this; 1030 } 1031 1032 // -------------------------------------------------------------------- 1033 1034 /** 1035 * Starts a query group, but ORs the group 1036 * 1037 * @return CI_DB_query_builder 1038 */ 1039 public function or_group_start() 1040 { 1041 return $this->group_start('', 'OR '); 1042 } 1043 1044 // -------------------------------------------------------------------- 1045 1046 /** 1047 * Starts a query group, but NOTs the group 1048 * 1049 * @return CI_DB_query_builder 1050 */ 1051 public function not_group_start() 1052 { 1053 return $this->group_start('NOT ', 'AND '); 1054 } 1055 1056 // -------------------------------------------------------------------- 1057 1058 /** 1059 * Starts a query group, but OR NOTs the group 1060 * 1061 * @return CI_DB_query_builder 1062 */ 1063 public function or_not_group_start() 1064 { 1065 return $this->group_start('NOT ', 'OR '); 1066 } 1067 1068 // -------------------------------------------------------------------- 1069 1070 /** 1071 * Ends a query group 1072 * 1073 * @return CI_DB_query_builder 1074 */ 1075 public function group_end() 1076 { 1077 $this->qb_where_group_started = FALSE; 1078 $where = array( 1079 'condition' => str_repeat(' ', $this->qb_where_group_count--).')', 1080 'value' => NULL, 1081 'escape' => FALSE 1082 ); 1083 1084 $this->qb_where[] = $where; 1085 if ($this->qb_caching) 1086 { 1087 $this->qb_cache_where[] = $where; 1088 } 1089 1090 return $this; 1091 } 1092 1093 // -------------------------------------------------------------------- 1094 1095 /** 1096 * Group_get_type 1097 * 1098 * @used-by group_start() 1099 * @used-by _like() 1100 * @used-by _wh() 1101 * @used-by _where_in() 1102 * 1103 * @param string $type 1104 * @return string 1105 */ 1106 protected function _group_get_type($type) 1107 { 1108 if ($this->qb_where_group_started) 1109 { 1110 $type = ''; 1111 $this->qb_where_group_started = FALSE; 1112 } 1113 1114 return $type; 1115 } 1116 1117 // -------------------------------------------------------------------- 1118 1119 /** 1120 * GROUP BY 1121 * 1122 * @param string $by 1123 * @param bool $escape 1124 * @return CI_DB_query_builder 1125 */ 1126 public function group_by($by, $escape = NULL) 1127 { 1128 is_bool($escape) OR $escape = $this->_protect_identifiers; 1129 1130 if (is_string($by)) 1131 { 1132 $by = ($escape === TRUE) 1133 ? explode(',', $by) 1134 : array($by); 1135 } 1136 1137 foreach ($by as $val) 1138 { 1139 $val = trim($val); 1140 1141 if ($val !== '') 1142 { 1143 $val = array('field' => $val, 'escape' => $escape); 1144 1145 $this->qb_groupby[] = $val; 1146 if ($this->qb_caching === TRUE) 1147 { 1148 $this->qb_cache_groupby[] = $val; 1149 $this->qb_cache_exists[] = 'groupby'; 1150 } 1151 } 1152 } 1153 1154 return $this; 1155 } 1156 1157 // -------------------------------------------------------------------- 1158 1159 /** 1160 * HAVING 1161 * 1162 * Separates multiple calls with 'AND'. 1163 * 1164 * @param string $key 1165 * @param string $value 1166 * @param bool $escape 1167 * @return CI_DB_query_builder 1168 */ 1169 public function having($key, $value = NULL, $escape = NULL) 1170 { 1171 return $this->_wh('qb_having', $key, $value, 'AND ', $escape); 1172 } 1173 1174 // -------------------------------------------------------------------- 1175 1176 /** 1177 * OR HAVING 1178 * 1179 * Separates multiple calls with 'OR'. 1180 * 1181 * @param string $key 1182 * @param string $value 1183 * @param bool $escape 1184 * @return CI_DB_query_builder 1185 */ 1186 public function or_having($key, $value = NULL, $escape = NULL) 1187 { 1188 return $this->_wh('qb_having', $key, $value, 'OR ', $escape); 1189 } 1190 1191 // -------------------------------------------------------------------- 1192 1193 /** 1194 * ORDER BY 1195 * 1196 * @param string $orderby 1197 * @param string $direction ASC, DESC or RANDOM 1198 * @param bool $escape 1199 * @return CI_DB_query_builder 1200 */ 1201 public function order_by($orderby, $direction = '', $escape = NULL) 1202 { 1203 $direction = strtoupper(trim($direction)); 1204 1205 if ($direction === 'RANDOM') 1206 { 1207 $direction = ''; 1208 1209 // Do we have a seed value? 1210 $orderby = ctype_digit((string) $orderby) 1211 ? sprintf($this->_random_keyword[1], $orderby) 1212 : $this->_random_keyword[0]; 1213 } 1214 elseif (empty($orderby)) 1215 { 1216 return $this; 1217 } 1218 elseif ($direction !== '') 1219 { 1220 $direction = in_array($direction, array('ASC', 'DESC'), TRUE) ? ' '.$direction : ''; 1221 } 1222 1223 is_bool($escape) OR $escape = $this->_protect_identifiers; 1224 1225 if ($escape === FALSE) 1226 { 1227 $qb_orderby[] = array('field' => $orderby, 'direction' => $direction, 'escape' => FALSE); 1228 } 1229 else 1230 { 1231 $qb_orderby = array(); 1232 foreach (explode(',', $orderby) as $field) 1233 { 1234 $qb_orderby[] = ($direction === '' && preg_match('/\s+(ASC|DESC)$/i', rtrim($field), $match, PREG_OFFSET_CAPTURE)) 1235 ? array('field' => ltrim(substr($field, 0, $match[0][1])), 'direction' => ' '.$match[1][0], 'escape' => TRUE) 1236 : array('field' => trim($field), 'direction' => $direction, 'escape' => TRUE); 1237 } 1238 } 1239 1240 $this->qb_orderby = array_merge($this->qb_orderby, $qb_orderby); 1241 if ($this->qb_caching === TRUE) 1242 { 1243 $this->qb_cache_orderby = array_merge($this->qb_cache_orderby, $qb_orderby); 1244 $this->qb_cache_exists[] = 'orderby'; 1245 } 1246 1247 return $this; 1248 } 1249 1250 // -------------------------------------------------------------------- 1251 1252 /** 1253 * LIMIT 1254 * 1255 * @param int $value LIMIT value 1256 * @param int $offset OFFSET value 1257 * @return CI_DB_query_builder 1258 */ 1259 public function limit($value, $offset = 0) 1260 { 1261 is_null($value) OR $this->qb_limit = (int) $value; 1262 empty($offset) OR $this->qb_offset = (int) $offset; 1263 1264 return $this; 1265 } 1266 1267 // -------------------------------------------------------------------- 1268 1269 /** 1270 * Sets the OFFSET value 1271 * 1272 * @param int $offset OFFSET value 1273 * @return CI_DB_query_builder 1274 */ 1275 public function offset($offset) 1276 { 1277 empty($offset) OR $this->qb_offset = (int) $offset; 1278 return $this; 1279 } 1280 1281 // -------------------------------------------------------------------- 1282 1283 /** 1284 * LIMIT string 1285 * 1286 * Generates a platform-specific LIMIT clause. 1287 * 1288 * @param string $sql SQL Query 1289 * @return string 1290 */ 1291 protected function _limit($sql) 1292 { 1293 return $sql.' LIMIT '.($this->qb_offset ? $this->qb_offset.', ' : '').(int) $this->qb_limit; 1294 } 1295 1296 // -------------------------------------------------------------------- 1297 1298 /** 1299 * The "set" function. 1300 * 1301 * Allows key/value pairs to be set for inserting or updating 1302 * 1303 * @param mixed 1304 * @param string 1305 * @param bool 1306 * @return CI_DB_query_builder 1307 */ 1308 public function set($key, $value = '', $escape = NULL) 1309 { 1310 $key = $this->_object_to_array($key); 1311 1312 if ( ! is_array($key)) 1313 { 1314 $key = array($key => $value); 1315 } 1316 1317 is_bool($escape) OR $escape = $this->_protect_identifiers; 1318 1319 foreach ($key as $k => $v) 1320 { 1321 $this->qb_set[$this->protect_identifiers($k, FALSE, $escape)] = ($escape) 1322 ? $this->escape($v) : $v; 1323 } 1324 1325 return $this; 1326 } 1327 1328 // -------------------------------------------------------------------- 1329 1330 /** 1331 * Get SELECT query string 1332 * 1333 * Compiles a SELECT query string and returns the sql. 1334 * 1335 * @param string the table name to select from (optional) 1336 * @param bool TRUE: resets QB values; FALSE: leave QB values alone 1337 * @return string 1338 */ 1339 public function get_compiled_select($table = '', $reset = TRUE) 1340 { 1341 if ($table !== '') 1342 { 1343 $this->_track_aliases($table); 1344 $this->from($table); 1345 } 1346 1347 $select = $this->_compile_select(); 1348 1349 if ($reset === TRUE) 1350 { 1351 $this->_reset_select(); 1352 } 1353 1354 return $select; 1355 } 1356 1357 // -------------------------------------------------------------------- 1358 1359 /** 1360 * Get 1361 * 1362 * Compiles the select statement based on the other functions called 1363 * and runs the query 1364 * 1365 * @param string the table 1366 * @param string the limit clause 1367 * @param string the offset clause 1368 * @return CI_DB_result 1369 */ 1370 public function get($table = '', $limit = NULL, $offset = NULL) 1371 { 1372 if ($table !== '') 1373 { 1374 $this->_track_aliases($table); 1375 $this->from($table); 1376 } 1377 1378 if ( ! empty($limit)) 1379 { 1380 $this->limit($limit, $offset); 1381 } 1382 1383 $result = $this->query($this->_compile_select()); 1384 $this->_reset_select(); 1385 return $result; 1386 } 1387 1388 // -------------------------------------------------------------------- 1389 1390 /** 1391 * "Count All Results" query 1392 * 1393 * Generates a platform-specific query string that counts all records 1394 * returned by an Query Builder query. 1395 * 1396 * @param string 1397 * @param bool the reset clause 1398 * @return int 1399 */ 1400 public function count_all_results($table = '', $reset = TRUE) 1401 { 1402 if ($table !== '') 1403 { 1404 $this->_track_aliases($table); 1405 $this->from($table); 1406 } 1407 1408 // ORDER BY usage is often problematic here (most notably 1409 // on Microsoft SQL Server) and ultimately unnecessary 1410 // for selecting COUNT(*) ... 1411 $qb_orderby = $this->qb_orderby; 1412 $qb_cache_orderby = $this->qb_cache_orderby; 1413 $this->qb_orderby = $this->qb_cache_orderby = array(); 1414 1415 $result = ($this->qb_distinct === TRUE OR ! empty($this->qb_groupby) OR ! empty($this->qb_cache_groupby) OR $this->qb_limit OR $this->qb_offset) 1416 ? $this->query($this->_count_string.$this->protect_identifiers('numrows')."\nFROM (\n".$this->_compile_select()."\n) CI_count_all_results") 1417 : $this->query($this->_compile_select($this->_count_string.$this->protect_identifiers('numrows'))); 1418 1419 if ($reset === TRUE) 1420 { 1421 $this->_reset_select(); 1422 } 1423 else 1424 { 1425 $this->qb_orderby = $qb_orderby; 1426 $this->qb_cache_orderby = $qb_cache_orderby; 1427 } 1428 1429 if ($result->num_rows() === 0) 1430 { 1431 return 0; 1432 } 1433 1434 $row = $result->row(); 1435 return (int) $row->numrows; 1436 } 1437 1438 // -------------------------------------------------------------------- 1439 1440 /** 1441 * get_where() 1442 * 1443 * Allows the where clause, limit and offset to be added directly 1444 * 1445 * @param string $table 1446 * @param string $where 1447 * @param int $limit 1448 * @param int $offset 1449 * @return CI_DB_result 1450 */ 1451 public function get_where($table = '', $where = NULL, $limit = NULL, $offset = NULL) 1452 { 1453 if ($table !== '') 1454 { 1455 $this->from($table); 1456 } 1457 1458 if ($where !== NULL) 1459 { 1460 $this->where($where); 1461 } 1462 1463 if ( ! empty($limit)) 1464 { 1465 $this->limit($limit, $offset); 1466 } 1467 1468 $result = $this->query($this->_compile_select()); 1469 $this->_reset_select(); 1470 return $result; 1471 } 1472 1473 // -------------------------------------------------------------------- 1474 1475 /** 1476 * Insert_Batch 1477 * 1478 * Compiles batch insert strings and runs the queries 1479 * 1480 * @param string $table Table to insert into 1481 * @param array $set An associative array of insert values 1482 * @param bool $escape Whether to escape values and identifiers 1483 * @return int Number of rows inserted or FALSE on failure 1484 */ 1485 public function insert_batch($table, $set = NULL, $escape = NULL, $batch_size = 100) 1486 { 1487 if ($set === NULL) 1488 { 1489 if (empty($this->qb_set)) 1490 { 1491 return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE; 1492 } 1493 } 1494 else 1495 { 1496 if (empty($set)) 1497 { 1498 return ($this->db_debug) ? $this->display_error('insert_batch() called with no data') : FALSE; 1499 } 1500 1501 $this->set_insert_batch($set, '', $escape); 1502 } 1503 1504 if (strlen($table) === 0) 1505 { 1506 if ( ! isset($this->qb_from[0])) 1507 { 1508 return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; 1509 } 1510 1511 $table = $this->qb_from[0]; 1512 } 1513 1514 // Batch this baby 1515 $affected_rows = 0; 1516 for ($i = 0, $total = count($this->qb_set); $i < $total; $i += $batch_size) 1517 { 1518 if ($this->query($this->_insert_batch($this->protect_identifiers($table, TRUE, $escape, FALSE), $this->qb_keys, array_slice($this->qb_set, $i, $batch_size)))) 1519 { 1520 $affected_rows += $this->affected_rows(); 1521 } 1522 } 1523 1524 $this->_reset_write(); 1525 return $affected_rows; 1526 } 1527 1528 // -------------------------------------------------------------------- 1529 1530 /** 1531 * Insert batch statement 1532 * 1533 * Generates a platform-specific insert string from the supplied data. 1534 * 1535 * @param string $table Table name 1536 * @param array $keys INSERT keys 1537 * @param array $values INSERT values 1538 * @return string 1539 */ 1540 protected function _insert_batch($table, $keys, $values) 1541 { 1542 return 'INSERT INTO '.$table.' ('.implode(', ', $keys).') VALUES '.implode(', ', $values); 1543 } 1544 1545 // -------------------------------------------------------------------- 1546 1547 /** 1548 * The "set_insert_batch" function. Allows key/value pairs to be set for batch inserts 1549 * 1550 * @param mixed 1551 * @param string 1552 * @param bool 1553 * @return CI_DB_query_builder 1554 */ 1555 public function set_insert_batch($key, $value = '', $escape = NULL) 1556 { 1557 $key = $this->_object_to_array_batch($key); 1558 1559 if ( ! is_array($key)) 1560 { 1561 $key = array($key => $value); 1562 } 1563 1564 is_bool($escape) OR $escape = $this->_protect_identifiers; 1565 1566 $keys = array_keys($this->_object_to_array(reset($key))); 1567 sort($keys); 1568 1569 foreach ($key as $row) 1570 { 1571 $row = $this->_object_to_array($row); 1572 if (count(array_diff($keys, array_keys($row))) > 0 OR count(array_diff(array_keys($row), $keys)) > 0) 1573 { 1574 // batch function above returns an error on an empty array 1575 $this->qb_set[] = array(); 1576 return; 1577 } 1578 1579 ksort($row); // puts $row in the same order as our keys 1580 1581 if ($escape !== FALSE) 1582 { 1583 $clean = array(); 1584 foreach ($row as $value) 1585 { 1586 $clean[] = $this->escape($value); 1587 } 1588 1589 $row = $clean; 1590 } 1591 1592 $this->qb_set[] = '('.implode(',', $row).')'; 1593 } 1594 1595 foreach ($keys as $k) 1596 { 1597 $this->qb_keys[] = $this->protect_identifiers($k, FALSE, $escape); 1598 } 1599 1600 return $this; 1601 } 1602 1603 // -------------------------------------------------------------------- 1604 1605 /** 1606 * Get INSERT query string 1607 * 1608 * Compiles an insert query and returns the sql 1609 * 1610 * @param string the table to insert into 1611 * @param bool TRUE: reset QB values; FALSE: leave QB values alone 1612 * @return string 1613 */ 1614 public function get_compiled_insert($table = '', $reset = TRUE) 1615 { 1616 if ($this->_validate_insert($table) === FALSE) 1617 { 1618 return FALSE; 1619 } 1620 1621 $sql = $this->_insert( 1622 $this->protect_identifiers( 1623 $this->qb_from[0], TRUE, NULL, FALSE 1624 ), 1625 array_keys($this->qb_set), 1626 array_values($this->qb_set) 1627 ); 1628 1629 if ($reset === TRUE) 1630 { 1631 $this->_reset_write(); 1632 } 1633 1634 return $sql; 1635 } 1636 1637 // -------------------------------------------------------------------- 1638 1639 /** 1640 * Insert 1641 * 1642 * Compiles an insert string and runs the query 1643 * 1644 * @param string the table to insert data into 1645 * @param array an associative array of insert values 1646 * @param bool $escape Whether to escape values and identifiers 1647 * @return bool TRUE on success, FALSE on failure 1648 */ 1649 public function insert($table = '', $set = NULL, $escape = NULL) 1650 { 1651 if ($set !== NULL) 1652 { 1653 $this->set($set, '', $escape); 1654 } 1655 1656 if ($this->_validate_insert($table) === FALSE) 1657 { 1658 return FALSE; 1659 } 1660 1661 $sql = $this->_insert( 1662 $this->protect_identifiers( 1663 $this->qb_from[0], TRUE, $escape, FALSE 1664 ), 1665 array_keys($this->qb_set), 1666 array_values($this->qb_set) 1667 ); 1668 1669 $this->_reset_write(); 1670 return $this->query($sql); 1671 } 1672 1673 // -------------------------------------------------------------------- 1674 1675 /** 1676 * Validate Insert 1677 * 1678 * This method is used by both insert() and get_compiled_insert() to 1679 * validate that the there data is actually being set and that table 1680 * has been chosen to be inserted into. 1681 * 1682 * @param string the table to insert data into 1683 * @return string 1684 */ 1685 protected function _validate_insert($table = '') 1686 { 1687 if (count($this->qb_set) === 0) 1688 { 1689 return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE; 1690 } 1691 1692 if ($table !== '') 1693 { 1694 $this->qb_from[0] = $table; 1695 } 1696 elseif ( ! isset($this->qb_from[0])) 1697 { 1698 return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; 1699 } 1700 1701 return TRUE; 1702 } 1703 1704 // -------------------------------------------------------------------- 1705 1706 /** 1707 * Replace 1708 * 1709 * Compiles an replace into string and runs the query 1710 * 1711 * @param string the table to replace data into 1712 * @param array an associative array of insert values 1713 * @return bool TRUE on success, FALSE on failure 1714 */ 1715 public function replace($table = '', $set = NULL) 1716 { 1717 if ($set !== NULL) 1718 { 1719 $this->set($set); 1720 } 1721 1722 if (count($this->qb_set) === 0) 1723 { 1724 return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE; 1725 } 1726 1727 if ($table === '') 1728 { 1729 if ( ! isset($this->qb_from[0])) 1730 { 1731 return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; 1732 } 1733 1734 $table = $this->qb_from[0]; 1735 } 1736 1737 $sql = $this->_replace($this->protect_identifiers($table, TRUE, NULL, FALSE), array_keys($this->qb_set), array_values($this->qb_set)); 1738 1739 $this->_reset_write(); 1740 return $this->query($sql); 1741 } 1742 1743 // -------------------------------------------------------------------- 1744 1745 /** 1746 * Replace statement 1747 * 1748 * Generates a platform-specific replace string from the supplied data 1749 * 1750 * @param string the table name 1751 * @param array the insert keys 1752 * @param array the insert values 1753 * @return string 1754 */ 1755 protected function _replace($table, $keys, $values) 1756 { 1757 return 'REPLACE INTO '.$table.' ('.implode(', ', $keys).') VALUES ('.implode(', ', $values).')'; 1758 } 1759 1760 // -------------------------------------------------------------------- 1761 1762 /** 1763 * FROM tables 1764 * 1765 * Groups tables in FROM clauses if needed, so there is no confusion 1766 * about operator precedence. 1767 * 1768 * Note: This is only used (and overridden) by MySQL and CUBRID. 1769 * 1770 * @return string 1771 */ 1772 protected function _from_tables() 1773 { 1774 return implode(', ', $this->qb_from); 1775 } 1776 1777 // -------------------------------------------------------------------- 1778 1779 /** 1780 * Get UPDATE query string 1781 * 1782 * Compiles an update query and returns the sql 1783 * 1784 * @param string the table to update 1785 * @param bool TRUE: reset QB values; FALSE: leave QB values alone 1786 * @return string 1787 */ 1788 public function get_compiled_update($table = '', $reset = TRUE) 1789 { 1790 // Combine any cached components with the current statements 1791 $this->_merge_cache(); 1792 1793 if ($this->_validate_update($table) === FALSE) 1794 { 1795 return FALSE; 1796 } 1797 1798 $sql = $this->_update($this->qb_from[0], $this->qb_set); 1799 1800 if ($reset === TRUE) 1801 { 1802 $this->_reset_write(); 1803 } 1804 1805 return $sql; 1806 } 1807 1808 // -------------------------------------------------------------------- 1809 1810 /** 1811 * UPDATE 1812 * 1813 * Compiles an update string and runs the query. 1814 * 1815 * @param string $table 1816 * @param array $set An associative array of update values 1817 * @param mixed $where 1818 * @param int $limit 1819 * @return bool TRUE on success, FALSE on failure 1820 */ 1821 public function update($table = '', $set = NULL, $where = NULL, $limit = NULL) 1822 { 1823 // Combine any cached components with the current statements 1824 $this->_merge_cache(); 1825 1826 if ($set !== NULL) 1827 { 1828 $this->set($set); 1829 } 1830 1831 if ($this->_validate_update($table) === FALSE) 1832 { 1833 return FALSE; 1834 } 1835 1836 if ($where !== NULL) 1837 { 1838 $this->where($where); 1839 } 1840 1841 if ( ! empty($limit)) 1842 { 1843 $this->limit($limit); 1844 } 1845 1846 $sql = $this->_update($this->qb_from[0], $this->qb_set); 1847 $this->_reset_write(); 1848 return $this->query($sql); 1849 } 1850 1851 // -------------------------------------------------------------------- 1852 1853 /** 1854 * Validate Update 1855 * 1856 * This method is used by both update() and get_compiled_update() to 1857 * validate that data is actually being set and that a table has been 1858 * chosen to be update. 1859 * 1860 * @param string the table to update data on 1861 * @return bool 1862 */ 1863 protected function _validate_update($table) 1864 { 1865 if (count($this->qb_set) === 0) 1866 { 1867 return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE; 1868 } 1869 1870 if ($table !== '') 1871 { 1872 $this->qb_from = array($this->protect_identifiers($table, TRUE, NULL, FALSE)); 1873 } 1874 elseif ( ! isset($this->qb_from[0])) 1875 { 1876 return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; 1877 } 1878 1879 return TRUE; 1880 } 1881 1882 // -------------------------------------------------------------------- 1883 1884 /** 1885 * Update_Batch 1886 * 1887 * Compiles an update string and runs the query 1888 * 1889 * @param string the table to retrieve the results from 1890 * @param array an associative array of update values 1891 * @param string the where key 1892 * @return int number of rows affected or FALSE on failure 1893 */ 1894 public function update_batch($table, $set = NULL, $index = NULL, $batch_size = 100) 1895 { 1896 // Combine any cached components with the current statements 1897 $this->_merge_cache(); 1898 1899 if ($index === NULL) 1900 { 1901 return ($this->db_debug) ? $this->display_error('db_must_use_index') : FALSE; 1902 } 1903 1904 if ($set === NULL) 1905 { 1906 if (empty($this->qb_set_ub)) 1907 { 1908 return ($this->db_debug) ? $this->display_error('db_must_use_set') : FALSE; 1909 } 1910 } 1911 else 1912 { 1913 if (empty($set)) 1914 { 1915 return ($this->db_debug) ? $this->display_error('update_batch() called with no data') : FALSE; 1916 } 1917 1918 $this->set_update_batch($set, $index); 1919 } 1920 1921 if (strlen($table) === 0) 1922 { 1923 if ( ! isset($this->qb_from[0])) 1924 { 1925 return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; 1926 } 1927 1928 $table = $this->qb_from[0]; 1929 } 1930 1931 // Batch this baby 1932 $affected_rows = 0; 1933 for ($i = 0, $total = count($this->qb_set_ub); $i < $total; $i += $batch_size) 1934 { 1935 if ($this->query($this->_update_batch($this->protect_identifiers($table, TRUE, NULL, FALSE), array_slice($this->qb_set_ub, $i, $batch_size), $index))) 1936 { 1937 $affected_rows += $this->affected_rows(); 1938 } 1939 1940 $this->qb_where = array(); 1941 } 1942 1943 $this->_reset_write(); 1944 return $affected_rows; 1945 } 1946 1947 // -------------------------------------------------------------------- 1948 1949 /** 1950 * Update_Batch statement 1951 * 1952 * Generates a platform-specific batch update string from the supplied data 1953 * 1954 * @param string $table Table name 1955 * @param array $values Update data 1956 * @param string $index WHERE key 1957 * @return string 1958 */ 1959 protected function _update_batch($table, $values, $index) 1960 { 1961 $ids = array(); 1962 foreach ($values as $key => $val) 1963 { 1964 $ids[] = $val[$index]['value']; 1965 1966 foreach (array_keys($val) as $field) 1967 { 1968 if ($field !== $index) 1969 { 1970 $final[$val[$field]['field']][] = 'WHEN '.$val[$index]['field'].' = '.$val[$index]['value'].' THEN '.$val[$field]['value']; 1971 } 1972 } 1973 } 1974 1975 $cases = ''; 1976 foreach ($final as $k => $v) 1977 { 1978 $cases .= $k." = CASE \n" 1979 .implode("\n", $v)."\n" 1980 .'ELSE '.$k.' END, '; 1981 } 1982 1983 $this->where($val[$index]['field'].' IN('.implode(',', $ids).')', NULL, FALSE); 1984 1985 return 'UPDATE '.$table.' SET '.substr($cases, 0, -2).$this->_compile_wh('qb_where'); 1986 } 1987 1988 // -------------------------------------------------------------------- 1989 1990 /** 1991 * The "set_update_batch" function. Allows key/value pairs to be set for batch updating 1992 * 1993 * @param array 1994 * @param string 1995 * @param bool 1996 * @return CI_DB_query_builder 1997 */ 1998 public function set_update_batch($key, $index = '', $escape = NULL) 1999 { 2000 $key = $this->_object_to_array_batch($key); 2001 2002 if ( ! is_array($key)) 2003 { 2004 // @todo error 2005 } 2006 2007 is_bool($escape) OR $escape = $this->_protect_identifiers; 2008 2009 foreach ($key as $k => $v) 2010 { 2011 $index_set = FALSE; 2012 $clean = array(); 2013 foreach ($v as $k2 => $v2) 2014 { 2015 if ($k2 === $index) 2016 { 2017 $index_set = TRUE; 2018 } 2019 2020 $clean[$k2] = array( 2021 'field' => $this->protect_identifiers($k2, FALSE, $escape), 2022 'value' => ($escape === FALSE ? $v2 : $this->escape($v2)) 2023 ); 2024 } 2025 2026 if ($index_set === FALSE) 2027 { 2028 return $this->display_error('db_batch_missing_index'); 2029 } 2030 2031 $this->qb_set_ub[] = $clean; 2032 } 2033 2034 return $this; 2035 } 2036 2037 // -------------------------------------------------------------------- 2038 2039 /** 2040 * Empty Table 2041 * 2042 * Compiles a delete string and runs "DELETE FROM table" 2043 * 2044 * @param string the table to empty 2045 * @return bool TRUE on success, FALSE on failure 2046 */ 2047 public function empty_table($table = '') 2048 { 2049 if ($table === '') 2050 { 2051 if ( ! isset($this->qb_from[0])) 2052 { 2053 return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; 2054 } 2055 2056 $table = $this->qb_from[0]; 2057 } 2058 else 2059 { 2060 $table = $this->protect_identifiers($table, TRUE, NULL, FALSE); 2061 } 2062 2063 $sql = $this->_delete($table); 2064 $this->_reset_write(); 2065 return $this->query($sql); 2066 } 2067 2068 // -------------------------------------------------------------------- 2069 2070 /** 2071 * Truncate 2072 * 2073 * Compiles a truncate string and runs the query 2074 * If the database does not support the truncate() command 2075 * This function maps to "DELETE FROM table" 2076 * 2077 * @param string the table to truncate 2078 * @return bool TRUE on success, FALSE on failure 2079 */ 2080 public function truncate($table = '') 2081 { 2082 if ($table === '') 2083 { 2084 if ( ! isset($this->qb_from[0])) 2085 { 2086 return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; 2087 } 2088 2089 $table = $this->qb_from[0]; 2090 } 2091 else 2092 { 2093 $table = $this->protect_identifiers($table, TRUE, NULL, FALSE); 2094 } 2095 2096 $sql = $this->_truncate($table); 2097 $this->_reset_write(); 2098 return $this->query($sql); 2099 } 2100 2101 // -------------------------------------------------------------------- 2102 2103 /** 2104 * Truncate statement 2105 * 2106 * Generates a platform-specific truncate string from the supplied data 2107 * 2108 * If the database does not support the truncate() command, 2109 * then this method maps to 'DELETE FROM table' 2110 * 2111 * @param string the table name 2112 * @return string 2113 */ 2114 protected function _truncate($table) 2115 { 2116 return 'TRUNCATE '.$table; 2117 } 2118 2119 // -------------------------------------------------------------------- 2120 2121 /** 2122 * Get DELETE query string 2123 * 2124 * Compiles a delete query string and returns the sql 2125 * 2126 * @param string the table to delete from 2127 * @param bool TRUE: reset QB values; FALSE: leave QB values alone 2128 * @return string 2129 */ 2130 public function get_compiled_delete($table = '', $reset = TRUE) 2131 { 2132 $this->return_delete_sql = TRUE; 2133 $sql = $this->delete($table, '', NULL, $reset); 2134 $this->return_delete_sql = FALSE; 2135 return $sql; 2136 } 2137 2138 // -------------------------------------------------------------------- 2139 2140 /** 2141 * Delete 2142 * 2143 * Compiles a delete string and runs the query 2144 * 2145 * @param mixed the table(s) to delete from. String or array 2146 * @param mixed the where clause 2147 * @param mixed the limit clause 2148 * @param bool 2149 * @return mixed 2150 */ 2151 public function delete($table = '', $where = '', $limit = NULL, $reset_data = TRUE) 2152 { 2153 // Combine any cached components with the current statements 2154 $this->_merge_cache(); 2155 2156 if ($table === '') 2157 { 2158 if ( ! isset($this->qb_from[0])) 2159 { 2160 return ($this->db_debug) ? $this->display_error('db_must_set_table') : FALSE; 2161 } 2162 2163 $table = $this->qb_from[0]; 2164 } 2165 elseif (is_array($table)) 2166 { 2167 empty($where) && $reset_data = FALSE; 2168 2169 foreach ($table as $single_table) 2170 { 2171 $this->delete($single_table, $where, $limit, $reset_data); 2172 } 2173 2174 return; 2175 } 2176 else 2177 { 2178 $table = $this->protect_identifiers($table, TRUE, NULL, FALSE); 2179 } 2180 2181 if ($where !== '') 2182 { 2183 $this->where($where); 2184 } 2185 2186 if ( ! empty($limit)) 2187 { 2188 $this->limit($limit); 2189 } 2190 2191 if (count($this->qb_where) === 0) 2192 { 2193 return ($this->db_debug) ? $this->display_error('db_del_must_use_where') : FALSE; 2194 } 2195 2196 $sql = $this->_delete($table); 2197 if ($reset_data) 2198 { 2199 $this->_reset_write(); 2200 } 2201 2202 return ($this->return_delete_sql === TRUE) ? $sql : $this->query($sql); 2203 } 2204 2205 // -------------------------------------------------------------------- 2206 2207 /** 2208 * Delete statement 2209 * 2210 * Generates a platform-specific delete string from the supplied data 2211 * 2212 * @param string the table name 2213 * @return string 2214 */ 2215 protected function _delete($table) 2216 { 2217 return 'DELETE FROM '.$table.$this->_compile_wh('qb_where') 2218 .($this->qb_limit !== FALSE ? ' LIMIT '.$this->qb_limit : ''); 2219 } 2220 2221 // -------------------------------------------------------------------- 2222 2223 /** 2224 * DB Prefix 2225 * 2226 * Prepends a database prefix if one exists in configuration 2227 * 2228 * @param string the table 2229 * @return string 2230 */ 2231 public function dbprefix($table = '') 2232 { 2233 if ($table === '') 2234 { 2235 $this->display_error('db_table_name_required'); 2236 } 2237 2238 return $this->dbprefix.$table; 2239 } 2240 2241 // -------------------------------------------------------------------- 2242 2243 /** 2244 * Set DB Prefix 2245 * 2246 * Set's the DB Prefix to something new without needing to reconnect 2247 * 2248 * @param string the prefix 2249 * @return string 2250 */ 2251 public function set_dbprefix($prefix = '') 2252 { 2253 return $this->dbprefix = $prefix; 2254 } 2255 2256 // -------------------------------------------------------------------- 2257 2258 /** 2259 * Track Aliases 2260 * 2261 * Used to track SQL statements written with aliased tables. 2262 * 2263 * @param string The table to inspect 2264 * @return string 2265 */ 2266 protected function _track_aliases($table) 2267 { 2268 if (is_array($table)) 2269 { 2270 foreach ($table as $t) 2271 { 2272 $this->_track_aliases($t); 2273 } 2274 return; 2275 } 2276 2277 // Does the string contain a comma? If so, we need to separate 2278 // the string into discreet statements 2279 if (strpos($table, ',') !== FALSE) 2280 { 2281 return $this->_track_aliases(explode(',', $table)); 2282 } 2283 2284 // if a table alias is used we can recognize it by a space 2285 if (strpos($table, ' ') !== FALSE) 2286 { 2287 // if the alias is written with the AS keyword, remove it 2288 $table = preg_replace('/\s+AS\s+/i', ' ', $table); 2289 2290 // Grab the alias 2291 $table = trim(strrchr($table, ' ')); 2292 2293 // Store the alias, if it doesn't already exist 2294 if ( ! in_array($table, $this->qb_aliased_tables, TRUE)) 2295 { 2296 $this->qb_aliased_tables[] = $table; 2297 if ($this->qb_caching === TRUE && ! in_array($table, $this->qb_cache_aliased_tables, TRUE)) 2298 { 2299 $this->qb_cache_aliased_tables[] = $table; 2300 $this->qb_cache_exists[] = 'aliased_tables'; 2301 } 2302 } 2303 } 2304 } 2305 2306 // -------------------------------------------------------------------- 2307 2308 /** 2309 * Compile the SELECT statement 2310 * 2311 * Generates a query string based on which functions were used. 2312 * Should not be called directly. 2313 * 2314 * @param bool $select_override 2315 * @return string 2316 */ 2317 protected function _compile_select($select_override = FALSE) 2318 { 2319 // Combine any cached components with the current statements 2320 $this->_merge_cache(); 2321 2322 // Write the "select" portion of the query 2323 if ($select_override !== FALSE) 2324 { 2325 $sql = $select_override; 2326 } 2327 else 2328 { 2329 $sql = ( ! $this->qb_distinct) ? 'SELECT ' : 'SELECT DISTINCT '; 2330 2331 if (count($this->qb_select) === 0) 2332 { 2333 $sql .= '*'; 2334 } 2335 else 2336 { 2337 // Cycle through the "select" portion of the query and prep each column name. 2338 // The reason we protect identifiers here rather than in the select() function 2339 // is because until the user calls the from() function we don't know if there are aliases 2340 foreach ($this->qb_select as $key => $val) 2341 { 2342 $no_escape = isset($this->qb_no_escape[$key]) ? $this->qb_no_escape[$key] : NULL; 2343 $this->qb_select[$key] = $this->protect_identifiers($val, FALSE, $no_escape); 2344 } 2345 2346 $sql .= implode(', ', $this->qb_select); 2347 } 2348 } 2349 2350 // Write the "FROM" portion of the query 2351 if (count($this->qb_from) > 0) 2352 { 2353 $sql .= "\nFROM ".$this->_from_tables(); 2354 } 2355 2356 // Write the "JOIN" portion of the query 2357 if (count($this->qb_join) > 0) 2358 { 2359 $sql .= "\n".implode("\n", $this->qb_join); 2360 } 2361 2362 $sql .= $this->_compile_wh('qb_where') 2363 .$this->_compile_group_by() 2364 .$this->_compile_wh('qb_having') 2365 .$this->_compile_order_by(); // ORDER BY 2366 2367 // LIMIT 2368 if ($this->qb_limit !== FALSE OR $this->qb_offset) 2369 { 2370 return $this->_limit($sql."\n"); 2371 } 2372 2373 return $sql; 2374 } 2375 2376 // -------------------------------------------------------------------- 2377 2378 /** 2379 * Compile WHERE, HAVING statements 2380 * 2381 * Escapes identifiers in WHERE and HAVING statements at execution time. 2382 * 2383 * Required so that aliases are tracked properly, regardless of whether 2384 * where(), or_where(), having(), or_having are called prior to from(), 2385 * join() and dbprefix is added only if needed. 2386 * 2387 * @param string $qb_key 'qb_where' or 'qb_having' 2388 * @return string SQL statement 2389 */ 2390 protected function _compile_wh($qb_key) 2391 { 2392 if (count($this->$qb_key) > 0) 2393 { 2394 for ($i = 0, $c = count($this->$qb_key); $i < $c; $i++) 2395 { 2396 // Is this condition already compiled? 2397 if (is_string($this->{$qb_key}[$i])) 2398 { 2399 continue; 2400 } 2401 elseif ($this->{$qb_key}[$i]['escape'] === FALSE) 2402 { 2403 $this->{$qb_key}[$i] = $this->{$qb_key}[$i]['condition'].(isset($this->{$qb_key}[$i]['value']) ? ' '.$this->{$qb_key}[$i]['value'] : ''); 2404 continue; 2405 } 2406 2407 // Split multiple conditions 2408 $conditions = preg_split( 2409 '/((?:^|\s+)AND\s+|(?:^|\s+)OR\s+)/i', 2410 $this->{$qb_key}[$i]['condition'], 2411 -1, 2412 PREG_SPLIT_DELIM_CAPTURE | PREG_SPLIT_NO_EMPTY 2413 ); 2414 2415 for ($ci = 0, $cc = count($conditions); $ci < $cc; $ci++) 2416 { 2417 if (($op = $this->_get_operator($conditions[$ci])) === FALSE 2418 OR ! preg_match('/^(\(?)(.*)('.preg_quote($op, '/').')\s*(.*(?<!\)))?(\)?)$/i', $conditions[$ci], $matches)) 2419 { 2420 continue; 2421 } 2422 2423 // $matches = array( 2424 // 0 => '(test <= foo)', /* the whole thing */ 2425 // 1 => '(', /* optional */ 2426 // 2 => 'test', /* the field name */ 2427 // 3 => ' <= ', /* $op */ 2428 // 4 => 'foo', /* optional, if $op is e.g. 'IS NULL' */ 2429 // 5 => ')' /* optional */ 2430 // ); 2431 2432 if ( ! empty($matches[4])) 2433 { 2434 $this->_is_literal($matches[4]) OR $matches[4] = $this->protect_identifiers(trim($matches[4])); 2435 $matches[4] = ' '.$matches[4]; 2436 } 2437 2438 $conditions[$ci] = $matches[1].$this->protect_identifiers(trim($matches[2])) 2439 .' '.trim($matches[3]).$matches[4].$matches[5]; 2440 } 2441 2442 $this->{$qb_key}[$i] = implode('', $conditions).(isset($this->{$qb_key}[$i]['value']) ? ' '.$this->{$qb_key}[$i]['value'] : ''); 2443 } 2444 2445 return ($qb_key === 'qb_having' ? "\nHAVING " : "\nWHERE ") 2446 .implode("\n", $this->$qb_key); 2447 } 2448 2449 return ''; 2450 } 2451 2452 // -------------------------------------------------------------------- 2453 2454 /** 2455 * Compile GROUP BY 2456 * 2457 * Escapes identifiers in GROUP BY statements at execution time. 2458 * 2459 * Required so that aliases are tracked properly, regardless of whether 2460 * group_by() is called prior to from(), join() and dbprefix is added 2461 * only if needed. 2462 * 2463 * @return string SQL statement 2464 */ 2465 protected function _compile_group_by() 2466 { 2467 if (count($this->qb_groupby) > 0) 2468 { 2469 for ($i = 0, $c = count($this->qb_groupby); $i < $c; $i++) 2470 { 2471 // Is it already compiled? 2472 if (is_string($this->qb_groupby[$i])) 2473 { 2474 continue; 2475 } 2476 2477 $this->qb_groupby[$i] = ($this->qb_groupby[$i]['escape'] === FALSE OR $this->_is_literal($this->qb_groupby[$i]['field'])) 2478 ? $this->qb_groupby[$i]['field'] 2479 : $this->protect_identifiers($this->qb_groupby[$i]['field']); 2480 } 2481 2482 return "\nGROUP BY ".implode(', ', $this->qb_groupby); 2483 } 2484 2485 return ''; 2486 } 2487 2488 // -------------------------------------------------------------------- 2489 2490 /** 2491 * Compile ORDER BY 2492 * 2493 * Escapes identifiers in ORDER BY statements at execution time. 2494 * 2495 * Required so that aliases are tracked properly, regardless of whether 2496 * order_by() is called prior to from(), join() and dbprefix is added 2497 * only if needed. 2498 * 2499 * @return string SQL statement 2500 */ 2501 protected function _compile_order_by() 2502 { 2503 if (empty($this->qb_orderby)) 2504 { 2505 return ''; 2506 } 2507 2508 for ($i = 0, $c = count($this->qb_orderby); $i < $c; $i++) 2509 { 2510 if (is_string($this->qb_orderby[$i])) 2511 { 2512 continue; 2513 } 2514 2515 if ($this->qb_orderby[$i]['escape'] !== FALSE && ! $this->_is_literal($this->qb_orderby[$i]['field'])) 2516 { 2517 $this->qb_orderby[$i]['field'] = $this->protect_identifiers($this->qb_orderby[$i]['field']); 2518 } 2519 2520 $this->qb_orderby[$i] = $this->qb_orderby[$i]['field'].$this->qb_orderby[$i]['direction']; 2521 } 2522 2523 return "\nORDER BY ".implode(', ', $this->qb_orderby); 2524 } 2525 2526 // -------------------------------------------------------------------- 2527 2528 /** 2529 * Object to Array 2530 * 2531 * Takes an object as input and converts the class variables to array key/vals 2532 * 2533 * @param object 2534 * @return array 2535 */ 2536 protected function _object_to_array($object) 2537 { 2538 if ( ! is_object($object)) 2539 { 2540 return $object; 2541 } 2542 2543 $array = array(); 2544 foreach (get_object_vars($object) as $key => $val) 2545 { 2546 // There are some built in keys we need to ignore for this conversion 2547 if ( ! is_object($val) && ! is_array($val) && $key !== '_parent_name') 2548 { 2549 $array[$key] = $val; 2550 } 2551 } 2552 2553 return $array; 2554 } 2555 2556 // -------------------------------------------------------------------- 2557 2558 /** 2559 * Object to Array 2560 * 2561 * Takes an object as input and converts the class variables to array key/vals 2562 * 2563 * @param object 2564 * @return array 2565 */ 2566 protected function _object_to_array_batch($object) 2567 { 2568 if ( ! is_object($object)) 2569 { 2570 return $object; 2571 } 2572 2573 $array = array(); 2574 $out = get_object_vars($object); 2575 $fields = array_keys($out); 2576 2577 foreach ($fields as $val) 2578 { 2579 // There are some built in keys we need to ignore for this conversion 2580 if ($val !== '_parent_name') 2581 { 2582 $i = 0; 2583 foreach ($out[$val] as $data) 2584 { 2585 $array[$i++][$val] = $data; 2586 } 2587 } 2588 } 2589 2590 return $array; 2591 } 2592 2593 // -------------------------------------------------------------------- 2594 2595 /** 2596 * Start Cache 2597 * 2598 * Starts QB caching 2599 * 2600 * @return CI_DB_query_builder 2601 */ 2602 public function start_cache() 2603 { 2604 $this->qb_caching = TRUE; 2605 return $this; 2606 } 2607 2608 // -------------------------------------------------------------------- 2609 2610 /** 2611 * Stop Cache 2612 * 2613 * Stops QB caching 2614 * 2615 * @return CI_DB_query_builder 2616 */ 2617 public function stop_cache() 2618 { 2619 $this->qb_caching = FALSE; 2620 return $this; 2621 } 2622 2623 // -------------------------------------------------------------------- 2624 2625 /** 2626 * Flush Cache 2627 * 2628 * Empties the QB cache 2629 * 2630 * @return CI_DB_query_builder 2631 */ 2632 public function flush_cache() 2633 { 2634 $this->_reset_run(array( 2635 'qb_cache_select' => array(), 2636 'qb_cache_from' => array(), 2637 'qb_cache_join' => array(), 2638 'qb_cache_where' => array(), 2639 'qb_cache_groupby' => array(), 2640 'qb_cache_having' => array(), 2641 'qb_cache_orderby' => array(), 2642 'qb_cache_set' => array(), 2643 'qb_cache_exists' => array(), 2644 'qb_cache_no_escape' => array(), 2645 'qb_cache_aliased_tables' => array() 2646 )); 2647 2648 return $this; 2649 } 2650 2651 // -------------------------------------------------------------------- 2652 2653 /** 2654 * Merge Cache 2655 * 2656 * When called, this function merges any cached QB arrays with 2657 * locally called ones. 2658 * 2659 * @return void 2660 */ 2661 protected function _merge_cache() 2662 { 2663 if (count($this->qb_cache_exists) === 0) 2664 { 2665 return; 2666 } 2667 elseif (in_array('select', $this->qb_cache_exists, TRUE)) 2668 { 2669 $qb_no_escape = $this->qb_cache_no_escape; 2670 } 2671 2672 foreach (array_unique($this->qb_cache_exists) as $val) // select, from, etc. 2673 { 2674 $qb_variable = 'qb_'.$val; 2675 $qb_cache_var = 'qb_cache_'.$val; 2676 $qb_new = $this->$qb_cache_var; 2677 2678 for ($i = 0, $c = count($this->$qb_variable); $i < $c; $i++) 2679 { 2680 if ( ! in_array($this->{$qb_variable}[$i], $qb_new, TRUE)) 2681 { 2682 $qb_new[] = $this->{$qb_variable}[$i]; 2683 if ($val === 'select') 2684 { 2685 $qb_no_escape[] = $this->qb_no_escape[$i]; 2686 } 2687 } 2688 } 2689 2690 $this->$qb_variable = $qb_new; 2691 if ($val === 'select') 2692 { 2693 $this->qb_no_escape = $qb_no_escape; 2694 } 2695 } 2696 } 2697 2698 // -------------------------------------------------------------------- 2699 2700 /** 2701 * Is literal 2702 * 2703 * Determines if a string represents a literal value or a field name 2704 * 2705 * @param string $str 2706 * @return bool 2707 */ 2708 protected function _is_literal($str) 2709 { 2710 $str = trim($str); 2711 2712 if (empty($str) OR ctype_digit($str) OR (string) (float) $str === $str OR in_array(strtoupper($str), array('TRUE', 'FALSE'), TRUE)) 2713 { 2714 return TRUE; 2715 } 2716 2717 static $_str; 2718 2719 if (empty($_str)) 2720 { 2721 $_str = ($this->_escape_char !== '"') 2722 ? array('"', "'") : array("'"); 2723 } 2724 2725 return in_array($str[0], $_str, TRUE); 2726 } 2727 2728 // -------------------------------------------------------------------- 2729 2730 /** 2731 * Reset Query Builder values. 2732 * 2733 * Publicly-visible method to reset the QB values. 2734 * 2735 * @return CI_DB_query_builder 2736 */ 2737 public function reset_query() 2738 { 2739 $this->_reset_select(); 2740 $this->_reset_write(); 2741 return $this; 2742 } 2743 2744 // -------------------------------------------------------------------- 2745 2746 /** 2747 * Resets the query builder values. Called by the get() function 2748 * 2749 * @param array An array of fields to reset 2750 * @return void 2751 */ 2752 protected function _reset_run($qb_reset_items) 2753 { 2754 foreach ($qb_reset_items as $item => $default_value) 2755 { 2756 $this->$item = $default_value; 2757 } 2758 } 2759 2760 // -------------------------------------------------------------------- 2761 2762 /** 2763 * Resets the query builder values. Called by the get() function 2764 * 2765 * @return void 2766 */ 2767 protected function _reset_select() 2768 { 2769 $this->_reset_run(array( 2770 'qb_select' => array(), 2771 'qb_from' => array(), 2772 'qb_join' => array(), 2773 'qb_where' => array(), 2774 'qb_groupby' => array(), 2775 'qb_having' => array(), 2776 'qb_orderby' => array(), 2777 'qb_aliased_tables' => array(), 2778 'qb_no_escape' => array(), 2779 'qb_distinct' => FALSE, 2780 'qb_limit' => FALSE, 2781 'qb_offset' => FALSE 2782 )); 2783 } 2784 2785 // -------------------------------------------------------------------- 2786 2787 /** 2788 * Resets the query builder "write" values. 2789 * 2790 * Called by the insert() update() insert_batch() update_batch() and delete() functions 2791 * 2792 * @return void 2793 */ 2794 protected function _reset_write() 2795 { 2796 $this->_reset_run(array( 2797 'qb_set' => array(), 2798 'qb_set_ub' => array(), 2799 'qb_from' => array(), 2800 'qb_join' => array(), 2801 'qb_where' => array(), 2802 'qb_orderby' => array(), 2803 'qb_keys' => array(), 2804 'qb_limit' => FALSE 2805 )); 2806 } 2807 2808} 2809