Zend Framework SQL Joins Examples

You may have custom of using advanced queries. It often requires writing complex queries if you are working on enterprise, large scale web application(s).
The use of joins can never be ignored.
Zend Framework developers have done tremendous job by providing simple method for implementing joins.
Lets look some examples of different type of joins.
Before discussing joins lets consider we have two tables, “authors” and “books”.
These are associated with author_id.

1. Inner Join
The simplest query will be
$select = $this->_db->select()
                ->from('books',array('col1','col2'…..))
                ->joinInner('authors','books.id=authors.bks_id',array('col1','col3'…))
                ->where('where condition here')
                ->order('column name ASC/DESC');

2. Left Join
$select = $this->_db->select()
                ->from('books',array('col1','col2'…..))
                ->joinLeft('authors','books.id=authors.bks_id',array('col1','col3'…))
                ->where('where condition here')
                ->group('group by column name here')
                ->order('column name ASC/DESC');

3. Right Join
$select = $this->_db->select()
                ->from('books',array('col1','col2'…..))
                ->joinRight('authors','books.id=authors.bks_id',array('col1','col3'…))
                ->where('where condition here')
                ->group('group by column name here')
                ->order('column name ASC/DESC');

4. Full Join
$select = $this->_db->select()
                ->from('books',array('col1','col2'…..))
                ->joinFull('authors','books.id=authors.bks_id',array('col1','col3'…))
                ->where('where condition here')
                ->group('group by column name here')
                ->order('column name ASC/DESC');

5. Cross Join
$select = $this->_db->select()
                ->from('books',array('col1','col2'…..))
                ->joinFull('authors','books.id=authors.bks_id',array('col1','col3'…))
                ->where('where condition here')
                ->group('group by column name here')
                ->order('column name ASC/DESC');

Once you write these queries, you can fetch a single row or multiple rows as

$result = $this->getAdapter()->fetchRow($select);
$result = $this->getAdapter()->fetchAll($select);

The first statement fetch only one row, while the second statement fetch the entire dataset.


6. Insert


$table = new Bugs();

$data = array(
    'created_on'      => '2007-03-22',
    'bug_description' => 'Something wrong',
    'bug_status'      => 'NEW'
);

$table->insert($data);



7. Updating rows in a Table

$table = new Bugs();

$data = array(
    'updated_on'      => '2007-03-23',
    'bug_status'      => 'FIXED'
);

$where = $table->getAdapter()->quoteInto('bug_id = ?', 1234);

$table->update($data, $where);


8.Deleting Rows from a Table

$table = new Bugs();

$where = $table->getAdapter()->quoteInto('bug_id = ?', 1235);

$table->delete($where);


Retrieving specific columns


$table = new Bugs();

$select = $table->select();
$select->from($table, array('bug_id', 'bug_description'))
       ->where('bug_status = ?', 'NEW');

$rows = $table->fetchAll($select);







Retrieving expressions as columns


$table = new Bugs();

$select = $table->select();
$select->from($table,
              array('COUNT(reported_by) as `count`', 'reported_by'))
       ->where('bug_status = ?', 'NEW')
       ->group('reported_by');

$rows = $table->fetchAll($select);



                       (or)

Public function getUserDataById($id) {

$select = $this->_db->select()

->from($this->_name)

->where(‘id = ?’, $id);

$result = $this->getAdapter()->fetchAll($select);

return $result;

}

    or

$table = new My_Db_Table_Picture();
$select = $table->select()
->from($table, array('image'))
->where('gallery_id = '.$gallery_id)
->limit(1);
$rowset = $table->fetchAll($select)->toArray();







Using a lookup table to refine the results of fetchAll()

$table = new Bugs();

// retrieve with from part set, important when joining
$select = $table->select(Zend_Db_Table::SELECT_WITH_FROM_PART);
$select->setIntegrityCheck(false)
       ->where('bug_status = ?', 'NEW')
       ->join('accounts', 'accounts.account_name = bugs.reported_by')
       ->where('accounts.account_name = ?', 'Bob');

$rows = $table->fetchAll($select);



Example of finding a single row by an expression


$table = new Bugs();

$select  = $table->select()->where('bug_status = ?', 'NEW')
                           ->order('bug_id');

$row = $table->fetchRow($select);