Class Zend_Db_Select

Class for SQL SELECT generation and results.

Direct known subclasses

Package: Zend\Db\Select
Category: Zend
Copyright: Copyright (c) 2005-2010 Zend Technologies USA Inc. (http://www.zend.com)
License: New BSD License
Located at Zend/Db/Select.php

Methods summary
`public`	# `__construct( Zend_Db_Adapter_Abstract $adapter )` Class constructor Class constructor Parameters `$adapter` `Zend_Db_Adapter_Abstract`
`public array`	# `getBind( )` Get bind variables Get bind variables Returns `array`
`public Zend_Db_Select`	# `bind( mixed $bind )` Set bind variables Set bind variables Parameters `$bind` `mixed` Returns `Zend_Db_Select`
`public Zend_Db_Select`	# `distinct( boolean $flag = true )` Makes the query SELECT DISTINCT. Makes the query SELECT DISTINCT. Parameters `$flag` `boolean` Whether or not the SELECT is DISTINCT (default true). Returns `Zend_Db_Select` This Zend_Db_Select object.
`public Zend_Db_Select`	# `from( array\|string\|Zend_Db_Expr $name, array\|string\|Zend_Db_Expr $cols = '*', string $schema = null )` Adds a FROM table and optional columns to the query. Adds a FROM table and optional columns to the query. The first parameter $name can be a simple string, in which case the correlation name is generated automatically. If you want to specify the correlation name, the first parameter must be an associative array in which the key is the correlation name, and the value is the physical table name. For example, array('alias' => 'table'). The correlation name is prepended to all columns fetched for this table. The second parameter can be a single string or Zend_Db_Expr object, or else an array of strings or Zend_Db_Expr objects. The first parameter can be null or an empty string, in which case no correlation name is generated or prepended to the columns named in the second parameter. Parameters `$name` `array\|string\|Zend_Db_Expr` The table name or an associative array relating correlation name to table name. `$cols` `array\|string\|Zend_Db_Expr` The columns to select from this table. `$schema` `string` The schema name to specify, if any. Returns `Zend_Db_Select` This Zend_Db_Select object.
`public Zend_Db_Select`	# `columns( array\|string\|Zend_Db_Expr $cols = '*', string $correlationName = null )` Specifies the columns used in the FROM clause. Specifies the columns used in the FROM clause. The parameter can be a single string or Zend_Db_Expr object, or else an array of strings or Zend_Db_Expr objects. Parameters `$cols` `array\|string\|Zend_Db_Expr` The columns to select from this table. `$correlationName` `string` Correlation name of target table. OPTIONAL Returns `Zend_Db_Select` This Zend_Db_Select object.
`public Zend_Db_Select`	# `union( array $select = array(), $type = Zend_Db_Select::SQL_UNION )` Adds a UNION clause to the query. Adds a UNION clause to the query. The first parameter has to be an array of Zend_Db_Select or sql query strings. $sql1 = $db->select(); $sql2 = "SELECT ..."; $select = $db->select() ->union(array($sql1, $sql2)) ->order("id"); Parameters `$select` `array` Array of select clauses for the union. `$type` Returns `Zend_Db_Select` This Zend_Db_Select object.
`public Zend_Db_Select`	# `join( array\|string\|Zend_Db_Expr $name, string $cond, array\|string $cols = Zend_Db_Select::SQL_WILDCARD, string $schema = null )` Adds a JOIN table and columns to the query. Adds a JOIN table and columns to the query. The $name and $cols parameters follow the same logic as described in the from() method. Parameters `$name` `array\|string\|Zend_Db_Expr` The table name. `$cond` `string` Join on this condition. `$cols` `array\|string` The columns to select from the joined table. `$schema` `string` The database name to specify, if any. Returns `Zend_Db_Select` This Zend_Db_Select object.
`public Zend_Db_Select`	# `joinInner( array\|string\|Zend_Db_Expr $name, string $cond, array\|string $cols = Zend_Db_Select::SQL_WILDCARD, string $schema = null )` Add an INNER JOIN table and colums to the query Rows in both tables are matched according to the expression in the $cond argument. The result set is comprised of all cases where rows from the left table match rows from the right table. Add an INNER JOIN table and colums to the query Rows in both tables are matched according to the expression in the $cond argument. The result set is comprised of all cases where rows from the left table match rows from the right table. The $name and $cols parameters follow the same logic as described in the from() method. Parameters `$name` `array\|string\|Zend_Db_Expr` The table name. `$cond` `string` Join on this condition. `$cols` `array\|string` The columns to select from the joined table. `$schema` `string` The database name to specify, if any. Returns `Zend_Db_Select` This Zend_Db_Select object.
`public Zend_Db_Select`	# `joinLeft( array\|string\|Zend_Db_Expr $name, string $cond, array\|string $cols = Zend_Db_Select::SQL_WILDCARD, string $schema = null )` Add a LEFT OUTER JOIN table and colums to the query All rows from the left operand table are included, matching rows from the right operand table included, and the columns from the right operand table are filled with NULLs if no row exists matching the left table. Add a LEFT OUTER JOIN table and colums to the query All rows from the left operand table are included, matching rows from the right operand table included, and the columns from the right operand table are filled with NULLs if no row exists matching the left table. The $name and $cols parameters follow the same logic as described in the from() method. Parameters `$name` `array\|string\|Zend_Db_Expr` The table name. `$cond` `string` Join on this condition. `$cols` `array\|string` The columns to select from the joined table. `$schema` `string` The database name to specify, if any. Returns `Zend_Db_Select` This Zend_Db_Select object.
`public Zend_Db_Select`	# `joinRight( array\|string\|Zend_Db_Expr $name, string $cond, array\|string $cols = Zend_Db_Select::SQL_WILDCARD, string $schema = null )` Add a RIGHT OUTER JOIN table and colums to the query. Right outer join is the complement of left outer join. All rows from the right operand table are included, matching rows from the left operand table included, and the columns from the left operand table are filled with NULLs if no row exists matching the right table. Add a RIGHT OUTER JOIN table and colums to the query. Right outer join is the complement of left outer join. All rows from the right operand table are included, matching rows from the left operand table included, and the columns from the left operand table are filled with NULLs if no row exists matching the right table. The $name and $cols parameters follow the same logic as described in the from() method. Parameters `$name` `array\|string\|Zend_Db_Expr` The table name. `$cond` `string` Join on this condition. `$cols` `array\|string` The columns to select from the joined table. `$schema` `string` The database name to specify, if any. Returns `Zend_Db_Select` This Zend_Db_Select object.
`public Zend_Db_Select`	# `joinFull( array\|string\|Zend_Db_Expr $name, string $cond, array\|string $cols = Zend_Db_Select::SQL_WILDCARD, string $schema = null )` Add a FULL OUTER JOIN table and colums to the query. A full outer join is like combining a left outer join and a right outer join. All rows from both tables are included, paired with each other on the same row of the result set if they satisfy the join condition, and otherwise paired with NULLs in place of columns from the other table. Add a FULL OUTER JOIN table and colums to the query. A full outer join is like combining a left outer join and a right outer join. All rows from both tables are included, paired with each other on the same row of the result set if they satisfy the join condition, and otherwise paired with NULLs in place of columns from the other table. The $name and $cols parameters follow the same logic as described in the from() method. Parameters `$name` `array\|string\|Zend_Db_Expr` The table name. `$cond` `string` Join on this condition. `$cols` `array\|string` The columns to select from the joined table. `$schema` `string` The database name to specify, if any. Returns `Zend_Db_Select` This Zend_Db_Select object.
`public Zend_Db_Select`	# `joinCross( array\|string\|Zend_Db_Expr $name, array\|string $cols = Zend_Db_Select::SQL_WILDCARD, string $schema = null )` Add a CROSS JOIN table and colums to the query. A cross join is a cartesian product; there is no join condition. Add a CROSS JOIN table and colums to the query. A cross join is a cartesian product; there is no join condition. The $name and $cols parameters follow the same logic as described in the from() method. Parameters `$name` `array\|string\|Zend_Db_Expr` The table name. `$cols` `array\|string` The columns to select from the joined table. `$schema` `string` The database name to specify, if any. Returns `Zend_Db_Select` This Zend_Db_Select object.
`public Zend_Db_Select`	# `joinNatural( array\|string\|Zend_Db_Expr $name, array\|string $cols = Zend_Db_Select::SQL_WILDCARD, string $schema = null )` Add a NATURAL JOIN table and colums to the query. A natural join assumes an equi-join across any column(s) that appear with the same name in both tables. Only natural inner joins are supported by this API, even though SQL permits natural outer joins as well. Add a NATURAL JOIN table and colums to the query. A natural join assumes an equi-join across any column(s) that appear with the same name in both tables. Only natural inner joins are supported by this API, even though SQL permits natural outer joins as well. The $name and $cols parameters follow the same logic as described in the from() method. Parameters `$name` `array\|string\|Zend_Db_Expr` The table name. `$cols` `array\|string` The columns to select from the joined table. `$schema` `string` The database name to specify, if any. Returns `Zend_Db_Select` This Zend_Db_Select object.
`public Zend_Db_Select`	# `where( string $cond, mixed $value = null, integer $type = null )` Adds a WHERE condition to the query by AND. Adds a WHERE condition to the query by AND. If a value is passed as the second param, it will be quoted and replaced into the condition wherever a question-mark appears. Array values are quoted and comma-separated. // simplest but non-secure $select->where("id = $id"); // secure (ID is quoted but matched anyway) $select->where('id = ?', $id); // alternatively, with named binding $select->where('id = :id'); Note that it is more correct to use named bindings in your queries for values other than strings. When you use named bindings, don't forget to pass the values when actually making a query: $db->fetchAll($select, array('id' => 5)); Parameters `$cond` `string` The WHERE condition. `$value` `mixed` OPTIONAL The value to quote into the condition. `$type` `integer` OPTIONAL The type of the given value Returns `Zend_Db_Select` This Zend_Db_Select object.
`public Zend_Db_Select`	# `orWhere( string $cond, mixed $value = null, integer $type = null )` Adds a WHERE condition to the query by OR. Adds a WHERE condition to the query by OR. Otherwise identical to where(). Parameters `$cond` `string` The WHERE condition. `$value` `mixed` OPTIONAL The value to quote into the condition. `$type` `integer` OPTIONAL The type of the given value Returns `Zend_Db_Select` This Zend_Db_Select object. See `Zend_Db_Select::where()`
`public Zend_Db_Select`	# `group( array\|string $spec )` Adds grouping to the query. Adds grouping to the query. Parameters `$spec` `array\|string` The column(s) to group by. Returns `Zend_Db_Select` This Zend_Db_Select object.
`public Zend_Db_Select`	# `having( string $cond, mixed $value = null, integer $type = null )` Adds a HAVING condition to the query by AND. Adds a HAVING condition to the query by AND. If a value is passed as the second param, it will be quoted and replaced into the condition wherever a question-mark appears. See `Zend_Db_Select::where()` for an example Parameters `$cond` `string` The HAVING condition. `$value` `mixed` OPTIONAL The value to quote into the condition. `$type` `integer` OPTIONAL The type of the given value Returns `Zend_Db_Select` This Zend_Db_Select object.
`public Zend_Db_Select`	# `orHaving( string $cond, mixed $value = null, integer $type = null )` Adds a HAVING condition to the query by OR. Adds a HAVING condition to the query by OR. Otherwise identical to orHaving(). Parameters `$cond` `string` The HAVING condition. `$value` `mixed` OPTIONAL The value to quote into the condition. `$type` `integer` OPTIONAL The type of the given value Returns `Zend_Db_Select` This Zend_Db_Select object. See `Zend_Db_Select::having()`
`public Zend_Db_Select`	# `order( mixed $spec )` Adds a row order to the query. Adds a row order to the query. Parameters `$spec` `mixed` The column(s) and direction to order by. Returns `Zend_Db_Select` This Zend_Db_Select object.
`public Zend_Db_Select`	# `limit( integer $count = null, integer $offset = null )` Sets a limit count and offset to the query. Sets a limit count and offset to the query. Parameters `$count` `integer` OPTIONAL The number of rows to return. `$offset` `integer` OPTIONAL Start returning after this many rows. Returns `Zend_Db_Select` This Zend_Db_Select object.
`public Zend_Db_Select`	# `limitPage( integer $page, integer $rowCount )` Sets the limit and count by page number. Sets the limit and count by page number. Parameters `$page` `integer` Limit results to this page number. `$rowCount` `integer` Use this many rows per page. Returns `Zend_Db_Select` This Zend_Db_Select object.
`public Zend_Db_Select`	# `forUpdate( boolean $flag = true )` Makes the query SELECT FOR UPDATE. Makes the query SELECT FOR UPDATE. Parameters `$flag` `boolean` Whether or not the SELECT is FOR UPDATE (default true). Returns `Zend_Db_Select` This Zend_Db_Select object.
`public mixed`	# `getPart( string $part )` Get part of the structured information for the currect query. Get part of the structured information for the currect query. Parameters `$part` `string` Returns `mixed` Throws `Zend_Db_Select_Exception`
`public PDO_Statement\|Zend_Db_Statement`	# `query( integer $fetchMode = null, mixed $bind = array() )` Executes the current select object and returns the result Executes the current select object and returns the result Parameters `$fetchMode` `integer` OPTIONAL `$bind` `mixed` An array of data to bind to the placeholders. Returns `PDO_Statement\|Zend_Db_Statement`
`public string\|null`	# `assemble( )` Converts this object to an SQL SELECT string. Converts this object to an SQL SELECT string. Returns `string\|null` This object as a SELECT string. (or null if a string cannot be produced.)
`public Zend_Db_Select`	# `reset( string $part = null )` Clear parts of the Select object, or an individual part. Clear parts of the Select object, or an individual part. Parameters `$part` `string` OPTIONAL Returns `Zend_Db_Select`
`public Zend_Db_Adapter_Abstract`	# `getAdapter( )` Gets the Zend_Db_Adapter_Abstract for this particular Zend_Db_Select object. Gets the Zend_Db_Adapter_Abstract for this particular Zend_Db_Select object. Returns `Zend_Db_Adapter_Abstract`
`protected Zend_Db_Select`	# `_join( null\|string $type, array\|string\|Zend_Db_Expr $name, string $cond, array\|string $cols, string $schema = null )` Populate the `Zend_Db_Select::$_parts` 'join' key Populate the `Zend_Db_Select::$_parts` 'join' key Does the dirty work of populating the join key. The $name and $cols parameters follow the same logic as described in the from() method. Parameters `$type` `null\|string` Type of join; inner, left, and null are currently supported `$name` `array\|string\|Zend_Db_Expr` Table name `$cond` `string` Join on this condition `$cols` `array\|string` The columns to select from the joined table `$schema` `string` The database name to specify, if any. Returns `Zend_Db_Select` This Zend_Db_Select object Throws `Zend_Db_Select_Exception`
`public Zend_Db_Select`	# `_joinUsing( $type, $name, $cond, $cols = '', $schema = null )` Handle JOIN... USING... syntax Handle JOIN... USING... syntax This is functionality identical to the existing JOIN methods, however the join condition can be passed as a single column name. This method then completes the ON condition by using the same field for the FROM table and the JOIN table. $select = $db->select()->from('table1') ->joinUsing('table2', 'column1'); // SELECT FROM table1 JOIN table2 ON table1.column1 = table2.column2 These joins are called by the developer simply by adding 'Using' to the method name. E.g. joinUsing joinInnerUsing joinFullUsing joinRightUsing joinLeftUsing Returns `Zend_Db_Select` This Zend_Db_Select object.
`protected`	# `_tableCols( string $correlationName, array\|string $cols, boolean\|string $afterCorrelationName = null )` Adds to the internal table-to-column mapping array. Adds to the internal table-to-column mapping array. Parameters `$correlationName` `string` $tbl The table/join the columns come from. `$cols` `array\|string` The list of columns; preferably as an array, but possibly as a string containing one column. `$afterCorrelationName` `boolean\|string` True if it should be prepended, a correlation name if it should be inserted
`protected string`	# `_where( string $condition, mixed $value = null, string $type = null, boolean $bool = true )` Internal function for creating the where clause Internal function for creating the where clause Parameters `$condition` `string` `$value` `mixed` optional `$type` `string` optional `$bool` `boolean` true = AND, false = OR Returns `string` clause
`protected array`	# `_getDummyTable( )` Returns `array`
`protected string\|null`	# `_getQuotedSchema( string $schema = null )` Return a quoted schema name Return a quoted schema name Parameters `$schema` `string` The schema name OPTIONAL Returns `string\|null`
`protected string`	# `_getQuotedTable( string $tableName, string $correlationName = null )` Return a quoted table name Return a quoted table name Parameters `$tableName` `string` The table name `$correlationName` `string` The correlation name OPTIONAL Returns `string`
`protected string`	# `_renderDistinct( string $sql )` Render DISTINCT clause Render DISTINCT clause Parameters `$sql` `string` SQL query Returns `string`
`protected string\|null`	# `_renderColumns( string $sql )` Render DISTINCT clause Render DISTINCT clause Parameters `$sql` `string` SQL query Returns `string\|null`
`protected string`	# `_renderFrom( string $sql )` Render FROM clause Render FROM clause Parameters `$sql` `string` SQL query Returns `string`
`protected string`	# `_renderUnion( string $sql )` Render UNION query Render UNION query Parameters `$sql` `string` SQL query Returns `string`
`protected string`	# `_renderWhere( string $sql )` Render WHERE clause Render WHERE clause Parameters `$sql` `string` SQL query Returns `string`
`protected string`	# `_renderGroup( string $sql )` Render GROUP clause Render GROUP clause Parameters `$sql` `string` SQL query Returns `string`
`protected string`	# `_renderHaving( string $sql )` Render HAVING clause Render HAVING clause Parameters `$sql` `string` SQL query Returns `string`
`protected string`	# `_renderOrder( string $sql )` Render ORDER clause Render ORDER clause Parameters `$sql` `string` SQL query Returns `string`
`protected string`	# `_renderLimitoffset( string $sql )` Render LIMIT OFFSET clause Render LIMIT OFFSET clause Parameters `$sql` `string` SQL query Returns `string`
`protected string`	# `_renderForupdate( string $sql )` Render FOR UPDATE clause Render FOR UPDATE clause Parameters `$sql` `string` SQL query Returns `string`
`public Zend_Db_Select`	# `__call( string $method, array $args )` Turn magic function calls into non-magic function calls for joinUsing syntax Turn magic function calls into non-magic function calls for joinUsing syntax Parameters `$method` `string` `$args` `array` OPTIONAL Zend_Db_Table_Select query modifier Returns `Zend_Db_Select` Throws `Zend_Db_Select_Exception` If an invalid method is called.
`public string`	# `__toString( )` Implements magic method. Implements magic method. Returns `string` This object as a SELECT string.

Constants summary
`string`	`DISTINCT`	`'distinct'`	#
`string`	`COLUMNS`	`'columns'`	#
`string`	`FROM`	`'from'`	#
`string`	`UNION`	`'union'`	#
`string`	`WHERE`	`'where'`	#
`string`	`GROUP`	`'group'`	#
`string`	`HAVING`	`'having'`	#
`string`	`ORDER`	`'order'`	#
`string`	`LIMIT_COUNT`	`'limitcount'`	#
`string`	`LIMIT_OFFSET`	`'limitoffset'`	#
`string`	`FOR_UPDATE`	`'forupdate'`	#
`string`	`INNER_JOIN`	`'inner join'`	#
`string`	`LEFT_JOIN`	`'left join'`	#
`string`	`RIGHT_JOIN`	`'right join'`	#
`string`	`FULL_JOIN`	`'full join'`	#
`string`	`CROSS_JOIN`	`'cross join'`	#
`string`	`NATURAL_JOIN`	`'natural join'`	#
`string`	`SQL_WILDCARD`	`'*'`	#
`string`	`SQL_SELECT`	`'SELECT'`	#
`string`	`SQL_UNION`	`'UNION'`	#
`string`	`SQL_UNION_ALL`	`'UNION ALL'`	#
`string`	`SQL_FROM`	`'FROM'`	#
`string`	`SQL_WHERE`	`'WHERE'`	#
`string`	`SQL_DISTINCT`	`'DISTINCT'`	#
`string`	`SQL_GROUP_BY`	`'GROUP BY'`	#
`string`	`SQL_ORDER_BY`	`'ORDER BY'`	#
`string`	`SQL_HAVING`	`'HAVING'`	#
`string`	`SQL_FOR_UPDATE`	`'FOR UPDATE'`	#
`string`	`SQL_AND`	`'AND'`	#
`string`	`SQL_AS`	`'AS'`	#
`string`	`SQL_OR`	`'OR'`	#
`string`	`SQL_ON`	`'ON'`	#
`string`	`SQL_ASC`	`'ASC'`	#
`string`	`SQL_DESC`	`'DESC'`	#

Properties summary
`protected array`	`$_bind`	`array()`	# Bind variables for query Bind variables for query
`protected Zend_Db_Adapter_Abstract`	`$_adapter`		# Zend_Db_Adapter_Abstract object. Zend_Db_Adapter_Abstract object.
`protected static array`	`$_partsInit`	`array( self::DISTINCT => false, self::COLUMNS => array(), self::UNION => array(), self::FROM => array(), self::WHERE => array(), self::GROUP => array(), self::HAVING => array(), self::ORDER => array(), self::LIMIT_COUNT => null, self::LIMIT_OFFSET => null, self::FOR_UPDATE => false )`	# The initial values for the $_parts array. NOTE: It is important for the 'FOR_UPDATE' part to be last to ensure meximum compatibility with database adapters. The initial values for the $_parts array. NOTE: It is important for the 'FOR_UPDATE' part to be last to ensure meximum compatibility with database adapters.
`protected static array`	`$_joinTypes`	`array( self::INNER_JOIN, self::LEFT_JOIN, self::RIGHT_JOIN, self::FULL_JOIN, self::CROSS_JOIN, self::NATURAL_JOIN, )`	# Specify legal join types. Specify legal join types.
`protected static array`	`$_unionTypes`	`array( self::SQL_UNION, self::SQL_UNION_ALL )`	# Specify legal union types. Specify legal union types.
`protected array`	`$_parts`	`array()`	# The component parts of a SELECT statement. Initialized to the $_partsInit array in the constructor. The component parts of a SELECT statement. Initialized to the $_partsInit array in the constructor.
`protected array`	`$_tableCols`	`array()`	# Tracks which columns are being select from each table and join. Tracks which columns are being select from each table and join.

Packages

Classes

Exceptions

Class Zend_Db_Select

Direct known subclasses

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

See

Parameters

Returns

Parameters

Returns

Parameters

Returns

See

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Throws

Parameters

Returns

Returns

Parameters

Returns

Returns

Parameters

Returns

Throws

Returns

Parameters

Parameters

Returns

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns