Specify the table for this query using the from() method. You can specify the table name as a simple string. Zend_Db_Select applies identifier quoting around the table name, so you can use special characters.

// Build this query:
//   SELECT *
//   FROM "products"

$select = $db->select()
             ->from( 'products' );

You can also specify the correlation name (sometimes called the "table alias") for a table. Instead of a simple string, use an associative array mapping the correlation name to the table name. In other clauses of the SQL query, use this correlation name. If your query joins more than one table, Zend_Db_Select generates unique correlation names based on the table names, for any tables for which you don't specify the correlation name.

// Build this query:
//   SELECT p.*
//   FROM "products" AS p

$select = $db->select()
             ->from( array('p' => 'products') );

Some RDBMS brands support a leading schema specifier for a table. You can specify the table name as "schemaName.tableName", where Zend_Db_Select quotes each part individually, or you may specify the schema name separately. A schema name specified in the table name takes precedence over a schema provided separately in the event that both are provided.

// Build this query:
//   SELECT *
//   FROM "myschema"."products"

$select = $db->select()
             ->from( 'myschema.products' );

// or

$select = $db->select()
             ->from('products', '*', 'myschema');

In the second argument of the from() method, you can specify the columns to select from the respective table. If you specify no columns, the default is "*", the SQL wildcard for "all columns".

You can list the columns in a simple array of strings, or as an associative mapping of column alias to column name. If you only have one column to query, and you don't need to specify a column alias, you can list it as a plain string instead of an array.

If you give an empty array as the columns argument, no columns from the respective table are included in the result set. See a code example under the section on the join() method.

You can specify the column name as "correlationName.columnName". Zend_Db_Select quotes each part individually. If you don't specify a correlation name for a column, it uses the correlation name for the table named in the current from() method.

// Build this query:
//   SELECT p."product_id", p."product_name"
//   FROM "products" AS p

$select = $db->select()
             ->from(array('p' => 'products'),
                    array('product_id', 'product_name'));

// Build the same query, specifying correlation names:
//   SELECT p."product_id", p."product_name"
//   FROM "products" AS p

$select = $db->select()
             ->from(array('p' => 'products'),
                    array('p.product_id', 'p.product_name'));

// Build this query with an alias for one column:
//   SELECT p."product_id" AS prodno, p."product_name"
//   FROM "products" AS p

$select = $db->select()
             ->from(array('p' => 'products'),
                    array('prodno' => 'product_id', 'product_name'));

Columns in SQL queries are sometimes expressions, not simply column names from a table. Expressions should not have correlation names or quoting applied. If your column string contains parentheses, Zend_Db_Select recognizes it as an expression.

You also can create an object of type Zend_Db_Expr explicitly, to prevent a string from being treated as a column name. Zend_Db_Expr is a minimal class that contains a single string. Zend_Db_Select recognizes objects of type Zend_Db_Expr and converts them back to string, but does not apply any alterations, such as quoting or correlation names.

// Build this query:
//   SELECT p."product_id", LOWER(product_name)
//   FROM "products" AS p
// An expression with parentheses implicitly becomes
// a Zend_Db_Expr.

$select = $db->select()
             ->from(array('p' => 'products'),
                    array('product_id', 'LOWER(product_name)'));

// Build this query:
//   SELECT p."product_id", (p.cost * 1.08) AS cost_plus_tax
//   FROM "products" AS p

$select = $db->select()
             ->from(array('p' => 'products'),
                    array('product_id',
                          'cost_plus_tax' => '(p.cost * 1.08)')
                   );

// Build this query using Zend_Db_Expr explicitly:
//   SELECT p."product_id", p.cost * 1.08 AS cost_plus_tax
//   FROM "products" AS p

$select = $db->select()
             ->from(array('p' => 'products'),
                    array('product_id',
                          'cost_plus_tax' =>
                              new Zend_Db_Expr('p.cost * 1.08'))
                    );

In the cases above, Zend_Db_Select does not alter the string to apply correlation names or identifier quoting. If those changes are necessary to resolve ambiguity, you must make the changes manually in the string.

If your column names are SQL keywords or contain special characters, you should use the Adapter's quoteIdentifier() method and interpolate the result into the string. The quoteIdentifier() method uses SQL quoting to delimit the identifier, which makes it clear that it is an identifier for a table or a column, and not any other part of SQL syntax.

Your code is more database-independent if you use the quoteIdentifier() method instead of typing quotes literally in your string, because some RDBMS brands use nonstandard symbols for quoting identifiers. The quoteIdentifier() method is designed to use the appropriate quoting symbols based on the adapter type. The quoteIdentifier() method also escapes any quote characters that appear within the identifier name itself.

// Build this query,
// quoting the special column name "from" in the expression:
//   SELECT p."from" + 10 AS origin
//   FROM "products" AS p

$select = $db->select()
             ->from(array('p' => 'products'),
                    array('origin' =>
                              '(p.' . $db->quoteIdentifier('from') . ' + 10)')
                   );

There may be cases where you wish to add columns to an existing FROM or JOIN table after those methods have been called. The columns() method allows you to add specific columns at any point before the query is executed. You can supply the columns as either a string or Zend_Db_Expr or as an array of these elements. The second argument to this method can be omitted, implying that the columns are to be added to the FROM table, otherwise an existing correlation name must be used.

// Build this query:
//   SELECT p."product_id", p."product_name"
//   FROM "products" AS p

$select = $db->select()
             ->from(array('p' => 'products'), 'product_id')
             ->columns('product_name');

// Build the same query, specifying correlation names:
//   SELECT p."product_id", p."product_name"
//   FROM "products" AS p

$select = $db->select()
             ->from(array('p' => 'products'), 'p.product_id')
             ->columns('product_name', 'p');
             // Alternatively use columns('p.product_name')

Many useful queries involve using a JOIN to combine rows from multiple tables. You can add tables to a Zend_Db_Select query using the join() method. Using this method is similar to the from() method, except you can also specify a join condition in most cases.

// Build this query:
//   SELECT p."product_id", p."product_name", l.*
//   FROM "products" AS p JOIN "line_items" AS l
//     ON p.product_id = l.product_id

$select = $db->select()
             ->from(array('p' => 'products'),
                    array('product_id', 'product_name'))
             ->join(array('l' => 'line_items'),
                    'p.product_id = l.product_id');

The second argument to join() is a string that is the join condition. This is an expression that declares the criteria by which rows in one table match rows in the other table. You can use correlation names in this expression.

The third argument to join() is an array of column names, like that used in the from() method. It defaults to "*", supports correlation names, expressions, and Zend_Db_Expr in the same way as the array of column names in the from() method.

To select no columns from a table, use an empty array for the list of columns. This usage works in the from() method too, but typically you want some columns from the primary table in your queries, whereas you might want no columns from a joined table.

// Build this query:
//   SELECT p."product_id", p."product_name"
//   FROM "products" AS p JOIN "line_items" AS l
//     ON p.product_id = l.product_id

$select = $db->select()
             ->from(array('p' => 'products'),
                    array('product_id', 'product_name'))
             ->join(array('l' => 'line_items'),
                    'p.product_id = l.product_id',
                    array() ); // empty list of columns

SQL has several types of joins. See the list below for the methods to support different join types in Zend_Db_Select.

In addition to these join methods, you can simplify your queries by using the JoinUsing methods. Instead of supplying a full condition to your join, you simply pass the column name on which to join and the Zend_Db_Select object completes the condition for you.

// Build this query:
//   SELECT *
//   FROM "table1"
//   JOIN "table2"
//   ON "table1".column1 = "table2".column1
//   WHERE column2 = 'foo'

$select = $db->select()
             ->from('table1')
             ->joinUsing('table2', 'column1')
             ->where('column2 = ?', 'foo');

Each of the applicable join methods in the Zend_Db_Select component has a corresponding 'using' method.

You can specify criteria for restricting rows of the result set using the where() method. The first argument of this method is a SQL expression, and this expression is used in a SQL WHERE clause in the query.

// Build this query:
//   SELECT product_id, product_name, price
//   FROM "products"
//   WHERE price > 100.00

$select = $db->select()
             ->from('products',
                    array('product_id', 'product_name', 'price'))
             ->where('price > 100.00');

The second argument to the where() method is optional. It is a value to substitute into the expression. Zend_Db_Select quotes the value and substitutes it for a question-mark ("?") symbol in the expression.

// Build this query:
//   SELECT product_id, product_name, price
//   FROM "products"
//   WHERE (price > 100.00)

$minimumPrice = 100;

$select = $db->select()
             ->from('products',
                    array('product_id', 'product_name', 'price'))
             ->where('price > ?', $minimumPrice);

You can pass an array as the second parameter to the where() method when using the SQL IN operator.

// Build this query:
//   SELECT product_id, product_name, price
//   FROM "products"
//   WHERE (product_id IN (1, 2, 3))

$productIds = array(1, 2, 3);

$select = $db->select()
             ->from('products',
                    array('product_id', 'product_name', 'price'))
             ->where('product_id IN (?)', $productIds);

You can invoke the where() method multiple times on the same Zend_Db_Select object. The resulting query combines the multiple terms together using AND between them.

// Build this query:
//   SELECT product_id, product_name, price
//   FROM "products"
//   WHERE (price > 100.00)
//     AND (price < 500.00)

$minimumPrice = 100;
$maximumPrice = 500;

$select = $db->select()
             ->from('products',
                    array('product_id', 'product_name', 'price'))
             ->where('price > ?', $minimumPrice)
             ->where('price < ?', $maximumPrice);

If you need to combine terms together using OR, use the orWhere() method. This method is used in the same way as the where() method, except that the term specified is preceded by OR, instead of AND.

// Build this query:
//   SELECT product_id, product_name, price
//   FROM "products"
//   WHERE (price < 100.00)
//     OR (price > 500.00)

$minimumPrice = 100;
$maximumPrice = 500;

$select = $db->select()
             ->from('products',
                    array('product_id', 'product_name', 'price'))
             ->where('price < ?', $minimumPrice)
             ->orWhere('price > ?', $maximumPrice);

Zend_Db_Select automatically puts parentheses around each expression you specify using the where() or orWhere() methods. This helps to ensure that Boolean operator precedence does not cause unexpected results.

// Build this query:
//   SELECT product_id, product_name, price
//   FROM "products"
//   WHERE (price < 100.00 OR price > 500.00)
//     AND (product_name = 'Apple')

$minimumPrice = 100;
$maximumPrice = 500;
$prod = 'Apple';

$select = $db->select()
             ->from('products',
                    array('product_id', 'product_name', 'price'))
             ->where("price < $minimumPrice OR price > $maximumPrice")
             ->where('product_name = ?', $prod);

In the example above, the results would be quite different without the parentheses, because AND has higher precedence than OR. Zend_Db_Select applies the parentheses so the effect is that each expression in successive calls to the where() bind more tightly than the AND that combines the expressions.

In SQL, the GROUP BY clause allows you to reduce the rows of a query result set to one row per unique value found in the columns named in the GROUP BY clause.

In Zend_Db_Select, you can specify the columns to use for calculating the groups of rows using the group() method. The argument to this method is a column or an array of columns to use in the GROUP BY clause.

// Build this query:
//   SELECT p."product_id", COUNT(*) AS line_items_per_product
//   FROM "products" AS p JOIN "line_items" AS l
//     ON p.product_id = l.product_id
//   GROUP BY p.product_id

$select = $db->select()
             ->from(array('p' => 'products'),
                    array('product_id'))
             ->join(array('l' => 'line_items'),
                    'p.product_id = l.product_id',
                    array('line_items_per_product' => 'COUNT(*)'))
             ->group('p.product_id');

Like the columns array in the from() method, you can use correlation names in the column name strings, and the column is quoted as an identifier unless the string contains parentheses or is an object of type Zend_Db_Expr.

In SQL, the HAVING clause applies a restriction condition on groups of rows. This is similar to how a WHERE clause applies a restriction condition on rows. But the two clauses are different because WHERE conditions are applied before groups are defined, whereas HAVING conditions are applied after groups are defined.

In Zend_Db_Select, you can specify conditions for restricting groups using the having() method. Its usage is similar to that of the where() method. The first argument is a string containing a SQL expression. The optional second argument is a value that is used to replace a positional parameter placeholder in the SQL expression. Expressions given in multiple invocations of the having() method are combined using the Boolean AND operator, or the OR operator if you use the orHaving() method.

// Build this query:
//   SELECT p."product_id", COUNT(*) AS line_items_per_product
//   FROM "products" AS p JOIN "line_items" AS l
//     ON p.product_id = l.product_id
//   GROUP BY p.product_id
//   HAVING line_items_per_product > 10

$select = $db->select()
             ->from(array('p' => 'products'),
                    array('product_id'))
             ->join(array('l' => 'line_items'),
                    'p.product_id = l.product_id',
                    array('line_items_per_product' => 'COUNT(*)'))
             ->group('p.product_id')
             ->having('line_items_per_product > 10');

In SQL, the ORDER BY clause specifies one or more columns or expressions by which the result set of a query is sorted. If multiple columns are listed, the secondary columns are used to resolve ties; the sort order is determined by the secondary columns if the preceding columns contain identical values. The default sorting is from least value to greatest value. You can also sort by greatest value to least value for a given column in the list by specifying the keyword DESC after that column.

In Zend_Db_Select, you can use the order() method to specify a column or an array of columns by which to sort. Each element of the array is a string naming a column. Optionally with the ASC DESC keyword following it, separated by a space.

Like in the from() and group() methods, column names are quoted as identifiers, unless they contain parentheses or are an object of type Zend_Db_Expr.

// Build this query:
//   SELECT p."product_id", COUNT(*) AS line_items_per_product
//   FROM "products" AS p JOIN "line_items" AS l
//     ON p.product_id = l.product_id
//   GROUP BY p.product_id
//   ORDER BY "line_items_per_product" DESC, "product_id"

$select = $db->select()
             ->from(array('p' => 'products'),
                    array('product_id'))
             ->join(array('l' => 'line_items'),
                    'p.product_id = l.product_id',
                    array('line_items_per_product' => 'COUNT(*)'))
             ->group('p.product_id')
             ->order(array('line_items_per_product DESC',
                           'product_id'));

Some RDBMS brands extend SQL with a query clause known as the LIMIT clause. This clause reduces the number of rows in the result set to at most a number you specify. You can also specify to skip a number of rows before starting to output. This feature makes it easy to take a subset of a result set, for example when displaying query results on progressive pages of output.

In Zend_Db_Select, you can use the limit() method to specify the count of rows and the number of rows to skip. The first argument to this method is the desired count of rows. The second argument is the number of rows to skip.

// Build this query:
//   SELECT p."product_id", p."product_name"
//   FROM "products" AS p
//   LIMIT 10, 20
// Equivalent to:
//   SELECT p."product_id", p."product_name"
//   FROM "products" AS p
//   LIMIT 20 OFFSET 10

$select = $db->select()
             ->from(array('p' => 'products'),
                    array('product_id', 'product_name'))
             ->limit(20, 10);

Use the limitPage() method for an alternative way to specify row count and offset. This method allows you to limit the result set to one of a series of fixed-length subsets of rows from the query's total result set. In other words, you specify the length of a "page" of results, and the ordinal number of the single page of results you want the query to return. The page number is the first argument of the limitPage() method, and the page length is the second argument. Both arguments are required; they have no default values.

// Build this query:
//   SELECT p."product_id", p."product_name"
//   FROM "products" AS p
//   LIMIT 10, 20

$select = $db->select()
             ->from(array('p' => 'products'),
                    array('product_id', 'product_name'))
             ->limitPage(2, 10);

The distinct() method enables you to add the DISTINCT keyword to your SQL query.

// Build this query:
//   SELECT DISTINCT p."product_name"
//   FROM "products" AS p

$select = $db->select()
             ->distinct()
             ->from(array('p' => 'products'), 'product_name');

The forUpdate() method enables you to add the FOR UPDATE modifier to your SQL query.

// Build this query:
//   SELECT FOR UPDATE p.*
//   FROM "products" AS p

$select = $db->select()
             ->forUpdate()
             ->from(array('p' => 'products'));

You can build union queries with Zend_Db_Select by passing an array of Zend_Db_Select or SQL Query strings into the union() method. As second parameter you can pass the Zend_Db_Select::SQL_UNION or Zend_Db_Select::SQL_UNION_ALL constants to specify which type of union you want to perform.

$sql1 = $db->select();
$sql2 = "SELECT ...";

$select = $db->select()
    ->union(array($sql1, $sql2))
    ->order("id");

You can execute the query represented by the Zend_Db_Select object by passing it as the first argument to the query() method of a Zend_Db_Adapter_Abstract object. Use the Zend_Db_Select objects instead of a string query.

The query() method returns an object of type Zend_Db_Statement or PDOStatement, depending on the adapter type.

$select = $db->select()
             ->from('products');

$stmt = $db->query($select);
$result = $stmt->fetchAll();

As an alternative to using the query() method of the adapter object, you can use the query() method of the Zend_Db_Select object. Both methods return an object of type Zend_Db_Statement or PDOStatement, depending on the adapter type.

$select = $db->select()
             ->from('products');

$stmt = $select->query();
$result = $stmt->fetchAll();

If you need access to a string representation of the SQL query corresponding to the Zend_Db_Select object, use the __toString() method.

$select = $db->select()
             ->from('products');

$sql = $select->__toString();
echo "$sql\n";

// The output is the string:
//   SELECT * FROM "products"

The getPart() method returns a representation of one part of your SQL query. For example, you can use this method to return the array of expressions for the WHERE clause, or the array of columns (or column expressions) that are in the SELECT list, or the values of the count and offset for the LIMIT clause.

The return value is not a string containing a fragment of SQL syntax. The return value is an internal representation, which is typically an array structure containing values and expressions. Each part of the query has a different structure.

The single argument to the getPart() method is a string that identifies which part of the Select query to return. For example, the string 'from' identifies the part of the Select object that stores information about the tables in the FROM clause, including joined tables.

The Zend_Db_Select class defines constants you can use for parts of the SQL query. You can use these constant definitions, or you can the literal strings.

$select = $db->select()
             ->from('products')
             ->order('product_id');

// You can use a string literal to specify the part
$orderData = $select->getPart( 'order' );

// You can use a constant to specify the same part
$orderData = $select->getPart( Zend_Db_Select::ORDER );

// The return value may be an array structure, not a string.
// Each part has a different structure.
print_r( $orderData );

The reset() method enables you to clear one specified part of the SQL query, or else clear all parts of the SQL query if you omit the argument.

The single argument is optional. You can specify the part of the query to clear, using the same strings you used in the argument to the getPart() method. The part of the query you specify is reset to a default state.

If you omit the parameter, reset() changes all parts of the query to their default state. This makes the Zend_Db_Select object equivalent to a new object, as though you had just instantiated it.

// Build this query:
//   SELECT p.*
//   FROM "products" AS p
//   ORDER BY "product_name"

$select = $db->select()
             ->from(array('p' => 'products')
             ->order('product_name');

// Changed requirement, instead order by a different columns:
//   SELECT p.*
//   FROM "products" AS p
//   ORDER BY "product_id"

// Clear one part so we can redefine it
$select->reset( Zend_Db_Select::ORDER );

// And specify a different column
$select->order('product_id');

// Clear all parts of the query
$select->reset();