asked on

How to fetch row set with JOINs in Zend Framework

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

ASKER CERTIFIED SOLUTION

hernst42

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sean-keys

ASKER

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

hernst42

factory needs additional parameters:
http://framework.zend.com/manual/en/zend.db.html#zend.db.adapter.connecting.factory.example

hernst42

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

sean-keys

ASKER

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>> ?

sean-keys

ASKER

// 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