以后台的思维来理解网上的一切

注册账号
更新自己信息
销毁个人账号
查看东西
看淘宝上卖什么
本质上就是在对数据库进行CRUD

复习前面学过的SQL语句

查看用户管理项目的最终效果，理清需求

回顾我们之前的案例

水果案例
- 以txt文本来存储我们的数据
- 特点
  1. 读取的时候需要使用explode拆分数据
  2. 写回去的时候，需要使用implode合并数据
  3. 操作起来十分的麻烦
  4. 单个文件如果数据量太大了，操作起来十分的不方便，而且慢
注册案例
- 以json文件来存储我们的数据
- 特点
  1. 使用json_decode,json_encode来操作数据
  2. 单个文件如果数据量太大了，操作起来十分的不方便
用户管理系统
- 以数据库来存储我们的数据
- 特点
  1. 数据有专门的数据库服务器进行管理维护备份，数据十分的安全，效率十分的高
  2. 基本上所有的我们见到的动态网站都是用数据库来维护管理数据的(主流有mysql,oracle,sqlServer,mongoDB)
用一张图来理解客户端、服务器、数据库之间的关系

new lesson

01-php中建立数据库连接.avi
02-php中执行增加修改和删除操作.avi
03-php中查询结果集(上).avi
04-php中查询结果集(下).avi
05-php操作数据库的回顾.avi
06-操作简易封装.avi

用户管理系统

提取头部

观察发现两个页面的头部是一样的
使用include_once把导航部分提取出去

查询

观察列表页，设计数据库表的结构
- 在mybase数据库中创建表demo
- demo字段如下
  1. id int primary key auto increment not null
  2. src varchar(255)
  3. name varchar(20)
  4. gender char(2)
  5. age int

DROP TABLE IF EXISTS `demo`;
CREATE TABLE `demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `src` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `gender` char(2) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

填充假数据

INSERT INTO `demo` VALUES ('1', './assets/img/icon-04.png', '小明', '男', '20');
INSERT INTO `demo` VALUES ('2', './assets/img/icon-05.png', '小李', '女', '21');
INSERT INTO `demo` VALUES ('3', './assets/img/icon-06.png', '小马', '男', '22');
INSERT INTO `demo` VALUES ('4', './assets/img/icon-07.png', '小宋', '女', '19');

再次思考：上面的设计是否合理
- 不合理,因为一个人的年纪会一年一年的大，如果写死了，那么明年打开这个页面就还是这么大
重新设计数据库表的结构
- 在mybase数据库中创建表demo
- demo字段如下
  1. id int primary key auto increment not null
  2. src varchar(255)
  3. name varchar(20)
  4. gender char(2)
  5. birthday date

CREATE TABLE `ceshi` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `src` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `gender` char(2) DEFAULT '男',
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `ceshi` VALUES ('1', './assets/img/icon-04.png', '小明', '男', '2010-06-03');
INSERT INTO `ceshi` VALUES ('2', './assets/img/icon-05.png', '小李', '男', '2011-06-03');
INSERT INTO `ceshi` VALUES ('3', './assets/img/icon-06.png', '小马', '女', '1999-06-03');
INSERT INTO `ceshi` VALUES ('4', './assets/img/icon-07.png', '小王', '女', '2000-06-03');

创建mysql.php文件，封装查询函数、增删改函数

<?php
/**
 * select 查询操作
 * @param mixed $sql 示例参数："select * from demo";
 * @return mixed 如果返回值为false,则说明查询失败，否则返回值为数组，建立使用var_dump来打印返回值
 */
function select($sql)
{
    //建立数据库连接 主机域名localhost 账号root 密码root 连接的数据库mybase 
    $conn = mysqli_connect('localhost', 'root', 'root', 'mybase');
    if (!$conn) {
        die('连接失败');
    }
    //设置编码为utf8
    mysqli_set_charset($conn, 'utf8');
    $res = mysqli_query($conn, $sql);
    if ($res && mysqli_num_rows($res) > 0) {
        while ($arr = mysqli_fetch_assoc($res)) {
            $result[] = $arr;
        }
        mysqli_close($conn);//释放连接
        return $result;
    } else {
        mysqli_close($conn);//释放连接
        //不管操作失败还是没获取数据统一返回false
        return false;
    }
}

/**
 * opt 增删改操作相关函数
 * @param mixed $sql 
 * @return mixed 如果返回值为true，说明操作成功，如果返回值为false，说明操作失败
 */
function opt($sql)
{
    $conn = mysqli_connect('localhost', 'root', 'root', 'mybase');
    mysqli_set_charset($conn, 'utf8');
    $res = mysqli_query($conn, $sql);
    if ($res && mysqli_affected_rows($conn) > 0) {
        return true;//表示成功影响了行数，操作成功
    } else {
        return false;
    }
    mysqli_close($conn);
}
?>

在static.php中引入mysql.php,查询数据并打印出来

include_once "./mysql.php";
$res = select("select * from demo");
var_dump($res);

渲染列表

<?php
include_once "./mysql.php";
$res = select("select * from demo");
// var_dump($res);
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>XXX管理系统</title>
  <link rel="stylesheet" href="assets/css/bootstrap.css">
  <link rel="stylesheet" href="assets/css/style.css">
</head>
<body>
  <?php include_once "./_nav.php"; ?>
  <main class="container">
    <h1 class="heading">用户管理 <a class="btn btn-link btn-sm" href="add.html">添加</a></h1>
    <table class="table table-hover">
      <thead>
        <tr>
          <th>#</th>
          <th>头像</th>
          <th>姓名</th>
          <th>性别</th>
          <th>年龄</th>
          <th class="text-center" width="140">操作</th>
        </tr>
      </thead>
      <tbody>
        <?php if(is_array($res)){ ?>
          <?php foreach($res as $key=>$value){ ?>
          <tr>
            <th scope="row">
              <?php echo $value['id'];?>
            </th>
            <td>
              <img src="<?php echo $value['src']; ?>" class="rounded">
            </td>
            <td>
              <?php echo $value['name']; ?>
            </td>
            <td>
              <?php echo $value['gender']; ?>
            </td>
            <td>
              <?php echo $value['birthday']; ?>
            </td>
            <td class="text-center">
              <a href="#" class="btn btn-info btn-sm">编辑</a>
              <a href="#" class="btn btn-danger btn-sm">删除</a>
            </td>
          </tr>
          <?php } ?>
        <?php }else{ ?>
        <tr>
          <td colspan='6'>暂无数据</td>
        </tr>
        <?php } ?>
      </tbody>
    </table>
  </main>
</body>
</html>

我们不应该显示生日，而是应该显示年龄

<?php
header('content-type:text/html;charset=utf-8');
include_once "./mysql.php";
$res = select("select * from ceshi");
var_dump($res);
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>XXX管理系统</title>
  <link rel="stylesheet" href="assets/css/bootstrap.css">
  <link rel="stylesheet" href="assets/css/style.css">
</head>
<body>
  <?php include_once "./_nav.php";?>
  <main class="container">
    <h1 class="heading">用户管理 <a class="btn btn-link btn-sm" href="add.html">添加</a></h1>
    <table class="table table-hover">
      <thead>
        <tr>
          <th>#</th>
          <th>头像</th>
          <th>姓名</th>
          <th>性别</th>
          <th>年龄</th>
          <th class="text-center" width="140">操作</th>
        </tr>
      </thead>
      <?php if (is_array($res)) { ?>
          <?php foreach ($res as $key => $value) { ?>
          <tr>
            <th scope="row">
              <?php echo $value['id']; ?>
            </th>
            <td>
              <img src="<?php echo $value['src']; ?>" class="rounded">
            </td>
            <td>
              <?php echo $value['name']; ?>
            </td>
            <td>
              <?php echo $value['gender']; ?>
            </td>
            <td>
              <?php
                $age = ceil((time() - strtotime($value['birthday'])) / (365 * 24 * 60 * 60));
              ?>
              <?php echo $age; ?>
            </td>
            <td class="text-center">
              <a href="#" class="btn btn-info btn-sm">编辑</a>
              <a href="#" class="btn btn-danger btn-sm">删除</a>
            </td>
          </tr>
          <?php 
        } ?>
        <?php 
      } else { ?>
        <tr>
          <td colspan='6'>暂无数据</td>
        </tr>
        <?php 
      } ?>
      </tbody>
    </table>
  </main>
</body>
</html>

删除

修改static.php中的删除超链接

<a href="del.php?id=<?php echo $value['id'] ?>" class="btn btn-danger btn-sm">删除</a>

创建del.php文件，接收GET传递过来的参数

header('content-type:text/html;charset=utf-8');
print_r($_GET);

在del.php当中文件加载引入mysql.php

header('content-type:text/html;charset=utf-8');
- // print_r($_GET);
+ include_once "./mysql.php";

构建删除SQL语句

delete from ceshi where id = $id

完成删除逻辑

header('content-type:text/html;charset=utf-8');
// print_r($_GET);
$id = $_GET['id'];
include_once "./mysql.php";
$sql = "delete from ceshi where id = $id";
$res = opt($sql);
// var_dump($res);
if ($res) {
    echo '删除成功';
} else {
    echo '删除失败';
}
header('refresh:2;url=static.php');

新增

修改static.php中的添加的超链接为add.php
添加form表单的action,method,enctype属性

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" enctype="multipart/form-data">

接收打印POST、FILES数据

header('content-type:text/html;charset=utf-8');
if($_SERVER['REQUEST_METHOD'] == 'POST'){
  print_r($_POST);
  print_r($_FILES);
}

上传文件功能

header('content-type:text/html;charset=utf-8');
if($_SERVER['REQUEST_METHOD'] == 'POST'){
  // print_r($_POST);
  // print_r($_FILES);
  if(isset($_FILES['img'])&&$_FILES['img']['error'] == 0){
    $file = $_FILES['img'];
    $name = time().rand();
    $ext = strrchr($file['name'],'.');
    $fileName = './assets/img/'.$name.$ext;
    move_uploaded_file($file['tmp_name'],$fileName);
  }
}

引入mysql.php,构建增加SQL语句，完成相应的逻辑

<?php
header('content-type:text/html;charset=utf-8');
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
  // print_r($_POST);
  // print_r($_FILES);
  if (isset($_FILES['img']) && $_FILES['img']['error'] == 0) {
    $file = $_FILES['img'];
    $name = time() . rand();
    $ext = strrchr($file['name'], '.');
    $fileName = './assets/img/' . $name . $ext;
    move_uploaded_file($file['tmp_name'], $fileName);
    include_once "./mysql.php";
    $name = $_POST['name'];
    $gender = $_POST['gender'];
    $birthday = $_POST['birthday'];
    $src = $fileName;
    $sql = "insert into ceshi values(null,'$src','$name','$gender','$birthday')";
    $res = opt($sql);
    var_dump($res);
  }
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>XXX管理系统</title>
  <link rel="stylesheet" href="assets/css/bootstrap.css">
  <link rel="stylesheet" href="assets/css/style.css">
</head>
<body>
  <?php include_once "./_nav.php"; ?>
  <main class="container">
    <h1 class="heading">添加用户</h1>    
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" enctype="multipart/form-data">
      <div class="form-group">
        <label for="name">姓名</label>
        <input type="text" class="form-control" id="name" name="name">
      </div>
      <div class="form-group">
        <label for="gender">性别</label>
        <select class="form-control" id="gender" name="gender">
          <option value="-1">请选择性别</option>
          <option value="男">男</option>
          <option value="女">女</option>
        </select>
      </div>
      <div class="form-group">
        <label for="birthday">生日</label>
        <input type="date" class="form-control" id="birthday" name="birthday">
      </div>
      <div class="form-group">
        <label for="img">头像</label>
        <input type="file" class="form-control" id="img" name="img">
      </div>
      <button class="btn btn-primary">保存</button>
    </form>
  </main>
</body>
</html>

如果成功，跳转到列表页，如果失败，跳转到当前页

if($res){
  echo '增加成功';
  header('refresh:2;url=static.php');
}else{
  echo '增加失败';
  header('refresh:2;url=add.php');
}

编辑

修改列表页中编辑的超链接为edit.php?id=<?php echo $value['id'] ?>
创建edit.php文件，接收GET数据

header('content-type:text/html;charset=utf-8');
print_r($_GET['id']);

拷贝增加的html到编辑页面
根据当前传过来的id去数据库中取对应的数据
- 引入数据库mysql.php文件
- 构建SQL语句
- 查询数据库
- 打印数据

<?php 
header('content-type:text/html;charset=utf-8');
// print_r($_GET['id']);
include_once "./mysql.php";
$id = $_GET['id'];
$sql = "select * from ceshi where id = $id";
$res = select($sql);
print_r($res);
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>XXX管理系统</title>
  <link rel="stylesheet" href="assets/css/bootstrap.css">
  <link rel="stylesheet" href="assets/css/style.css">
</head>
<body>
  <?php include_once "./_nav.php"; ?>
  <main class="container">
    <h1 class="heading">添加用户</h1>    
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" enctype="multipart/form-data">
      <div class="form-group">
        <label for="name">姓名</label>
        <input type="text" class="form-control" id="name" name="name">
      </div>
      <div class="form-group">
        <label for="gender">性别</label>
        <select class="form-control" id="gender" name="gender">
          <option value="-1">请选择性别</option>
          <option value="男">男</option>
          <option value="女">女</option>
        </select>
      </div>
      <div class="form-group">
        <label for="birthday">生日</label>
        <input type="date" class="form-control" id="birthday" name="birthday">
      </div>
      <div class="form-group">
        <label for="img">头像</label>
        <input type="file" class="form-control" id="img" name="img">
      </div>
      <button class="btn btn-primary">保存</button>
    </form>
  </main>
</body>
</html>

渲染页面默认值

<?php 
header('content-type:text/html;charset=utf-8');
// print_r($_GET['id']);
include_once "./mysql.php";
$id = $_GET['id'];
$sql = "select * from ceshi where id = $id";
$res = select($sql);
$data = $res[0];
// print_r($res);
?>
<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>XXX管理系统</title>
  <link rel="stylesheet" href="assets/css/bootstrap.css">
  <link rel="stylesheet" href="assets/css/style.css">
</head>
<body>
  <?php include_once "./_nav.php"; ?>
  <main class="container">
    <h1 class="heading">添加用户</h1>    
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" enctype="multipart/form-data">
      <div class="form-group">
        <label for="name">姓名</label>
        <input type="text" class="form-control" id="name" name="name" value="<?php echo $data['name']; ?>">
      </div>
      <div class="form-group">
        <label for="gender">性别</label>
        <select class="form-control" id="gender" name="gender">
          <option value="-1">请选择性别</option>
          <option value="男" <?php echo $data['gender']=='男'?'selected':''; ?>>男</option>
          <option value="女" <?php echo $data['gender'] == '女' ? 'selected' : ''; ?>>女</option>
        </select>
      </div>
      <div class="form-group">
        <label for="birthday">生日</label>
        <input type="date" class="form-control" id="birthday" name="birthday" value="<?php echo $data['birthday']; ?>">
      </div>
      <div class="form-group">
        <label for="img">头像</label>
        <input type="file" class="form-control" id="img" name="img">
      </div>
      <button class="btn btn-primary">保存</button>
    </form>
  </main>
</body>
</html>

修改edit.php的form表单的action为edit_handler.php

<form action="edit_handler.php" method="post" enctype="multipart/form-data">

添加隐藏域，把id传给后台

<input type="hidden" name="id" value="<?php echo $data['id']; ?>">

在edit_handler.php中接收数据打印测试

header('content-type:text/html;charset=utf-8');
print_r($_POST);
print_r($_FILES);

上传文件

header('content-type:text/html;charset=utf-8');
// print_r($_POST);
// print_r($_FILES);
if(isset($_FILES['img']) && $_FILES['img']['error'] == 0){
    $file = $_FILES['img'];
    $name = time().rand();
    $ext = strrchr($file['name'],'.');
    $fileName = './assets/img/'.$name.$ext;
    move_uploaded_file($file['tmp_name'],$fileName);
}

把数据更新到数据库当中
- 引入mysql.php
- 构建update语句
- 使用opt函数插入来数据库当中
- 使用var_dump打印测试是否成功

header('content-type:text/html;charset=utf-8');
// print_r($_POST);
// print_r($_FILES);
if(isset($_FILES['img']) && $_FILES['img']['error'] == 0){
    $file = $_FILES['img'];
    $name = time().rand();
    $ext = strrchr($file['name'],'.');
    $fileName = './assets/img/'.$name.$ext;
    move_uploaded_file($file['tmp_name'],$fileName);
}
include_once "./mysql.php";
$id = $_POST['id'];
$name = $_POST['name'];
$gender = $_POST['gender'];
$birthday = $_POST['birthday'];
$src = $fileName;
$sql = "update ceshi set src='$src',name='$name',gender='$gender',birthday='$birthday' where id = $id";
$res = opt($sql);
var_dump($res);

如果成功或失败则跳转回列表页

header('content-type:text/html;charset=utf-8');
// print_r($_POST);
// print_r($_FILES);
if(isset($_FILES['img']) && $_FILES['img']['error'] == 0){
    $file = $_FILES['img'];
    $name = time().rand();
    $ext = strrchr($file['name'],'.');
    $fileName = './assets/img/'.$name.$ext;
    move_uploaded_file($file['tmp_name'],$fileName);
}
include_once "./mysql.php";
$id = $_POST['id'];
$name = $_POST['name'];
$gender = $_POST['gender'];
$birthday = $_POST['birthday'];
$src = $fileName;
$sql = "update ceshi set src='$src',name='$name',gender='$gender',birthday='$birthday' where id = $id";
$res = opt($sql);
// var_dump($res);
if($res){
    echo '编辑成功';
}else{
    echo '编辑失败';
}
header('refresh:2;url=static.php');

重新思考上面的代码存在的问题
- 万一用户并没有上传新的图片怎么办

header('content-type:text/html;charset=utf-8');
// print_r($_POST);
// print_r($_FILES);
if(isset($_FILES['img']) && $_FILES['img']['error'] == 0){
    $file = $_FILES['img'];
    $name = time().rand();
    $ext = strrchr($file['name'],'.');
    $fileName = './assets/img/'.$name.$ext;
    move_uploaded_file($file['tmp_name'],$fileName);
}
include_once "./mysql.php";
$id = $_POST['id'];
$name = $_POST['name'];
$gender = $_POST['gender'];
$birthday = $_POST['birthday'];
if(isset($fileName)){
    $src = $fileName;
    $sql = "update ceshi set src='$src',name='$name',gender='$gender',birthday='$birthday' where id = $id";
}else{
    $sql = "update ceshi set name='$name',gender='$gender',birthday='$birthday' where id = $id";
}
$res = opt($sql);
// var_dump($res);
if($res){
    echo '编辑成功';
}else{
    echo '编辑失败';
}
header('refresh:2;url=static.php');

php后续学习

https://github.com/zhengwei1949/myblog/issues/130

zhengwei1949 / myblog

用户管理系统思路 #143

以后台的思维来理解网上的一切

复习前面学过的SQL语句

回顾我们之前的案例

new lesson

用户管理系统

提取头部

查询

删除

新增

编辑

php后续学习