Community Learn Tools Library Leisure
search
English
Home > List of blog posts > PHP分页查询
Blogger Information
Blog 32
fans 0
comment 0
visits 27778
Special Recommendation
More>
Related recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
PHP分页查询
Yang_Sir
Original
968 people have browsed it
  • 分页查询的原理就是利用查询条件LIMIT进行返回数据的限定
  • 纯用PHP处理页面信息并不方便
  • PHP通过get或post获取的数字实际是字符串格式的，用于比较时要注意

1.分页查询类

  • 创建一个Paging分页查询类，该类基础Db数据库查询类
  • 当进行分页查询时，自动生成页码HTML代码 
  1. <?php
  2. // session_start();
  3. include 'Db.php';
  4. //创建分页查询类,继承Db类
  6. class Paging extends Db
  7. {
  9.     public static $sql='';
  10.     public static $current_page = 1;//当前页
  11.     public static $total = 0;//总数
  12.     public static $pagecount = 0;//总页数
  13.     public static $num = 10;//每页数量
  14.     public static $displaypage = 5;//页码显示数量
  15.     public static $pageHtml = '';//保存分页
  17.     /**
  18.      * 按页查询的时候调用
  19.      */
  20.     public  function paginate($table,$where='',$current_page=1,$pagecount='',$field='*',$order=''){
  21.         self::$current_page = $current_page;
  23.         //当页码数为空时，认为没有初始化，需要查询总数
  24.         if($pagecount===''){
  25.             self::setPagecount($table,$where);
  26.         }else{
  27.             self::$pagecount = $pagecount;
  28.         }
  29.         self::createSqlStr($table,$field,$where,$order);
  31.         self::createPage(self::$pagecount,$current_page);
  32.         return parent::find(self::$sql);
  34.     }
  36.     /**
  37.      * 生成分页HTML代码
  38.      */
  39.     public static function createPage($pagecount,$current_page=1,$custom_style=''){
  41.         if($pagecount<1){
  43.             return self::$pageHtml='<div class="pagecontainer"><small>没有数据</small></div>';
  44.         }
  45.         $style = "<style>
  46.             .pagecontainer{
  47.                 width;80%;
  48.                 display: flex;
  49.                 flex-flow: row nowrap;
  50.                 justify-content: center;
  51.                 padding: 10px;
  52.             }
  53.             .pagecontainer button{
  54.                 background-color: white;
  55.                 border: 1px solid #4CAF50;
  56.                 margin:0 10px;
  57.                 padding:8px 15px;
  58.                  color:red;
  59.                  border-radius: 5px;
  60.                  font-size:14px;
  61.             }
  62.             .pagecontainer input{
  63.                 font-size:18px;
  64.                 width:50px;
  65.                 height:30px;
  67.             }
  68.             #act{
  69.                 border: 1px solid #8080ff;
  70.                 background-color: #80ffff;
  71.             }
  73.             .pagecontainer button:hover {background-color: #e7e7e7;}
  74.             </style>
  75.             /*直接跳转*/
  76.             <script>
  77.             function getpage(){
  78.                 var page = document.getElementById('jpage').value;
  79.                 var p = '?p='+page;
  80.                 window.location.href=p;
  81.             }
  82.             </script>
  83.         ";
  84.         //用户自定义样式
  85.         if($custom_style!==''){
  86.             $style= $custom_style;
  87.         }
  88.         //上下页码
  89.         $pre = $current_page>1?$current_page-1:1;
  90.         $next = $current_page<$pagecount?$current_page+1:$pagecount;
  92.         $a=$style."<div class='pagecontainer'><a href='?p={$pre}'><button value='pgup'>上一页</button></a>";
  94.         //判断总页数是否少于可展示页码数，如果是：输出全部页码
  95.         if($pagecount<=self::$displaypage){
  96.             for($i=1;$i<=$pagecount;$i++){
  97.                 if($i ==$current_page){
  98.                     $a.="<a href='?p=$i'><button id='act'>$i</button></a>";
  99.                     continue;
  100.                 }
  101.                 $a.="<a href='?p=$i'><button>$i</button></a>";
  102.             }
  103.         }else{//如果不是
  105.             //第一页必输出，当前页单独设置样式
  106.             if(1==$current_page){
  107.                 $a.="<a href='?p=1'><button id='act'>1</button></a>";
  108.             }else{
  109.                 $a.="<a href='?p=1'><button>1</button></a>";
  110.             }
  112.             //左右偏移量
  113.             $offset = intval(self::$displaypage/2);
  115.             /**当前页码之前的页码输出 */
  117.             //如果当前页大于偏移量+2（第一页和他自己），输出...、输出当前页之前的偏移量页码数
  118.             if($current_page>$offset+2){
  119.                 $a.="<a><button>...</button></a>";
  120.                 $i = $offset;
  121.                 while($i){
  122.                     $page = $current_page-$i;
  123.                     $a.="<a href='?p={$page}'><button>$page</button></a>";
  124.                     $i--;
  125.                 }
  126.                 $a.="<a href='?p={$current_page}'><button id='act'>$current_page</button></a>";
  128.             }else{
  129.                 //如当前页没有大于偏移量+2，输出第二页到当前页的页码
  130.                 $i = 2;
  131.                 while($i<=$current_page){
  132.                     if($i ==$current_page){
  133.                         $a.="<a href='?p={$i}'><button id='act'>$i</button></a>";
  134.                         $i++;
  135.                         continue;
  136.                     }
  137.                     $a.="<a href='?p={$i}'><button >$i</button></a>";
  138.                     $i++;
  139.                 }
  140.             }
  142.             /**当前页码之后的页码输出 */
  144.             //如果当前页码加上偏移量+1（尾页总显示）小于总页数，//输出当前页后偏移量数目的页码，然后输出...和尾页
  145.            if(($current_page+$offset+1)<$pagecount){
  146.                 $i = 1;
  147.                 while($i<=$offset){
  148.                     $page = $current_page+$i;
  149.                     $a.="<a href='?p={$page}'><button>$page</button></a>";
  150.                     $i++;
  151.                 }
  152.                 $a.="<a><button>...</button></a>";
  153.                 //输出尾页
  154.                 $a.="<a href='?p={$pagecount}'><button>$pagecount</button></a>";
  156.             }else{
  157.                 //如果不是，则输出当前页至倒数第二页的页码
  158.                 $i = $pagecount-$current_page;
  159.                 //echo $pagecount-$current_page;
  160.                 // echo $i;//exit;
  161.                 while($i>0){
  162.                     $current_page++;
  163.                     $a.="<a href='?p={$current_page}'><button>$current_page</button></a>";
  164.                     $i--;
  165.                 }
  166.             }   
  167.         }
  168.         $a.="<a href='?p={$next}'><button>下一页</button></a>";
  169.         $a.="<aa><input type='text' id='jpage''/></aa> <a><button onclick='getpage()'>跳转</button></a></div>";
  170.         self::$pageHtml = $a;
  171.         return $a;
  172.     }
  174.     /**
  175.      * 设置每页数量
  176.      */
  178.     public static function setNum(int $num){
  179.         self::$num = $num;
  180.     }
  182.     /**
  183.      * 设置最多显示的页码
  184.      */
  186.     public static function setDisplaypage(int $num){
  187.         self::$num = $num;
  188.     }
  190.     /**
  191.      * 初始化页面,当前页为1的时候调用一次
  192.      */
  193.     public static function setPagecount($table,$where=''){
  194.         $sql = "select count(*) as count from `{$table}` {$where}";
  195.         $count = parent::find($sql);
  196.         self::$total = $count[0]['count'];
  197.         self::$pagecount = ceil(self::$total/self::$num);
  198.         // return self::$pagecount;
  200.     }
  202.     /**
  203.      * 拼接sql查询语句
  204.      */
  205.     public function createSqlStr($table,$field='*',$where='',$order=''){
  206.         $limit = '';
  207.         $offset = (self::$current_page-1)*self::$num;
  208.         if(self::$pagecount!==1){
  209.             $limit = " LIMIT ".self::$num." OFFSET ".$offset;
  210.         }
  211.         self::$sql = "select {$field} from `{$table}` {$where} {$limit} {$order}";
  212.         return self::$sql;
  213.     }
  215. }

生成的页码样式：

2.分页查询示例

  • 现有一张商品信息表,80余条数据
  • 有按条件查询功能

2.1获取数据

  • 根据当前url查询字符串中的p属性，获取当前页码，默认为1
  • 启用session保存查询条件，使分页查询时查询条件不丢失，以及保存页码数可以不用每次都查询总数
  1. <?php 
  2. require('Paging.php');
  3. include 'config.php';
  4. session_start();
  5. $paging = new Paging($dsn,$username,$password);
  6. $where = '';
  8. //判断是否提交了条件查询，删除原查询的信息
  9. if(isset($_POST['goodsname'])){
  10.     unset($_SESSION['pagecount']);
  11.     unset( $_SESSION['goodsname']);
  12.     unset( $_SESSION['goodsmodel']);
  13. }
  15. //如果有提交条件查询，保存查询信息，用于分页查询时的where条件
  16. if(isset($_POST['goodsname'])&&$_POST['goodsname']!='') {
  17.     $_SESSION['goodsname']=$_POST['goodsname'];
  18. }
  19. if(isset($_POST['goodsmodel'])&&$_POST['goodsmodel']!=''){
  20.     $_SESSION['goodsmodel']=$_POST['goodsmodel'];
  21. }
  23. //根据查询条件设置where语句
  24. if(isset($_SESSION['goodsname'])&&isset($_SESSION['goodsmodel'])){
  25.     $where = " where `name`='{$_SESSION['goodsname']}' and `model`='{$_SESSION['goodsmodel']}'";
  26. }elseif(isset($_SESSION['goodsname'])){
  27.     $where = " where `name`='{$_SESSION['goodsname']}'";
  28. }elseif(isset($_SESSION['goodsmodel'])){
  29.     $where = " where `model`='{$_SESSION['goodsmodel']}'";
  30. }
  32. //保存总页数，可以不用每次都去统计总数
  33. if(!isset($_SESSION['pagecount'])){
  34.     $data = $paging->paginate($table,$where);
  35.     $page = $paging::$pageHtml;//调用页码
  36.     $_SESSION['pagecount'] = $paging::$pagecount;
  38. }else{
  39.     $p = 1;//当前页
  40.     if(isset($_GET['p'])){
  41.         $p = $_GET['p'];
  42.     }
  43.     $pagecount = $_SESSION['pagecount'];//获取总页数
  44.     $data = $paging->paginate($table,$where,$p,$pagecount);
  45.     $_SESSION['pagecount'] = $paging::$pagecount;//更新session
  46.     $page = $paging::$pageHtml;//调用页码
  47. }

2.2 展示数据

  • 循环输出查询结果中的数据到表格中
  • 输出生成的页码
  2. <?php require('data.php');?>
  3. <!DOCTYPE html>
  4. <html lang="en">
  6. <head>
  7.     <meta charset="UTF-8">
  8.     <meta name="viewport" content="width=device-width, initial-scale=1.0">
  9.     <title>商品信息维护</title>
  10.     <style>
  11.     body {
  13.         display: flex;
  14.         flex-flow: column nowrap;
  15.         align-items: center;
  16.     }
  18.     form {
  19.         display: flex;
  20.         flex-flow: row wrap;
  21.     }
  23.     form>section {
  24.         margin: 10px;
  25.         display: flex;
  26.         flex-flow: row nowrap;
  27.     }
  29.     table {
  30.         margin-top: 30px;
  31.         width: 1000px;
  32.         font-family: verdana, arial, sans-serif;
  33.         font-size: 11px;
  34.         color: #333333;
  35.         border-width: 1px;
  36.         border-color: #666666;
  37.         border-collapse: collapse;
  38.     }
  40.     table>thead {
  41.         background-color: #80ff80;
  42.     }
  44.     table th {
  45.         border-width: 1px;
  46.         padding: 8px;
  47.         border-style: solid;
  48.         border-color: #666666;
  50.     }
  52.     table td {
  53.         border-width: 1px;
  54.         padding: 8px;
  55.         border-style: solid;
  56.         border-color: #666666;
  57.         background-color: #ffffff;
  58.         text-align: center;
  59.     }
  61.     tfoot>tr,
  62.     tfoot>tr>td {
  63.         width: initial;
  64.     }
  66.     </style>
  67. </head>
  69. <body>
  70. <!-- 进行条件查询时要清除页码信息 -->
  71.     <form action="<?php echo $_SERVER['PHP_SELF'] ?>" class="queryterms" method="POST">
  72.         <section>
  73.             <label for="goodsname">商品名称：</label>
  74.             <input type="text" name="goodsname" id="goodsname" value="<?php if(isset($_SESSION['goodsname'])) echo $_SESSION['goodsname']?>">
  75.         </section>
  76.         <section>
  77.             <label for="goodsmodel">商品型号：</label>
  78.             <input type="text" name="goodsmodel" id="goodsmodel" value="<?php if(isset($_SESSION['goodsmodel'])) echo $_SESSION['goodsmodel']?>">
  79.         </section>
  80.         <section>
  81.             <button>查询</button>
  82.         </section>
  83.     </form>
  84.     <div>
  85.         <table>
  86.             <thead>
  87.                 <tr>
  88.                     <th>ID</th>
  89.                     <th>名称</th>
  90.                     <th>型号</th>
  91.                     <th>价格</th>
  92.                     <th>数量</th>
  93.                     <th>状态</th>
  94.                     <th>操作</th>
  95.                 </tr>
  96.             </thead>
  97.             <tbody>
  98.                 <?php foreach($data as $val):?>
  99.                 <tr>
  100.                     <td><?php echo $val['id'] ?></td>
  101.                     <td><?php echo $val['name']?></td>
  102.                     <td><?php echo $val['model']?></td>
  103.                     <td><?php echo $val['price']?></td>
  104.                     <td><?php echo $val['number']?></td>
  105.                     <td><?php echo $val['status']?></td>
  106.                     <td><a href="handle.php?act=edit&id=<?php echo $val['id'] ?>">编辑</a>
  108.                     <a href="handle.php?act=delete&id=<?php echo $val['id'] ?>">删除</a></td>
  109.                 </tr>
  110.                 <?php endforeach; ?>
  111.             </tbody>
  112.             <tfoot>
  113.                 <tr>
  114.                     <td colspan="7"><?php echo $page; ?></td>
  115.                 </tr>
  116.             </tfoot>
  117.         </table>
  118.     </div>
  119. </body>
  121. </html>

效果图，初始页面：

效果图，分页查询：

效果图，有条件查询：

效果图，跳转查询：

3.扩展，页面操作更新和删除数据

1 . 数据处理页，handle.php

  • 根据url中的查询字符串判断是什么类型的操作，分发处理

    1. <?php
    2. include 'config.php';//数据库参数
    3. include 'Db.php';
    4. $db = new Db($dsn,$username,$password);
    5. $act = $_GET['act'];
    6. $where =" where `id`= '{$_GET['id']}' "; 
    7. switch($act)
    8. {
    9.   case 'delete':
    10.      $row = $db::delete($table,$where);
    11.      echo $db::$sql;var_dump($row);
    12.       if($row==1) echo '<script>alert("删除成功");location.href="goods.php";</script>';
    13.   break;
    14.   case 'update':
    16.       if(isset($_POST['id'])){
    17.           $data = $_POST;
    18.           $where =" where `id`= '{$_POST['id']}' "; 
    19.           $row = $db::update($table,$where,$data);
    20.       }
    21.        if($row==1){
    22.            echo '<script>alert("更新成功");location.href="goods.php";</script>';
    23.           }else{
    24.               echo '<script>alert("更新失败");location.href="goods.php";</script>';
    25.           } 
    26.    break;
    27.   case 'edit':
    28.       $sql = "select * from `{$table}` {$where}";
    29.       $edit_data = $db::find($sql)[0];
    30.       if(!empty($edit_data)){include'edit.php';unset($_SESSION['pagecount']);}else{
    31.           echo '<script>alert("获取数据失败");location.href="goods.php";</script>';
    32.       }
    33.   break;
    34.   case 'add':
    35.    break;
    36. }

    2 . 编辑信息页，edit.php

  • 根据隐藏的input中id的值获取数据，显示到页面中。
  • 提交修改后的信息进行更新
  1. <!DOCTYPE html>
  2. <html lang="en">
  4. <head>
  5.     <meta charset="UTF-8">
  6.     <meta name="viewport" content="width=xiug, initial-scale=1.0">
  7.     <title>修改商品信息</title>
  9.     <style>
  10.     body {
  11.         margin: 0;
  12.         text-align: center;
  13.         display: flex;
  14.         flex-flow: column nowrap;
  15.         align-items: center;
  16.     }
  18.     form {
  19.         background-color: #80ffff;
  20.         border: 1px solid #c0c0c0;
  21.         width: 600px;
  22.         display: flex;
  23.         flex-flow: column nowrap;
  24.         align-items: center;
  25.         padding: 30px;
  26.     }
  28.     form>section {
  29.         width: 80%;
  30.         margin: 30px 0;
  31.         display: grid;
  32.         grid-template-columns: 200px 300px;
  33.         font-size: 1.2em;
  35.     }
  37.     form>section input {
  38.         font-size: 1.2em;
  39.     }
  41.     button {
  42.         background-color: #0080c0;
  43.         width: 200px;
  44.         margin-top: 30px;
  45.         padding: 10px;
  46.         border-radius: 5px;
  48.     }
  49.     </style>
  51. </head>
  53. <body>
  54.     <?php
  55.     //  print_r($edit_data);
  56.     ?>
  58.     <h1>修改商品信息</h1>
  59.     <form action="handle.php?act=update" method="POST">
  61.         <input type="hidden" name="id" value="<?php  echo $edit_data['id'] ?>">
  63.         <section>
  64.             <label for="name">商品名称：</label>
  65.             <input type="text" name="name" id="name" value="<?php echo $edit_data['name']?>">
  66.         </section>
  67.         <section>
  68.             <label for="model">商品型号：</label>
  69.             <input type="text" name="model" id="model" value="<?php echo $edit_data['model'] ?>">
  70.         </section>
  71.         <section>
  72.             <label for="price">价格：</label>
  73.             <input type="text" name="price" id="price" value="<?php echo $edit_data['price'] ?>">
  74.         </section>
  75.         <section>
  76.             <label for="number">数量：</label>
  77.             <input type="text" name="number" id="number" value="<?php echo $edit_data['number'] ?>">
  78.         </section>
  79.         <section>
  80.             <label for="status">状态：</label>
  81.             <input type="text" name="status" id="status" value="<?php echo $edit_data['status'] ?>">
  82.         </section>
  83.         <div class='button'>
  84.             <button type="submit">提交</button>
  85.         </div>
  86.     </form>
  87. </body>
  89. </html>

效果图，编辑页面：

效果图，删除：

Correcting teacher：天蓬老师天蓬老师

Correction status：qualified

Teacher's comments：你的表格和表单配色有点辣眼睛呀, 不过, 写得非常不错
Statement of this Website
The copyright of this blog article belongs to the blogger. Please specify the address when reprinting! If there is any infringement or violation of the law, please contact admin@php.cn Report processing!
All comments Speak rationally on civilized internet, please comply with News Comment Service Agreement
0 comments
Author's latest blog post