分页就是对较多的数据按照一定的数量和条件进行分页展示。
分页的展示形式一般有三种:
- 直接展示记录的页数
- 加载更多
- 瀑布流的自动加载
分页的关键变量:
- current_page 当前页码
- total_num 总记录数
- num 每页展示的记录数
分页的注意点:
- 不能超越极限范围
分页类的封装:
- 使用面向对象的思想来封装分页类
分页类的美化:
- 可以使用jQuery、bootstrap来进行美化
分页类的高级用法:
- 多条件查询可以满足大部分查询需求,按照特定条件进行排序。
分页类的优化:
1 2 |
SELECT * FROM list LIMIT 1,2000; SELECT * FROM list LIMIT 1000000,2000; |
1,1000000叫做偏移量,偏移量越大查询的效率就越低。
pagenation.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
<html> <head> <meta http-equiv='content-type' content='text/html;charset=utf-8'> <!--引入jquery.js文件--> <script src='http://cdn.bootcss.com/jquery/2.2.4/jquery.js'></script> <!--引入插件.js文件--> <script src='http://cdn.bootcss.com/bootstrap/3.3.7/js/bootstrap.min.js'></script> <!--引入插件.css文件--> <link href='http://cdn.bootcss.com/bootstrap/3.3.7/css/bootstrap.min.css' type='text/css' rel='stylesheet' /> </head> <body> <div class='text-center' style='margin-top:20px;'> <form class="form-inline" role="form" action='/page.php' method='get'> <div class="form-group"> <label class="sr-only" for="exampleInputEmail2"></label> 用户名:<input type="text" name='name' class="form-control" id="exampleInputEmail2" placeholder="" value='<?php if(isset($name) && !empty($name)) echo $name; ?>'> </div> <div class="form-group"> <label class="sr-only" for="exampleInputPassword2"></label> 年龄:<input type="text" name='age' class="form-control" id="exampleInputPassword2" placeholder="" value='<?php if(isset($age) && !empty($age)) echo $age; ?>'> </div> <button type="submit" class="btn btn-default btn-success">查询</button> </form> </div> <table class="table table-bordered table-hover table-striped" style='width:60%;margin:0 auto;'> <tr class='success'> <th>序号</th> <th>姓名</th> <th>年龄 <?php $tmpOne = "/page.php?name=<?php if(isset($name) && !empty($name)) echo $name;?>&age=<?php if(isset($age) && !empty($age)) echo $age;?>"; ?> <a class='btn <?php if($sortOne=='age' && $orderOne=='plus') echo 'btn-warning';else echo 'btn-success';?>' href='<?php echo $tmpOne;?>&sortOne=age&orderOne=plus&sortTwo=<?php if(!empty($sortTwo)) echo $sortTwo;?>&orderTwo=<?php if(!empty($orderTwo)) echo $orderTwo;?>'>升序</a> <!--|<a class='btn <?php if($sortOne=='age' && $orderOne=='less') echo 'btn-warning';else echo 'btn-success';?>' href='/page.php?name=<?php if(isset($name) && !empty($name)) echo $name;?>&age=<?php if(isset($age) && !empty($age)) echo $age;?>&sortOne=age&orderOne=less&sortTwo=<?php if(!empty($sortTwo)) echo $sortTwo;?>&orderTwo=<?php if(!empty($orderTwo)) echo $orderTwo;?>'>降序</a>--> <?php $str = "<a class='btn "; if($sortOne=='age' && $orderOne=='less'){ $str .= 'btn-warning'; }else{ $str .= 'btn-success'; } $str .= " href='/page.php?name='"; if(isset($name) && !empty($name)) $str.= $name; $str .= '&age='; if(isset($age) && !empty($age)) $str .= $age; $str .= '&sortOne=age&orderOne=less'; $str .= '&sortTwo='; if(!empty($sortTwo)) $str.= $sortTwo; $str .= '&orderTwo='; if(!empty($orderTwo)) $str.= $orderTwo; echo $str."'>"; ?> 降序</a> </th> <th>创建时间 <a class='btn <?php if($sortTwo=='createtime' && $orderTwo=='plus') echo 'btn-warning';else echo 'btn-success';?>' href='/page.php?name=<?php if(isset($name) && !empty($name)) echo $name;?>&age=<?php if(isset($age) && !empty($age)) echo $age;?>&sortTwo=createtime&orderTwo=plus&sortOne=<?php if(!empty($sortOne)) echo $sortOne;?>&orderOne=<?php if(!empty($orderOne)) echo $orderOne;?>'>升序</a> <a class='btn <?php if($sortTwo=='createtime' && $orderTwo=='less') echo 'btn-warning';else echo 'btn-success';?>' href='/page.php?name=<?php if(isset($name) && !empty($name)) echo $name;?>&age=<?php if(isset($age) && !empty($age)) echo $age;?>&sortTwo=createtime&orderTwo=less&sortOne=<?php if(!empty($sortOne)) echo $sortOne;?>&orderOne=<?php if(!empty($orderOne)) echo $orderOne;?>'>降序</a> </th> <tr> <?php foreach($dataRes as $k=>$v){ ?> <tr > <?php echo '<td>'.$v['id'].'</td>'; ?> <?php echo '<td>'.$v['name'].'</td>'; ?> <?php echo '<td>'.$v['age'].'</td>'; ?> <?php echo '<td>'.date('Y-m-d H:i:s',$v['createtime']).'</td>'; ?> </tr> <?php } ?> </table> <nav class='text-center'> <ul class="pagination"> <li><a href="/page.php?name=<?php if(isset($name) && !empty($name)) echo $name;?>&age=<?php if(isset($age) && !empty($age)) echo $age;?>&p=<?php echo ($current_page>=2 ? $current_page-1 : 1); ?>&sortOne=<?php echo $sortOne; ?>&orderOne=<?php echo $orderOne; ?>&sortTwo=<?php echo $sortTwo; ?>&orderTwo=<?php echo $orderTwo; ?>">上一页</a></li> <?php for($i=1;$i<=$pages;$i++){ ?> <li <?php if($current_page == $i){ echo "class='active'";}?>><a href="/page.php?name=<?php if(isset($name) && !empty($name)) echo $name;?>&age=<?php if(isset($age) && !empty($age)) echo $age;?>&p=<?php echo $i ?>&sortOne=<?php echo $sortOne; ?>&orderOne=<?php echo $orderOne; ?>&sortTwo=<?php echo $sortTwo; ?>&orderTwo=<?php echo $orderTwo; ?>" ><?php echo $i; ?></a></li> <?php } ?> <li><a href="/page.php?name=<?php if(isset($name) && !empty($name)) echo $name;?>&age=<?php if(isset($age) && !empty($age)) echo $age;?>&p=<?php echo ($current_page<=($pages-1) ? $current_page+1 : $pages); ?>&sortOne=<?php echo $sortOne; ?>&orderOne=<?php echo $orderOne;?>&sortTwo=<?php echo $sortTwo; ?>&orderTwo=<?php echo $orderTwo; ?>">下一页</a></li> </ul> </nav> </body> </html> |
page.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
<?php // total_num 总记录数 // current_page 当前所在页 // perpage_num 每页显示多少条记录 function dump($arr){ echo "<pre>"; var_dump($arr); echo "</pre>"; } header('content-type:text/html;charset=utf-8'); //实例化PDO $pdo = new PDO('mysql:host=127.0.0.1;dbname=test','root','root'); /** * 由于引入多条件查询,需要增加where子句 */ $name = isset($_GET['name']) ? $_GET['name'] : ''; $age = isset($_GET['age']) ? intval($_GET['age']) : 0 ; $where = ' where 1=1'; if( !empty($name)){ $where .= " and name='".$name."'"; } if( !empty($age)){ $where .= ' and age='.$age; } $order = ''; $sortOne = isset($_GET['sortOne']) ? $_GET['sortOne'] : ''; $orderOne = isset($_GET['orderOne']) ? $_GET['orderOne'] : ''; if(!empty($sortOne) && !empty($orderOne)){ $orderBy = $orderOne == 'plus' ? 'asc' : ' desc'; $order .= " $sortOne $orderBy"; } $sortTwo = isset($_GET['sortTwo']) ? $_GET['sortTwo'] : ''; $orderTwo = isset($_GET['orderTwo']) ? $_GET['orderTwo'] : ''; if(!empty($sortTwo) && !empty($orderTwo)){ $orderBy = $orderTwo == 'plus' ? 'asc' : ' desc'; $order .= ",$sortTwo $orderBy"; } if(!empty($order)){ $order = 'order by '.trim($order,','); } dump($order); //查询总记录数 $res = $pdo->query('select count(*) as num from user'.$where); $dbRes = $res->fetch(PDO::FETCH_ASSOC); $total_num = $dbRes['num']; //设定每页显示的条数 $perpage_num = 10; //计算出总页数 $pages = ceil($total_num/$perpage_num); $current_page = isset($_GET['p']) ? $_GET['p'] : 1;//默认是第一页 //limit 0,20 第一页 20 //limit 20,20 第二页 20 20-40 //limit 40,20 第三页 20 40-60 $limit = " limit ".(($current_page-1)*$perpage_num).','.$perpage_num; //筛选出最前的20条记录 $data = $pdo->query("select id,name,createtime,age from user ". $where . $order .$limit); $dataRes = $data->fetchAll(); /*$table = '<table align=center border=1>'; $table .= '<tr>'; $table .= '<td>序号</td><td>姓名</td><td>年龄</td><td>创建时间</td>'; $table .= '</tr>'; foreach($dataRes as $k=>$v){ $table .='<tr><td>'.$v['id'].'</td><td>'.$v['name'].'</td><td>'.$v['age'].'</td><td>'. date('Y-m-d H:i:s',$v['createtime']).'</td></tr>'; } $table .= '</table>'; echo $table; $page = ''; $page .= "<a href='/page.php?p=1'>首页</a> "; $page .= "<a href='/page.php?p=".($current_page>=2 ? $current_page-1 : 1)."'>上一页</a> "; for($i=1;$i<=$pages;$i++){ $page .= "<a href='/page.php?p=$i'>"; if($i == $current_page){ $page .= "<font color=red>".$i."</font>"; }else{ $page .= $i; } $page .= "</a> "; } $page .= "<a href='/page.php?p=".($current_page<=($pages-1) ? $current_page+1 : $pages)."'>下一页</a> "; $page .= "<a href='/page.php?p=".$pages."'>尾页</a>"; $page .= '| 总共'.$total_num.'条记录,目前是在第'.$current_page.'页'; echo $page; */ require './pagenation.php'; |
page.class.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
<?php class Page{ public $total_num; public $perpage_num; public $current_page; public $pages; public function __construct($total_num,$perpage_num,$current_page){ header('content-type:text/html;charset=utf-8'); $this->total_num=$total_num; $this->perpage_num=$perpage_num; $this->current_page=$current_page; $this->pages=ceil($total_num/$perpage_num); } public function showPage(){ $page = ''; if($this->current_page!=1){ $page .= "<a href='/page.class.php?p=1'>首页</a> "; $page .= "<a href='/page.class.php?p=".($this->current_page>=2 ? $this->current_page-1 : 1)."'>上一页</a> "; } for($i=1;$i<=$this->pages;$i++){ $page .= "<a href='/page.class.php?p=$i'>"; if($i == $this->current_page){ $page .= "<font color=red>".$i."</font>"; }else{ $page .= $i; } $page .= "</a> "; } if($this->current_page!=$this->pages){ $page .= "<a href='/page.class.php?p=".($this->current_page<=($this->pages-1) ? $this->current_page+1 : $this->pages)."'>下一页</a> "; $page .= "<a href='/page.class.php?p=".$this->pages."'>尾页</a>"; } $page .= '| 总共'.$this->total_num.'条记录,目前是在第'.$this->current_page.'页'; echo $page; } } $pdo = new PDO('mysql:host=127.0.0.1;dbname=test','root','root'); //查询总记录数 $res = $pdo->query('select count(*) as num from user'); $dbRes = $res->fetch(PDO::FETCH_ASSOC); $total_num = $dbRes['num']; //设定每页显示的条数 $perpage_num = 20; $current_page = isset($_GET['p']) ? $_GET['p'] : 1;//默认是第一页 //limit 0,20 第一页 20 //limit 20,20 第二页 20 20-40 //limit 40,20 第三页 20 40-60 $limit = " limit ".(($current_page-1)*20).',20'; //筛选出最前的20条记录 $data = $pdo->query("select id,name,createtime,age from user ". $limit); $dataRes = $data->fetchAll(); $table = '<table align=center border=1>'; $table .= '<tr>'; $table .= '<td>序号</td><td>姓名</td><td>年龄</td><td>创建时间</td>'; $table .= '</tr>'; foreach($dataRes as $k=>$v){ $table .='<tr><td>'.$v['id'].'</td><td>'.$v['name'].'</td><td>'.$v['age'].'</td><td>'. date('Y-m-d H:i:s',$v['createtime']).'</td></tr>'; } $table .= '</table>'; echo $table; $pageObj=new Page($total_num,$perpage_num,$current_page); $pageObj->showPage(); |