Db/Select.php

Show: PublicProtectedPrivateinherited
Table of Contents
Zend Framework
LICENSE This source file is subject to the new BSD license that is bundled with this package in the file LICENSE.txt. It is also available through the world-wide-web at this URL: http://framework.zend.com/license/new-bsd If you did not receive a copy of the license and are unable to obtain it through the world-wide-web, please send an email to license@zend.com so we can send you a copy immediately.
Category
Zend  
Copyright
Copyright (c) 2005-2014 Zend Technologies USA Inc. (http://www.zend.com)  
License
New BSD License  
Package
Zend_Db  
Subpackage
Select  
Version
$Id$  

\Zend_Db_Select

Package: Zend_Db\Select
Class for SQL SELECT generation and results.
Children
\Zend_Db_Table_Select
Category
Zend  
Copyright
Copyright (c) 2005-2014 Zend Technologies USA Inc. (http://www.zend.com)  
License
New BSD License  

Constants

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

Properties

Methods

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

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

Parameters
NameTypeDescription
$methodstring
$argsarray

OPTIONAL Zend_Db_Table_Select query modifier

Returns
TypeDescription
\Zend_Db_Select
Throws
ExceptionDescription
\Zend_Db_Select_ExceptionIf an invalid method is called.
methodpublic__construct(\Zend_Db_Adapter_Abstract $adapter) : void

Class constructor

Parameters
NameTypeDescription
$adapter\Zend_Db_Adapter_Abstract
methodpublic__toString() : string

Implements magic method.

Returns
TypeDescription
stringThis object as a SELECT string.
methodpublic_joinUsing( $type,  $name,  $cond,  $cols = '*',  $schema = null) : \Zend_Db_Select

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
Parameters
NameTypeDescription
$type
$name
$cond
$cols
$schema
Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpublicassemble() : string | null

Converts this object to an SQL SELECT string.

Returns
TypeDescription
string | nullThis object as a SELECT string. (or null if a string cannot be produced.)
methodpublicbind(mixed $bind) : \Zend_Db_Select

Set bind variables

Parameters
NameTypeDescription
$bindmixed
Returns
TypeDescription
\Zend_Db_Select
methodpubliccolumns(array | string | \Zend_Db_Expr $cols = '*', string $correlationName = null) : \Zend_Db_Select

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
NameTypeDescription
$colsarray | string | \Zend_Db_Expr

The columns to select from this table.

$correlationNamestring

Correlation name of target table. OPTIONAL

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpublicdistinct(bool $flag = true) : \Zend_Db_Select

Makes the query SELECT DISTINCT.

Parameters
NameTypeDescription
$flagbool

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

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpublicforUpdate(bool $flag = true) : \Zend_Db_Select

Makes the query SELECT FOR UPDATE.

Parameters
NameTypeDescription
$flagbool

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

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpublicfrom(array | string | \Zend_Db_Expr $name, array | string | \Zend_Db_Expr $cols = '*', string $schema = null) : \Zend_Db_Select

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
NameTypeDescription
$namearray | string | \Zend_Db_Expr

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

$colsarray | string | \Zend_Db_Expr

The columns to select from this table.

$schemastring

The schema name to specify, if any.

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpublicgetAdapter() : \Zend_Db_Adapter_Abstract

Gets the Zend_Db_Adapter_Abstract for this particular Zend_Db_Select object.

Returns
TypeDescription
\Zend_Db_Adapter_Abstract
methodpublicgetBind() : array

Get bind variables

Returns
TypeDescription
array
methodpublicgetPart(string $part) : mixed

Get part of the structured information for the current query.

Parameters
NameTypeDescription
$partstring
Returns
TypeDescription
mixed
Throws
ExceptionDescription
\Zend_Db_Select_Exception
methodpublicgroup(array | string $spec) : \Zend_Db_Select

Adds grouping to the query.

Parameters
NameTypeDescription
$specarray | string

The column(s) to group by.

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpublichaving(string $cond, mixed $value = null, int $type = null) : \Zend_Db_Select

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 {@link where()} for an example
Parameters
NameTypeDescription
$condstring

The HAVING condition.

$valuemixed

OPTIONAL The value to quote into the condition.

$typeint

OPTIONAL The type of the given value

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpublicjoin(array | string | \Zend_Db_Expr $name, string $cond, array | string $cols = self::SQL_WILDCARD, string $schema = null) : \Zend_Db_Select

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
NameTypeDescription
$namearray | string | \Zend_Db_Expr

The table name.

$condstring

Join on this condition.

$colsarray | string

The columns to select from the joined table.

$schemastring

The database name to specify, if any.

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpublicjoinCross(array | string | \Zend_Db_Expr $name, array | string $cols = self::SQL_WILDCARD, string $schema = null) : \Zend_Db_Select

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
NameTypeDescription
$namearray | string | \Zend_Db_Expr

The table name.

$colsarray | string

The columns to select from the joined table.

$schemastring

The database name to specify, if any.

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpublicjoinFull(array | string | \Zend_Db_Expr $name, string $cond, array | string $cols = self::SQL_WILDCARD, string $schema = null) : \Zend_Db_Select

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
NameTypeDescription
$namearray | string | \Zend_Db_Expr

The table name.

$condstring

Join on this condition.

$colsarray | string

The columns to select from the joined table.

$schemastring

The database name to specify, if any.

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpublicjoinInner(array | string | \Zend_Db_Expr $name, string $cond, array | string $cols = self::SQL_WILDCARD, string $schema = null) : \Zend_Db_Select

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
NameTypeDescription
$namearray | string | \Zend_Db_Expr

The table name.

$condstring

Join on this condition.

$colsarray | string

The columns to select from the joined table.

$schemastring

The database name to specify, if any.

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpublicjoinLeft(array | string | \Zend_Db_Expr $name, string $cond, array | string $cols = self::SQL_WILDCARD, string $schema = null) : \Zend_Db_Select

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
NameTypeDescription
$namearray | string | \Zend_Db_Expr

The table name.

$condstring

Join on this condition.

$colsarray | string

The columns to select from the joined table.

$schemastring

The database name to specify, if any.

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpublicjoinNatural(array | string | \Zend_Db_Expr $name, array | string $cols = self::SQL_WILDCARD, string $schema = null) : \Zend_Db_Select

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
NameTypeDescription
$namearray | string | \Zend_Db_Expr

The table name.

$colsarray | string

The columns to select from the joined table.

$schemastring

The database name to specify, if any.

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpublicjoinRight(array | string | \Zend_Db_Expr $name, string $cond, array | string $cols = self::SQL_WILDCARD, string $schema = null) : \Zend_Db_Select

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
NameTypeDescription
$namearray | string | \Zend_Db_Expr

The table name.

$condstring

Join on this condition.

$colsarray | string

The columns to select from the joined table.

$schemastring

The database name to specify, if any.

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpubliclimit(int $count = null, int $offset = null) : \Zend_Db_Select

Sets a limit count and offset to the query.

Parameters
NameTypeDescription
$countint

OPTIONAL The number of rows to return.

$offsetint

OPTIONAL Start returning after this many rows.

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpubliclimitPage(int $page, int $rowCount) : \Zend_Db_Select

Sets the limit and count by page number.

Parameters
NameTypeDescription
$pageint

Limit results to this page number.

$rowCountint

Use this many rows per page.

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpublicorHaving(string $cond, mixed $value = null, int $type = null) : \Zend_Db_Select

Adds a HAVING condition to the query by OR.

Otherwise identical to orHaving().
Parameters
NameTypeDescription
$condstring

The HAVING condition.

$valuemixed

OPTIONAL The value to quote into the condition.

$typeint

OPTIONAL The type of the given value

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
Details
See
 
methodpublicorWhere(string $cond, mixed $value = null, int $type = null) : \Zend_Db_Select

Adds a WHERE condition to the query by OR.

Otherwise identical to where().
Parameters
NameTypeDescription
$condstring

The WHERE condition.

$valuemixed

OPTIONAL The value to quote into the condition.

$typeint

OPTIONAL The type of the given value

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
Details
See
 
methodpublicorder(mixed $spec) : \Zend_Db_Select

Adds a row order to the query.

Parameters
NameTypeDescription
$specmixed

The column(s) and direction to order by.

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpublicquery(integer $fetchMode = null, mixed $bind = array()) : \PDO_Statement | \Zend_Db_Statement

Executes the current select object and returns the result

Parameters
NameTypeDescription
$fetchModeinteger

OPTIONAL

$bindmixed

An array of data to bind to the placeholders.

Returns
TypeDescription
\PDO_Statement | \Zend_Db_Statement
methodpublicreset(string $part = null) : \Zend_Db_Select

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

Parameters
NameTypeDescription
$partstring

OPTIONAL

Returns
TypeDescription
\Zend_Db_Select
methodpublicunion(array $select = array(),  $type = self::SQL_UNION) : \Zend_Db_Select

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
NameTypeDescription
$selectarray

Array of select clauses for the union.

$type
Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
methodpublicwhere(string $cond, mixed $value = null, int $type = null) : \Zend_Db_Select

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
NameTypeDescription
$condstring

The WHERE condition.

$valuemixed

OPTIONAL The value to quote into the condition.

$typeint

OPTIONAL The type of the given value

Returns
TypeDescription
\Zend_Db_SelectThis Zend_Db_Select object.
Documentation was generated by phpDocumentor 2.2.0 .