将数据库中存储的用户信息渲染到网页中,分页展示。
编辑、新增、删除数据
效果如下:
mysql数据库创建表及准备数据
命令行输入mysql -u root -p
,进入mysql界面
执行以下sql语句创建表,以及新增数据
CREATE TABLE users ( id VARCHAR(10) not null primary key, name VARCHAR(10), gender TINYINT, birthday DATE, avatar VARCHAR(30), e-mail VARCHAR(30) );
INSERT INTO users VALUES ('Jason','王小明',1,'1996-03-24','/users/avatar/001.jpg', '123@qq.com');
INSERT INTO users VALUES ('12345678','李小红',0,'1996-10-09','/users/avatar/002.jpg', '456@qq.com');
INSERT INTO users VALUES ('asdfdsf','王大锤',1,'1996-10-15','/users/avatar/003.jpg', 'xyz@qq.com');
同时,在将准备好的头像图片文件传入对应的目录中。
业务需求及逻辑
分页展示
将存储在数据库中的数据逐条分页在网页中展示,每个页面展示8条数据,最后一页可以展示不足8条数据,展示完为止。可通过网页下方的分页按钮控制跳转到指定的页数。
通过get方式传递page参数(表示查看第几页),服务端根据page参数通过sql语句select * from users limit (page-1)*每页数据数, 每页数据数
查询相应的数据,并渲染成动态网页。
新增、编辑、删除
通过PHP操作mysql数据库实现
代码实现
分页展示 user_list.php
<?php
$page = 1;
$item_num_each_page = 8;
if(isset($_GET['page'])){
$page = $_GET['page'];
}
$limit_start = ($page - 1) * $item_num_each_page;
$connection = @mysqli_connect('127.0.0.1', 'root', 'passwd', 'users');
if(! $connection){
exit('<h1>连接数据库失败!</h1>');
}
$query = mysqli_query($connection, 'select * from users limit '.$limit_start.','.$item_num_each_page.';');
if(! $query){
exit('<h1>查询失败</h1>');
}
$query_count = mysqli_query($connection, 'select count(1) as count from users;');
$total_num = (int)mysqli_fetch_assoc($query_count)['count'];
$page_num = (int)ceil($total_num / $item_num_each_page);
$data = [];
while($row = mysqli_fetch_assoc($query)){
$data[] = $row;
}
?>
<!DOCTYPE html>
<html lang="zh">
<head>
<meta charset="UTF-8">
<link rel="stylesheet" href="./bootstrap-3.3.7-dist/css/bootstrap.min.css">
<script src="./bootstrap-3.3.7-dist/js/jquery-3.4.1.min.js"></script>
<script src="./bootstrap-3.3.7-dist/js/bootstrap.min.js"></script>
<title>用户列表</title>
<style>
table {
font-size: 1.5em;
}
.panel-heading {
font-size: 2.5em;
}
tbody tr td {
line-height: 50px !important;
}
tbody tr td img {
width: 50px;
}
div.panel {
width: 70%;
margin: 100px auto 0;
}
nav{
text-align: center;
}
</style>
</head>
<body>
<div class="panel panel-default">
<div class="panel-heading" >
用户管理
<a href="add_user.php" class="btn btn-success col-md-offset-9">添加</a>
</div>
<table class="table">
<thead>
<tr>
<th>id</th>
<th>姓名</th>
<th>头像</th>
<th>性别</th>
<th>年龄</th>
<th>电子邮箱</th>
</tr>
</thead>
<tbody>
<?php foreach($data as $items): ?>
<tr>
<td><?php echo $items['id']; ?></td>
<td><?php echo $items['name']; ?></td>
<td><img src="<?php echo isset($items['avatar'])?$items['avatar']:"/users/imgs/avatar.jpg"; ?>" alt="" class="img-circle" ></td>
<td><?php echo $items['gender']=='1'?'男':'女'; ?></td>
<td><?php echo isset($items['birthday'])?(int)date("Y")-(int)explode("-",$items['birthday'])[0]:'' ?></td>
<td><?php echo $items['email'] ?></td>
<td>
<a href="edit_user.php?id=<?php echo $items['id']?>" class="btn btn-default">编辑</a>
<a href="del_user.php?id=<?php echo $items['id']?>" class="btn btn-danger">删除</a>
</td>
</tr>
<?php endforeach ?>
</tbody>
</table>
</div>
<nav aria-label="Page navigation ">
<ul class="pagination pagination-lg">
<li <?php echo $page<=1?'class="disabled"':'' ?>>
<?php if($page>1): ?>
<a href="<?php echo $_SERVER['PHP_SELF'].'?page='.($page-1)?>" aria-label="Previous">
<span aria-hidden="true">«</span>
</a>
<?php else: ?>
<span aria-hidden="true">«</span>
<?php endif ?>
</li>
<?php for($i=1;$i<=$page_num;$i++): ?>
<li <?php echo $i==$page?'class="active"':'' ?>>
<?php echo '<a href='.$_SERVER['PHP_SELF'].'?page='.$i.'>'.$i.'</a>' ?>
</li>
<?php endfor?>
<li <?php echo $page>=$page_num?'class="disabled"':'' ?>>
<?php if($page<$page_num): ?>
<a href="<?php echo $_SERVER['PHP_SELF'