join方法 import("@.ORG.Page"); $Form = M('gly'); $where=''; if ($_POST['qcx']=='查询'){ if (!empty($_POST['username'])){ $where = 'glyname like \'%'.trim($_POST['username']).'%\''; } } $count = $Form->join('think_role ON think_gly.rid = think_role.id')->where($where)->count('think_gly.id'); //计算总数 $Page = new Page($count, 10); $list = $Form->field('think_gly.*,think_role.name as rolename')->join('think_role on think_gly.rid=think_role.id' )->where($where)->limit($Page->firstRow. ',' . $Page->listRows)->order('think_role.id desc')->select(); // 模拟设置分页额外传入的参数 // 设置分页显示 $Page->setConfig('header', '条数据'); $Page->setConfig('first', '<上页'); $Page->setConfig('last', '下页>'); $page = $Page->show(); $this->assign("page", $page); $this->assign("list", $list); $this->display();
import("@.ORG.Page"); $Form = M('gly'); $where=''; if ($_POST['qcx']=='查询'){ if (!empty($_POST['username'])){ $where = 'glyname like \'%'.trim($_POST['username']).'%\''; } } $count = $Form->table('think_role a,think_gly b')->where('a.id=b.rid')->count('b.id'); //计算总数 $Page = new Page($count, 10); $list = $Form->field('b.*,a.name as rolename')->table('think_role a,think_gly b' )->where('a.id=b.rid')->limit($Page->firstRow. ',' . $Page->listRows)->order('b.id desc')->select(); // 模拟设置分页额外传入的参数 // 设置分页显示 $Page->setConfig('header', '条数据'); $Page->setConfig('first', '<上页'); $Page->setConfig('last', '下页>'); $page = $Page->show(); $this->assign("page", $page); $this->assign("list", $list); $this->display();
sql的执行效果如下: SELECT COUNT(b.id) AS tp_count FROM think_role a,think_gly b WHERE a.id=b.rid LIMIT 1; SELECT b.*,a.name as rolename FROM think_role a,think_gly b WHERE a.id=b.rid ORDER BY b.id desc LIMIT 0,10 参考的资料如下: THINKPHP 中关联查询(多表查询)可以使用 table() 方法或和join方法,请看示例: 联合查询 1、原生查询 $sql = 'select f.*,s.sort_name from think_form as f, think_sort as s where f.sort_id=s.sort_id order by f.id DESC limit 3'; $list = $Form->query($sql); 2、join() 两表查询 $list = $Form->join('think_sort ON think_form.sort_id = think_sort.sort_id' )->select(); 3、join() 多表查询 $list = $Form->join('think_sort ON think_form.sort_id = think_sort.sort_id' )->join('think_brand ON think_form.brand_id = think_brand.brand_id' )->select(); 4、table() $list = $user->table('user_status stats, user_profile profile')->where('stats.id = profile.typeid')->field('stats.id as id, stats.display as display, profile.title as title,profile.content as content')->order('stats.id desc' )->select();