PHP统计排行，分页

1.分页参数

count 　　总数

firstRow   起始行

listRows   每一次获取记录数

list          每一页的记录（要与count对应一致就行）

 

2.分页对象

可以针对真实的数据表

也可以针对统计出来的数据表，或者说是虚拟的表

因为LIMIT是最后执行的，哪怕你进行group操作，哪怕你进行子查询

 

html

<include file="Public:head" title="" />
<style type="text/css">
.top {
    font-size: 18px;
    border-bottom: #ddd 1px solid;
    margin-bottom: -1px;
    font-weight: bold;
}
.top .title {
    margin:10px;
    border:1px solid #EF6C00;
    display:-webkit-box;
    border-radius: 3px;
}
.top .title .title_child {
    width: 50%;
    line-height:40px;
    -webkit-box-flex:1;
    display:block;
    color:#EF6C00;
    text-decoration:none;
}
.top .title .title_child.active {
    color:#FFF;
    background:#EF6C00;
}
.page{
    margin-right: 10px;
}
.ranknum{
    font-weight: bold;
    color:#F92672;

}
#myrank{
    color: #FFF;
    font-weight:bold;
    background-color: #FBC853;
}
</style>
<script type="text/javascript">
    
</script>
<body>
<div class="top text-center">
    <div class="title">
        <a class="title_child <if condition='$type neq 1'>active</if>" href="{sh::U('User/ranklist', array('type' => 0))}">月排行</a>
        <a class="title_child <if condition='$type eq 1'>active</if>" href="{sh::U('User/ranklist', array('type' => 1))}">总排行</a>
    </div>
</div>
<div id="myrank" class="alert alert-danger text-center">
    我的商户数:{sh:$my_user_count} &nbsp;&nbsp; 当前排名: {sh:$my_rank}
</div>
<div id="datalist">
<table class="table table-hover">
      <thead>
        <tr>
          <th>&nbsp;&nbsp;#</th>
          <th>姓名</th>
          <th>商户数</th>
        </tr>
      </thead>
      <tbody>
          <volist name="list" id="vo">
        <tr>
          <th scope="row" class="ranknum">
          <if condition="$vo.rank eq 1"><img src="{sh::RES}public/img/gold.png" style="width: 30px;">
          <elseif condition="$vo.rank eq 2"/><img src="{sh::RES}public/img/silver.png" style="width: 30px;">
          <elseif condition="$vo.rank eq 3"/><img src="{sh::RES}public/img/copper.png" style="width: 30px;">
          <else />
          &nbsp;&nbsp;{sh:$vo.rank}
          </if>
          </th>
          <td>{sh:$vo.name}</td>
          <td>{sh:$vo.usercount}</td>
        </tr>
        </volist>
      </tbody>
</table>
<div class="page text-right">
        {sh:$page}
</div>
</div>

</body>
</html>

php

// 排行榜
    public function ranklist(){
        $type = $this->_get('type','trim');
        $this->assign('type',$type);
        $opener_id = $this->opener_id;
        if($type == 0){  // 上月排行
            $arrLastMonth = $this->getLastMonthStartEndDay();
            $lastStartDay = $arrLastMonth['lastStartDay'];
            $lastEndDay = $arrLastMonth['lastEndDay'].' 23:59:59'; 
            $b_time = strtotime($lastStartDay);
            $e_time = strtotime($lastEndDay);
            $where['b.addtime'] = array(array('gt',$b_time),array('lt',$e_time),'and'); 
        }
        $where['a.status'] = array('eq','1');
        M()->query('SET @rank =0;');
        $subQuery = M()->table('sh_opener a')->join('sh_user b on a.id = b.opener_id')->where($where)->group('a.id')->order('usercount desc')->field('a.id,count(b.id) as usercount,a.name')->select(false);
        $all  = M()->table(''.$subQuery.' a')->getField('a.id,a.usercount,a.name,(@rank:=IFNULL(@rank,0)+1) as rank');
        $count      = count($all);
        $Page       = new Page($count, 10);
        $list       = M()->table('sh_opener a')->join('sh_user b on a.id = b.opener_id')->where($where)->group('a.id')->order('usercount desc')->limit($Page->firstRow.','.$Page->listRows)->field('count(b.id) as usercount,a.name,a.id')->select();
        foreach ($list as $k => $v) {
            $list[$k]['rank'] = $k + 1 + $Page->firstRow;
        }
        // 我的商户
        $my_user_count = $all[$opener_id]['usercount']?$all[$opener_id]['usercount']:0;
        $my_rank = $all[$opener_id]['rank']?$all[$opener_id]['rank']:'-';

        $this->assign('my_user_count',$my_user_count);
        $this->assign('my_rank',$my_rank);
        $this->assign('page',$Page->show());
        $this->assign('list', $list);
        $this->display();
    }

    // 获取上一月开始与结束日期
    private function getLastMonthStartEndDay(){
        $thismonth = date('m');
        $thisyear  = date('Y');
        if ($thismonth == 1) {
            $lastmonth = 12;
            $lastyear  = $thisyear - 1;
        } else {
            $lastmonth = $thismonth - 1;
            $lastyear  = $thisyear;
        }
        $lastStartDay = $lastyear . '-' . $lastmonth . '-1';
        $lastEndDay   = $lastyear . '-' . $lastmonth . '-' . date('t', strtotime($lastStartDay)); //t 给定月份所应有的天数,28到31
        return array('lastStartDay'=>$lastStartDay,'lastEndDay'=>$lastEndDay);
    }

这里用的是thinkphp的分页类实现的。

案例效果

posted @ 2015-12-18 14:35  TBHacker  阅读(2420)  评论(0)  编辑  收藏  举报