database/DB_active_rec.php

   1 <?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');
   2 /**
   3  * CodeIgniter
   4  *
   5  * An open source application development framework for PHP 4.3.2 or newer
   6  *
   7  * @package             CodeIgniter
   8  * @author              ExpressionEngine Dev Team
   9  * @copyright   Copyright (c) 2008, EllisLab, Inc.
  10  * @license             http://codeigniter.com/user_guide/license.html
  11  * @link                http://codeigniter.com
  12  * @since               Version 1.0
  13  * @filesource
  14  */
  15
  16 // ------------------------------------------------------------------------
  17
  18 /**
  19  * Active Record Class
  20  *
  21  * This is the platform-independent base Active Record implementation class.
  22  *
  23  * @package             CodeIgniter
  24  * @subpackage  Drivers
  25  * @category    Database
  26  * @author              ExpressionEngine Dev Team
  27  * @link                http://codeigniter.com/user_guide/database/
  28  */
  29 class CI_DB_active_record extends CI_DB_driver {
  30
  31         var $ar_select                          = array();
  32         var $ar_distinct                        = FALSE;
  33         var $ar_from                            = array();
  34         var $ar_join                            = array();
  35         var $ar_where                           = array();
  36         var $ar_like                            = array();
  37         var $ar_groupby                         = array();
  38         var $ar_having                          = array();
  39         var $ar_limit                           = FALSE;
  40         var $ar_offset                          = FALSE;
  41         var $ar_order                           = FALSE;
  42         var $ar_orderby                         = array();
  43         var $ar_set                                     = array();
  44         var $ar_wherein                         = array();
  45         var $ar_aliased_tables          = array();
  46         var $ar_store_array                     = array();
  47
  48         // Active Record Caching variables
  49         var $ar_caching                         = FALSE;
  50         var $ar_cache_exists            = array();
  51         var $ar_cache_select            = array();
  52         var $ar_cache_from                      = array();
  53         var $ar_cache_join                      = array();
  54         var $ar_cache_where                     = array();
  55         var $ar_cache_like                      = array();
  56         var $ar_cache_groupby           = array();
  57         var $ar_cache_having            = array();
  58         var $ar_cache_orderby           = array();
  59         var $ar_cache_set                       = array();
  60
  61
  62         // --------------------------------------------------------------------
  63
  64         /**
  65          * Select
  66          *
  67          * Generates the SELECT portion of the query
  68          *
  69          * @access      public
  70          * @param       string
  71          * @return      object
  72          */
  73         function select($select = '*', $escape = NULL)
  74         {
  75                 // Set the global value if this was sepecified
  76                 if (is_bool($escape))
  77                 {
  78                         $this->_protect_identifiers = $escape;
  79                 }
  80
  81                 if (is_string($select))
  82                 {
  83                         $select = explode(',', $select);
  84                 }
  85
  86                 foreach ($select as $val)
  87                 {
  88                         $val = trim($val);
  89
  90                         if ($val != '')
  91                         {
  92                                 $this->ar_select[] = $val;
  93
  94                                 if ($this->ar_caching === TRUE)
  95                                 {
  96                                         $this->ar_cache_select[] = $val;
  97                                         $this->ar_cache_exists[] = 'select';
  98                                 }
  99                         }
 100                 }
 101                 return $this;
 102         }
 103
 104         // --------------------------------------------------------------------
 105
 106         /**
 107          * Select Max
 108          *
 109          * Generates a SELECT MAX(field) portion of a query
 110          *
 111          * @access      public
 112          * @param       string  the field
 113          * @param       string  an alias
 114          * @return      object
 115          */
 116         function select_max($select = '', $alias = '')
 117         {
 118                 return $this->_max_min_avg_sum($select, $alias, 'MAX');
 119         }
 120
 121         // --------------------------------------------------------------------
 122
 123         /**
 124          * Select Min
 125          *
 126          * Generates a SELECT MIN(field) portion of a query
 127          *
 128          * @access      public
 129          * @param       string  the field
 130          * @param       string  an alias
 131          * @return      object
 132          */
 133         function select_min($select = '', $alias = '')
 134         {
 135                 return $this->_max_min_avg_sum($select, $alias, 'MIN');
 136         }
 137
 138         // --------------------------------------------------------------------
 139
 140         /**
 141          * Select Average
 142          *
 143          * Generates a SELECT AVG(field) portion of a query
 144          *
 145          * @access      public
 146          * @param       string  the field
 147          * @param       string  an alias
 148          * @return      object
 149          */
 150         function select_avg($select = '', $alias = '')
 151         {
 152                 return $this->_max_min_avg_sum($select, $alias, 'AVG');
 153         }
 154
 155         // --------------------------------------------------------------------
 156
 157         /**
 158          * Select Sum
 159          *
 160          * Generates a SELECT SUM(field) portion of a query
 161          *
 162          * @access      public
 163          * @param       string  the field
 164          * @param       string  an alias
 165          * @return      object
 166          */
 167         function select_sum($select = '', $alias = '')
 168         {
 169                 return $this->_max_min_avg_sum($select, $alias, 'SUM');
 170         }
 171
 172         // --------------------------------------------------------------------
 173
 174         /**
 175          * Processing Function for the four functions above:
 176          *
 177          *      select_max()
 178          *      select_min()
 179          *      select_avg()
 180          *  select_sum()
 181          *
 182          * @access      public
 183          * @param       string  the field
 184          * @param       string  an alias
 185          * @return      object
 186          */
 187         function _max_min_avg_sum($select = '', $alias = '', $type = 'MAX')
 188         {
 189                 if ( ! is_string($select) OR $select == '')
 190                 {
 191                         $this->display_error('db_invalid_query');
 192                 }
 193
 194                 $type = strtoupper($type);
 195
 196                 if ( ! in_array($type, array('MAX', 'MIN', 'AVG', 'SUM')))
 197                 {
 198                         show_error('Invalid function type: '.$type);
 199                 }
 200
 201                 if ($alias == '')
 202                 {
 203                         $alias = $this->_create_alias_from_table(trim($select));
 204                 }
 205
 206                 $sql = $type.'('.$this->_protect_identifiers(trim($select)).') AS '.$alias;
 207
 208                 $this->ar_select[] = $sql;
 209
 210                 if ($this->ar_caching === TRUE)
 211                 {
 212                         $this->ar_cache_select[] = $sql;
 213                         $this->ar_cache_exists[] = 'select';
 214                 }
 215
 216                 return $this;
 217         }
 218
 219         // --------------------------------------------------------------------
 220
 221         /**
 222          * Determines the alias name based on the table
 223          *
 224          * @access      private
 225          * @param       string
 226          * @return      string
 227          */
 228         function _create_alias_from_table($item)
 229         {
 230                 if (strpos($item, '.') !== FALSE)
 231                 {
 232                         return end(explode('.', $item));
 233                 }
 234
 235                 return $item;
 236         }
 237
 238         // --------------------------------------------------------------------
 239
 240         /**
 241          * DISTINCT
 242          *
 243          * Sets a flag which tells the query string compiler to add DISTINCT
 244          *
 245          * @access      public
 246          * @param       bool
 247          * @return      object
 248          */
 249         function distinct($val = TRUE)
 250         {
 251                 $this->ar_distinct = (is_bool($val)) ? $val : TRUE;
 252                 return $this;
 253         }
 254
 255         // --------------------------------------------------------------------
 256
 257         /**
 258          * From
 259          *
 260          * Generates the FROM portion of the query
 261          *
 262          * @access      public
 263          * @param       mixed   can be a string or array
 264          * @return      object
 265          */
 266         function from($from)
 267         {
 268                 foreach ((array)$from as $val)
 269                 {
 270                         // Extract any aliases that might exist.  We use this information
 271                         // in the _protect_identifiers to know whether to add a table prefix
 272                         $this->_track_aliases($val);
 273
 274                         $this->ar_from[] = $this->_protect_identifiers($val, TRUE, NULL, FALSE);
 275
 276                         if ($this->ar_caching === TRUE)
 277                         {
 278                                 $this->ar_cache_from[] = $this->_protect_identifiers($val, TRUE, NULL, FALSE);
 279                                 $this->ar_cache_exists[] = 'from';
 280                         }
 281                 }
 282
 283                 return $this;
 284         }
 285
 286         // --------------------------------------------------------------------
 287
 288         /**
 289          * Join
 290          *
 291          * Generates the JOIN portion of the query
 292          *
 293          * @access      public
 294          * @param       string
 295          * @param       string  the join condition
 296          * @param       string  the type of join
 297          * @return      object
 298          */
 299         function join($table, $cond, $type = '')
 300         {
 301                 if ($type != '')
 302                 {
 303                         $type = strtoupper(trim($type));
 304
 305                         if ( ! in_array($type, array('LEFT', 'RIGHT', 'OUTER', 'INNER', 'LEFT OUTER', 'RIGHT OUTER')))
 306                         {
 307                                 $type = '';
 308                         }
 309                         else
 310                         {
 311                                 $type .= ' ';
 312                         }
 313                 }
 314
 315                 // Extract any aliases that might exist.  We use this information
 316                 // in the _protect_identifiers to know whether to add a table prefix
 317                 $this->_track_aliases($table);
 318
 319                 // Strip apart the condition and protect the identifiers
 320                 if (preg_match('/([\w\.]+)([\W\s]+)(.+)/', $cond, $match))
 321                 {
 322                         $match[1] = $this->_protect_identifiers($match[1]);
 323                         $match[3] = $this->_protect_identifiers($match[3]);
 324
 325                         $cond = $match[1].$match[2].$match[3];
 326                 }
 327
 328                 // Assemble the JOIN statement
 329                 $join = $type.'JOIN '.$this->_protect_identifiers($table, TRUE, NULL, FALSE).' ON '.$cond;
 330
 331                 $this->ar_join[] = $join;
 332                 if ($this->ar_caching === TRUE)
 333                 {
 334                         $this->ar_cache_join[] = $join;
 335                         $this->ar_cache_exists[] = 'join';
 336                 }
 337
 338                 return $this;
 339         }
 340
 341         // --------------------------------------------------------------------
 342
 343         /**
 344          * Where
 345          *
 346          * Generates the WHERE portion of the query. Separates
 347          * multiple calls with AND
 348          *
 349          * @access      public
 350          * @param       mixed
 351          * @param       mixed
 352          * @return      object
 353          */
 354         function where($key, $value = NULL, $escape = TRUE)
 355         {
 356                 return $this->_where($key, $value, 'AND ', $escape);
 357         }
 358
 359         // --------------------------------------------------------------------
 360
 361         /**
 362          * OR Where
 363          *
 364          * Generates the WHERE portion of the query. Separates
 365          * multiple calls with OR
 366          *
 367          * @access      public
 368          * @param       mixed
 369          * @param       mixed
 370          * @return      object
 371          */
 372         function or_where($key, $value = NULL, $escape = TRUE)
 373         {
 374                 return $this->_where($key, $value, 'OR ', $escape);
 375         }
 376
 377         // --------------------------------------------------------------------
 378
 379         /**
 380          * orwhere() is an alias of or_where()
 381          * this function is here for backwards compatibility, as
 382          * orwhere() has been deprecated
 383          */
 384         function orwhere($key, $value = NULL, $escape = TRUE)
 385         {
 386                 return $this->or_where($key, $value, $escape);
 387         }
 388
 389         // --------------------------------------------------------------------
 390
 391         /**
 392          * Where
 393          *
 394          * Called by where() or orwhere()
 395          *
 396          * @access      private
 397          * @param       mixed
 398          * @param       mixed
 399          * @param       string
 400          * @return      object
 401          */
 402         function _where($key, $value = NULL, $type = 'AND ', $escape = NULL)
 403         {
 404                 if ( ! is_array($key))
 405                 {
 406                         $key = array($key => $value);
 407                 }
 408
 409                 // If the escape value was not set will will base it on the global setting
 410                 if ( ! is_bool($escape))
 411                 {
 412                         $escape = $this->_protect_identifiers;
 413                 }
 414
 415                 foreach ($key as $k => $v)
 416                 {
 417                         $prefix = (count($this->ar_where) == 0 AND count($this->ar_cache_where) == 0) ? '' : $type;
 418
 419                         if (is_null($v) && ! $this->_has_operator($k))
 420                         {
 421                                 // value appears not to have been set, assign the test to IS NULL
 422                                 $k .= ' IS NULL';
 423                         }
 424
 425                         if ( ! is_null($v))
 426                         {
 427                                 if ($escape === TRUE)
 428                                 {
 429                                         $k = $this->_protect_identifiers($k, FALSE, $escape);
 430
 431                                         $v = ' '.$this->escape($v);
 432                                 }
 433
 434                                 if ( ! $this->_has_operator($k))
 435                                 {
 436                                         $k .= ' =';
 437                                 }
 438                         }
 439                         else
 440                         {
 441                                 $k = $this->_protect_identifiers($k, FALSE, $escape);
 442                         }
 443
 444                         $this->ar_where[] = $prefix.$k.$v;
 445
 446                         if ($this->ar_caching === TRUE)
 447                         {
 448                                 $this->ar_cache_where[] = $prefix.$k.$v;
 449                                 $this->ar_cache_exists[] = 'where';
 450                         }
 451
 452                 }
 453
 454                 return $this;
 455         }
 456
 457         // --------------------------------------------------------------------
 458
 459         /**
 460          * Where_in
 461          *
 462          * Generates a WHERE field IN ('item', 'item') SQL query joined with
 463          * AND if appropriate
 464          *
 465          * @access      public
 466          * @param       string  The field to search
 467          * @param       array   The values searched on
 468          * @return      object
 469          */
 470         function where_in($key = NULL, $values = NULL)
 471         {
 472                 return $this->_where_in($key, $values);
 473         }
 474
 475         // --------------------------------------------------------------------
 476
 477         /**
 478          * Where_in_or
 479          *
 480          * Generates a WHERE field IN ('item', 'item') SQL query joined with
 481          * OR if appropriate
 482          *
 483          * @access      public
 484          * @param       string  The field to search
 485          * @param       array   The values searched on
 486          * @return      object
 487          */
 488         function or_where_in($key = NULL, $values = NULL)
 489         {
 490                 return $this->_where_in($key, $values, FALSE, 'OR ');
 491         }
 492
 493         // --------------------------------------------------------------------
 494
 495         /**
 496          * Where_not_in
 497          *
 498          * Generates a WHERE field NOT IN ('item', 'item') SQL query joined
 499          * with AND if appropriate
 500          *
 501          * @access      public
 502          * @param       string  The field to search
 503          * @param       array   The values searched on
 504          * @return      object
 505          */
 506         function where_not_in($key = NULL, $values = NULL)
 507         {
 508                 return $this->_where_in($key, $values, TRUE);
 509         }
 510
 511         // --------------------------------------------------------------------
 512
 513         /**
 514          * Where_not_in_or
 515          *
 516          * Generates a WHERE field NOT IN ('item', 'item') SQL query joined
 517          * with OR if appropriate
 518          *
 519          * @access      public
 520          * @param       string  The field to search
 521          * @param       array   The values searched on
 522          * @return      object
 523          */
 524         function or_where_not_in($key = NULL, $values = NULL)
 525         {
 526                 return $this->_where_in($key, $values, TRUE, 'OR ');
 527         }
 528
 529         // --------------------------------------------------------------------
 530
 531         /**
 532          * Where_in
 533          *
 534          * Called by where_in, where_in_or, where_not_in, where_not_in_or
 535          *
 536          * @access      public
 537          * @param       string  The field to search
 538          * @param       array   The values searched on
 539          * @param       boolean If the statement would be IN or NOT IN
 540          * @param       string
 541          * @return      object
 542          */
 543         function _where_in($key = NULL, $values = NULL, $not = FALSE, $type = 'AND ')
 544         {
 545                 if ($key === NULL OR $values === NULL)
 546                 {
 547                         return;
 548                 }
 549
 550                 if ( ! is_array($values))
 551                 {
 552                         $values = array($values);
 553                 }
 554
 555                 $not = ($not) ? ' NOT' : '';
 556
 557                 foreach ($values as $value)
 558                 {
 559                         $this->ar_wherein[] = $this->escape($value);
 560                 }
 561
 562                 $prefix = (count($this->ar_where) == 0) ? '' : $type;
 563
 564                 $where_in = $prefix . $this->_protect_identifiers($key) . $not . " IN (" . implode(", ", $this->ar_wherein) . ") ";
 565
 566                 $this->ar_where[] = $where_in;
 567                 if ($this->ar_caching === TRUE)
 568                 {
 569                         $this->ar_cache_where[] = $where_in;
 570                         $this->ar_cache_exists[] = 'where';
 571                 }
 572
 573                 // reset the array for multiple calls
 574                 $this->ar_wherein = array();
 575                 return $this;
 576         }
 577
 578         // --------------------------------------------------------------------
 579
 580         /**
 581          * Like
 582          *
 583          * Generates a %LIKE% portion of the query. Separates
 584          * multiple calls with AND
 585          *
 586          * @access      public
 587          * @param       mixed
 588          * @param       mixed
 589          * @return      object
 590          */
 591         function like($field, $match = '', $side = 'both')
 592         {
 593                 return $this->_like($field, $match, 'AND ', $side);
 594         }
 595
 596         // --------------------------------------------------------------------
 597
 598         /**
 599          * Not Like
 600          *
 601          * Generates a NOT LIKE portion of the query. Separates
 602          * multiple calls with AND
 603          *
 604          * @access      public
 605          * @param       mixed
 606          * @param       mixed
 607          * @return      object
 608          */
 609         function not_like($field, $match = '', $side = 'both')
 610         {
 611                 return $this->_like($field, $match, 'AND ', $side, 'NOT');
 612         }
 613
 614         // --------------------------------------------------------------------
 615
 616         /**
 617          * OR Like
 618          *
 619          * Generates a %LIKE% portion of the query. Separates
 620          * multiple calls with OR
 621          *
 622          * @access      public
 623          * @param       mixed
 624          * @param       mixed
 625          * @return      object
 626          */
 627         function or_like($field, $match = '', $side = 'both')
 628         {
 629                 return $this->_like($field, $match, 'OR ', $side);
 630         }
 631
 632         // --------------------------------------------------------------------
 633
 634         /**
 635          * OR Not Like
 636          *
 637          * Generates a NOT LIKE portion of the query. Separates
 638          * multiple calls with OR
 639          *
 640          * @access      public
 641          * @param       mixed
 642          * @param       mixed
 643          * @return      object
 644          */
 645         function or_not_like($field, $match = '', $side = 'both')
 646         {
 647                 return $this->_like($field, $match, 'OR ', $side, 'NOT');
 648         }
 649
 650         // --------------------------------------------------------------------
 651
 652         /**
 653          * orlike() is an alias of or_like()
 654          * this function is here for backwards compatibility, as
 655          * orlike() has been deprecated
 656          */
 657         function orlike($field, $match = '', $side = 'both')
 658         {
 659                 return $this->or_like($field, $match, $side);
 660         }
 661
 662         // --------------------------------------------------------------------
 663
 664         /**
 665          * Like
 666          *
 667          * Called by like() or orlike()
 668          *
 669          * @access      private
 670          * @param       mixed
 671          * @param       mixed
 672          * @param       string
 673          * @return      object
 674          */
 675         function _like($field, $match = '', $type = 'AND ', $side = 'both', $not = '')
 676         {
 677                 if ( ! is_array($field))
 678                 {
 679                         $field = array($field => $match);
 680                 }
 681
 682                 foreach ($field as $k => $v)
 683                 {
 684                         $k = $this->_protect_identifiers($k);
 685
 686                         $prefix = (count($this->ar_like) == 0) ? '' : $type;
 687
 688                         $v = $this->escape_str($v);
 689
 690                         if ($side == 'before')
 691                         {
 692                                 $like_statement = $prefix." $k $not LIKE '%{$v}'";
 693                         }
 694                         elseif ($side == 'after')
 695                         {
 696                                 $like_statement = $prefix." $k $not LIKE '{$v}%'";
 697                         }
 698                         else
 699                         {
 700                                 $like_statement = $prefix." $k $not LIKE '%{$v}%'";
 701                         }
 702
 703                         $this->ar_like[] = $like_statement;
 704                         if ($this->ar_caching === TRUE)
 705                         {
 706                                 $this->ar_cache_like[] = $like_statement;
 707                                 $this->ar_cache_exists[] = 'like';
 708                         }
 709
 710                 }
 711                 return $this;
 712         }
 713
 714         // --------------------------------------------------------------------
 715
 716         /**
 717          * GROUP BY
 718          *
 719          * @access      public
 720          * @param       string
 721          * @return      object
 722          */
 723         function group_by($by)
 724         {
 725                 if (is_string($by))
 726                 {
 727                         $by = explode(',', $by);
 728                 }
 729
 730                 foreach ($by as $val)
 731                 {
 732                         $val = trim($val);
 733
 734                         if ($val != '')
 735                         {
 736                                 $this->ar_groupby[] = $this->_protect_identifiers($val);
 737
 738                                 if ($this->ar_caching === TRUE)
 739                                 {
 740                                         $this->ar_cache_groupby[] = $this->_protect_identifiers($val);
 741                                         $this->ar_cache_exists[] = 'groupby';
 742                                 }
 743                         }
 744                 }
 745                 return $this;
 746         }
 747
 748         // --------------------------------------------------------------------
 749
 750         /**
 751          * groupby() is an alias of group_by()
 752          * this function is here for backwards compatibility, as
 753          * groupby() has been deprecated
 754          */
 755         function groupby($by)
 756         {
 757                 return $this->group_by($by);
 758         }
 759
 760         // --------------------------------------------------------------------
 761
 762         /**
 763          * Sets the HAVING value
 764          *
 765          * Separates multiple calls with AND
 766          *
 767          * @access      public
 768          * @param       string
 769          * @param       string
 770          * @return      object
 771          */
 772         function having($key, $value = '', $escape = TRUE)
 773         {
 774                 return $this->_having($key, $value, 'AND ', $escape);
 775         }
 776
 777         // --------------------------------------------------------------------
 778
 779         /**
 780          * orhaving() is an alias of or_having()
 781          * this function is here for backwards compatibility, as
 782          * orhaving() has been deprecated
 783          */
 784
 785         function orhaving($key, $value = '', $escape = TRUE)
 786         {
 787                 return $this->or_having($key, $value, $escape);
 788         }
 789         // --------------------------------------------------------------------
 790
 791         /**
 792          * Sets the OR HAVING value
 793          *
 794          * Separates multiple calls with OR
 795          *
 796          * @access      public
 797          * @param       string
 798          * @param       string
 799          * @return      object
 800          */
 801         function or_having($key, $value = '', $escape = TRUE)
 802         {
 803                 return $this->_having($key, $value, 'OR ', $escape);
 804         }
 805
 806         // --------------------------------------------------------------------
 807
 808         /**
 809          * Sets the HAVING values
 810          *
 811          * Called by having() or or_having()
 812          *
 813          * @access      private
 814          * @param       string
 815          * @param       string
 816          * @return      object
 817          */
 818         function _having($key, $value = '', $type = 'AND ', $escape = TRUE)
 819         {
 820                 if ( ! is_array($key))
 821                 {
 822                         $key = array($key => $value);
 823                 }
 824
 825                 foreach ($key as $k => $v)
 826                 {
 827                         $prefix = (count($this->ar_having) == 0) ? '' : $type;
 828
 829                         if ($escape === TRUE)
 830                         {
 831                                 $k = $this->_protect_identifiers($k);
 832                         }
 833
 834                         if ( ! $this->_has_operator($k))
 835                         {
 836                                 $k .= ' = ';
 837                         }
 838
 839                         if ($v != '')
 840                         {
 841                                 $v = ' '.$this->escape_str($v);
 842                         }
 843
 844                         $this->ar_having[] = $prefix.$k.$v;
 845                         if ($this->ar_caching === TRUE)
 846                         {
 847                                 $this->ar_cache_having[] = $prefix.$k.$v;
 848                                 $this->ar_cache_exists[] = 'having';
 849                         }
 850                 }
 851
 852                 return $this;
 853         }
 854
 855         // --------------------------------------------------------------------
 856
 857         /**
 858          * Sets the ORDER BY value
 859          *
 860          * @access      public
 861          * @param       string
 862          * @param       string  direction: asc or desc
 863          * @return      object
 864          */
 865         function order_by($orderby, $direction = '')
 866         {
 867                 if (strtolower($direction) == 'random')
 868                 {
 869                         $orderby = ''; // Random results want or don't need a field name
 870                         $direction = $this->_random_keyword;
 871                 }
 872                 elseif (trim($direction) != '')
 873                 {
 874                         $direction = (in_array(strtoupper(trim($direction)), array('ASC', 'DESC'), TRUE)) ? ' '.$direction : ' ASC';
 875                 }
 876
 877                 $orderby_statement = $this->_protect_identifiers($orderby).$direction;
 878
 879                 $this->ar_orderby[] = $orderby_statement;
 880                 if ($this->ar_caching === TRUE)
 881                 {
 882                         $this->ar_cache_orderby[] = $orderby_statement;
 883                         $this->ar_cache_exists[] = 'orderby';
 884                 }
 885
 886                 return $this;
 887         }
 888
 889         // --------------------------------------------------------------------
 890
 891         /**
 892          * orderby() is an alias of order_by()
 893          * this function is here for backwards compatibility, as
 894          * orderby() has been deprecated
 895          */
 896         function orderby($orderby, $direction = '')
 897         {
 898                 return $this->order_by($orderby, $direction);
 899         }
 900
 901         // --------------------------------------------------------------------
 902
 903         /**
 904          * Sets the LIMIT value
 905          *
 906          * @access      public
 907          * @param       integer the limit value
 908          * @param       integer the offset value
 909          * @return      object
 910          */
 911         function limit($value, $offset = '')
 912         {
 913                 $this->ar_limit = $value;
 914
 915                 if ($offset != '')
 916                 {
 917                         $this->ar_offset = $offset;
 918                 }
 919
 920                 return $this;
 921         }
 922
 923         // --------------------------------------------------------------------
 924
 925         /**
 926          * Sets the OFFSET value
 927          *
 928          * @access      public
 929          * @param       integer the offset value
 930          * @return      object
 931          */
 932         function offset($offset)
 933         {
 934                 $this->ar_offset = $offset;
 935                 return $this;
 936         }
 937
 938         // --------------------------------------------------------------------
 939
 940         /**
 941          * The "set" function.  Allows key/value pairs to be set for inserting or updating
 942          *
 943          * @access      public
 944          * @param       mixed
 945          * @param       string
 946          * @param       boolean
 947          * @return      object
 948          */
 949         function set($key, $value = '', $escape = TRUE)
 950         {
 951                 $key = $this->_object_to_array($key);
 952
 953                 if ( ! is_array($key))
 954                 {
 955                         $key = array($key => $value);
 956                 }
 957
 958                 foreach ($key as $k => $v)
 959                 {
 960                         if ($escape === FALSE)
 961                         {
 962                                 $this->ar_set[$this->_protect_identifiers($k)] = $v;
 963                         }
 964                         else
 965                         {
 966                                 $this->ar_set[$this->_protect_identifiers($k)] = $this->escape($v);
 967                         }
 968                 }
 969
 970                 return $this;
 971         }
 972
 973         // --------------------------------------------------------------------
 974
 975         /**
 976          * Get
 977          *
 978          * Compiles the select statement based on the other functions called
 979          * and runs the query
 980          *
 981          * @access      public
 982          * @param       string  the table
 983          * @param       string  the limit clause
 984          * @param       string  the offset clause
 985          * @return      object
 986          */
 987         function get($table = '', $limit = null, $offset = null)
 988         {
 989                 if ($table != '')
 990                 {
 991                         $this->_track_aliases($table);
 992                         $this->from($table);
 993                 }
 994
 995                 if ( ! is_null($limit))
 996                 {
 997                         $this->limit($limit, $offset);
 998                 }
 999
1000                 $sql = $this->_compile_select();
1001
1002                 $result = $this->query($sql);
1003                 $this->_reset_select();
1004                 return $result;
1005         }
1006
1007         /**
1008          * "Count All Results" query
1009          *
1010          * Generates a platform-specific query string that counts all records
1011          * returned by an Active Record query.
1012          *
1013          * @access      public
1014          * @param       string
1015          * @return      string
1016          */
1017         function count_all_results($table = '')
1018         {
1019                 if ($table != '')
1020                 {
1021                         $this->_track_aliases($table);
1022                         $this->from($table);
1023                 }
1024
1025                 $sql = $this->_compile_select($this->_count_string . $this->_protect_identifiers('numrows'));
1026
1027                 $query = $this->query($sql);
1028                 $this->_reset_select();
1029
1030                 if ($query->num_rows() == 0)
1031                 {
1032                         return '0';
1033                 }
1034
1035                 $row = $query->row();
1036                 return $row->numrows;
1037         }
1038
1039         // --------------------------------------------------------------------
1040
1041         /**
1042          * Get_Where
1043          *
1044          * Allows the where clause, limit and offset to be added directly
1045          *
1046          * @access      public
1047          * @param       string  the where clause
1048          * @param       string  the limit clause
1049          * @param       string  the offset clause
1050          * @return      object
1051          */
1052         function get_where($table = '', $where = null, $limit = null, $offset = null)
1053         {
1054                 if ($table != '')
1055                 {
1056                         $this->from($table);
1057                 }
1058
1059                 if ( ! is_null($where))
1060                 {
1061                         $this->where($where);
1062                 }
1063
1064                 if ( ! is_null($limit))
1065                 {
1066                         $this->limit($limit, $offset);
1067                 }
1068
1069                 $sql = $this->_compile_select();
1070
1071                 $result = $this->query($sql);
1072                 $this->_reset_select();
1073                 return $result;
1074         }
1075
1076         // --------------------------------------------------------------------
1077
1078         /**
1079          * getwhere() is an alias of get_where()
1080          * this function is here for backwards compatibility, as
1081          * getwhere() has been deprecated
1082          */
1083         function getwhere($table = '', $where = null, $limit = null, $offset = null)
1084         {
1085                 return $this->get_where($table, $where, $limit, $offset);
1086         }
1087
1088         // --------------------------------------------------------------------
1089
1090         /**
1091          * Insert
1092          *
1093          * Compiles an insert string and runs the query
1094          *
1095          * @access      public
1096          * @param       string  the table to retrieve the results from
1097          * @param       array   an associative array of insert values
1098          * @return      object
1099          */
1100         function insert($table = '', $set = NULL)
1101         {
1102                 if ( ! is_null($set))
1103                 {
1104                         $this->set($set);
1105                 }
1106
1107                 if (count($this->ar_set) == 0)
1108                 {
1109                         if ($this->db_debug)
1110                         {
1111                                 return $this->display_error('db_must_use_set');
1112                         }
1113                         return FALSE;
1114                 }
1115
1116                 if ($table == '')
1117                 {
1118                         if ( ! isset($this->ar_from[0]))
1119                         {
1120                                 if ($this->db_debug)
1121                                 {
1122                                         return $this->display_error('db_must_set_table');
1123                                 }
1124                                 return FALSE;
1125                         }
1126
1127                         $table = $this->ar_from[0];
1128                 }
1129
1130                 $sql = $this->_insert($this->_protect_identifiers($table, TRUE, NULL, FALSE), array_keys($this->ar_set), array_values($this->ar_set));
1131
1132                 $this->_reset_write();
1133                 return $this->query($sql);
1134         }
1135
1136         // --------------------------------------------------------------------
1137
1138         /**
1139          * Update
1140          *
1141          * Compiles an update string and runs the query
1142          *
1143          * @access      public
1144          * @param       string  the table to retrieve the results from
1145          * @param       array   an associative array of update values
1146          * @param       mixed   the where clause
1147          * @return      object
1148          */
1149         function update($table = '', $set = NULL, $where = NULL, $limit = NULL)
1150         {
1151                 // Combine any cached components with the current statements
1152                 $this->_merge_cache();
1153
1154                 if ( ! is_null($set))
1155                 {
1156                         $this->set($set);
1157                 }
1158
1159                 if (count($this->ar_set) == 0)
1160                 {
1161                         if ($this->db_debug)
1162                         {
1163                                 return $this->display_error('db_must_use_set');
1164                         }
1165                         return FALSE;
1166                 }
1167
1168                 if ($table == '')
1169                 {
1170                         if ( ! isset($this->ar_from[0]))
1171                         {
1172                                 if ($this->db_debug)
1173                                 {
1174                                         return $this->display_error('db_must_set_table');
1175                                 }
1176                                 return FALSE;
1177                         }
1178
1179                         $table = $this->ar_from[0];
1180                 }
1181
1182                 if ($where != NULL)
1183                 {
1184                         $this->where($where);
1185                 }
1186
1187                 if ($limit != NULL)
1188                 {
1189                         $this->limit($limit);
1190                 }
1191
1192                 $sql = $this->_update($this->_protect_identifiers($table, TRUE, NULL, FALSE), $this->ar_set, $this->ar_where, $this->ar_orderby, $this->ar_limit);
1193
1194                 $this->_reset_write();
1195                 return $this->query($sql);
1196         }
1197
1198         // --------------------------------------------------------------------
1199
1200         /**
1201          * Empty Table
1202          *
1203          * Compiles a delete string and runs "DELETE FROM table"
1204          *
1205          * @access      public
1206          * @param       string  the table to empty
1207          * @return      object
1208          */
1209         function empty_table($table = '')
1210         {
1211                 if ($table == '')
1212                 {
1213                         if ( ! isset($this->ar_from[0]))
1214                         {
1215                                 if ($this->db_debug)
1216                                 {
1217                                         return $this->display_error('db_must_set_table');
1218                                 }
1219                                 return FALSE;
1220                         }
1221
1222                         $table = $this->ar_from[0];
1223                 }
1224                 else
1225                 {
1226                         $table = $this->_protect_identifiers($table, TRUE, NULL, FALSE);
1227                 }
1228
1229                 $sql = $this->_delete($table);
1230
1231                 $this->_reset_write();
1232
1233                 return $this->query($sql);
1234         }
1235
1236         // --------------------------------------------------------------------
1237
1238         /**
1239          * Truncate
1240          *
1241          * Compiles a truncate string and runs the query
1242          * If the database does not support the truncate() command
1243          * This function maps to "DELETE FROM table"
1244          *
1245          * @access      public
1246          * @param       string  the table to truncate
1247          * @return      object
1248          */
1249         function truncate($table = '')
1250         {
1251                 if ($table == '')
1252                 {
1253                         if ( ! isset($this->ar_from[0]))
1254                         {
1255                                 if ($this->db_debug)
1256                                 {
1257                                         return $this->display_error('db_must_set_table');
1258                                 }
1259                                 return FALSE;
1260                         }
1261
1262                         $table = $this->ar_from[0];
1263                 }
1264                 else
1265                 {
1266                         $table = $this->_protect_identifiers($table, TRUE, NULL, FALSE);
1267                 }
1268
1269                 $sql = $this->_truncate($table);
1270
1271                 $this->_reset_write();
1272
1273                 return $this->query($sql);
1274         }
1275
1276         // --------------------------------------------------------------------
1277
1278         /**
1279          * Delete
1280          *
1281          * Compiles a delete string and runs the query
1282          *
1283          * @access      public
1284          * @param       mixed   the table(s) to delete from. String or array
1285          * @param       mixed   the where clause
1286          * @param       mixed   the limit clause
1287          * @param       boolean
1288          * @return      object
1289          */
1290         function delete($table = '', $where = '', $limit = NULL, $reset_data = TRUE)
1291         {
1292                 // Combine any cached components with the current statements
1293                 $this->_merge_cache();
1294
1295                 if ($table == '')
1296                 {
1297                         if ( ! isset($this->ar_from[0]))
1298                         {
1299                                 if ($this->db_debug)
1300                                 {
1301                                         return $this->display_error('db_must_set_table');
1302                                 }
1303                                 return FALSE;
1304                         }
1305
1306                         $table = $this->ar_from[0];
1307                 }
1308                 elseif (is_array($table))
1309                 {
1310                         foreach($table as $single_table)
1311                         {
1312                                 $this->delete($single_table, $where, $limit, FALSE);
1313                         }
1314
1315                         $this->_reset_write();
1316                         return;
1317                 }
1318                 else
1319                 {
1320                         $table = $this->_protect_identifiers($table, TRUE, NULL, FALSE);
1321                 }
1322
1323                 if ($where != '')
1324                 {
1325                         $this->where($where);
1326                 }
1327
1328                 if ($limit != NULL)
1329                 {
1330                         $this->limit($limit);
1331                 }
1332
1333                 if (count($this->ar_where) == 0 && count($this->ar_like) == 0)
1334                 {
1335                         if ($this->db_debug)
1336                         {
1337                                 return $this->display_error('db_del_must_use_where');
1338                         }
1339
1340                         return FALSE;
1341                 }
1342
1343                 $sql = $this->_delete($table, $this->ar_where, $this->ar_like, $this->ar_limit);
1344
1345                 if ($reset_data)
1346                 {
1347                         $this->_reset_write();
1348                 }
1349
1350                 return $this->query($sql);
1351         }
1352
1353         // --------------------------------------------------------------------
1354
1355         /**
1356          * DB Prefix
1357          *
1358          * Prepends a database prefix if one exists in configuration
1359          *
1360          * @access      public
1361          * @param       string  the table
1362          * @return      string
1363          */
1364         function dbprefix($table = '')
1365         {
1366                 if ($table == '')
1367                 {
1368                         $this->display_error('db_table_name_required');
1369                 }
1370
1371                 return $this->dbprefix.$table;
1372         }
1373
1374         // --------------------------------------------------------------------
1375
1376         /**
1377          * Track Aliases
1378          *
1379          * Used to track SQL statements written with aliased tables.
1380          *
1381          * @access      private
1382          * @param       string  The table to inspect
1383          * @return      string
1384          */
1385         function _track_aliases($table)
1386         {
1387                 if (is_array($table))
1388                 {
1389                         foreach ($table as $t)
1390                         {
1391                                 $this->_track_aliases($t);
1392                         }
1393                         return;
1394                 }
1395
1396                 // Does the string contain a comma?  If so, we need to separate
1397                 // the string into discreet statements
1398                 if (strpos($table, ',') !== FALSE)
1399                 {
1400                         return $this->_track_aliases(explode(',', $table));
1401                 }
1402
1403                 // if a table alias is used we can recognize it by a space
1404                 if (strpos($table, " ") !== FALSE)
1405                 {
1406                         // if the alias is written with the AS keyword, remove it
1407                         $table = preg_replace('/ AS /i', ' ', $table);
1408
1409                         // Grab the alias
1410                         $table = trim(strrchr($table, " "));
1411
1412                         // Store the alias, if it doesn't already exist
1413                         if ( ! in_array($table, $this->ar_aliased_tables))
1414                         {
1415                                 $this->ar_aliased_tables[] = $table;
1416                         }
1417                 }
1418         }
1419
1420         // --------------------------------------------------------------------
1421
1422         /**
1423          * Compile the SELECT statement
1424          *
1425          * Generates a query string based on which functions were used.
1426          * Should not be called directly.  The get() function calls it.
1427          *
1428          * @access      private
1429          * @return      string
1430          */
1431         function _compile_select($select_override = FALSE)
1432         {
1433                 // Combine any cached components with the current statements
1434                 $this->_merge_cache();
1435
1436                 // ----------------------------------------------------------------
1437
1438                 // Write the "select" portion of the query
1439
1440                 if ($select_override !== FALSE)
1441                 {
1442                         $sql = $select_override;
1443                 }
1444                 else
1445                 {
1446                         $sql = ( ! $this->ar_distinct) ? 'SELECT ' : 'SELECT DISTINCT ';
1447
1448                         if (count($this->ar_select) == 0)
1449                         {
1450                                 $sql .= '*';
1451                         }
1452                         else
1453                         {
1454                                 // Cycle through the "select" portion of the query and prep each column name.
1455                                 // The reason we protect identifiers here rather then in the select() function
1456                                 // is because until the user calls the from() function we don't know if there are aliases
1457                                 foreach ($this->ar_select as $key => $val)
1458                                 {
1459                                         $this->ar_select[$key] = $this->_protect_identifiers($val);
1460                                 }
1461
1462                                 $sql .= implode(', ', $this->ar_select);
1463                         }
1464                 }
1465
1466                 // ----------------------------------------------------------------
1467
1468                 // Write the "FROM" portion of the query
1469
1470                 if (count($this->ar_from) > 0)
1471                 {
1472                         $sql .= "\nFROM ";
1473
1474                         $sql .= $this->_from_tables($this->ar_from);
1475                 }
1476
1477                 // ----------------------------------------------------------------
1478
1479                 // Write the "JOIN" portion of the query
1480
1481                 if (count($this->ar_join) > 0)
1482                 {
1483                         $sql .= "\n";
1484
1485                         $sql .= implode("\n", $this->ar_join);
1486                 }
1487
1488                 // ----------------------------------------------------------------
1489
1490                 // Write the "WHERE" portion of the query
1491
1492                 if (count($this->ar_where) > 0 OR count($this->ar_like) > 0)
1493                 {
1494                         $sql .= "\n";
1495
1496                         $sql .= "WHERE ";
1497                 }
1498
1499                 $sql .= implode("\n", $this->ar_where);
1500
1501                 // ----------------------------------------------------------------
1502
1503                 // Write the "LIKE" portion of the query
1504
1505                 if (count($this->ar_like) > 0)
1506                 {
1507                         if (count($this->ar_where) > 0)
1508                         {
1509                                 $sql .= "\nAND ";
1510                         }
1511
1512                         $sql .= implode("\n", $this->ar_like);
1513                 }
1514
1515                 // ----------------------------------------------------------------
1516
1517                 // Write the "GROUP BY" portion of the query
1518
1519                 if (count($this->ar_groupby) > 0)
1520                 {
1521                         $sql .= "\nGROUP BY ";
1522
1523                         $sql .= implode(', ', $this->ar_groupby);
1524                 }
1525
1526                 // ----------------------------------------------------------------
1527
1528                 // Write the "HAVING" portion of the query
1529
1530                 if (count($this->ar_having) > 0)
1531                 {
1532                         $sql .= "\nHAVING ";
1533                         $sql .= implode("\n", $this->ar_having);
1534                 }
1535
1536                 // ----------------------------------------------------------------
1537
1538                 // Write the "ORDER BY" portion of the query
1539
1540                 if (count($this->ar_orderby) > 0)
1541                 {
1542                         $sql .= "\nORDER BY ";
1543                         $sql .= implode(', ', $this->ar_orderby);
1544
1545                         if ($this->ar_order !== FALSE)
1546                         {
1547                                 $sql .= ($this->ar_order == 'desc') ? ' DESC' : ' ASC';
1548                         }
1549                 }
1550
1551                 // ----------------------------------------------------------------
1552
1553                 // Write the "LIMIT" portion of the query
1554
1555                 if (is_numeric($this->ar_limit))
1556                 {
1557                         $sql .= "\n";
1558                         $sql = $this->_limit($sql, $this->ar_limit, $this->ar_offset);
1559                 }
1560
1561                 return $sql;
1562         }
1563
1564         // --------------------------------------------------------------------
1565
1566         /**
1567          * Object to Array
1568          *
1569          * Takes an object as input and converts the class variables to array key/vals
1570          *
1571          * @access      public
1572          * @param       object
1573          * @return      array
1574          */
1575         function _object_to_array($object)
1576         {
1577                 if ( ! is_object($object))
1578                 {
1579                         return $object;
1580                 }
1581
1582                 $array = array();
1583                 foreach (get_object_vars($object) as $key => $val)
1584                 {
1585                         // There are some built in keys we need to ignore for this conversion
1586                         if ( ! is_object($val) && ! is_array($val) && $key != '_parent_name' && $key != '_ci_scaffolding' && $key != '_ci_scaff_table')
1587                         {
1588                                 $array[$key] = $val;
1589                         }
1590                 }
1591
1592                 return $array;
1593         }
1594
1595         // --------------------------------------------------------------------
1596
1597         /**
1598          * Start Cache
1599          *
1600          * Starts AR caching
1601          *
1602          * @access      public
1603          * @return      void
1604          */
1605         function start_cache()
1606         {
1607                 $this->ar_caching = TRUE;
1608         }
1609
1610         // --------------------------------------------------------------------
1611
1612         /**
1613          * Stop Cache
1614          *
1615          * Stops AR caching
1616          *
1617          * @access      public
1618          * @return      void
1619          */
1620         function stop_cache()
1621         {
1622                 $this->ar_caching = FALSE;
1623         }
1624
1625         // --------------------------------------------------------------------
1626
1627         /**
1628          * Flush Cache
1629          *
1630          * Empties the AR cache
1631          *
1632          * @access      public
1633          * @return      void
1634          */
1635         function flush_cache()
1636         {
1637                 $this->_reset_run(
1638                                                         array(
1639                                                                         'ar_cache_select'       => array(),
1640                                                                         'ar_cache_from'         => array(),
1641                                                                         'ar_cache_join'         => array(),
1642                                                                         'ar_cache_where'        => array(),
1643                                                                         'ar_cache_like'         => array(),
1644                                                                         'ar_cache_groupby'      => array(),
1645                                                                         'ar_cache_having'       => array(),
1646                                                                         'ar_cache_orderby'      => array(),
1647                                                                         'ar_cache_set'          => array(),
1648                                                                         'ar_cache_exists'       => array()
1649                                                                 )
1650                                                         );
1651         }
1652
1653         // --------------------------------------------------------------------
1654
1655         /**
1656          * Merge Cache
1657          *
1658          * When called, this function merges any cached AR arrays with
1659          * locally called ones.
1660          *
1661          * @access      private
1662          * @return      void
1663          */
1664         function _merge_cache()
1665         {
1666                 if (count($this->ar_cache_exists) == 0)
1667                 {
1668                         return;
1669                 }
1670
1671                 foreach ($this->ar_cache_exists as $val)
1672                 {
1673                         $ar_variable    = 'ar_'.$val;
1674                         $ar_cache_var   = 'ar_cache_'.$val;
1675
1676                         if (count($this->$ar_cache_var) == 0)
1677                         {
1678                                 continue;
1679                         }
1680
1681                         $this->$ar_variable = array_unique(array_merge($this->$ar_cache_var, $this->$ar_variable));
1682                 }
1683
1684                 // If we are "protecting identifiers" we need to examine the "from"
1685                 // portion of the query to determine if there are any aliases
1686                 if ($this->_protect_identifiers === TRUE AND count($this->ar_cache_from) > 0)
1687                 {
1688                         $this->_track_aliases($this->ar_from);
1689                 }
1690         }
1691
1692         // --------------------------------------------------------------------
1693
1694         /**
1695          * Resets the active record values.  Called by the get() function
1696          *
1697          * @access      private
1698          * @param       array   An array of fields to reset
1699          * @return      void
1700          */
1701         function _reset_run($ar_reset_items)
1702         {
1703                 foreach ($ar_reset_items as $item => $default_value)
1704                 {
1705                         if ( ! in_array($item, $this->ar_store_array))
1706                         {
1707                                 $this->$item = $default_value;
1708                         }
1709                 }
1710         }
1711
1712         // --------------------------------------------------------------------
1713
1714         /**
1715          * Resets the active record values.  Called by the get() function
1716          *
1717          * @access      private
1718          * @return      void
1719          */
1720         function _reset_select()
1721         {
1722                 $ar_reset_items = array(
1723                                                                 'ar_select'                     => array(),
1724                                                                 'ar_from'                       => array(),
1725                                                                 'ar_join'                       => array(),
1726                                                                 'ar_where'                      => array(),
1727                                                                 'ar_like'                       => array(),
1728                                                                 'ar_groupby'            => array(),
1729                                                                 'ar_having'                     => array(),
1730                                                                 'ar_orderby'            => array(),
1731                                                                 'ar_wherein'            => array(),
1732                                                                 'ar_aliased_tables'     => array(),
1733                                                                 'ar_distinct'           => FALSE,
1734                                                                 'ar_limit'                      => FALSE,
1735                                                                 'ar_offset'                     => FALSE,
1736                                                                 'ar_order'                      => FALSE,
1737                                                         );
1738
1739                 $this->_reset_run($ar_reset_items);
1740         }
1741
1742         // --------------------------------------------------------------------
1743
1744         /**
1745          * Resets the active record "write" values.
1746          *
1747          * Called by the insert() update() and delete() functions
1748          *
1749          * @access      private
1750          * @return      void
1751          */
1752         function _reset_write()
1753         {
1754                 $ar_reset_items = array(
1755                                                                 'ar_set'                => array(),
1756                                                                 'ar_from'               => array(),
1757                                                                 'ar_where'              => array(),
1758                                                                 'ar_like'               => array(),
1759                                                                 'ar_orderby'    => array(),
1760                                                                 'ar_limit'              => FALSE,
1761                                                                 'ar_order'              => FALSE
1762                                                                 );
1763
1764                 $this->_reset_run($ar_reset_items);
1765         }
1766
1767 }
1768
1769 /* End of file DB_active_rec.php */
1770 /* Location: ./system/database/DB_active_rec.php */