b2evolution PHP Cross Reference Blogging Systems

Source: /inc/_core/model/db/_sql.class.php - 455 lines - 9250 bytes - Summary - Text - Print

Description: This file implements the SQL class. This file is part of the evoCore framework - {@link http://evocore.net/} See also {@link http://sourceforge.net/projects/evocms/}.

   1  <?php
   2  /**
   3   * This file implements the SQL class.
   4   *
   5   * This file is part of the evoCore framework - {@link http://evocore.net/}
   6   * See also {@link http://sourceforge.net/projects/evocms/}.
   7   *
   8   * @copyright (c)2003-2014 by Francois Planque - {@link http://fplanque.com/}
   9   *
  10   * {@internal License choice
  11   * - If you have received this file as part of a package, please find the license.txt file in
  12   *   the same folder or the closest folder above for complete license terms.
  13   * - If you have received this file individually (e-g: from http://evocms.cvs.sourceforge.net/)
  14   *   then you must choose one of the following licenses before using the file:
  15   *   - GNU General Public License 2 (GPL) - http://www.opensource.org/licenses/gpl-license.php
  16   *   - Mozilla Public License 1.1 (MPL) - http://www.opensource.org/licenses/mozilla1.1.php
  17   * }}
  18   *
  19   * {@internal Open Source relicensing agreement:
  20   * }}
  21   *
  22   * @package evocore
  23   *
  24   * {@internal Below is a list of authors who have contributed to design/coding of this file: }}
  25   * @author fplanque: Francois PLANQUE.
  26   *
  27   * @version $Id: _sql.class.php 6136 2014-03-08 07:59:48Z manuel $
  28   */
  29  if( !defined('EVO_MAIN_INIT') ) die( 'Please, do not access this page directly.' );
  30  
  31  
  32  /**
  33   * SQL class: help constructing queries
  34   *
  35   * @todo dh> should provide quoting, e.g. via $DB->quote()..
  36   *           Maybe using printf-style, where all args get quoted.
  37   *
  38   * @todo (fplanque)
  39   */
  40  class SQL
  41  {
  42      var $select = '';
  43      var $from = '';
  44      var $where = '';
  45      var $group_by = '';
  46      var $having = '';
  47      var $order_by = '';
  48      var $limit = '';
  49      var $search_field = array();
  50      var $search_field_regexp = array();
  51      var $title;
  52  
  53  
  54      /**
  55       * Constructor.
  56       */
  57  	function SQL($title = NULL)
  58      {
  59          if( $title )
  60              $this->title = $title;
  61      }
  62  
  63  
  64    /**
  65       * Get whole query
  66       */
  67  	function get()
  68      {
  69          $sql = '';
  70          $sql .= $this->get_select();
  71          $sql .= $this->get_from();
  72          $sql .= $this->get_where();
  73          $sql .= $this->get_group_by();
  74          $sql .= $this->get_having();
  75          $sql .= $this->get_order_by();
  76          $sql .= $this->get_limit();
  77          return $sql;
  78      }
  79  
  80  
  81    /**
  82       * Get whole query
  83       */
  84  	function display()
  85      {
  86          echo $this->get_select( '<br />SELECT ' );
  87          echo $this->get_from( '<br />FROM ' );
  88          echo $this->get_where( '<br />WHERE ' );
  89          echo $this->get_group_by( '<br />GROUP BY ' );
  90          echo $this->get_having( '<br />HAVING ' );
  91          echo $this->get_order_by( '<br />ORDER BY ' );
  92          echo $this->get_limit( '<br />LIMIT ' );
  93      }
  94  
  95  
  96    /**
  97       * Get SELECT clause if there is something inside
  98       */
  99  	function get_select( $prefix = ' SELECT ' )
 100      {
 101          if( !empty($this->select) )
 102          {
 103              return $prefix.$this->select;
 104          }
 105  
 106          return '';
 107      }
 108  
 109  
 110    /**
 111       * Get FROM clause if there is something inside
 112       */
 113  	function get_from( $prefix = ' FROM ' )
 114      {
 115          if( !empty($this->from) )
 116          {
 117              return $prefix.$this->from;
 118          }
 119  
 120          return '';
 121      }
 122  
 123  
 124    /**
 125       * Get WHERE clause if there is something inside
 126       */
 127  	function get_where( $prefix = ' WHERE ' )
 128      {
 129          if( !empty($this->where) )
 130          {
 131              return $prefix.$this->where;
 132          }
 133  
 134          return '';
 135      }
 136  
 137  
 138    /**
 139       * Get GROUP BY clause if there is something inside
 140       */
 141  	function get_group_by( $prefix = ' GROUP BY ' )
 142      {
 143          if( !empty($this->group_by) )
 144          {
 145              return $prefix.$this->group_by;
 146          }
 147  
 148          return '';
 149      }
 150  
 151  
 152    /**
 153       * Get HAVING clause if there is something inside
 154       */
 155  	function get_having( $prefix = ' HAVING ' )
 156      {
 157          if( !empty($this->having) )
 158          {
 159              return $prefix.$this->having;
 160          }
 161  
 162          return '';
 163      }
 164  
 165  
 166    /**
 167       * Get ORDER BY clause if there is something inside
 168       */
 169  	function get_order_by( $prefix = ' ORDER BY ' )
 170      {
 171          if( !empty($this->order_by) )
 172          {
 173              return $prefix.$this->order_by;
 174          }
 175  
 176          return '';
 177      }
 178  
 179  
 180    /**
 181       * Get LIMIT clause if there is something inside
 182       */
 183  	function get_limit( $prefix = ' LIMIT ' )
 184      {
 185          if( !empty($this->limit) )
 186          {
 187              return $prefix.$this->limit;
 188          }
 189  
 190          return '';
 191      }
 192  
 193  
 194    /**
 195       * Set SELECT clause
 196       */
 197  	function SELECT( $select )
 198      {
 199          $this->select = $select;
 200      }
 201  
 202  
 203      /**
 204       * Extends the SELECT clause.
 205       *
 206       * @param srting should typically start with a comma ','
 207       */
 208  	function SELECT_add( $select_add )
 209      {
 210          if( empty( $this->select ) ) debug_die( 'Cannot extend empty SELECT clause' );
 211  
 212          $this->select .= ' '.$select_add;
 213      }
 214  
 215  
 216    /**
 217       *
 218       */
 219  	function FROM( $from )
 220      {
 221          $this->from = $from;
 222      }
 223  
 224      /**
 225       * Extends the FROM clause.
 226       *
 227       * @param string should typically start with INNER JOIN or LEFT JOIN
 228       */
 229  	function FROM_add( $from_add )
 230      {
 231          if( empty( $this->from ) ) debug_die( 'Cannot extend empty FROM clause' );
 232  
 233          $this->from .= ' '.$from_add;
 234      }
 235  
 236  
 237    /**
 238       *
 239       */
 240  	function WHERE( $where )
 241      {
 242          $this->where = $where;
 243      }
 244  
 245      /**
 246       * Extends the WHERE clause with AND
 247       * @param string
 248       */
 249  	function WHERE_and( $where_and )
 250      {
 251          if( empty($where_and) )
 252          {    // Nothing to append:
 253              return false;
 254          }
 255  
 256          if( ! empty($this->where) )
 257          { // We already have something in the WHERE clause:
 258              $this->where .= ' AND ';
 259          }
 260  
 261          // Append payload:
 262          $this->where .= '('.$where_and.')';
 263      }
 264  
 265      /**
 266       * Extends the WHERE clause with OR
 267       *
 268       * NOTE: there is almost NEVER a good reason to use this! Think again!
 269       *
 270       * @param string
 271       */
 272  	function WHERE_or( $where_or )
 273      {
 274          if( empty($where_or) )
 275          {    // Nothing to append:
 276              return false;
 277          }
 278  
 279          if( ! empty($this->where) )
 280          { // We already have something in the WHERE clause:
 281              $this->where .= ' OR ';
 282          }
 283  
 284          // Append payload:
 285          $this->where .= '('.$where_or.')';
 286      }
 287  
 288  	function GROUP_BY( $group_by )
 289      {
 290          $this->group_by = $group_by;
 291      }
 292  
 293  	function HAVING( $having )
 294      {
 295          $this->having = $having;
 296      }
 297  
 298      /**
 299       * Extends the HAVING clause with AND
 300       *
 301       * @param string
 302       */
 303  	function HAVING_and( $having_and )
 304      {
 305          if( empty( $having_and ) )
 306          {    // Nothing to append:
 307              return false;
 308          }
 309  
 310          if( ! empty( $this->having ) )
 311          { // We already have something in the HAVING clause:
 312              $this->having .= ' AND ';
 313          }
 314  
 315          // Append payload:
 316          $this->having .= '('.$having_and.')';
 317      }
 318  
 319  	function ORDER_BY( $order_by )
 320      {
 321          $this->order_by = $order_by;
 322      }
 323  
 324  	function ORDER_BY_prepend( $order_by_prepend )
 325      {
 326          if( empty( $order_by_prepend ) )
 327          {
 328              return;
 329          }
 330  
 331          if( empty( $this->order_by ) )
 332          {
 333              $this->order_by = $order_by_prepend;
 334          }
 335          else
 336          {
 337              $this->order_by = $order_by_prepend.', '.$this->order_by;
 338          }
 339      }
 340  
 341  	function LIMIT( $limit )
 342      {
 343          $this->limit = $limit;
 344      }
 345  
 346      /**
 347       * create array of search fields
 348       *
 349       * @param string field to search on
 350       * @param string regular expression we want to use on the search for the field param
 351       */
 352  	function add_search_field( $field, $reg_exp = '' )
 353      {
 354          $this->search_field[] = $field;
 355  
 356          if( !empty( $reg_exp ) )
 357          {    // We want to use a regular expression on the search for this field, so add to the search field regexp array
 358              $this->search_field_regexp[$field] = $reg_exp;
 359          }
 360      }
 361  
 362      /**
 363       * create the filter whith the search field array
 364       *
 365       * @param string keywords separated by space
 366       * @param string operator( AND , OR , PHRASE ) for the filter
 367       */
 368  	function WHERE_keywords( $search, $search_kw_combine )
 369      {
 370          global $DB;
 371  
 372          // Concat the list of search fields ( concat(' ',field1,field2,field3...) )
 373          if (count( $this->search_field ) > 1)
 374          {
 375              $search_field = 'CONCAT_WS(\' \','.implode( ',', $this->search_field).')';
 376          }
 377          else
 378          {
 379              $search_field = $this->search_field[0];
 380          }
 381  
 382          switch( $search_kw_combine )
 383          {
 384              case 'AND':
 385              case 'OR':
 386                  // Create array of key words of the search string
 387                  $keyword_array = explode( ' ', $search );
 388                  $keyword_array = array_filter( $keyword_array, create_function( '$val', 'return !empty($val);' ) );
 389  
 390                  $twhere = array();
 391                  // Loop on all keywords
 392                  foreach($keyword_array as $keyword)
 393                  {
 394                      $twhere[] = '( '.$search_field.' LIKE \'%'.$DB->escape( $keyword ).'%\''.$this->WHERE_regexp( $keyword, $search_kw_combine ).' )';
 395                  }
 396                  $where = implode( ' '.$search_kw_combine.' ', $twhere);
 397                  break;
 398  
 399              case 'PHRASE':
 400                      $where = $search_field." LIKE '%".$DB->escape( $search )."%'".$this->WHERE_regexp( $search, $search_kw_combine );
 401                      break;
 402  
 403              case 'BEGINWITH':
 404                  $twhere = array();
 405                  foreach( $this->search_field as $field )
 406                  {
 407                      $twhere[] = $field." LIKE '".$DB->escape( $search )."%'";
 408                  }
 409                  $where = implode( ' OR ', $twhere ).$this->WHERE_regexp( $search, $search_kw_combine);
 410                  break;
 411  
 412          }
 413          $this->WHERE_and( $where );
 414      }
 415  
 416      /**
 417       * create the filter whith the search field regexp array
 418       *
 419       * @param string search
 420       * @param string operator( AND , OR , PHRASE ) for the filter
 421       *
 422       */
 423  	function WHERE_regexp( $search, $search_kw_combine )
 424      {
 425          $where = '';
 426  
 427          // Loop on all fields we have to use a replace regular expression on search:
 428          foreach( $this->search_field_regexp as $field=>$reg_exp )
 429          {
 430                  // Use reg exp replace on search
 431                  $search_reg_exp = preg_replace( $reg_exp, '', $search );
 432  
 433                  if( !empty( $search_reg_exp ) )
 434                  {    // The reg exp search is not empty, so we add it to the request with an 'OR' operator:
 435                      switch( $search_kw_combine )
 436                      {
 437                          case 'AND':
 438                          case 'OR':
 439                          case 'PHRASE':
 440                              $where .= ' OR '.$field.' LIKE \'%'.$DB->escape( $search_reg_exp ).'%\'';
 441                              break;
 442  
 443                          case 'BEGINWITH':
 444                              $where .= ' OR '.$field.' LIKE \''.$DB->escape( $search_reg_exp ).'%\'';
 445                              break;
 446                      }
 447                  }
 448          }
 449          return $where;
 450      }
 451  
 452  
 453  }
 454  
 455  ?>

title

Description

title

Description

title

Description

title

title

Body