thinkphp Multi-Table query

thinkphp Multi-Table query processing

thinkphp Multi-table connection query processing

Thinkphp Association query (Multi-table query)

Three ways are found online: Table (), join (), native SQL statement query. (The following three methods output the same, and the thinkphp own paging function is well preserved)

First type: Table () method

Example: Two tables (table Agent and table Transinfo) that require connection query

$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);//Assignment DataSet $this- >assign (' page ', $show);//Assignment Paging output

Second type: Join () method

Example: Two tables (table Agent and table Transinfo) that require connection query

$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);//  assignmentData set $this->assign (' page ', $show);//  assignment Paging output 

Tip: You can also instantiate more succinctly (officially recommended): $agentModel = M (' Agent '); Instantiating a User object

The third type: Native SQL statement Query method

$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);//Instantiate the number of incoming total records for a paging class and the number of records displayed per page $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);//Assignment Data Set $this->assign (' page ', $show);//Assignment Paging output

Resources:

Http://hi.baidu.com/wjlhh001/item/18d9a91031081b5e2a3e22af

Http://www.yunda51.com/304.html

Http://www.w3school.com.cn/sql/sql_join_right.asp

END

This article is from "Phper Xuchen-Focus on PHP Technology" blog, be sure to keep this source http://xuqin.blog.51cto.com/5183168/1564492

thinkphp Multi-Table query