Solved

How to fetch row set with JOINs in Zend Framework

Posted on 2008-06-20
6
2,430 Views
Last Modified: 2008-06-23
I have a php script that loops though a rowset.

I'm tying to figure out a way to use joins so that my rowset will contain more data.  My problem is that joins are not allowed.  

"You can not specify columns from a JOINed tabled to be returned in a row/rowset. Doing so will trigger a PHP error. This was done to ensure the integrity of the Zend_Db_Table is retained. i.e. A Zend_Db_Table_Row should only reference columns derived from its parent table. "

What method should I be using ?


IndexController.php
 
 
 public function showallAction()
    {
    	$this->view->title = "Showing All PSI Inventory";
	    
    	$items = new Items();
	   	$fields = '"public"."t_item_types"."name",
 		"public"."t_items"."id", "public"."t_items"."serial",
 		"public"."t_items"."nickname",
		 "public"."t_sites"."name" AS sitename, "public"."t_status"."name" AS statusname
	   	';
	   	$joins = 'INNER JOIN "public"."t_status" ON
	   	("public"."t_items"."status_id" = "public"."t_status"."id")
 		INNER JOIN "public"."t_sites" ON ("public"."t_items".
 		"site_id" = "public"."t_sites"."id") INNER JOIN
 		"public"."t_item_types" ON ("public"."t_items"."
 		type_id" = "public"."t_item_types"."id")' ;
 
    	$select = $items->select($fields);
	   	$select->join($joins);
		$this->view->items = $items->fetchAll($select);
		echo $fields;
		echo $joins;
 
    }
 
 
 
showall.phtml
 
<p><a>Displaying Short View</a></p>
<table>
<tr>
     <th>nickname</th>
     <th>serial</th>
     <th>&nbsp;</th>
</tr>
<?php foreach($this->items as $item) : ?>
<tr>
     <td><?php echo $this->escape($item->nickname);?></td>
     <td><?php echo $this->escape($item->serial);?></td>
     <td>
          <a href="<?php echo $this->url(array('controller'=>'index',
              'action'=>'edit', 'id'=>$item->id));?>">Edit</a>
          <a href="<?php echo $this->url(array('controller'=>'index',
              'action'=>'delete', 'id'=>$item->id));?>">Delete</a>
     </td>
</tr>
<?php endforeach; ?>
</table>
 
 
and my items.php file
 
require_once 'Zend/Db/Table/Abstract.php';
 
class Items extends Zend_Db_Table_Abstract  {
	/**
	 * Returns ShortView Data
	 */
	protected $_name = 't_items';
	
	
}

Open in new window

0
Comment
Question by:sean-keys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 48

Accepted Solution

by:
hernst42 earned 500 total points
ID: 21834771
Start here to read:
http://framework.zend.com/manual/en/zend.db.select.html#zend.db.select.building

You need a Zend_Db_Select class and not a Zend_Db_Table_Abstract class. Zend_Db_Table_Abstract implements the Active Record pattern which typical does not work with join on the database backend.
0
 

Author Comment

by:sean-keys
ID: 21835379
Thanks for clearing that up.  I read about Select and came up with this

I get this error
Fatal error: Uncaught exception 'Zend_Db_Exception' with message 'Adapter name must be specified in a string' in

I think I have an error because $db isn't my current factory???


I have

// setup database
$db = Zend_Db::factory($config_db->db);

in my index.php
 public function showallAction()
    {
    	$this->view->title = "Showing All PSI Inventory";
	    
		$db = Zend_Db::factory();
		$select = new Zend_Db_Select($db);
		$select->from('t_items', array('id','serial','nickname'));
		$query = $db->query($select);
		$result = $query->fetchAll();
		echo $result;

Open in new window

0
 
LVL 48

Expert Comment

by:hernst42
ID: 21835494
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Expert Comment

by:hernst42
ID: 21835497
In your posted code it's only
$db = Zend_Db::factory();
without arguments. Sorry not realy sure with the Zend-Framework
0
 

Author Comment

by:sean-keys
ID: 21847364
I already have a factory setup in my index.php  How can I reference it?  I think its something along the lines of $this or get>> ?
0
 

Author Comment

by:sean-keys
ID: 21847522

// load configuration
$config_db = new Zend_Config_Ini('../application/default/config.ini', 'general');
$registry = Zend_Registry::getInstance();
$registry->set('config', $config_db);
 
// setup database
$db = Zend_Db::factory($config_db->db);
Zend_Db_Table::setDefaultAdapter($db);

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question