[Sommaire] [Imprimer]

   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-2007 by Francois PLANQUE - {@link http://fplanque.net/}

   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,v 1.1 2007/06/25 10:58:59 fplanque Exp $

  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 (fplanque)

  36   */
  37  class SQL
  38  {
  39      var $select = '';
  40      var $from = '';
  41      var $where = '';
  42      var $group_by = '';
  43      var $order_by = '';
  44      var $limit = '';
  45      var $search_field = array();
  46      var $search_field_regexp = array();
  47  
  48  
  49      /**

  50       * Constructor.

  51       */
  52  	function SQL()
  53      {
  54      }
  55  
  56  
  57    /**

  58       * Get whole query

  59       */
  60  	function get()
  61      {
  62          $sql = '';
  63          $sql .= $this->get_select();
  64          $sql .= $this->get_from();
  65          $sql .= $this->get_where();
  66          $sql .= $this->get_group_by();
  67          $sql .= $this->get_order_by();
  68          $sql .= $this->get_limit();
  69          return $sql;
  70      }
  71  
  72  
  73    /**

  74       * Get whole query

  75       */
  76  	function display()
  77      {
  78          echo $this->get_select( '<br />SELECT ' );
  79          echo $this->get_from( '<br />FROM ' );
  80          echo $this->get_where( '<br />WHERE ' );
  81          echo $this->get_group_by( '<br />GROUP BY ' );
  82          echo $this->get_order_by( '<br />ORDER BY ' );
  83          echo $this->get_limit( '<br />LIMIT ' );
  84      }
  85  
  86  
  87    /**

  88       * Get SELECT clause if there is something inside

  89       */
  90  	function get_select( $prefix = ' SELECT ' )
  91      {
  92          if( !empty($this->select) )
  93          {
  94              return $prefix.$this->select;
  95          }
  96  
  97          return '';
  98      }
  99  
 100  
 101    /**

 102       * Get FROM clause if there is something inside

 103       */
 104  	function get_from( $prefix = ' FROM ' )
 105      {
 106          if( !empty($this->from) )
 107          {
 108              return $prefix.$this->from;
 109          }
 110  
 111          return '';
 112      }
 113  
 114  
 115    /**

 116       * Get WHERE clause if there is something inside

 117       */
 118  	function get_where( $prefix = ' WHERE ' )
 119      {
 120          if( !empty($this->where) )
 121          {
 122              return $prefix.$this->where;
 123          }
 124  
 125          return '';
 126      }
 127  
 128  
 129    /**

 130       * Get GROUP BY clause if there is something inside

 131       */
 132  	function get_group_by( $prefix = ' GROUP BY ' )
 133      {
 134          if( !empty($this->group_by) )
 135          {
 136              return $prefix.$this->group_by;
 137          }
 138  
 139          return '';
 140      }
 141  
 142  
 143    /**

 144       * Get ORDER BY clause if there is something inside

 145       */
 146  	function get_order_by( $prefix = ' ORDER BY ' )
 147      {
 148          if( !empty($this->order_by) )
 149          {
 150              return $prefix.$this->order_by;
 151          }
 152  
 153          return '';
 154      }
 155  
 156  
 157    /**

 158       * Get LIMIT clause if there is something inside

 159       */
 160  	function get_limit( $prefix = ' LIMIT ' )
 161      {
 162          if( !empty($this->limit) )
 163          {
 164              return $prefix.$this->limit;
 165          }
 166  
 167          return '';
 168      }
 169  
 170  
 171    /**

 172       * Set SELECT clause

 173       */
 174  	function SELECT( $select )
 175      {
 176          $this->select = $select;
 177      }
 178  
 179  
 180      /**

 181       * Extends the SELECT clause.

 182       *

 183       * @param srting should typically start with a comma ','

 184       */
 185  	function SELECT_add( $select_add )
 186      {
 187          if( empty( $this->select ) ) debug_die( 'Cannot extend empty SELECT clause' );
 188  
 189          $this->select .= ' '.$select_add;
 190      }
 191  
 192  
 193    /**

 194       *

 195       */
 196      function FROM( $from )
 197      {
 198          $this->from = $from;
 199      }
 200  
 201      /**

 202       * Extends the FROM clause.

 203       *

 204       * @param string should typically start with INNER JOIN or LEFT JOIN

 205       */
 206  	function FROM_add( $from_add )
 207      {
 208          if( empty( $this->from ) ) debug_die( 'Cannot extend empty FROM clause' );
 209  
 210          $this->from .= ' '.$from_add;
 211      }
 212  
 213  
 214    /**

 215       *

 216       */
 217  	function WHERE( $where )
 218      {
 219          $this->where = $where;
 220      }
 221  
 222      /**

 223       * Extends the WHERE clause with AND

 224       * @param string

 225       */
 226  	function WHERE_and( $where_and )
 227      {
 228          if( empty($where_and) )
 229          {    // Nothing to append:
 230              return false;
 231          }
 232  
 233          if( ! empty($this->where) )
 234          { // We already have something in the WHERE clause:
 235              $this->where .= ' AND ';
 236          }
 237  
 238          // Append payload:

 239          $this->where .= '('.$where_and.')';
 240      }
 241  
 242      /**

 243       * Extends the WHERE clause with OR

 244       *

 245       * NOTE: there is almost NEVER a good reason to use this! Think again!

 246       *

 247       * @param string

 248       */
 249  	function WHERE_or( $where_or )
 250      {
 251          if( empty($where_or) )
 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 .= ' OR ';
 259          }
 260  
 261          // Append payload:

 262          $this->where .= '('.$where_or.')';
 263      }
 264  
 265  	function GROUP_BY( $group_by )
 266      {
 267          $this->group_by = $group_by;
 268      }
 269  
 270  	function ORDER_BY( $order_by )
 271      {
 272          $this->order_by = $order_by;
 273      }
 274  
 275   	function ORDER_BY_prepend( $order_by_prepend )
 276      {
 277          if( empty( $order_by_prepend ) )
 278          {
 279              return;
 280          }
 281  
 282          if( empty( $this->order_by ) )
 283          {
 284              $this->order_by = $order_by_prepend;
 285          }
 286          else
 287          {
 288              $this->order_by = $order_by_prepend.', '.$this->order_by;
 289          }
 290      }
 291  
 292  	function LIMIT( $limit )
 293      {
 294          $this->limit = $limit;
 295      }
 296  
 297      /**

 298       * create array of search fields

 299       *

 300       * @param string field to search on

 301       * @param string regular expression we want to use on the search for the field param

 302       */
 303  	function add_search_field( $field,  $reg_exp = '' )
 304      {
 305          $this->search_field[] = $field;
 306  
 307          if( !empty( $reg_exp ) )
 308          {    // We want to use a regular expression on the search for this field, so add to the search field regexp array
 309              $this->search_field_regexp[$field] = $reg_exp;
 310          }
 311      }
 312  
 313      /**

 314       * create the filter whith the search field array

 315       * @param string search

 316       * @param string operator( AND , OR , PHRASE ) for the filter

 317       */
 318  	function WHERE_keyword( $search, $search_kw_combine )
 319      {
 320          global $DB;
 321      
 322          // Concat the list of search fields ( concat(' ',field1,field2,field3...) )

 323          if (count( $this->search_field ) > 1)
 324          {
 325              $search_field = 'CONCAT_WS(\' \',' . implode( ',', $this->search_field).')';
 326          }
 327          else
 328          {
 329              $search_field = $this->search_field[0];
 330          }
 331  
 332          switch( $search_kw_combine )
 333          {
 334              case 'AND':
 335              case 'OR':
 336                  // Create array of key words of the search string

 337                  $keyword_array = explode( ' ', $search );
 338                  $keyword_array = array_filter( $keyword_array, 'filter_empty' );
 339  
 340                  $twhere = array();
 341                  // Loop on all keywords

 342                  foreach($keyword_array as $keyword)
 343                  {
 344                      $twhere[] = '( '.$search_field.' LIKE \'%'.$DB->escape( $keyword ).'%\''.$this->WHERE_regexp( $keyword, $search_kw_combine ).' )';
 345                  }
 346                  $where = implode( ' '.$search_kw_combine.' ', $twhere);
 347                  break;
 348  
 349              case 'PHRASE':
 350                      $where = $search_field." LIKE '%".$DB->escape( $search )."%'".$this->WHERE_regexp( $search, $search_kw_combine );
 351                      break;
 352  
 353              case 'BEGINWITH':
 354                  $twhere = array();
 355                  foreach( $this->search_field as $field )
 356                  {
 357                      $twhere[] = $field." LIKE '".$DB->escape( $search )."%'";
 358                  }
 359                  $where = implode( ' OR ', $twhere ).$this->WHERE_regexp( $search, $search_kw_combine);
 360                  break;
 361  
 362          }
 363          $this->WHERE_and( $where );
 364      }
 365  
 366      /**

 367       * create the filter whith the search field regexp array

 368       *

 369       * @param string search

 370       * @param string operator( AND , OR , PHRASE ) for the filter

 371       *

 372       */
 373  	function WHERE_regexp( $search, $search_kw_combine )
 374      {
 375          $where = '';
 376  
 377          // Loop on all fields we have to use a replace regular expression on search:

 378          foreach( $this->search_field_regexp as $field=>$reg_exp )
 379          {
 380                  // Use reg exp replace on search

 381                  $search_reg_exp = preg_replace( $reg_exp, '', $search );
 382  
 383                  if( !empty( $search_reg_exp ) )
 384                  {    // The reg exp search is not empty, so we add it to the request with an 'OR' operator:
 385                      switch( $search_kw_combine )
 386                      {
 387                          case 'AND':
 388                          case 'OR':
 389                          case 'PHRASE':
 390                              $where .= ' OR '.$field.' LIKE \'%'.$DB->escape( $search_reg_exp ).'%\'';
 391                              break;
 392  
 393                          case 'BEGINWITH':
 394                              $where .= ' OR '.$field.' LIKE \''.$DB->escape( $search_reg_exp ).'%\'';
 395                              break;
 396                      }
 397                  }
 398          }
 399          return $where;
 400      }
 401  
 402  
 403  }
 404  
 405  /*

 406   * $Log: _sql.class.php,v $

 407   * Revision 1.1  2007/06/25 10:58:59  fplanque

 408   * MODULES (refactored MVC)

 409   *

 410   * Revision 1.11  2007/04/26 00:11:08  fplanque

 411   * (c) 2007

 412   *

 413   * Revision 1.10  2007/02/06 13:27:26  waltercruz

 414   * Changing double quotes to single quotes

 415   *

 416   * Revision 1.9  2006/11/24 18:27:27  blueyed

 417   * Fixed link to b2evo CVS browsing interface in file docblocks

 418   */
 419  ?>