## Zend_Db_Select

Description

Class for SQL SELECT generation and results.

- license: New BSD License
- copyright: Copyright (c) 2005-2007 Zend Technologies USA Inc. (http://www.zend.com)

Located in /Zend/Db/Select.php (line 41)

Class Constant Summary
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'
JOIN = 'join'
LEFT_JOIN = 'left join'
LIMIT_COUNT = 'limitcount'
LIMIT_OFFSET = 'limitoffset'
NATURAL_JOIN = 'natural join'
ORDER = 'order'
RIGHT_JOIN = 'right join'
WHERE = 'where'
Variable Summary
static array $_partsInit
Zend_Db_Adapter_Abstract $_adapter
array $_parts
array $_tableCols
Method Summary
Zend_Db_Select __construct (Zend_Db_Adapter_Abstract $adapter)
Zend_Db_Select distinct ([bool $flag = true])
Zend_Db_Select forUpdate ([bool $flag = true])
Zend_Db_Select from (mixed $name, [array|string|Zend_Db_Expr $cols = '*'])
mixed getPart (string $part)
void group (mixed $spec)
void having (string $cond, string $val)
Zend_Db_Select join (string $name, string $cond, [array|string $cols = '*'])
Zend_Db_Select joinCross (string $name, [array|string $cols = '*'], string $cond)
Zend_Db_Select joinFull (string $name, string $cond, [array|string $cols = '*'])
Zend_Db_Select joinInner (string $name, string $cond, [array|string $cols = '*'])
Zend_Db_Select joinLeft (string $name, string $cond, [array|string $cols = '*'])
Zend_Db_Select joinNatural (string $name, [array|string $cols = '*'])
Zend_Db_Select joinRight (string $name, string $cond, [array|string $cols = '*'])
void limit ([int $count = null], [int $offset = null])
void limitPage (int $page, int $rowCount)
void order (mixed $spec)
void orHaving (string $cond, string $val)
void orWhere (string $cond, string $val)
PDO_Statement|Zend_Db_Statement query ([integer $fetchMode = null])
void reset ([string $part = null])
void where (string $cond, string $val)
Zend_Db_Select _join (null|string $type, string $name, string $cond, array|string $cols)
void _tableCols ( $correlationName, mixed $cols, string $tbl)
string __toString ()
Variables
static array $_partsInit = array(
self::DISTINCT => false,
self::FOR_UPDATE => false,
self::COLUMNS => array(),self::FROM=>array(),self::JOIN=>array(),self::WHERE=>array(),self::GROUP=>array(),self::HAVING=>array(),self::ORDER=>array(),self::LIMIT_COUNT=>null,self::LIMIT_OFFSET=>null,) (line 75)

The initial values for the $_parts array.

- access: protected

Zend_Db_Adapter_Abstract $_adapter (line 68)

Zend_Db_Adapter_Abstract object.

- access: protected

array $_parts = array() (line 95)

The component parts of a SELECT statement.

Initialized to the $_partsInit array in the constructor.

- access: protected

array $_tableCols = array() (line 102)

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

- access: protected

Methods
Constructor __construct (line 109)

Class constructor

- access: public

Zend_Db_Select __construct (Zend_Db_Adapter_Abstract $adapter)

- Zend_Db_Adapter_Abstract $adapter

distinct (line 259)

Makes the query SELECT DISTINCT.

- return: This Zend_Db_Select object.
- access: public

Zend_Db_Select distinct ([bool $flag = true])

- bool $flag: Whether or not the SELECT is DISTINCT (default true).

forUpdate (line 271)

Makes the query SELECT FOR UPDATE.

- return: This Zend_Db_Select object.
- access: public

Zend_Db_Select forUpdate ([bool $flag = true])

- bool $flag: Whether or not the SELECT is FOR UPDATE (default true).

from (line 299)

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 physical table name, and the value is the correlation name. For example, array('table' => 'alias'). 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.

- return: This Zend_Db_Select object.
- access: public

Zend_Db_Select from (mixed $name, [array|string|Zend_Db_Expr $cols = '*'])

- mixed $name: The table name or an associative array relating table name to correlation name.
- array|string|Zend_Db_Expr $cols: The columns to select from this table.

getPart (line 734)

Get part of the structured information for the currect query.

- access: public
- throws: Zend_Db_Select_Exception

mixed getPart (string $part)

- string $part

group (line 579)

Adds grouping to the query.

- access: public

void group (mixed $spec)

- mixed $spec: The column(s) to group by.

having (line 603)

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 where() for an example

- access: public

void having (string $cond, string $val)

- string $cond: The HAVING condition.
- string $val: A single value to quote into the condition.

join (line 376)

Adds a JOIN table and columns to the query.

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

- return: This Zend_Db_Select object.
- access: public

Zend_Db_Select join (string $name, string $cond, [array|string $cols = '*'])

- string $name: The table name.
- string $cond: Join on this condition.
- array|string $cols: The columns to select from the joined table.

joinCross (line 475)

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.

- return: This Zend_Db_Select object.
- access: public

Zend_Db_Select joinCross (string $name, [array|string $cols = '*'], string $cond)

- string $name: The table name.
- string $cond: Join on this condition.
- array|string $cols: The columns to select from the joined table.

joinFull (line 458)

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.

- return: This Zend_Db_Select object.
- access: public

Zend_Db_Select joinFull (string $name, string $cond, [array|string $cols = '*'])

- string $name: The table name.
- string $cond: Join on this condition.
- array|string $cols: The columns to select from the joined table.

joinInner (line 396)

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.

- return: This Zend_Db_Select object.
- access: public

Zend_Db_Select joinInner (string $name, string $cond, [array|string $cols = '*'])

- string $name: The table name.
- string $cond: Join on this condition.
- array|string $cols: The columns to select from the joined table.

joinLeft (line 416)

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.

- return: This Zend_Db_Select object.
- access: public

Zend_Db_Select joinLeft (string $name, string $cond, [array|string $cols = '*'])

- string $name: The table name.
- string $cond: Join on this condition.
- array|string $cols: The columns to select from the joined table.

joinNatural (line 494)

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.

- return: This Zend_Db_Select object.
- access: public

Zend_Db_Select joinNatural (string $name, [array|string $cols = '*'])

- string $name: The table name.
- array|string $cols: The columns to select from the joined table.

joinRight (line 437)

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.

- return: This Zend_Db_Select object.
- access: public

Zend_Db_Select joinRight (string $name, string $cond, [array|string $cols = '*'])

- string $name: The table name.
- string $cond: Join on this condition.
- array|string $cols: The columns to select from the joined table.

limit (line 682)

Sets a limit count and offset to the query.

- access: public

void limit ([int $count = null], [int $offset = null])

- int $count: The number of rows to return.
- int $offset: Start returning after this many rows.

limitPage (line 696)

Sets the limit and count by page number.

- access: public

void limitPage (int $page, int $rowCount)

- int $page: Limit results to this page number.
- int $rowCount: Use this many rows per page.

order (line 652)

Adds a row order to the query.

- access: public

void order (mixed $spec)

- mixed $spec: The column(s) and direction to order by.

orHaving (line 630)

Adds a HAVING condition to the query by OR.

Otherwise identical to orHaving().

- access: public
- see: Zend_Db_Select::having()

void orHaving (string $cond, string $val)

- string $cond: The HAVING condition.
- string $val: A single value to quote into the condition.

orWhere (line 557)

Adds a WHERE condition to the query by OR.

Otherwise identical to where().

- access: public
- see: Zend_Db_Select::where()

void orWhere (string $cond, string $val)

- string $cond: The WHERE condition.
- string $val: A value to quote into the condition.

query (line 747)

- access: public

PDO_Statement|Zend_Db_Statement query ([integer $fetchMode = null])

- integer $fetchMode: OPTIONAL

reset (line 763)

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

- access: public

void reset ([string $part = null])

- string $part: OPTIONAL

where (line 530)

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.

1. // simplest but non-secure
2. $select->where("id = $id");
4. // secure (ID is quoted but matched anyway)
5. $select->where('id = ?', $id);
7. // alternatively, with named binding
8. $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:

1. $db->fetchAll($select, array('id' => 5));

- access: public

void where (string $cond, string $val)

- string $cond: The WHERE condition.
- string $val: A single value to quote into the condition.

_join (line 321)

Populate the $_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.

- return: This Zend_Db_Select object
- access: protected
- throws: Zend_Db_Select_Exception

Zend_Db_Select _join (null|string $type, string $name, string $cond, array|string $cols)

- null|string $type: Type of join; inner, left, and null are currently supported
- string $name: Table name
- string $cond: Join on this condition
- array|string $cols: The columns to select from the joined table

_tableCols (line 713)

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

- access: protected

void _tableCols ( $correlationName, mixed $cols, string $tbl)

- string $tbl: The table/join the columns come from.
- mixed $cols: The list of columns; preferably as an array, but possibly as a string containing one column.
- $correlationName

__toString (line 124)

Converts this object to an SQL SELECT string.

- return: This object as a SELECT string.
- access: public
- todo: use $this->_adapter->quoteColumns() for non-PDO adapters
- todo: use $this->_adapter->quoteTableNames() for non-PDO adapters
- todo: use prepared queries for PDO adapters instead of constructing all the SQL ourselves like in Adapter/Abstract.php.html:query()

string __toString ()
Class Constants
COLUMNS = 'columns' (line 46)
CROSS_JOIN = 'cross join' (line 60)
DISTINCT = 'distinct' (line 44)
FOR_UPDATE = 'forupdate' (line 45)
FROM = 'from' (line 47)
FULL_JOIN = 'full join' (line 59)
GROUP = 'group' (line 50)
HAVING = 'having' (line 51)
INNER_JOIN = 'inner join' (line 56)
JOIN = 'join' (line 48)
LEFT_JOIN = 'left join' (line 57)
LIMIT_COUNT = 'limitcount' (line 53)
LIMIT_OFFSET = 'limitoffset' (line 54)
NATURAL_JOIN = 'natural join' (line 61)
ORDER = 'order' (line 52)
RIGHT_JOIN = 'right join' (line 58)
WHERE = 'where' (line 49)

Documentation generated on Wed, 21 Feb 2007 11:59:22 -0800 by phpDocumentor 1.3.1