Zend Framework Selecting data from multiple tables using joinsr

26 Nov

When I was working in simple php, it was real pain in ass to write queries, especially complex queries like using joins and sub queries.
Zend has done fabulous job by implementing and providing simple methods for fetching data from multiple tables
Let us take a simple example and feel the power of Zend.
Consider we have two tables “books” and “authors”.
Books has the following fields

  • id
  • book_name
  • book_pub_date

and author table has

  • id
  • author_name
  • author_address
  • book_id(FK)

To select data from these tables using simple join, write the following code

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

->from(array(‘bks’=>’books’),array(‘id’,‘book_name’,’book_pub_date’))

->join(array(‘auth’=>’authors’),’bks.id=auth.book_id’,array(‘id’,’author_name’));

->where(‘bks.id=?’,2);

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

Zend_Debug::dump($results);

 

In the above code we first make our select statement.
In the form table we have defined two arrays. array(‘bks’=>’books’) define alias for table books and array(‘id’,‘book_name’,’book_pub_date’) define which columns of the books table we want to fetch.
Next we define join as

->join(array(‘auth’=>’authors’),’bks.id=auth.book_id’,array(‘id’,’author_name’));

The first array define alias for books. The second parameter is condition(on condition that is used in joining table) and the next array define the columns we want to fetch from the second table. Next line define the where clause. We then fetch records and dump it using Zend_Debug::dump($results) to see results that are returned.
The above example was for simple join, sometime you need to use left or right join. So its is as easy as the above example.
To write a left join, consider the following code.

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

->from(array(‘bks’=>’books’),array(‘id’,‘book_name’,’book_pub_date’))

->joinLeft(array(‘auth’=>’authors’),’bks.id=auth.book_id’,array(‘id’,’author_name’));

->where(‘bks.id=?’,2);

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

Zend_Debug::dump($results);



The code above is similar to the first example, we have only changed join to joinLeft. And that’s it your left join. Similarly you use other joins.
Feel free to post comments.

About these ads

5 Responses to “Zend Framework Selecting data from multiple tables using joinsr”

  1. Outsourcing India January 15, 2009 at 10:00 am #

    Is it possible to run the mysql_fetch_array from the result, I need the result in array.

  2. Faheem Abbas January 15, 2009 at 11:22 am #

    What?
    Isn’t it returning an array.
    If you are fetching records in your controller, it will not be a good idea. Write your database related code in the model.
    More over if you want to change fetch mode, write

    $stmt = $db->query(‘SELECT * FROM bugs’);

    $stmt->setFetchMode(Zend_Db::FETCH_ASSOC);

    $rows = $stmt->fetchAll();

  3. Kusum Saini September 14, 2010 at 12:22 pm #

    The above code written in article is it to be written in model?
    if yes .. how will we call two tables in a model?

    pls reply…

  4. rambabu October 21, 2010 at 10:57 am #

    Thank u

  5. sawab October 31, 2010 at 3:44 pm #

    Hi

    I have two tables scheduledjobs and scheduledinstances, I tried the query as follows
    public function fetchCurrentJobs()
    {
    //
    $result = $this->getDbTable();
    $select = $result->select()->from(array(‘a’=>’scheduledinstances’),
    array(‘id’,’unixpid’,’jobid’,’serverid’,’starttime’,’endtime’,’lastchecktime’))
    ->join(array(‘b’=>’scheduledjobs’),’a.jobid=b.id’,array(‘id’,’name’))
    ->where(‘a.endtime is null’);
    //$select->where(‘endtime IS NULL’);
    $this->_tableData = $this->getDbTable()->fetchAll($select);
    return $this->_tableData;
    }

    but got an error saying as below

    Exception information:

    Message: Select query cannot join with another table

    Stack trace:

    #0 /home/netadm/admin_project/frameworks/ZendFramework-1.10.8/library/Zend/Db/Adapter/Abstract.php(456): Zend_Db_Table_Select->assemble()
    #1 /home/netadm/admin_project/frameworks/ZendFramework-1.10.8/library/Zend/Db/Table/Abstract.php(1505): Zend_Db_Adapter_Abstract->query(Object(Zend_Db_Table_Select))
    #2 /home/netadm/admin_project/frameworks/ZendFramework-1.10.8/library/Zend/Db/Table/Abstract.php(1321): Zend_Db_Table_Abstract->_fetch(Object(Zend_Db_Table_Select))
    #3 /web/microsites/scheduler/microTest/application/models/ScheduledInstancesMapper.php(94): Zend_Db_Table_Abstract->fetchAll(Object(Zend_Db_Table_Select))
    #4 /web/microsites/scheduler/microTest/application/controllers/ScheduledInstancesCurrentJobsController.php(13): Application_Model_ScheduledInstancesMapper->fetchCurrentJobs()
    #5 /home/netadm/admin_project/frameworks/ZendFramework-1.10.8/library/Zend/Controller/Action.php(513): ScheduledInstancesCurrentJobsController->indexAction()
    #6 /home/netadm/admin_project/frameworks/ZendFramework-1.10.8/library/Zend/Controller/Dispatcher/Standard.php(295): Zend_Controller_Action->dispatch('indexAction')
    #7 /home/netadm/admin_project/frameworks/ZendFramework-1.10.8/library/Zend/Controller/Front.php(954): Zend_Controller_Dispatcher_Standard->dispatch(Object(Zend_Controller_Request_Http), Object(Zend_Controller_Response_Http))
    #8 /home/netadm/admin_project/frameworks/ZendFramework-1.10.8/library/Zend/Application/Bootstrap/Bootstrap.php(97): Zend_Controller_Front->dispatch()
    #9 /home/netadm/admin_project/frameworks/ZendFramework-1.10.8/library/Zend/Application.php(366): Zend_Application_Bootstrap_Bootstrap->run()
    #10 /web/microsites/scheduler/microTest/public/index.php(26): Zend_Application->run()
    #11 {main}  

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 33 other followers

%d bloggers like this: