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



 /**
 * No update values specified for a query
 *
 * @access      private
 */
define('SQLBUILDER_ERROR_NO_UPDATE_VALUES', 301);

 
 
/**
 * Subclass of tgcSqlBuilder that helps to create UPDATE sql-statements.
 *
 * @package     tgcSqlBuilder
 * @access      public
 * @version     1.0.0
 * @author      Carsten Lucke <hide@address.com>
 */
class tgcSqlBuilder_Update extends tgcSqlBuilder 
{
    
   /**
    * 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 LIMIT information.
    *
    * @access       private
    * @var          int             limit information
    */
    var $_limit;
    
   /**
    * 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;
    
   /**
    * Update data.
    *
    * Array structure:
    * <pre>
    * array (
    *           $col1Name   =>  $col1Value,
    *           $col2Name   =>  $col2Value,
    *           ...
    *       )
    * </pre>
    *
    * @access       private
    * @var          array           update data
    */
    var $_update = array();
    
   /**
    * Raw UPDATE data.
    *
    * Array structure:
    * <pre>
    * array (
    *           statement1,
    *           statement2,
    *           ...
    *       )
    * </pre>
    *
    * @access       private
    * @var          array           UPDATE data
    */
    var $_rawUpdate = array();
    
    
    
   /**
    * Constructor
    *
    * @access       public
    * @param        object          $dbc            PEAR::DB connection object
    */
    function tgcSqlBuilder_Update(&$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_Update($dbc);
    * $query    =   $sql->generateQuery();
    * </code>
    *
    * @access       public
    * @return       string          sql-statement
    */
    function generateQuery()
    {
        // check if a table has been specified
        if (empty($this->_tables))
        {
            return PEAR::raiseError (
                                        'You have to specifiy a tablename first',
                                        SQLBUILDER_ERROR_NO_TABLE_FOUND
                                    );
        }
        
        $query               =      'UPDATE ' . $this->_tables . ' SET';
        
        $updateInformation   =      $this->_generateUpdateInformation($this->_update, $this->_rawUpdate);
        if ($updateInformation != '') {
            $query      .=      ' ' . $updateInformation;
            
            if (count($this->_where[SQLBUILDER_LOGICAL_AND]) || count($this->_where[SQLBUILDER_LOGICAL_OR])) {
                $query  .=      ' WHERE ' . $this->_generateWhereInformation($this->_where, $this->_rawWhere);
            }
            
            if (! is_null($this->_limit)) {
                $query  .=      ' LIMIT ' . $this->_limit;
            }
            
            $this->reset();
            return $query;
        }
        
        return PEAR::raiseError (
                                        'No update values found.',
                                        SQLBUILDER_ERROR_NO_UPDATE_VALUES,
                                        null,
                                        null,
                                        'To generate a valid query you have to specify at least one update value'
                                    );
    }
    
   /**
    * Generates the UPDATE information.
    *
    * @access       private
    * @param        array       $update         UPDATE information
    * @param        array       $rawUpdate      raw UPDATE information
    * @return       string      statement for the query on success, else empty string
    */
    function _generateUpdateInformation($update, $rawUpdate)
    {
        $information        =       '';
        
        if (count($update) || count($rawUpdate)) {
            $colNames       =       array_keys($update);
            $values         =       array_map(array($this, 'escape'), array_values($update));
            
            foreach ($rawUpdate as $name => $value) {
                array_push($colNames, $name);
                array_push($values, $value);
            }
            
            $parts          =   array();
            for ($i = 0; $i< count($colNames); $i++) {
                array_push($parts, sprintf('%s = %s', $colNames[$i], $values[$i]));
            }
            $information    =       implode(', ', $parts);
            $foundValues    =       true;
            return $information;
        }
        
        return '';
    }
    
   /**
    * Add the statements table.
    *
    * If you call this method twice, the tablename that was set in first call will be overwritten.
    *
    * <code>
    * $sql      =   new tgcSqlBuilder_Update($dbc);
    * $sql->addTable('users');
    * // now the generated sql-statement would look like: UPDATE users ...
    * </code>
    *
    * @access       public
    * @param        string          $tableName      tablename
    */
    function addTable($tableName)
    {
        $this->_tables      =   $tableName;
    }
    
   /**
    * Remove the tablename.
    *
    * @access       public
    */
    function removeTable()
    {
        $this->_tables      =   null;
    }
    
   /**
    * Set the LIMIT for the sql-statement.
    *
    * @access       public
    * @param        int         $offset         offset
    * @param        int         $rows           rows
    */
    function setLimit($rows)
    {
        $this->_limit       =       $rows;
    }
    
   /**
    * Remove the LIMIT for the sql-statement.
    *
    * @access       public
    */
    function unsetLimit()
    {
        $this->_limit       =       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 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 value to update.
    *
    * There are two ways of calling this method:
    *
    * <code>
    * // simple method-call
    * $sql      =       new tgcSqlBuilder_Update($dbc);
    * $sql->addUpdate('col_Username', 'superman');
    *
    * // complex method-call, you can add more than one update with only one method-call
    * $update   =       array   (
    *                               'col_Username'  =>  'superman',
    *                               'col_Email'     =>  'hide@address.com'
    *                           );
    * $sql->addUpdate($update);
    * </code>
    *
    * @access       public
    * @param        mixed           $colName        columnname or associative array containing pairs of $colName => $value
    * @param        mixed           $value          value to update
    * @return       mixed           true on success, else PEAR_Error (possible error(s): SQLBUILDER_ERROR_INVALID_PARAM_COMBO)
    */
    function addUpdate($colName, $value = null)
    {
        if (is_array($colName) && is_null($value)) {
            foreach ($colName as $key => $value) {
                $this->_update[$key]    =   $value;
            }
            return true;
        }
        
        if (! is_null($colName) && ! is_null($value)) {
            $this->_update[$colName]    =   $value;
            return true;
        }
        
        return PEAR::raiseError(    'Invalid parameter combination',
                                    SQLBUILDER_ERROR_INVALID_PARAM_COMBO,
                                    null,
                                    null,
                                    'You called this method with an invalid parameter combination.'
                                );
    }
    
   /**
    * Remove one or all update columns.
    *
    * If you specify a columnname, then just this column's update will be removed, else all updates will be removed.
    *
    * @access       public
    * @param        string          $colName        columnname
    * @return       mixed           true on success, else PEAR_Error (possible error(s): SQLBUILDER_ERROR_COLUMN_DOES_NOT_EXIST)
    */
    function removeUpdate($colName = null)
    {
        if(! is_null($colName)) {
            if (isset($this->_update[$colName])) {
                unset($this->_update[$colName]);
                return true;
            }
            
            return PEAR::raiseError (
                                        'Column doesn\'t exist',
                                        SQLBUILDER_ERROR_COLUMN_DOES_NOT_EXIST,
                                        null,
                                        null,
                                        'The column you tried to remove does not exist.'
                                    );
        }
        
        $this->_update  =   array();
        return true;
    }
    
   /**
    * Add a raw UPDATE statement
    *
    * There are two ways of calling this method:
    *
    * <code>
    * // simple method-call
    * $sql      =       new tgcSqlBuilder_Update($dbc);
    * $sql->addUpdate('creationTime', 'NOW()');
    *
    * // complex method-call, you can add more than one insert with only one method-call
    * $update   =       array   (
    *                               'created'   =>  'NOW()',
    *                               'changed'   =>  'NOW()'
    *                           );
    * $sql->addUpdate($update);
    * </code>
    *
    * @access       public
    * @param        mixed           $colName        columnname or associative array containing pairs of $colName => $value
    * @param        mixed           $value          value to insert
    * @return       mixed           true on success, else PEAR_Error (possible error(s): SQLBUILDER_ERROR_INVALID_PARAM_COMBO)
    */
    function addRawUpdate($colName, $value = null)
    {
        if (is_array($colName) && is_null($value)) {
            foreach ($colName as $key => $value) {
                $this->_rawUpdate[$key]    =   $value;
            }
            return true;
        }
        
        if (! is_null($colName) && ! is_null($value)) {
            $this->_rawUpdate[$colName]    =   $value;
            return true;
        }
        
        return PEAR::raiseError(    'Invalid parameter combination',
                                    SQLBUILDER_ERROR_INVALID_PARAM_COMBO,
                                    null,
                                    null,
                                    'You called this method with an invalid parameter combination.'
                                );
    }
    
   /**
    * Remove one or all UPDATE columns.
    *
    * If you specify a columnname, then just this column's UPDATE will be removed, else all UPDATEs will be removed.
    *
    * @access       public
    * @param        string          $colName        columnname
    * @return       mixed           true on success, else PEAR_Error (possible error(s): SQLBUILDER_ERROR_COLUMN_DOES_NOT_EXIST)
    */
    function removeRawUpdate($colName = null)
    {
        if(! is_null($colName)) {
            if (isset($this->_rawUpdate[$colName])) {
                unset($this->_rawUpdate[$colName]);
                return true;
            }
            
            return PEAR::raiseError (
                                        'Column doesn\'t exist',
                                        SQLBUILDER_ERROR_COLUMN_DOES_NOT_EXIST,
                                        null,
                                        null,
                                        'The column you tried to remove does not exist.'
                                    );
        }
        
        $this->_rawUpdate  =   array();
        return true;
    }
    
   /**
    * Reset the object's whole information.
    *
    * @access       public
    */
    function reset()
    {
        $this->removeTable();
        $this->removeUpdate();
        $this->removeRawUpdate();
        $this->removeRawWhere();
        $this->removeWhere();
        $this->unsetLimit();
    }
    
   /**
    * 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)
    {
        array_push($this->_rawWhere[$logic], $statement);
    }
    
   /**
    * Remove the raw WHERE statements, that have been stored so far.
    *
    * @access       public
    * @param        string      $logic          SQLBUILDER_LOGICAL_AND or SQLBUILDER_LOGICAL_OR
    * @return       boolean     true on success, else PEAR_Error
    */
    function removeRawWhere($logic = null)
    {
        // check $logic, if specified
        if (! is_null($logic) && ! ($logic == SQLBUILDER_LOGICAL_AND || $logic == SQLBUILDER_LOGICAL_OR)) {
            return PEAR::raiseError (
                                        'Invalid logical operator',
                                        SQLBUILDER_ERROR_INVALID_LOGICAL_OPERATOR
                                    );
        }
        
        // remove all raw WHERE statements
        if (is_null($logic)) {
            $this->_rawWhere    =   array
                                    (
                                        SQLBUILDER_LOGICAL_AND  =>  array(),
                                        SQLBUILDER_LOGICAL_OR   =>  array()
                                    );
            return true;
        }
        
        // delete depending on $logic
        $this->_rawWhere[$logic]    =   array();
        return true;
    }
    
}
?>
Return current item: tgcSqlBuilder