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