zend framework database 2

3. Zend_Db_Select
using query to fetch rowset.

// Create the Zend_Db_Select object
$select = $db->select(); //if inside the table class $db is $this->_db; $this->_db is setted in the Initializer.php file: Zend_Db_Table_Abstract::setDefaultAdapter($db);

// Add a FROM clause
$select->from( $table[, $columns] ); //specify table and columns

// Add a WHERE clause
$select->where([$query_condition]); //specify search criteria

// Add an ORDER BY clause
$select->order( [$order] ); //specify sorting criteria

3.1 from() function:

 1 // Build this query with an alias for one column:
 2 //   SELECT p."product_id" AS prodno, p."product_name"
 3 //   FROM "zend_study"."products" AS p
 4 
 5 $select = $db->select()  
 6              ->from(array('p' => 'products'), // p is table alias
 7                     array('prodno' => 'product_id', 'product_name'), // prodno is product_id column alias.
 8                     'zend_study');
 9 
 // 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'))
                     );
 
 // 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)'));
             //if you want to fetch other columns : ->columns('p.product_name'); or ->columns('product_name', 'p');
             //so the query will like this: SELECT p."from" + 10 AS origin, p.product_name FROM "products" AS p

3.2 Adding Another Table to the Query with JOIN
function join($table, $join, [$columns]); $table is the table name you want to join, $join is the join codition, $columns is an array of field columns in the joined table, default is *, if array() that means no column is needed.
joinInner() is the same as join();
others join function:
joinLeft($table, $join, [$columns]), joinRight($table, $join, [$columns]), joinFull($table, $join, [$columns]);
joinCross($table, [$columns]); joinNatural($table,  [$columns]) ; no join condition

3.3 Adding a WHERE, GOURP BY, ORDER BY, LIMIT Clause

 1 // Build this query with an alias for one column:
 2 //   SELECT p."product_id" AS prodno, p."product_name"
 3 //   FROM "zend_study"."products" AS p
 4 
 5 $select = $db->select()  
 6              ->from(array('p' => 'products'), // p is table alias
 7                     array('prodno' => 'product_id', 'product_name'), // prodno is product_id column alias.
 8                     'zend_study');
 9 
 // 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'))
                     );
 
 // 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)'));
             //if you want to fetch other columns : ->columns('p.product_name'); or ->columns('product_name', 'p');
             //so the query will like this: SELECT p."from" + 10 AS origin, p.product_name FROM "products" AS p

3.4 Adding the DISTINCT Query Modifier

 // Build this query:
 //   SELECT DISTINCT p."product_name"
 //   FROM "products" AS p
 
 $select = $db->select()
              ->distinct()
              ->from(array('p' => 'products'), 'product_name');

3.5 Executing Select Queries
Two ways:

 //Executing Select Queries from the Db Adapter
 $select = $db->select()->from('products');
 $result = $db->query($select)->fetchAll(); 
 
 //Executing Select Queries from the Object
 $select = $db->select()->from('products');
 $result = $select->query()->fetchAll();