tp框架下单表/多表搜索分页功能

1. 调用

$.ajax({ url: "{:U('UserCommon/search_page')}",

type: 'post',dataType: 'json', data: {'keyword':"",'page_now':2,"page_number":5}, 

 success: function (d) { console.log(d) } });

2. php里面的page方法和limit 方法

（知识点：page, limit ,table ，join ，from_unixtime,子查询）

 
M('user_order_list')->where(array('uid'=>6))->field("*,from_unixtime(cdate,'%Y-%m-%d %H:%i:%s') as ctime")
->order('id desc')->page("$page_now,$page_num")->select();


M('userOrderList a')->join('tp_business_corp_list b on a.order_id=b.order_id')->where(array('a.uid'=>6))
->field("a.*,from_unixtime(b.cdate,'%Y-%m-%d %H:%i:%s') as ctime")
->order('a.id desc')->page("$page_now,$page_num")->select();


$model=new Model();
$model->table('tp_user_order_list a')->join('tp_business_corp_list b on a.order_id=b.order_id')->where(array('a.uid'=>6))
->field("a.*,from_unixtime(b.cdate,'%Y-%m-%d %H:%i:%s') as ctime")
->order('a.id desc')->page("$page_now,$page_num")->select();


$model=new Model();
$model->table(array(
    'tp_user_order_list'=>'a',
     C('DB_PREFIX').'business_corp_list'=>'b'
      ))->where("a.order_id=b.order_id and a.uid=6")
->field("a.*,from_unixtime(b.cdate,'%Y-%m-%d %H:%i:%s') as ctime")
->order('a.id desc')->page("$page_now,$page_num")->select();   //这种方法是以前找到的，后面很少用，记不大清楚，所以还有点问题






$keyword=I('post.keyword','');
$page_now=I('post.page_now',1);// 当前显示页码
$page_number=I('post.page_number',10,'intval');// 每页显示多少条
$num=($page_now-1)*$page_number;
$model=new Model();
if(empty($keyword)){
    $error='no';
    $sql="SELECT a.*,from_unixtime(a.cdate,'%Y-%m-%d %H:%i:%s') ctime,from_unixtime(a.udate,'%Y-%m-%d %H:%i:%s') utime "
        ."FROM tp_user_order_list a RIGHT JOIN (SELECT b.order_id,max(b.udate) maxdate,GROUP_CONCAT(DISTINCT b.status) status "
        ."FROM tp_user_order_list b WHERE b.order_type = '商务订单' and b.uid=".$_SESSION['uid']
        ." GROUP BY b.order_id) c ON a.order_id = c.order_id and a.udate = c.maxdate where c.status not like '%已删除%' order by a.id desc";
}else{
    $error='yes';
    $sql="SELECT a.*,from_unixtime(a.cdate,'%Y-%m-%d %H:%i:%s') ctime,from_unixtime(a.udate,'%Y-%m-%d %H:%i:%s') utime "
        ."FROM tp_user_order_list a RIGHT JOIN (SELECT b.order_id,max(b.udate) maxdate,GROUP_CONCAT(DISTINCT b.status) status "
        ."FROM tp_user_order_list b WHERE b.order_type = '商务订单' and b.uid=".$_SESSION['uid']
        ." and (order_id LIKE '%".$keyword."%' or order_state LIKE '%". $keyword."%' or order_state LIKE '%"
        .$keyword."%' or status LIKE '%".$keyword."%' or pay_state LIKE '%".$keyword."%')"
        ." GROUP BY b.order_id) c ON a.order_id = c.order_id and a.udate = c.maxdate where c.status not like '%已删除%' order by a.id desc";
}
$count=count($model->query($sql));
$list=$model->query($sql." limit $num,$page_number");
$pagetotal=ceil($count/$page_number); 
if(!empty($list)) $this->ajaxReturn(array('status'=>'yes','pagetoatl'=>$pagetotal,'page_now'=>$page_now,'msg'=>$list,'show_error'=>$error,));
else $this->ajaxReturn(array('status'=>'no','pagetoatl'=>0,'page_now'=>0,'msg'=>"您要找的内容暂时没找到",'show_error'=>$error,));