Location: PHPKode > scripts > tgcSqlBuilder > include/tgcSqlBuilder/Select.php
<?php
/**
 * $Id: Select.php,v 1.1.1.1 2004/02/14 01:43:22 luckec Exp $
 *
 * Subclass of tgcSqlBuilder that helps to create SELECT sql-statements.
 *
 * @package     tgcSqlBuilder
 * @author      Carsten Lucke <hide@address.com>
 * @copyright   Carsten Lucke <http://www.tool-garage.de>
 */


 
 /**
 * Removing a SELECT value failed
 *
 * @access      public
 */
define('SQLBUILDER_ERROR_SELECT_REMOVE', 101);

 /**
 * Invalid ORDER BY direction
 *
 * @access      public
 */
define('SQLBUILDER_ERROR_INVALID_ORDER_BY_DIRECTION', 102);



/**
 * Subclass of tgcSqlBuilder that helps to create SELECT sql-statements.
 *
 * @package     tgcSqlBuilder
 * @access      public
 * @version     1.0.0
 * @author      Carsten Lucke <hide@address.com>
 */
class tgcSqlBuilder_Select extends tgcSqlBuilder 
{
    
   /**
    * Keeps the ORDER BY information.
    *
    * The array's structure looks like that:
    * <pre>
    * array(
    *       array(
    *               'table'     =>      $tableName,
    *               'column'    =>      $columnName,
    *               'direction' =>      $direction
    *       ),
    *       array( ... ),
    *       ...
    * )
    * </pre>
    *
    * @access       private
    * @var          array           ORDER BY information
    *
    *
    */
    var $_orderBy = array();
    
   /**
    * Keeps the WHERE information.
    *
    * Structure:
    * <pre>
    * array(
    *       'OR'    =>  array(
    *                           array(
    *                                   'table'     =>  $tableName,
    *                                   'column'    =>  $columnName,
    *                                   'value'     =>  $value,
    *                                   'compOp'    =>  $comparisonOperator,
    *                           },
    *                           ...
    *                   ),
    *       'AND'   =>  array(
    *                           array(
    *                                   'table'     =>  $tableName,
    *                                   'column'    =>  $columnName,
    *                                   'value'     =>  $value,
    *                                   'compOp'    =>  $comparisonOperator,
    *                           },
    *                           ...
    *                   )
    * </pre>
    *
    * @access       private
    * @var          array           WHERE information
    */
    var $_where =   array(
                            SQLBUILDER_LOGICAL_AND  =>  array(),
                            SQLBUILDER_LOGICAL_OR   =>  array()
                    );
                    
   /**
    * Keeps the raw WHERE information.
    *
    * Structure:
    * <pre>
    * array(
    *       'OR'    =>  array(
    *                           $statement1,
    *                           $statement2,
    *                           ...
    *                   ),
    *       'AND'   =>  array(
    *                           $statement1,
    *                           $statement2,
    *                           ...
    *                   )
    * </pre>
    *
    * @access       private
    * @var          array           WHERE information
    */
    var $_rawWhere =   array(
                            SQLBUILDER_LOGICAL_AND  =>  array(),
                            SQLBUILDER_LOGICAL_OR   =>  array()
                    );
                    
   /**
    * Keeps the HAVING information.
    *
    * Structure:
    * <pre>
    * array(
    *       'OR'    =>  array(
    *                           array(
    *                                   'table'     =>  $tableName,
    *                                   'column'    =>  $columnName,
    *                                   'value'     =>  $value,
    *                                   'compOp'    =>  $comparisonOperator,
    *                           },
    *                           ...
    *                   ),
    *       'AND'   =>  array(
    *                           array(
    *                                   'table'     =>  $tableName,
    *                                   'column'    =>  $columnName,
    *                                   'value'     =>  $value,
    *                                   'compOp'    =>  $comparisonOperator,
    *                           },
    *                           ...
    *                   )
    * </pre>
    *
    * @access       private
    * @var          array           HAVING information
    */
    var $_having =  array(
                                SQLBUILDER_LOGICAL_AND  =>  array(),
                                SQLBUILDER_LOGICAL_OR   =>  array()
                    );
                    
   /**
    * Keeps the raw HAVING information.
    *
    * Structure:
    * <pre>
    * array(
    *       'OR'    =>  array(
    *                           $statement1,
    *                           $statement2,
    *                           ...
    *                   ),
    *       'AND'   =>  array(
    *                           $statement1,
    *                           $statement2,
    *                           ...
    *                   )
    * </pre>
    *
    * @access       private
    * @var          array           HAVING information
    */
    var $_rawHaving =   array(
                                SQLBUILDER_LOGICAL_AND  =>  array(),
                                SQLBUILDER_LOGICAL_OR   =>  array()
                        );
                    
   /**
    * DISTINCT option
    *
    * @access       private
    * @var          boolean         DISTINCT or not
    */
    var $_distinct = false;
    
   /**
    * Keeps the normal SELECT information.
    *
    * Structure:
    * <pre>
    * array(
    *       array(
    *               'table'     =>  $tableName,
    *               'column'    =>  $column,
    *               'alias'     =>  $alias
    *       ),
    *       array( ... )
    * )
    * </pre>
    *
    * @access       private
    * @var          array           SELECT information
    */
    var $_select = array();
    
    
   /**
    * Keeps raw SELECT information. Good to use mysql-functions (CONCAT, MAX, ...).
    *
    * Structure:
    * <pre>
    * array(
    *       array
    *       (
    *           'statement'     =>      $statement,
    *           'alias'         =>      $alias
    *       ),
    *       array
    *       (
    *           'statement'     =>      ...,
    *           'alias'         =>      ...
    *       ),
    *       ...
    * )
    * </pre>
    *
    * @access       private
    * @var          array           raw SELECT information
    */
    var $_rawSelect = array();
    
   /**
    * Tablename on which a sql-statement concerns.
    *
    * It's a numeric array, that contains the tablenames, that shall be used in an sql-statement.
    *
    * @access       private
    * @var          array           tablename
    */
    var $_tables = array();
    
   /**
    * GROUP BY information.
    *
    * The array's structure:
    * <pre>
    * array(
    *       array(
    *               'table'     =>      $tableName,
    *               'column'    =>      $columnName,
    *               'direction' =>      $direction,
    *               'alias'     =>      $alias,
    *               'int'       =>      $int
    *       ),
    *       array( ... ),
    *       ...
    * )
    * </pre>
    *
    * @access       private
    * @var          array           ORDER BY information
    *
    *
    */
    var $_groupBy = array();
    
   /**
    * LIMIT information
    *
    * @access       private
    * @var          array           LIMIT information
    */
    var $_limit = array (
                            'offset'    =>      null,
                            'rows'      =>      null
                        );



   /**
    * Constructor
    *
    * @access       public
    * @param        object          $dbc            PEAR::DB connection object
    */
    function tgcSqlBuilder_Select(&$dbc)
    {
        parent::tgcSqlBuilder($dbc);
    }
    
   /**
    * Generate the sql-statement.
    *
    * This method generates a query based on the object-information and returns it as a string.
    *
    * <code>
    * $sql      =   new tgcSqlBuilder_Select($dbc);
    * $query    =   $sql->generateQuery();
    * </code>
    *
    * @access       public
    * @return       string          sql-statement
    */
    function generateQuery()
    {
        // check if at least one table has been specified
        if (! count($this->_tables))
        {
            return PEAR::raiseError (
                                        'You have to specifiy at least one tablename',
                                        SQLBUILDER_ERROR_NO_TABLE_FOUND
                                    );
        }
        
        $query       =      $this->_distinct ?  'SELECT DISTINCT' : 'SELECT';
        
        if (count($this->_select) || count($this->_rawSelect)) {
            $query  .=      ' ' . $this->_generateSelectInformation($this->_select, $this->_rawSelect);
        } else {
            $query  .=      ' *';
        }
        
        $query      .=      ' FROM ' . $this->_generateTableInformation($this->_tables);
        
        if (count($this->_where[SQLBUILDER_LOGICAL_AND]) 
            || count($this->_where[SQLBUILDER_LOGICAL_OR])
            || count($this->_rawWhere[SQLBUILDER_LOGICAL_AND])
            || count($this->_rawWhere[SQLBUILDER_LOGICAL_OR])) {
            $query  .=      ' WHERE ' . $this->_generateWhereInformation($this->_where, $this->_rawWhere);
        }
        
        if (count($this->_groupBy)) {
            $query  .=      ' GROUPY BY ' . $this->_generateGroupByInformation($this->_groupBy);
        }
        
        if (count($this->_having[SQLBUILDER_LOGICAL_AND]) 
            || count($this->_having[SQLBUILDER_LOGICAL_OR])
            || count($this->_rawHaving[SQLBUILDER_LOGICAL_AND])
            || count($this->_rawHaving[SQLBUILDER_LOGICAL_OR])) {
            $query  .=      ' HAVING ' . $this->_generateHavingInformation($this->_having, $this->_rawHaving);
        }
        
        if (count($this->_orderBy)) {
            $query  .=      ' ORDER BY ' . $this->_generateOrderByInformation($this->_orderBy);
        }
        
        if (! (is_null($this->_limit['offset']) && is_null($this->_limit['rows']))) {
            $query  .=      ' LIMIT ' . $this->_generateLimitInformation($this->_limit);
        }
        
        $this->reset();
        return $query;
    }
    
   /**
    * Reset the object's whole information.
    *
    * @access       public
    */
    function reset()
    {
        $this->removeTable();
        $this->removeSelect();
        $this->removeRawSelect();
        $this->removeTable();
        $this->removeWhere();
        $this->removeRawWhere();
        $this->removeGroupBy();
        $this->removeHaving();
        $this->removeRawHaving();
        $this->removeOrderBy();
        $this->unsetLimit();
    }
    
   /**
    * Enable the distinct setting.
    *
    * @access       public
    */
    function enableDistinct()
    {
        $this->_distinct    =   true;
    }
    
   /**
    * Enable the distinct setting.
    *
    * @access       public
    */
    function disableDistinct()
    {
        $this->_distinct    =   false;
    }
    
   /**
    * Add a SELECT statement. Optionally you can specify an alias for the column.
    *
    * <code>
    * $sql      =       new tgcSqlBuilder_Select($dbc)
    * // this will add the following statement: SELECT users.username AS name ...
    * $sql->addSelect('users', 'username', 'name');
    * </code>
    *
    * If you call $sql->generateQuery() before you add a select statement, a SELECT * FROM ... will be performed.
    *
    * @access       public
    * @param        string      $table      tablename
    * @param        string      $column     columnname
    * @param        string      $alias      aliasname for the column
    * @see          addRawSelect(), removeSelect()
    */
    function addSelect($table, $column, $alias = null)
    {
        $statement  =   array
                        (
                            'table'     =>  $table,
                            'column'    =>  $column,
                            'alias'     =>  $alias
                        );
        array_push($this->_select, $statement);
    }
    
   /**
    * Remove a SELECT statement.
    *
    * If call the method without parameters, all SELECT statements that have been stored so far will be removed.
    * If you call the method with one or two parameters, then the specified SELECT statement will be removed.
    *
    * <code>
    * $sql      =       new tgcSqlBuilder_Select($dbc);
    * $sql->addSelect('users', 'userId');
    * $sql->addSelect('users', 'username', 'name');
    * $sql->addSelect('users', 'email', 'mail');
    * 
    * // remove a specific SELECT statement by alias
    * $sql->removeSelect('name');
    *
    * // remove a specific SELECT statement
    * $sql->removeSelect('users', 'userId');
    *
    * // remove all SELECT statements
    * $sql->removeSelect();
    * </code>
    *
    * @access       public
    * @param        string      $table      tablename
    * @param        string      $column     columnname
    * @return       mixed       true on success, else PEAR_Error object
    */
    function removeSelect($table = null, $column = null)
    {
        // delete all selects
        if (is_null($table) && is_null($column)) {
            $this->_select      =       array();
            return true;
        }
        
        // delete depending on alias
        if (! is_null($table) && is_null($column)) {
            $alias              =       $table;
            $newSelect          =       array();
            foreach ($this->_select as $selectData) {
                if ($selectData['alias'] != $alias) {
                    array_push($newSelect, $selectData);
                }
            }
            $this->_select      =       $newSelect;
            return true;
        }
        
        // delete depending on $table and $column
        if (! is_null($table) && ! is_null($column)) {
            $newSelect          =       array();
            foreach ($this->_select as $selectData) {
                if (! ($selectData['table'] == $table && $selectData['column'] == $column)) {
                    array_push($newSelect, $selectData);
                }
            }
            $this->_select      =       $newSelect;
            return true;
        }
        
        return  PEAR::raiseError(
                                    'Removing SELECT statement failed',
                                    SQLBUILDER_ERROR_SELECT_REMOVE
                );
    }
    
   /**
    * Add a SELECT statement. Optionally you can specify an alias for the column.
    *
    * <code>
    * $sql      =       new tgcSqlBuilder_Select($dbc)
    * // this will add the following statement: 
    * // SELECT MAX(users.userId) AS maxId, AVG(users.money) FROM ...
    * $sql->addRawSelect('MAX(users.userId)', 'maxId');
    * $sql->addRawSelect('AVG(users.money)');
    * </code>
    *
    * If you call $sql->generateQuery() before you add a (raw) SELECT statement, a SELECT * FROM ... will be performed.
    *
    * @access       public
    * @param        string      $statement      raw SELECT statement
    * @param        string      $alias          alias
    * @see          addSelect(), removeRawSelect()
    */
    function addRawSelect($statement, $alias = null)
    {
        $rawSelect                  =       array();
        $rawSelect['statement']     =       $statement;
        if (! is_null($alias)) {
            $rawSelect['alias']     =       $alias;
        }
        
        array_push($this->_rawSelect, $rawSelect);
    }
    
   /**
    * Remove a raw SELECT statement.
    *
    * If call the method without parameters, all raw SELECT statements that have been stored so far will be removed.
    * If you call the method with parameter, then the specified SELECT statement will be removed.
    *
    * <code>
    * $sql      =       new tgcSqlBuilder_Select($dbc);
    * $sql->addRawSelect('MAX(users.userId)', 'maxId');
    * $sql->addRawSelect('AVG(users.money)');
    * 
    * // remove a specific raw SELECT statement by alias
    * $sql->removeRawSelect('maxId');
    *
    * // remove all raw SELECT statements
    * $sql->removeRawSelect();
    * </code>
    *
    * @access       public
    * @param        string      $alias      alias
    */
    function removeRawSelect($alias = null)
    {
        // delete specific raw SELECT
        if (! is_null($alias)) {
            $newRawSelect       =       array();
            foreach ($this->_rawSelect as $rawSelectData) {
                if (isset($rawSelectData['alias']) && $rawSelectData['alias'] != $alias) {
                    array_push($newRawSelect, $rawSelectData);
                } elseif (! isset($rawSelectData['alias'])) {
                    array_push($newRawSelect, $rawSelectData);
                }
            }
            $this->_rawSelect   =       $newRawSelect;
            return;
        }
        
        // delete all raw SELECT statements
        $this->_rawSelect       =       array();
    }
    
   /**
    * Add a WHERE statement.
    *
    * Possible comparison operators are:
    * SQLBUILDER_COMP_EQUAL, SQLBUILDER_COMP_NOT_EQUAL, SQLBUILDER_COMP_LESSER_THAN, SQLBUILDER_COMP_LESSER_EQUAL,
    * SQLBUILDER_COMP_GREATER_EQUAL, SQLBUILDER_COMP_GREATER_THAN, SQLBUILDER_COMP_STARTSWITH, SQLBUILDER_COMP_CONTAINS, 
    * SQLBUILDER_COMP_ENDSWITH, SQLBUILDER_COMP_BETWEEN
    *
    * If none is specified then SQLBUILDER_COMP_EQUAL will be used.
    *
    * Possible logical expressions are:
    * SQLBUILDER_LOGICAL_AND, SQLBUILDER_LOGICAL_OR
    *
    * If none is specified, then SQLBUILDER_LOGICAL_AND will be used.
    *
    * When you are using SQLBUILDER_COMP_BETWEEN, then specify  $values as a numerical array with two values 
    * in correct order.
    *
    * @access       public
    * @param        string      $table      tablename
    * @param        string      $column     columnname
    * @param        mixed       $value      value(s)
    * @param        string      $compOp     comparison operator
    * @param        string      $logic      logical linkup
    */
    function addWhere($table, $column, $value, $compOp = null, $logic = null)
    {
        $this->_addWhereHaving($this->_where, $table, $column, $value, $compOp, $logic);
    }
    
   /**
    * Remove a WHERE statement.
    *
    * If you don't specify any parameter, then all WHERE information will be removed.
    * If you specify a tablename and a columnname, then this specific ORDER BY setting will be removed.
    *
    * @access       public
    * @param        string      $table      tablename
    * @param        string      $column     columnname
    * @param        string      $logic      logical operation (possible values: SQLBUILDER_LOGICAL_AND, SQLBUILDER_LOGICAL_OR)
    * @return       mixed       true on success or PEAR_Error (possible error(s): SQLBUILDER_ERROR_INVALID_PARAM_COMBO)
    */
    function removeWhere($table = null, $column = null, $logic = null)
    {
        return $this->_removeWhereHaving($this->_where, $table, $column, $logic);
    }
    
   /**
    * Add a raw WHERE statement.
    *
    * You can add a raw WHERE statement and define a logical operator. As default this is the logical AND.
    *
    * @access       public
    * @param        string      $statement      raw WHERE statement
    * @param        string      $logic          SQLBUILDER_LOGICAL_AND or SQLBUILDER_LOGICAL_OR
    */
    function addRawWhere($statement, $logic = SQLBUILDER_LOGICAL_AND)
    {
        $this->_addRawWhereHaving($this->_rawWhere, $statement, $logic);
    }
    
   /**
    * Remove the raw WHERE statements, that have been stored so far.
    *
    * @access       public
    * @param        string      $logic          SQLBUILDER_LOGICAL_AND or SQLBUILDER_LOGICAL_OR
    * @return       mixed       true on success, else PEAR_Error
    */
    function removeRawWhere($logic = null)
    {
        return $this->_removeRawWhereHaving($this->_rawWhere, $logic);
    }
    
   /**
    * Add a GROUP BY statement.
    *
    * How to call the method:
    * <code>
    * $sql      =       new tgcSqlBuilder_Select($dbc);
    * 
    * // add a GROUP BY for a tablename and a column, ASC
    * // as ASC is the default direction it doesn't have to be specified
    * $sql->addGroupBy('users', 'groupName');
    *
    * // group by alias 'grpName', direction DESC
    * $sql->addGroupBy('grpName', null, SQLBUILDER_ORDER_DESC);
    *
    * // group by column no. 2, direction ASC
    * $sql->addGroupBy(2);
    * </code>
    *
    * The statements will be found in that order in the query, in which they have been added.
    *
    * @access       public
    * @param        string      $table          tablename
    * @param        string      $column         columnname
    * @param        string      $direction      SQLBUILDER_ORDER_ASC or SQLBUILDER_ORDER_DESC
    * @return       mixed       true on success, else PEAR_Error
    */
    function addGroupBy($table, $column = null, $direction = SQLBUILDER_ORDER_ASC)
    {
        // check $direction
        if (! ($direction == SQLBUILDER_ORDER_ASC || $direction == SQLBUILDER_ORDER_DESC)) {
            return PEAR::raiseError (
                                        'Invalid ORDER BY direction',
                                        SQLBUILDER_ERROR_INVALID_ORDER_BY_DIRECTION
                                    );
        }
        
        // normal method-call
        if (! is_null($column)) {
            $groupBy        =       array
                                    (
                                        'table'     =>      $table,
                                        'column'    =>      $column,
                                        'direction' =>      $direction
                                    );
            array_push($this->_groupBy, $groupBy);
            return true;
        }
        
        // alias or col-number
        if (is_int($table)) {
            $groupBy        =       array
                                    (
                                        'int'       =>      $table,
                                        'direction' =>      $direction
                                    );
            array_push($this->_groupBy, $groupBy);
            return true;
        } elseif (is_string($table)) {
            $groupBy        =       array
                                    (
                                        'alias'     =>      $table,
                                        'direction' =>      $direction
                                    );
            array_push($this->_groupBy, $groupBy);
            return true;
        }
    }
    
   /**
    * Remove a GROUP BY statement.
    *
    * @access       public
    * @param        string      $table      tablename
    * @param        string      $column     columnname
    * @return       mixed       true on success, else PEAR_Error
    */
    function removeGroupBy($table = null, $column = null)
    {
        // remove all
        if (is_null($table) && is_null($column)) {
            $this->_groupBy     =       array();
            return true;
        }
        
        // remove depending on table- and columnname
        if (! is_null($table) && ! is_null($column)) {
            $newGroupBy         =       array();
            foreach ($this->_groupBy as $groupBy) {
                if (isset($groupBy['table']) && isset($groupBy['column'])) {
                    if (! ($groupBy['table'] == $table && $groupBy['column'] == $column)) {
                        array_push($newGroupBy, $groupBy);
                    }
                } else {
                    array_push($newGroupBy, $groupBy);
                }
            }
            return true;
        }
        
        // delete depending on alias or col-number
        if (! is_null($table) && is_null($column)) {
            $newGroupBy         =       array();
            
            // col-number
            if (is_int($table)) {
                foreach ($this->_groupBy as $groupBy) {
                    if (isset($groupBy['int'])) {
                        if ($groupBy['int'] != $table) {
                            array_push($newGroupBy, $groupBy);
                        }
                    } else {
                        array_push($newGroupBy, $groupBy);
                    }
                }
                return true;
            }
            
            // alias
            if (is_string($table)) {
                foreach ($this->_groupBy as $groupBy) {
                    if (isset($groupBy['alias'])) {
                        if ($groupBy['alias'] != $table) {
                            array_push($newGroupBy, $groupBy);
                        }
                    } else {
                        array_push($newGroupBy, $groupBy);
                    }
                }
                return true;
            }
            
            return PEAR::raiseError (
                                        'Invalid parameter-datatype',
                                        SQLBUILDER_ERROR_INVALID_PARAMETER
                                    );
        }
        
        return PEAR::raiseError (
                                        'The combination of parameters is invalid',
                                        SQLBUILDER_ERROR_INVALID_PARAM_COMBO
                                );
    }
    
   /**
    * Add an ORDER BY setting.
    *
    * The parameter $direction can be either SQLBUILDER_ORDER_ASC or SQLBUILDER_ORDER_DESC.
    * If none is specified, then SQLBUILDER_ORDER_ASC will be used.
    * 
    * You can also leave $column null, if you want to order by an alias.
    * <code>
    * $sql      =       new tgcSqlBuilder_Delete($dbc);
    *
    * // ... ORDER BY alias1 ASC ...
    * $sql->addOrderBy('alias1');
    *
    * // ... ORDER BY alias2 DESC ...
    * $sql->addOrderBy('alias2', null, SQLBUILDER_ORDER_DESC);
    * </code>
    *
    * If a setting for this table/column exists, it will be overwritten.
    *
    * @access       public
    * @param        string      $table      tablename
    * @param        string      $column     columnname
    * @param        string      $direction  oder direction
    */
    function addOrderBy($table, $column = null, $direction = null)
    {
        $direction  =   is_null($direction) ? SQLBUILDER_ORDER_ASC : $direction;
        
        if (! is_null($column)) {
            $orderBy    =   array(
                                    'table'     =>  $table,
                                    'column'    =>  $column,
                                    'direction' =>  $direction
                            );
        } else {
            $orderBy    =   array(
                                    'table'     =>  $table,
                                    'column'    =>  null,
                                    'direction' =>  $direction
                            );
        }
        
        $exists     =   false;
        foreach ($this->_orderBy as $index => $orderByData) {
            if ($orderByData['table'] == $table && $orderByData['column'] == $column) {
                $exists     =   true;
                break;
            }
        }
        if ($exists) {
            $this->_orderBy[$index]     =   $orderBy;
            return;
        }
        
        array_push($this->_orderBy, $orderBy);
    }
    
   /**
    * Remove an ORDER BY setting.
    *
    * If you don't specify any parameter, then all ORDER BY information will be removed.
    * If you specify a tablename and a columnname, then this specific ORDER BY setting will be removed.
    *
    * @access       public
    * @param        string      $table      tablename
    * @param        string      $column     columnname
    * @return       mixed       true on success or PEAR_Error (possible error(s): SQLBUILDER_ERROR_INVALID_PARAM_COMBO)
    */
    function removeOrderBy($table = null, $column = null)
    {
        // delete all
        if (is_null($table) && is_null($column)) {
            $this->_orderBy     =       array();
            return true;
        }
        
        // delete table.column
        if (! is_null($table) && ! is_null($column)) {
            $newOrderBy         =       array();
            foreach ($this->_orderBy as $orderByData) {
                if (! ($orderByData['table'] == $table && $orderByData['column'] == $column)) {
                    array_push($newOrderBy, $orderByData);
                }
            }
            $this->_orderBy     =       $newOrderBy;
            return true;
        }
        
        // delete alias
        if (! is_null($table) && is_null($column)) {
            $newOrderBy         =       array();
            foreach ($this->_orderBy as $orderByData) {
                if (! ($orderByData['table'] == $table && $orderByData['column'] == null)) {
                    array_push($newOrderBy, $orderByData);
                }
            }
            $this->_orderBy     =       $newOrderBy;
            return true;
        }
        
        return PEAR::raiseError (
                                    'The combination of parameters is invalid',
                                    SQLBUILDER_ERROR_INVALID_PARAM_COMBO,
                                    null,
                                    null,
                                    'You have to call the method with none or two parameters'
                                );
    }
    
   /**
    * Set a LIMIT for the query.
    *
    * Example:
    * <code>
    * $sql      =       new tgcSqlBuilder_Select($dbc);
    * 
    * // set a limit of 15 rows starting from offset 30
    * $sql->setLimit(30, 15);
    *
    * // set a limit of 20 rows (starting from offset 0)
    * $sql->setLimit(20);
    * </code>
    *
    * @access       public
    * @param        int         $offset         offset
    * @param        int         $rows           number of rows
    */
    function setLimit($offset, $rows = null)
    {
        if (is_null($rows)) {
            $this->_limit['offset']     =       null;
            $this->_limit['rows']       =       $offset;
            return;
        }
        
        $this->_limit['offset']     =       $offset;
        $this->_limit['rows']       =       $rows;
    }
    
   /**
    * Unset the current LIMIT information.
    *
    * @access       public
    */
    function unsetLimit()
    {
        $this->_limit['offset']     =       null;
        $this->_limit['rows']       =       null;
    }
    
   /**
    * Add a WHERE statement.
    *
    * Possible comparison operators are:
    * SQLBUILDER_COMP_EQUAL, SQLBUILDER_COMP_NOT_EQUAL, SQLBUILDER_COMP_LESSER_THAN, SQLBUILDER_COMP_LESSER_EQUAL,
    * SQLBUILDER_COMP_GREATER_EQUAL, SQLBUILDER_COMP_GREATER_THAN, SQLBUILDER_COMP_STARTSWITH, SQLBUILDER_COMP_CONTAINS, 
    * SQLBUILDER_COMP_ENDSWITH, SQLBUILDER_COMP_BETWEEN
    *
    * If none is specified then SQLBUILDER_COMP_EQUAL will be used.
    *
    * Possible logical expressions are:
    * SQLBUILDER_LOGICAL_AND, SQLBUILDER_LOGICAL_OR
    *
    * If none is specified, then SQLBUILDER_LOGICAL_AND will be used.
    *
    * When you are using SQLBUILDER_COMP_BETWEEN, then specify  $values as a numerical array with two values 
    * in correct order.
    *
    * @access       public
    * @param        string      $table      tablename
    * @param        string      $column     columnname
    * @param        mixed       $value      value(s)
    * @param        string      $compOp     comparison operator
    * @param        string      $logic      logical linkup
    */
    function addHaving($table, $column, $value, $compOp = null, $logic = null)
    {
        $this->_addWhereHaving($this->_having, $table, $column, $value, $compOp, $logic);
    }
    
   /**
    * Remove a WHERE statement.
    *
    * If you don't specify any parameter, then all WHERE information will be removed.
    * If you specify a tablename and a columnname, then this specific ORDER BY setting will be removed.
    *
    * @access       public
    * @param        string      $table      tablename
    * @param        string      $column     columnname
    * @param        string      $logic      logical operation (possible values: SQLBUILDER_LOGICAL_AND, SQLBUILDER_LOGICAL_OR)
    * @return       mixed       true on success or PEAR_Error (possible error(s): SQLBUILDER_ERROR_INVALID_PARAM_COMBO)
    */
    function removeHaving($table = null, $column = null, $logic = null)
    {
        return $this->_removeWhereHaving($this->_having, $table, $column, $logic);
    }
    
   /**
    * Add a raw HAVING statement.
    *
    * You can add a raw HAVING statement and define a logical operator. As default this is the logical AND.
    *
    * @access       public
    * @param        string      $statement      raw HAVING statement
    * @param       string      $logic          SQLBUILDER_LOGICAL_AND or SQLBUILDER_LOGICAL_OR
    */
    function addRawHaving($statement, $logic = SQLBUILDER_LOGICAL_AND)
    {
        $this->_addRawWhereHaving($this->_rawHaving, $statement, $logic);
    }
    
   /**
    * Remove the raw WHERE statements, that have been stored so far.
    *
    * @access       public
    * @param       string      $logic          SQLBUILDER_LOGICAL_AND or SQLBUILDER_LOGICAL_OR
    * @return       mixed       true on success, else PEAR_Error
    */
    function removeRawHaving($logic = null)
    {
        return $this->_removeRawWhereHaving($this->_rawHaving, $logic);
    }
    

    
   /**
    * Generates a string based on the objects tables.
    *
    * @access       private
    * @param        mixed           $att           TABLE/FROM attribute
    */
    function _generateTableInformation(&$att)
    {
        $statement      =       array();
        
        foreach ($att as $tableName => $alias) {
            if (! is_null($alias)) {
                array_push($statement, sprintf("%s as %s", $tableName, $alias));
            } else {
                array_push($statement, $tableName);
            }
        }
        
        return implode(', ', $statement);
    }
    
   /**
    * Generates a string based on the objects LIMIT information.
    *
    * @access       private
    * @param        mixed           $att           LIMIT attribute
    */
    function _generateLimitInformation(&$att)
    {
        if (! is_null($att['offset'])) {
            return sprintf("%d, %d", $att['offset'], $att['rows']);
        }
        
        return sprintf("%d", $att['rows']);
    }
    
   /**
    * Generates a string based on the object's SELECT information.
    *
    * @access       private
    * @param        mixed           $select        SELECT attribute
    * @param        mixed           $rawSelect     raw SELECT attribute
    */
    function _generateSelectInformation(&$select, &$rawSelect)
    {
        $statement      =       array();
        
        foreach ($select as $selectData) {
            if (! is_null($selectData['alias'])) {
                array_push($statement, sprintf("%s.%s as %s",   $selectData['table'], 
                                                                $selectData['column'], 
                                                                $selectData['alias']) );
            } else {
                array_push($statement, sprintf("%s.%s", $selectData['table'], $selectData['column']) );
            }
        }
        
        foreach ($rawSelect as $selectData) {
            if (isset($selectData['alias']) && ! is_null($selectData['alias'])) {
                array_push($statement, sprintf("%s as %s",  $selectData['statement'], 
                                                            $selectData['alias']) );
            } else {
                array_push($statement, $selectData['statement']);
            }
        }
        
        
        
        return implode(', ', $statement);
    }
    
   /**
    * Generate a string for the final query based on the GROUP BY information.
    *
    * @access       private
    * @param        mixed           $att           GROUP BY attribute
    *
    *
    *
    *
    * <pre>
    * array(
    *       array(
    *               'table'     =>      $tableName,
    *               'column'    =>      $columnName,
    *               'direction' =>      $direction,
    *               'alias'     =>      $alias,
    *               'int'       =>      $int
    *       ),
    *       array( ... ),
    *       ...
    * )
    * </pre>
    */
    function _generateGroupByInformation(&$att)
    {
        $statement      =       array();
        
        foreach ($att as $groupBy) {
            // by table and column
            if (isset($groupBy['table']) && isset($groupBy['column'])) {
                array_push($statement, sprintf("%s.%s %s", $groupBy['table'], $groupBy['column'], $groupBy['direction']) );
            } 
            // by alias
            elseif (isset($groupBy['alias'])) {
                array_push($statement, sprintf("%s %s", $groupBy['alias'], $groupBy['direction']));
            }
            // by col-number
            elseif (isset($groupBy['int'])) {
                array_push($statement, sprintf("%s %s", $groupBy['int'], $groupBy['direction']));
            }
        }
        
        return implode(', ', $statement);
    }
    
   /**
    * Generates a string based on the object's HAVING information.
    *
    * Must only be called from objects that contain a HAVING attribute.
    *
    * @access       private
    * @param        array           $having          HAVING attribute
    * @param        array           $rawHaving       raw HAVING attribute
    * @uses         _generateWhereInformation()
    */
    function _generateHavingInformation(&$having, &$rawHaving)
    {
        return $this->_generateWhereInformation($having, $rawHaving);
    }
    
}
?>
Return current item: tgcSqlBuilder