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 |
__call(string $method, array $args) : \Zend_Db_Select
string
array
OPTIONAL Zend_Db_Table_Select query modifier
\Zend_Db_Select_Exception |
If an invalid method is called. |
---|
\Zend_Db_Select
__construct(\Zend_Db_Adapter_Abstract $adapter)
\Zend_Db_Adapter_Abstract
__toString() : string
string
This object as a SELECT string._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.
\Zend_Db_Select
This Zend_Db_Select object.assemble() : string | null
string
null
This object as a SELECT string. (or null if a string cannot be produced.)bind(mixed $bind) : \Zend_Db_Select
mixed
\Zend_Db_Select
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.
array
string
\Zend_Db_Expr
The columns to select from this table.
string
Correlation name of target table. OPTIONAL
\Zend_Db_Select
This Zend_Db_Select object.distinct(boolean $flag = true
) : \Zend_Db_Select
boolean
Whether or not the SELECT is DISTINCT (default true).
\Zend_Db_Select
This Zend_Db_Select object.forUpdate(boolean $flag = true
) : \Zend_Db_Select
boolean
Whether or not the SELECT is FOR UPDATE (default true).
\Zend_Db_Select
This Zend_Db_Select object.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.
array
string
\Zend_Db_Expr
The table name or an associative array relating correlation name to table name.
array
string
\Zend_Db_Expr
The columns to select from this table.
string
The schema name to specify, if any.
\Zend_Db_Select
This Zend_Db_Select object.getAdapter() : \Zend_Db_Adapter_Abstract
\Zend_Db_Adapter_Abstract
getBind() : array
array
getPart(string $part) : mixed
string
\Zend_Db_Select_Exception |
---|
mixed
group(array|string $spec) : \Zend_Db_Select
array
string
The column(s) to group by.
\Zend_Db_Select
This Zend_Db_Select object.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
string
The HAVING condition.
mixed
OPTIONAL The value to quote into the condition.
integer
OPTIONAL The type of the given value
\Zend_Db_Select
This Zend_Db_Select object.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.
array
string
\Zend_Db_Expr
The table name.
string
Join on this condition.
array
string
The columns to select from the joined table.
string
The database name to specify, if any.
\Zend_Db_Select
This Zend_Db_Select object.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.
array
string
\Zend_Db_Expr
The table name.
array
string
The columns to select from the joined table.
string
The database name to specify, if any.
\Zend_Db_Select
This Zend_Db_Select object.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.
array
string
\Zend_Db_Expr
The table name.
string
Join on this condition.
array
string
The columns to select from the joined table.
string
The database name to specify, if any.
\Zend_Db_Select
This Zend_Db_Select object.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.
array
string
\Zend_Db_Expr
The table name.
string
Join on this condition.
array
string
The columns to select from the joined table.
string
The database name to specify, if any.
\Zend_Db_Select
This Zend_Db_Select object.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.
array
string
\Zend_Db_Expr
The table name.
string
Join on this condition.
array
string
The columns to select from the joined table.
string
The database name to specify, if any.
\Zend_Db_Select
This Zend_Db_Select object.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.
array
string
\Zend_Db_Expr
The table name.
array
string
The columns to select from the joined table.
string
The database name to specify, if any.
\Zend_Db_Select
This Zend_Db_Select object.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.
array
string
\Zend_Db_Expr
The table name.
string
Join on this condition.
array
string
The columns to select from the joined table.
string
The database name to specify, if any.
\Zend_Db_Select
This Zend_Db_Select object.limit(integer $count= null
, integer $offset= null
) : \Zend_Db_Select
integer
OPTIONAL The number of rows to return.
integer
OPTIONAL Start returning after this many rows.
\Zend_Db_Select
This Zend_Db_Select object.limitPage(integer $page, integer $rowCount) : \Zend_Db_Select
integer
Limit results to this page number.
integer
Use this many rows per page.
\Zend_Db_Select
This Zend_Db_Select object.orHaving(string $cond, mixed $value= null
, integer $type= null
) : \Zend_Db_Select
orWhere(string $cond, mixed $value= null
, integer $type= null
) : \Zend_Db_Select
order(mixed $spec) : \Zend_Db_Select
mixed
The column(s) and direction to order by.
\Zend_Db_Select
This Zend_Db_Select object.query(integer $fetchMode= null
, mixed $bind= array()
) : \PDO_Statement | \Zend_Db_Statement
integer
OPTIONAL
mixed
An array of data to bind to the placeholders.
\PDO_Statement
\Zend_Db_Statement
reset(string $part = null
) : \Zend_Db_Select
string
OPTIONAL
\Zend_Db_Select
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");
array
Array of select clauses for the union.
\Zend_Db_Select
This Zend_Db_Select object.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));
string
The WHERE condition.
mixed
OPTIONAL The value to quote into the condition.
integer
OPTIONAL The type of the given value
\Zend_Db_Select
This Zend_Db_Select object._getDummyTable() : array
array
_getQuotedSchema(string $schema = null
) : string | null
string
The schema name OPTIONAL
string
null
_getQuotedTable(string $tableName, string $correlationName = null
) : string
string
The table name
string
The correlation name OPTIONAL
string
_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.
null
string
Type of join; inner, left, and null are currently supported
array
string
\Zend_Db_Expr
Table name
string
Join on this condition
array
string
The columns to select from the joined table
string
The database name to specify, if any.
\Zend_Db_Select_Exception |
---|
\Zend_Db_Select
This Zend_Db_Select object_renderColumns(string $sql) : string | null
string
SQL query
string
null
_renderDistinct(string $sql) : string
string
SQL query
string
_renderForupdate(string $sql) : string
string
SQL query
string
_renderFrom(string $sql) : string
string
SQL query
string
_renderGroup(string $sql) : string
string
SQL query
string
_renderHaving(string $sql) : string
string
SQL query
string
_renderLimitoffset(string $sql) : string
string
SQL query
string
_renderOrder(string $sql) : string
string
SQL query
string
_renderUnion(string $sql) : string
string
SQL query
string
_renderWhere(string $sql) : string
string
SQL query
string
_tableCols($correlationName, array|string $cols, $afterCorrelationName = null
) : void
array
string
The list of columns; preferably as an array, but possibly as a string containing one column.
_where(string $condition, mixed $value= null
, string $type= null
, boolean $bool= true
) : string
string
mixed
optional
string
optional
boolean
true = AND, false = OR
string
clause_uniqueCorrelation(string|array $name) : string
string
array
A qualified identifier.
string
A unique correlation name.$_adapter : \Zend_Db_Adapter_Abstract
$_bind : array
array()
$_joinTypes : array
array(self::INNER_JOIN, self::LEFT_JOIN, self::RIGHT_JOIN, self::FULL_JOIN, self::CROSS_JOIN, self::NATURAL_JOIN)
$_parts : array
array()
Initialized to the $_partsInit array in the constructor.
$_partsInit : array
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)
NOTE: It is important for the 'FOR_UPDATE' part to be last to ensure meximum compatibility with database adapters.
$_tableCols : array
array()
$_unionTypes : array
array(self::SQL_UNION, self::SQL_UNION_ALL)
COLUMNS = 'columns'
CROSS_JOIN = 'cross join'
DISTINCT = 'distinct'
FOR_UPDATE = 'forupdate'
FROM = 'from'
FULL_JOIN = 'full join'
GROUP = 'group'
HAVING = 'having'
INNER_JOIN = 'inner join'
LEFT_JOIN = 'left join'
LIMIT_COUNT = 'limitcount'
LIMIT_OFFSET = 'limitoffset'
NATURAL_JOIN = 'natural join'
ORDER = 'order'
REGEX_COLUMN_EXPR = '/^([\w]*\(([^\(\)]|(?1))*\))$/'
RIGHT_JOIN = 'right join'
SQL_AND = 'AND'
SQL_AS = 'AS'
SQL_ASC = 'ASC'
SQL_DESC = 'DESC'
SQL_DISTINCT = 'DISTINCT'
SQL_FOR_UPDATE = 'FOR UPDATE'
SQL_FROM = 'FROM'
SQL_GROUP_BY = 'GROUP BY'
SQL_HAVING = 'HAVING'
SQL_ON = 'ON'
SQL_OR = 'OR'
SQL_ORDER_BY = 'ORDER BY'
SQL_SELECT = 'SELECT'
SQL_UNION = 'UNION'
SQL_UNION_ALL = 'UNION ALL'
SQL_WHERE = 'WHERE'
SQL_WILDCARD = '*'
UNION = 'union'
WHERE = 'where'