API Documentation

Db/Table/Select.php

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-2011 Zend Technologies USA Inc. (http://www.zend.com)  
license
New BSD License  
package
Zend_Db  
subpackage
Select  
version
$Id: Select.php 23775 2011-03-01 17:25:24Z ralph $  

\Zend_Db_Table_Select

Class for SQL SELECT query manipulation for the Zend_Db_Table component.

Extends from
\Zend_Db_Select
category
Zend  
copyright
Copyright (c) 2005-2011 Zend Technologies USA Inc. (http://www.zend.com)  
license
New BSD License  
package
Zend_Db  
subpackage
Table  

Properties

Propertyprotectedarray  $_info= ''

Table schema for parent Zend_Db_Table.

Details
Type
array
Propertyprotectedarray  $_integrityCheck= 'true'

Table integrity override.

Default valuetrueDetails
Type
array
Propertyprotected\Zend_Db_Table_Abstract  $_table= ''

Table instance that created this select object

Methods

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

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

Inherited from: \Zend_Db_Select::__call()
Parameters
Name Type Description
$method string
$args array OPTIONAL Zend_Db_Table_Select query modifier
Returns
Type Description
\Zend_Db_Select
Throws
Exception Description
\Zend_Db_Select_Exception If an invalid method is called.
methodpublic__construct(  $table ) : void

Class constructor

Parameters
Name Type Description
$table
methodpublic__toString( ) : string

Implements magic method.

Inherited from: \Zend_Db_Select::__toString()
Returns
Type Description
string This object as a SELECT string.
methodprotected_getDummyTable( ) : array
Returns
Type Description
array
methodprotected_getQuotedSchema( string $schema = null ) : string|null

Return a quoted schema name

Inherited from: \Zend_Db_Select::_getQuotedSchema()
Parameters
Name Type Description
$schema string The schema name OPTIONAL
Returns
Type Description
stringnull
methodprotected_getQuotedTable( string $tableName, string $correlationName = null ) : string

Return a quoted table name

Inherited from: \Zend_Db_Select::_getQuotedTable()
Parameters
Name Type Description
$tableName string The table name
$correlationName string The correlation name OPTIONAL
Returns
Type Description
string
methodprotected_join( null|string $type, array|string|\Zend_Db_Expr $name, string $cond, array|string $cols, string $schema = null ) : \Zend_Db_Select

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

Inherited from: \Zend_Db_Select::_join()

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
Name Type Description
$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
Type Description
\Zend_Db_Select This Zend_Db_Select object
Throws
Exception Description
\Zend_Db_Select_Exception
methodpublic_joinUsing(  $type,  $name,  $cond,  $cols = *,  $schema = null ) : \Zend_Db_Select

Handle JOIN... USING... syntax

Inherited from: \Zend_Db_Select::_joinUsing()

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
Name Type Description
$type
$name
$cond
$cols
$schema
Returns
Type Description
\Zend_Db_Select This Zend_Db_Select object.
methodprotected_renderColumns( string $sql ) : string|null

Render DISTINCT clause

Inherited from: \Zend_Db_Select::_renderColumns()
Parameters
Name Type Description
$sql string SQL query
Returns
Type Description
stringnull
methodprotected_renderDistinct( string $sql ) : string

Render DISTINCT clause

Inherited from: \Zend_Db_Select::_renderDistinct()
Parameters
Name Type Description
$sql string SQL query
Returns
Type Description
string
methodprotected_renderForupdate( string $sql ) : string

Render FOR UPDATE clause

Inherited from: \Zend_Db_Select::_renderForupdate()
Parameters
Name Type Description
$sql string SQL query
Returns
Type Description
string
methodprotected_renderFrom( string $sql ) : string

Render FROM clause

Inherited from: \Zend_Db_Select::_renderFrom()
Parameters
Name Type Description
$sql string SQL query
Returns
Type Description
string
methodprotected_renderGroup( string $sql ) : string

Render GROUP clause

Inherited from: \Zend_Db_Select::_renderGroup()
Parameters
Name Type Description
$sql string SQL query
Returns
Type Description
string
methodprotected_renderHaving( string $sql ) : string

Render HAVING clause

Inherited from: \Zend_Db_Select::_renderHaving()
Parameters
Name Type Description
$sql string SQL query
Returns
Type Description
string
methodprotected_renderLimitoffset( string $sql ) : string

Render LIMIT OFFSET clause

Inherited from: \Zend_Db_Select::_renderLimitoffset()
Parameters
Name Type Description
$sql string SQL query
Returns
Type Description
string
methodprotected_renderOrder( string $sql ) : string

Render ORDER clause

Inherited from: \Zend_Db_Select::_renderOrder()
Parameters
Name Type Description
$sql string SQL query
Returns
Type Description
string
methodprotected_renderUnion( string $sql ) : string

Render UNION query

Inherited from: \Zend_Db_Select::_renderUnion()
Parameters
Name Type Description
$sql string SQL query
Returns
Type Description
string
methodprotected_renderWhere( string $sql ) : string

Render WHERE clause

Inherited from: \Zend_Db_Select::_renderWhere()
Parameters
Name Type Description
$sql string SQL query
Returns
Type Description
string
methodprotected_tableCols(  $correlationName, array|string $cols, bool|string $afterCorrelationName = null ) : void

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

Inherited from: \Zend_Db_Select::_tableCols()
Parameters
Name Type Description
$correlationName
$cols array|string The list of columns; preferably as an array, but possibly as a string containing one column.
$afterCorrelationName bool|string True if it should be prepended, a correlation name if it should be inserted
methodprivate_uniqueCorrelation( string|array $name ) : string

Generate a unique correlation name

Inherited from: \Zend_Db_Select::_uniqueCorrelation()
Parameters
Name Type Description
$name string|array A qualified identifier.
Returns
Type Description
string A unique correlation name.
methodprotected_where( string $condition, mixed $value = null, string $type = null, boolean $bool = true ) : string

Internal function for creating the where clause

Inherited from: \Zend_Db_Select::_where()
Parameters
Name Type Description
$condition string
$value mixed optional
$type string optional
$bool boolean

true = AND, false = OR

Returns
Type Description
string clause
methodpublicassemble( ) : string|null

Performs a validation on the select query before passing back to the parent class.

Ensures that only columns from the primary Zend_Db_Table are returned in the result.

Returns
Type Description
stringnull This object as a SELECT string (or null if a string cannot be produced)
methodpublicbind( mixed $bind ) : \Zend_Db_Select

Set bind variables

Inherited from: \Zend_Db_Select::bind()
Parameters
Name Type Description
$bind mixed
Returns
Type Description
\Zend_Db_Select
methodpubliccolumns( array|string|\Zend_Db_Expr $cols = *, string $correlationName = null ) : \Zend_Db_Select

Specifies the columns used in the FROM clause.

Inherited from: \Zend_Db_Select::columns()

The parameter can be a single string or Zend_Db_Expr object, or else an array of strings or Zend_Db_Expr objects.

Parameters
Name Type Description
$cols array|string|\Zend_Db_Expr The columns to select from this table.
$correlationName string Correlation name of target table. OPTIONAL
Returns
Type Description
\Zend_Db_Select This Zend_Db_Select object.
methodpublicdistinct( bool $flag = true ) : \Zend_Db_Select

Makes the query SELECT DISTINCT.

Inherited from: \Zend_Db_Select::distinct()
Parameters
Name Type Description
$flag bool

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

Returns
Type Description
\Zend_Db_Select This Zend_Db_Select object.
methodpublicforUpdate( bool $flag = true ) : \Zend_Db_Select

Makes the query SELECT FOR UPDATE.

Inherited from: \Zend_Db_Select::forUpdate()
Parameters
Name Type Description
$flag bool

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

Returns
Type Description
\Zend_Db_Select This Zend_Db_Select object.
methodpublicfrom( array|string|\Zend_Db_Expr|\Zend_Db_Table_Abstract $name, array|string|\Zend_Db_Expr $cols = self, string $schema = null ) : \Zend_Db_Table_Select

Adds a FROM table and optional columns to the query.

The table name can be expressed

Parameters
Name Type Description
$name array|string|\Zend_Db_Expr|\Zend_Db_Table_Abstract The table name or an associative array relating table name to correlation name.
$cols array|string|\Zend_Db_Expr The columns to select from this table.
$schema string The schema name to specify, if any.
Returns
Type Description
\Zend_Db_Table_Select This Zend_Db_Table_Select object.
methodpublicgetAdapter( ) : \Zend_Db_Adapter_Abstract

Gets the Zend_Db_Adapter_Abstract for this particular Zend_Db_Select object.

Inherited from: \Zend_Db_Select::getAdapter()
Returns
Type Description
\Zend_Db_Adapter_Abstract
methodpublicgetBind( ) : array

Get bind variables

Inherited from: \Zend_Db_Select::getBind()
Returns
Type Description
array
methodpublicgetPart( string $part ) : mixed

Get part of the structured information for the currect query.

Inherited from: \Zend_Db_Select::getPart()
Parameters
Name Type Description
$part string
Returns
Type Description
mixed
Throws
Exception Description
\Zend_Db_Select_Exception
methodpublicgetTable( ) : \Zend_Db_Table_Abstract

Return the table that created this select object

Returns
Type Description
\Zend_Db_Table_Abstract
methodpublicgroup( array|string $spec ) : \Zend_Db_Select

Adds grouping to the query.

Inherited from: \Zend_Db_Select::group()
Parameters
Name Type Description
$spec array|string

The column(s) to group by.

Returns
Type Description
\Zend_Db_Select This 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.

Inherited from: \Zend_Db_Select::having()

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
Name Type Description
$cond string The HAVING condition.
$value mixed OPTIONAL The value to quote into the condition.
$type int OPTIONAL The type of the given value
Returns
Type Description
\Zend_Db_Select This Zend_Db_Select object.
methodpublicisReadOnly( ) : boolean

Tests query to determine if expressions or aliases columns exist.

Returns
Type Description
boolean
methodpublicjoin( array|string|\Zend_Db_Expr $name, string $cond, array|string $cols = self, string $schema = null ) : \Zend_Db_Select

Adds a JOIN table and columns to the query.

Inherited from: \Zend_Db_Select::join()

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

Parameters
Name Type Description
$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
Type Description
\Zend_Db_Select This Zend_Db_Select object.
methodpublicjoinCross( array|string|\Zend_Db_Expr $name, array|string $cols = self, string $schema = null ) : \Zend_Db_Select

Add a CROSS JOIN table and colums to the query.

Inherited from: \Zend_Db_Select::joinCross()

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 Type Description
$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
Type Description
\Zend_Db_Select This Zend_Db_Select object.
methodpublicjoinFull( array|string|\Zend_Db_Expr $name, string $cond, array|string $cols = self, string $schema = null ) : \Zend_Db_Select

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

Inherited from: \Zend_Db_Select::joinFull()

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 Type Description
$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
Type Description
\Zend_Db_Select This Zend_Db_Select object.
methodpublicjoinInner( array|string|\Zend_Db_Expr $name, string $cond, array|string $cols = self, 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.

Inherited from: \Zend_Db_Select::joinInner()

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

Parameters
Name Type Description
$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
Type Description
\Zend_Db_Select This Zend_Db_Select object.
methodpublicjoinLeft( array|string|\Zend_Db_Expr $name, string $cond, array|string $cols = self, 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.

Inherited from: \Zend_Db_Select::joinLeft()

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

Parameters
Name Type Description
$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
Type Description
\Zend_Db_Select This Zend_Db_Select object.
methodpublicjoinNatural( array|string|\Zend_Db_Expr $name, array|string $cols = self, string $schema = null ) : \Zend_Db_Select

Add a NATURAL JOIN table and colums to the query.

Inherited from: \Zend_Db_Select::joinNatural()

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 Type Description
$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
Type Description
\Zend_Db_Select This Zend_Db_Select object.
methodpublicjoinRight( array|string|\Zend_Db_Expr $name, string $cond, array|string $cols = self, string $schema = null ) : \Zend_Db_Select

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

Inherited from: \Zend_Db_Select::joinRight()

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 Type Description
$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
Type Description
\Zend_Db_Select This Zend_Db_Select object.
methodpubliclimit( int $count = null, int $offset = null ) : \Zend_Db_Select

Sets a limit count and offset to the query.

Inherited from: \Zend_Db_Select::limit()
Parameters
Name Type Description
$count int OPTIONAL The number of rows to return.
$offset int OPTIONAL Start returning after this many rows.
Returns
Type Description
\Zend_Db_Select This Zend_Db_Select object.
methodpubliclimitPage( int $page, int $rowCount ) : \Zend_Db_Select

Sets the limit and count by page number.

Inherited from: \Zend_Db_Select::limitPage()
Parameters
Name Type Description
$page int Limit results to this page number.
$rowCount int Use this many rows per page.
Returns
Type Description
\Zend_Db_Select This 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.

Inherited from: \Zend_Db_Select::orHaving()

Otherwise identical to orHaving().

Parameters
Name Type Description
$cond string The HAVING condition.
$value mixed OPTIONAL The value to quote into the condition.
$type int OPTIONAL The type of the given value
Returns
Type Description
\Zend_Db_Select This Zend_Db_Select object.
Details
see
\having()  
methodpublicorWhere( string $cond, mixed $value = null, int $type = null ) : \Zend_Db_Select

Adds a WHERE condition to the query by OR.

Inherited from: \Zend_Db_Select::orWhere()

Otherwise identical to where().

Parameters
Name Type Description
$cond string The WHERE condition.
$value mixed OPTIONAL The value to quote into the condition.
$type int OPTIONAL The type of the given value
Returns
Type Description
\Zend_Db_Select This Zend_Db_Select object.
Details
see
\where()  
methodpublicorder( mixed $spec ) : \Zend_Db_Select

Adds a row order to the query.

Inherited from: \Zend_Db_Select::order()
Parameters
Name Type Description
$spec mixed

The column(s) and direction to order by.

Returns
Type Description
\Zend_Db_Select This 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

Inherited from: \Zend_Db_Select::query()
Parameters
Name Type Description
$fetchMode integer OPTIONAL
$bind mixed An array of data to bind to the placeholders.
Returns
Type Description
\PDO_Statement\Zend_Db_Statement
methodpublicreset( string $part = null ) : \Zend_Db_Select

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

Inherited from: \Zend_Db_Select::reset()
Parameters
Name Type Description
$part string OPTIONAL
Returns
Type Description
\Zend_Db_Select
methodpublicsetIntegrityCheck(  $flag = true ) : \Zend_Db_Select

Sets the integrity check flag.

Setting this flag to false skips the checks for table joins, allowing 'hybrid' table rows to be created.

Parameters
Name Type Description
$flag
Returns
Type Description
\Zend_Db_Select This Zend_Db_Select object.
methodpublicsetTable(  $table ) : \Zend_Db_Select

Sets the primary table name and retrieves the table schema.

Parameters
Name Type Description
$table
Returns
Type Description
\Zend_Db_Select This Zend_Db_Select object.
methodpublicunion( array $select = array,  $type = self ) : \Zend_Db_Select

Adds a UNION clause to the query.

Inherited from: \Zend_Db_Select::union()

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
Name Type Description
$select array Array of select clauses for the union.
$type
Returns
Type Description
\Zend_Db_Select This 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.

Inherited from: \Zend_Db_Select::where()

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
Name Type Description
$cond string The WHERE condition.
$value mixed OPTIONAL The value to quote into the condition.
$type int OPTIONAL The type of the given value
Returns
Type Description
\Zend_Db_Select This Zend_Db_Select object.
Documentation was generated by DocBlox 0.13.3.