一 可行方法
/*多表查询 方法1*/(这个是关键)
$adapater = Zend_Db_Table::getDefaultAdapter();
$select = $adapater->select();//得到一个Zend_Table_Select
$select->from('course','*');
$select->join('menu','menu.mid=course.categoryid','name');
//打印查询语句测试
//echo $select;
$result=$adapater->fetchAll($select);
//$result = $db->fetchAll($select);
//打印查询结果测试
print_r($result);
/*多表查询 方法2*/
$adapater = Zend_Db_Table::getDefaultAdapter();
$select = $adapater->select()//得到一个Zend_Table_Select
->from(array('cs'=>'course'), array('organizer'=>'cs.organizer'))
->join(array('mu'=>'menu'), 'mu.mid=cs.categoryid',array('categoryname'=>'mu.name'))
//->where('mu.mid'.$mid.'AND cs.categoryid= '.$categoryid);
->where('mu.mid=cs.categoryid');
//echo $select;
$result=$adapater->fetchAll($select);
print_r($result);
二方法不选(测试不成功)
用 Zend Framework 实现多表联查并在前台页面显示结果
=========================================================
对数据库进行单表查询是最基本的操作,比较重要的还是多表联查。
我把模块中的主要文件列出来,供以后参考。
==========================================================
首先是控制器:IndexController文件,其他的不写了,只把多表查询时涉及的两个表三个function列出来。
IndexController.php
<?php
class IndexController extends Zend_Controller_Action{
//查询emp表
function testempAction(){
$this->view->title = "My EmpTable Test";
$emp = new Emp();
$this->view->emp = $emp->fetchAll();
}
//查询dept表
function testdeptAction(){
$this->view->title = "My DeptTable Test";
$dept = new Dept();
$this->view->dept = $dept->fetchAll();
}
//多表emp、dept查询
function testmultiAction(){
$this->view->title = "My MultiTable Test";
$emp = new Emp();
$dept = new Dept();
$params = array (
'host' => 'localhost',
'username' => 'root',
'password' => 'root',
'dbname' => 'zf'
);
$db = Zend_Db::factory('PDO_MYSQL', $params);
$select = $db->select();
$select->from('emp','*');
$select->join('dept','emp.deptid=dept.deptid','*');
//打印查询语句测试
//echo $select;
$result = $db->fetchAll($select);
//打印查询结果测试
//print_r($result);
//这个位置的$this->view->emps 的emps 与前台页面 foreach 中的 $this->emps 一致 。
$this->view->emps = $db->fetchAll($select);
}
}
===========================================================================
然后就是页面显示的模版了:*.phtml。一共是三个页面对比,testemp.phtml,testdept.phtml和testmulti.phtml。
testemp.phtml文件:
<table>
<tr><th>Name</th><th>Sex</th></tr>
<?php foreach($this->emp as $emps): ?>
<tr>
<td><?php echo $this->escape($emps->ename); ?></td>
<td><?php echo $this->escape($emps->esex); ?></td>
</tr>
<?php endforeach; ?>
</table>
testdept.phtml文件:
<table>
<tr><th>Name</th><th>Address</th></tr>
<?php foreach($this->dept as $depts): ?>
<tr>
<td><?php echo $this->escape($depts->dname); ?></td>
<td><?php echo $this->escape($depts->daddress); ?></td>
</tr>
<?php endforeach; ?>
</table>
testmulti.phtml文件:
<table>
<tr><th>Name</th><th>Sex</th><th>Department</th><th>Address</th></tr>
<?php foreach($this->emps as $k => $v): ?>
<tr>
<td><?php echo $v['ename']; ?></td>
<td><?php echo $v['esex']; ?></td>
<td><?php echo $v['dname']; ?></td>
<td><?php echo $v['daddress']; ?></td>
</tr>
<?php endforeach; ?>
</table>