mysql实现留言板分页

<?php
//显示所有数据
$link = mysqli_connect('localhost','root','123456');
if (mysqli_connect_errno($link)>0) {
echo mysqli_connect_error($link);exit;
}
mysqli_select_db($link,'ss34');
mysqli_set_charset($link,'utf8');
/**********************分页开始***********************/
//1.每页显示多少条
$num = 10;
//2.总条数
$sql="SELECT COUNT(id) as total FROM info";
$result = mysqli_query($link,$sql);
if($result && mysqli_num_rows($result)>0){
$row = mysqli_fetch_assoc($result);
}
//得到总条数
//var_dump($row);
//将得到的数组赋值给变量
$total = $row['total'];

//echo $total;

//3. 总页数
$amount = ceil($total/$num);
//echo $amount;

//4。获取当前页码
$page = isset($_GET['page'])?$_GET['page']:1;

//判断当前页码数的范围
if ($page < 1 ) {
$page = 1;
}
if($page > $amount){
$page = $amount;
}
//上一页
$prev = $page -1;
//下一页
$next = $page +1;

//5.偏移量$offset = (当前页码-1)*每页显示条数
$offset = ($page-1)*$num;

$sql="SELECT id,name,sex,age,city FROM info LIMIT {$offset},{$num}";

//$sql="SELECT id,name,sex,age,city FROM info";

$result = mysqli_query($link,$sql);

if ($result && mysqli_num_rows($result)>0) {
  //声明一个新数组
  $userlist = array();
  while($row = mysqli_fetch_assoc($result)){
   $userlist[]=$row;
  }
}
//var_dump($userlist);
//定义一个变量用来声明编号
$i=1;

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Document</title>
</head>
<body>
<h3><a href="add.html">添加用户</a></h3>
<hr>
<table border="1" width="800">
  <caption><h3>学员管理系统v1.0</h3></caption>
  <tr>
   <th>编号</th>
   <th>姓名</th>
   <th>年龄</th>
   <th>性别</th>
   <th>城市</th>
   <th>操作</th>
  </tr>
  <?php foreach($userlist as $value){?>
  <tr>
   <td><?php echo $offset+$i++?></td>
   <td><?php echo $value['name']?></td>
   <td><?php echo $value['age']?></td>
   <td>
    <?php
     $sex = $value['sex'];
     switch($sex){
      case 0:
       echo '女';
       break;
      case 1:
       echo '男';
       break;
      case 2:
       echo '保密';
       break;
      case 3:
       echo '琦琦';
       break;
      default:
       echo '人妖';
     }

   </td>
   <td><?php echo $value['city']?></td>
   <td><a href="./del.php?id=<?php echo $value['id']?>" onclick="return confirm('数据无价谨慎操作')">删除</a>|<a href="edit.php?id=<?php echo $value['id']?>">修改</a></td>
  </tr>
  <?php } ?>
  <tr>
   <td colspan="6"><a href="index.php?page=1">首页</a>| <a href="index.php?page=<?php echo $prev?>">上一页</a>| <a href="index.php?page=<?php echo $next?>">下一页</a> | <a href="index.php?page=<?php echo $amount ?>">尾页</a> </td>
  </tr>
</table>
</body>
</html>

相关文章