Class for SQL SELECT generation and results.

category Zend
package Zend_Db
subpackage Select
copyright Copyright (c) 2005-2015 Zend Technologies USA Inc. (http://www.zend.com)
license New BSD License

 Methods

Turn magic function calls into non-magic function calls for joinUsing syntax

__call(string $method, array $args) : \Zend_Db_Select

Parameters

$method

string

$args

array

OPTIONAL Zend_Db_Table_Select query modifier

Exceptions

\Zend_Db_Select_Exception If an invalid method is called.

Returns

\Zend_Db_Select

Class constructor

__construct(\Zend_Db_Adapter_Abstract $adapter) 

Parameters

$adapter

\Zend_Db_Adapter_Abstract

Implements magic method.

__toString() : string

Returns

stringThis object as a SELECT string.

Handle JOIN.

_joinUsing($type, $name, $cond, $cols = '*', $schema = null) : \Zend_Db_Select

.. 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

Parameters

$type

$name

$cond

$cols

$schema

Returns

\Zend_Db_SelectThis Zend_Db_Select object.

Converts this object to an SQL SELECT string.

assemble() : string | null

Returns

stringnullThis object as a SELECT string. (or null if a string cannot be produced.)

Set bind variables

bind(mixed $bind) : \Zend_Db_Select

Parameters

$bind

mixed

Returns

\Zend_Db_Select

Specifies the columns used in the FROM clause.

columns(array|string|\Zend_Db_Expr $cols = '*', string $correlationName = null) : \Zend_Db_Select

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

arraystring\Zend_Db_Expr

The columns to select from this table.

$correlationName

string

Correlation name of target table. OPTIONAL

Returns

\Zend_Db_SelectThis Zend_Db_Select object.

Makes the query SELECT DISTINCT.

distinct(boolean $flag = true) : \Zend_Db_Select

Parameters

$flag

boolean

Whether or not the SELECT is DISTINCT (default true).

Returns

\Zend_Db_SelectThis Zend_Db_Select object.

Makes the query SELECT FOR UPDATE.

forUpdate(boolean $flag = true) : \Zend_Db_Select

Parameters

$flag

boolean

Whether or not the SELECT is FOR UPDATE (default true).

Returns

\Zend_Db_SelectThis Zend_Db_Select object.

Adds a FROM table and optional columns to the query.

from(array|string|\Zend_Db_Expr $name, array|string|\Zend_Db_Expr $cols = '*', string $schema = null) : \Zend_Db_Select

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

arraystring\Zend_Db_Expr

The table name or an associative array relating correlation name to table name.

$cols

arraystring\Zend_Db_Expr

The columns to select from this table.

$schema

string

The schema name to specify, if any.

Returns

\Zend_Db_SelectThis Zend_Db_Select object.

Gets the Zend_Db_Adapter_Abstract for this particular Zend_Db_Select object.

getAdapter() : \Zend_Db_Adapter_Abstract

Returns

\Zend_Db_Adapter_Abstract

Get bind variables

getBind() : array

Returns

array

Get part of the structured information for the current query.

getPart(string $part) : mixed

Parameters

$part

string

Exceptions

\Zend_Db_Select_Exception

Returns

mixed

Adds grouping to the query.

group(array|string $spec) : \Zend_Db_Select

Parameters

$spec

arraystring

The column(s) to group by.

Returns

\Zend_Db_SelectThis Zend_Db_Select object.

Adds a HAVING condition to the query by AND.

having(string $cond, mixed $value = null, integer $type = null) : \Zend_Db_Select

If a value is passed as the second param, it will be quoted and replaced into the condition wherever a question-mark appears. See \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_SelectThis Zend_Db_Select object.

Adds a JOIN table and columns to the query.

join(array|string|\Zend_Db_Expr $name, string $cond, array|string $cols = self::SQL_WILDCARD, string $schema = null) : \Zend_Db_Select

The $name and $cols parameters follow the same logic as described in the from() method.

Parameters

$name

arraystring\Zend_Db_Expr

The table name.

$cond

string

Join on this condition.

$cols

arraystring

The columns to select from the joined table.

$schema

string

The database name to specify, if any.

Returns

\Zend_Db_SelectThis Zend_Db_Select object.

Add a CROSS JOIN table and colums to the query.

joinCross(array|string|\Zend_Db_Expr $name, array|string $cols = self::SQL_WILDCARD, string $schema = null) : \Zend_Db_Select

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

arraystring\Zend_Db_Expr

The table name.

$cols

arraystring

The columns to select from the joined table.

$schema

string

The database name to specify, if any.

Returns

\Zend_Db_SelectThis Zend_Db_Select object.

Add a FULL OUTER JOIN table and colums to the query.

joinFull(array|string|\Zend_Db_Expr $name, string $cond, array|string $cols = self::SQL_WILDCARD, string $schema = null) : \Zend_Db_Select

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

arraystring\Zend_Db_Expr

The table name.

$cond

string

Join on this condition.

$cols

arraystring

The columns to select from the joined table.

$schema

string

The database name to specify, if any.

Returns

\Zend_Db_SelectThis Zend_Db_Select object.

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.

joinInner(array|string|\Zend_Db_Expr $name, string $cond, array|string $cols = self::SQL_WILDCARD, string $schema = null) : \Zend_Db_Select

The $name and $cols parameters follow the same logic as described in the from() method.

Parameters

$name

arraystring\Zend_Db_Expr

The table name.

$cond

string

Join on this condition.

$cols

arraystring

The columns to select from the joined table.

$schema

string

The database name to specify, if any.

Returns

\Zend_Db_SelectThis Zend_Db_Select object.

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.

joinLeft(array|string|\Zend_Db_Expr $name, string $cond, array|string $cols = self::SQL_WILDCARD, string $schema = null) : \Zend_Db_Select

The $name and $cols parameters follow the same logic as described in the from() method.

Parameters

$name

arraystring\Zend_Db_Expr

The table name.

$cond

string

Join on this condition.

$cols

arraystring

The columns to select from the joined table.

$schema

string

The database name to specify, if any.

Returns

\Zend_Db_SelectThis Zend_Db_Select object.

Add a NATURAL JOIN table and colums to the query.

joinNatural(array|string|\Zend_Db_Expr $name, array|string $cols = self::SQL_WILDCARD, string $schema = null) : \Zend_Db_Select

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

arraystring\Zend_Db_Expr

The table name.

$cols

arraystring

The columns to select from the joined table.

$schema

string

The database name to specify, if any.

Returns

\Zend_Db_SelectThis Zend_Db_Select object.

Add a RIGHT OUTER JOIN table and colums to the query.

joinRight(array|string|\Zend_Db_Expr $name, string $cond, array|string $cols = self::SQL_WILDCARD, string $schema = null) : \Zend_Db_Select

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

arraystring\Zend_Db_Expr

The table name.

$cond

string

Join on this condition.

$cols

arraystring

The columns to select from the joined table.

$schema

string

The database name to specify, if any.

Returns

\Zend_Db_SelectThis Zend_Db_Select object.

Sets a limit count and offset to the query.

limit(integer $count = null, integer $offset = null) : \Zend_Db_Select

Parameters

$count

integer

OPTIONAL The number of rows to return.

$offset

integer

OPTIONAL Start returning after this many rows.

Returns

\Zend_Db_SelectThis Zend_Db_Select object.

Sets the limit and count by page number.

limitPage(integer $page, integer $rowCount) : \Zend_Db_Select

Parameters

$page

integer

Limit results to this page number.

$rowCount

integer

Use this many rows per page.

Returns

\Zend_Db_SelectThis Zend_Db_Select object.

Adds a HAVING condition to the query by OR.

orHaving(string $cond, mixed $value = null, integer $type = null) : \Zend_Db_Select

Otherwise identical to orHaving().

see

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_SelectThis Zend_Db_Select object.

Adds a WHERE condition to the query by OR.

orWhere(string $cond, mixed $value = null, integer $type = null) : \Zend_Db_Select

Otherwise identical to where().

see

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_SelectThis Zend_Db_Select object.

Adds a row order to the query.

order(mixed $spec) : \Zend_Db_Select

Parameters

$spec

mixed

The column(s) and direction to order by.

Returns

\Zend_Db_SelectThis Zend_Db_Select object.

Executes the current select object and returns the result

query(integer $fetchMode = null, mixed $bind = array()) : \PDO_Statement | \Zend_Db_Statement

Parameters

$fetchMode

integer

OPTIONAL

$bind

mixed

An array of data to bind to the placeholders.

Returns

\PDO_Statement\Zend_Db_Statement

Clear parts of the Select object, or an individual part.

reset(string $part = null) : \Zend_Db_Select

Parameters

$part

string

OPTIONAL

Returns

\Zend_Db_Select

Adds a UNION clause to the query.

union(array $select = array(), $type = self::SQL_UNION) : \Zend_Db_Select

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_SelectThis Zend_Db_Select object.

Adds a WHERE condition to the query by AND.

where(string $cond, mixed $value = null, integer $type = null) : \Zend_Db_Select

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_SelectThis Zend_Db_Select object.

_getDummyTable()

_getDummyTable() : array

Returns

array

Return a quoted schema name

_getQuotedSchema(string $schema = null) : string | null

Parameters

$schema

string

The schema name OPTIONAL

Returns

stringnull

Return a quoted table name

_getQuotedTable(string $tableName, string $correlationName = null) : string

Parameters

$tableName

string

The table name

$correlationName

string

The correlation name OPTIONAL

Returns

string

Populate the {@link $_parts} 'join' key

_join(null|string $type, array|string|\Zend_Db_Expr $name, string $cond, array|string $cols, string $schema = null) : \Zend_Db_Select

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

nullstring

Type of join; inner, left, and null are currently supported

$name

arraystring\Zend_Db_Expr

Table name

$cond

string

Join on this condition

$cols

arraystring

The columns to select from the joined table

$schema

string

The database name to specify, if any.

Exceptions

\Zend_Db_Select_Exception

Returns

\Zend_Db_SelectThis Zend_Db_Select object

Render DISTINCT clause

_renderColumns(string $sql) : string | null

Parameters

$sql

string

SQL query

Returns

stringnull

Render DISTINCT clause

_renderDistinct(string $sql) : string

Parameters

$sql

string

SQL query

Returns

string

Render FOR UPDATE clause

_renderForupdate(string $sql) : string

Parameters

$sql

string

SQL query

Returns

string

Render FROM clause

_renderFrom(string $sql) : string

Parameters

$sql

string

SQL query

Returns

string

Render GROUP clause

_renderGroup(string $sql) : string

Parameters

$sql

string

SQL query

Returns

string

Render HAVING clause

_renderHaving(string $sql) : string

Parameters

$sql

string

SQL query

Returns

string

Render LIMIT OFFSET clause

_renderLimitoffset(string $sql) : string

Parameters

$sql

string

SQL query

Returns

string

Render ORDER clause

_renderOrder(string $sql) : string

Parameters

$sql

string

SQL query

Returns

string

Render UNION query

_renderUnion(string $sql) : string

Parameters

$sql

string

SQL query

Returns

string

Render WHERE clause

_renderWhere(string $sql) : string

Parameters

$sql

string

SQL query

Returns

string

Adds to the internal table-to-column mapping array.

_tableCols($correlationName, array|string $cols, $afterCorrelationName = null) : void

Parameters

$correlationName

$cols

arraystring

The list of columns; preferably as an array, but possibly as a string containing one column.

$afterCorrelationName

Internal function for creating the where clause

_where(string $condition, mixed $value = null, string $type = null, boolean $bool = true) : string

Parameters

$condition

string

$value

mixed

optional

$type

string

optional

$bool

boolean

true = AND, false = OR

Returns

stringclause

Generate a unique correlation name

_uniqueCorrelation(string|array $name) : string

Parameters

$name

stringarray

A qualified identifier.

Returns

stringA unique correlation name.

 Properties

 

Zend_Db_Adapter_Abstract object.

$_adapter : \Zend_Db_Adapter_Abstract

Default

 

Bind variables for query

$_bind : array

Default

array()
 

Specify legal join types.

$_joinTypes : array

Default

array(self::INNER_JOIN, self::LEFT_JOIN, self::RIGHT_JOIN, self::FULL_JOIN, self::CROSS_JOIN, self::NATURAL_JOIN)
Static
 

The component parts of a SELECT statement.

$_parts : array

Default

array()

Initialized to the $_partsInit array in the constructor.

 

The initial values for the $_parts array.

$_partsInit : array

Default

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)
Static

NOTE: It is important for the 'FOR_UPDATE' part to be last to ensure meximum compatibility with database adapters.

 

Tracks which columns are being select from each table and join.

$_tableCols : array

Default

array()
 

Specify legal union types.

$_unionTypes : array

Default

array(self::SQL_UNION, self::SQL_UNION_ALL)
Static

 Constants

 

COLUMNS

COLUMNS = 'columns' 
 

CROSS_JOIN

CROSS_JOIN = 'cross join' 
 

DISTINCT

DISTINCT = 'distinct' 
 

FOR_UPDATE

FOR_UPDATE = 'forupdate' 
 

FROM

FROM = 'from' 
 

FULL_JOIN

FULL_JOIN = 'full join' 
 

GROUP

GROUP = 'group' 
 

HAVING

HAVING = 'having' 
 

INNER_JOIN

INNER_JOIN = 'inner join' 
 

LEFT_JOIN

LEFT_JOIN = 'left join' 
 

LIMIT_COUNT

LIMIT_COUNT = 'limitcount' 
 

LIMIT_OFFSET

LIMIT_OFFSET = 'limitoffset' 
 

NATURAL_JOIN

NATURAL_JOIN = 'natural join' 
 

ORDER

ORDER = 'order' 
 

REGEX_COLUMN_EXPR

REGEX_COLUMN_EXPR = '/^([\w]*\s*\(([^\(\)]|(?1))*\))$/' 
 

RIGHT_JOIN

RIGHT_JOIN = 'right join' 
 

SQL_AND

SQL_AND = 'AND' 
 

SQL_AS

SQL_AS = 'AS' 
 

SQL_ASC

SQL_ASC = 'ASC' 
 

SQL_DESC

SQL_DESC = 'DESC' 
 

SQL_DISTINCT

SQL_DISTINCT = 'DISTINCT' 
 

SQL_FOR_UPDATE

SQL_FOR_UPDATE = 'FOR UPDATE' 
 

SQL_FROM

SQL_FROM = 'FROM' 
 

SQL_GROUP_BY

SQL_GROUP_BY = 'GROUP BY' 
 

SQL_HAVING

SQL_HAVING = 'HAVING' 
 

SQL_ON

SQL_ON = 'ON' 
 

SQL_OR

SQL_OR = 'OR' 
 

SQL_ORDER_BY

SQL_ORDER_BY = 'ORDER BY' 
 

SQL_SELECT

SQL_SELECT = 'SELECT' 
 

SQL_UNION

SQL_UNION = 'UNION' 
 

SQL_UNION_ALL

SQL_UNION_ALL = 'UNION ALL' 
 

SQL_WHERE

SQL_WHERE = 'WHERE' 
 

SQL_WILDCARD

SQL_WILDCARD = '*' 
 

UNION

UNION = 'union' 
 

WHERE

WHERE = 'where'