本文共 2471 字,大约阅读时间需要 8 分钟。
ThinkPHP多表查询处理
ThinkPHP多表连接查询处理
ThinkPHP关联查询(多表查询)
网上找到三种方法:table()、join()、原生SQL语句查询。(以下三种方法输出结果一致,并且很好的保留了ThinkPHP自己的分页功能)
第一种:table()方法
实例:需要连接查询两张表(表agent和表transinfo)
1 2 3 4 5 6 7 8 9 | $Model = new Model(); $sqlcount = "select count(*) as mycount from agent a ,transinfo t where t.clientId=a.id and t.transType like '%agent%' and a.id in (" . $agent_str . ")" ; $listCount = $Model ->query( $sqlcount ); $Page = new Page ( $listCount [0][mycount], 2 ); $show = $Page ->show (); $list = $Model ->table( 'agent a, transinfo t' )->where( "t.clientId=a.id and t.transType like '%agent%' and a.id in (" . $agent_str . ")" )->limit ( $Page ->firstRow . ',' . $Page ->listRows )->select(); //echo $Model->getLastSql(); $this ->assign( 'list' , $list ); // 赋值数据集 $this ->assign( 'page' , $show ); // 赋值分页输出 |
第二种:join()方法
实例:需要连接查询两张表(表agent和表transinfo)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | $Model = new Model(); $agentModel = $Model ->Table( "agent" ); $listCount = $agentModel ->join( " AS a RIGHT JOIN transinfo t ON t.clientId=a.id and t.transType like '%agent%' and a.id in (" . $agent_str . ")" ) ->field( "count(*) as mycount" ) ->select(); $Page = new Page ( $listCount [0][mycount], 2 ); $show = $Page ->show (); $Model = new Model(); $agentModel = $Model ->Table( "agent" ); $list = $agentModel ->join( " AS a RIGHT JOIN transinfo t ON t.clientId=a.id and t.transType like '%agent%' and a.id in (" . $agent_str . ") order by t.id DESC limit " . $Page ->firstRow. "," . $Page ->listRows) ->field( "a.*,t.*" ) ->select(); //echo $agentModel->getLastSql(); $this ->assign( 'list' , $list ); // 赋值数据集 $this ->assign( 'page' , $show ); // 赋值分页输出 |
提示:你也可以这样实例化更简洁(官方推荐的):$agentModel = M('Agent'); // 实例化User对象
第三种:原生SQL语句查询法
1 2 3 4 5 6 7 8 9 | $Model = new Model(); $sqlcount = "select count(*) as mycount from agent a ,transinfo t where t.clientId=a.id " . $where . " and t.transType like '%agent%' and a.id in (" . $agent_str . ")" ; $listCount = $Model ->query( $sqlcount ); $Page = new Page( $listCount [0][ 'mycount' ],2); // 实例化分页类 传入总记录数和每页显示的记录数 $show = $Page ->show(); $sqlList = "select t.*,a.* from agent a ,transinfo t where t.clientId=a.id " . $where . " and t.transType like '%agent%' and a.id in (" . $agent_str . ") limit {$Page->firstRow},{$Page->listRows}" ; $list = $Model ->query( $sqlList ); $this ->assign( 'list' , $list ); // 赋值数据集 $this ->assign( 'page' , $show ); // 赋值分页输出 |
参考资料: