php+mysql页面分页

ゞ浴缸里的玫瑰 2022-12-21 03:18 121阅读 0赞

数据库代码部分

/* Navicat MySQL Data Transfer Source Server : page Source Server Version : 50649 Source Host : 数据库的位置:3306 Source Database : page Target Server Type : MYSQL Target Server Version : 50649 File Encoding : 65001 Date: 2020-11-14 10:59:59 */
    
    SET FOREIGN_KEY_CHECKS=0;
    
    -- ----------------------------
    -- Table structure for test
    -- ----------------------------
    DROP TABLE IF EXISTS `test`;
    CREATE TABLE `test` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) NOT NULL,
      `sex` varchar(2) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8;
    
    -- ----------------------------
    -- Records of test
    -- ----------------------------
    INSERT INTO `test` VALUES ('1', '张三', '男');
    INSERT INTO `test` VALUES ('2', '李四', '女');
    INSERT INTO `test` VALUES ('3', '王五', '男');
    INSERT INTO `test` VALUES ('4', '赵六', '女');
    INSERT INTO `test` VALUES ('5', '小七', '男');
    INSERT INTO `test` VALUES ('6', '小八', '男');
    INSERT INTO `test` VALUES ('7', '小九', '男');
    INSERT INTO `test` VALUES ('8', '小十', '女');
    INSERT INTO `test` VALUES ('9', '小十一', '男');
    INSERT INTO `test` VALUES ('10', '张三', '男');
    INSERT INTO `test` VALUES ('11', '李四', '女');
    INSERT INTO `test` VALUES ('12', '王五', '男');
    INSERT INTO `test` VALUES ('13', '赵六', '女');
    INSERT INTO `test` VALUES ('14', '小七', '男');
    INSERT INTO `test` VALUES ('15', '小八', '男');
    INSERT INTO `test` VALUES ('16', '小九', '男');
    INSERT INTO `test` VALUES ('17', '小十', '女');
    INSERT INTO `test` VALUES ('18', '小十一', '男');

php代码部分

<!DOCTYPE html>
    <html lang="zh">
    <head>
    	<meta charset="UTF-8" />
    	<meta name="viewport" content="width=device-width, initial-scale=1.0" />
    	<meta http-equiv="X-UA-Compatible" content="ie=edge" />
    	<title>Document</title>
    </head>
    <body>
        <?php
            $page=$_GET['p'];
            
            
            //数据库的参数
            $host="地址";//你的数据库的地址
            $user="page";
            $pw="000000";
            $db="page";
            $PageSize=5;
            $ShowPage=3;
            
            //连接数据库
            $conn=mysqli_connect($host,$user,$pw);
            if(!$conn){ 
                echo "faild";
            }else{ 
                echo "success";
            }
        
            //选择要操作的数据库
            mysqli_select_db($conn,$db);
            mysqli_query($conn,"SET NAMES UTF8");
            //编写sql获取分页数据: select * from 表名 limit 起始位置 ,显示条数
              $sql="select * from test limit " .($page-1)*$PageSize .",$PageSize";
           
            //把sql语句传送到数据库
            $result=mysqli_query($conn,$sql);
    
            //处理我们的数据
            echo "<table border=1 cellspacing=0 width=15%>";
            echo "<tr><td>ID</td><td>名字</td><td>性别</td></tr>";
            foreach($result as $row){ 
                echo "<tr>";
                echo "<td>{$row['id']}</td>";
                echo "<td>{$row['name']}</td>";
                echo "<td>{$row['sex']}</td>";
                echo "</tr>";
            }
            echo "</table>";
            
            $to_sql="select count(*) from test";
            $to_result=mysqli_fetch_array(mysqli_query($conn,$to_sql));
            $to=$to_result[0];
            
            
            //计算页数
            $to_pages=ceil($to/$PageSize);
            
            //显示数据+分页条
            $page_banner="";
            //计算偏移量
            $pageffset=($ShowPage-1)/2;
            
            if($page>1){ 
                $page_banner.="<a href='".$_SERVER['PHP_SELF']."?p=1'>首页</a>";
                $page_banner.="<a href='".$_SERVER['PHP_SELF']."?p=".($page-1)."'>上一页 </a>";
            }
            
           
           //初始化数据
            $start=1;
             $end=$to_pages;
             if ($to_pages>$ShowPage){ 
             if($page>$pageffset+1){ 
             $page_banner.="...";
             }
             if ($page>$pageffset){ 
             $start=$page-$pageffset;
             $end=$to_pages>$page+$pageffset?$page+$pageffset:$to_pages;
             }else{ 
             $start=1;
             $end=$to_pages>$ShowPage?$ShowPage:$to_pages;
             }
             if ($page+$pageffset>$to_pages){ 
             $start=$start-($page+$pageffset-$end);
             }
             }
             for($i=$start;$i<=$end;$i++){ 
             $page_banner.="<a href='".$_SERVER['PHP_SELF']."?p=".($i)."'>{$i}</a>";
             }
             //尾部省略
             if ($to_pages>$ShowPage&&$to_pages>$page+$pageffset){ 
             $page_banner.="...";
             }
           
           
           
           
            
            if($page<$to_pages){ 
                $page_banner.="<a href='".$_SERVER['PHP_SELF']."?p=".($page+1)."'>下一页</a>";
                
                $page_banner.="<a href='".$_SERVER['PHP_SELF']."?p=".($to_pages)."'>尾页</a>";
            }
            
            
            $page_banner.="共{$to_pages}页";
           
            echo $page_banner;
            
            
        
        ?>
    </body>
    </html>

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTA3OTk3NA_size_16_color_FFFFFF_t_70_pic_center]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NTA3OTk3NA_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/1f63bebc5aa247c186407046c251cc86.png