<?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;
}
}
?>