Thanks for helping on this Elemental. I have really been pulling my hair out, and I didn't have much to begin with.
I do not claim that this is the best way to do it, or that it is correct, but here is how I solved this and got JOIN working within Zend Framework.
/application/model/Asset.php
PHP Code:
class Asset extends Zend_Db_Table
{
private $dbAdapter;
public function __construct()
{
$this->dbAdapter= Zend_Registry::get('dbAdapter');
}
public function getAssetInfo()
{
$select = new Zend_Db_Select($this->dbAdapter);
$select->from(array('a' => 'asset'), '*');
$select->join(array('al' => 'asset_locations'),
'al.id = a.asset_locations_id');
$select->join(array('ac' => 'asset_categories'),
'ac.id = a.asset_categories_id');
$select->where('a.statuses_id <> 5');
$stmt = $select->query();
$result = $stmt->fetchAll();
return $result;
}
}
/application/controllers/AssetController.php
PHP Code:
public function indexAction()
{
// instantiate the classes, which brings in the Zend_Db_Table
$allAssetInfo = new Asset();
$this->view->allAssetInfo = $allAssetInfo->getAssetInfo();
}
/application/views/scripts/asset/index.phtml
HTML Code:
<table>
<?php foreach($this->allAssetInfo as $asset) : ?>
<tr>
<td><?php echo $this->escape($asset['asset_categories_name']); ?></td>
<td><?php echo $this->escape($asset['city']); ?></td>
<td><?php echo $this->escape($asset['asset_name']); ?></td>
</tr>
<?php endforeach; ?>
</table>