案例
基于数据库的增删改查
1. 列表功能
查询数据
<?php // 1. 建立连接 $conn = mysqli_connect('localhost', 'root', '123456', 'test'); mysqli_set_charset($conn, 'utf8'); if (!$conn) { exit('<h1>连接数据库失败</h1>'); } // 2. 开始查询 $query = mysqli_query($conn, 'select * from users;'); if (!$query) { exit('<h1>查询数据失败</h1>'); } // 3. 遍历结果集 // while ($item = mysqli_fetch_assoc($query)) { // $data[] = $item; // } ?> <!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> <nav class="navbar navbar-expand navbar-dark bg-dark fixed-top"> <a class="navbar-brand" href="#">XXX管理系统</a> <ul class="navbar-nav mr-auto"> <li class="nav-item active"> <a class="nav-link" href="index.html">用户管理</a> </li> <li class="nav-item"> <a class="nav-link" href="#">商品管理</a> </li> </ul> </nav> <main class="container"> <h1 class="heading">用户管理 <a class="btn btn-link btn-sm" href="add.php">添加</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 while ($item = mysqli_fetch_assoc($query)): ?> <tr> <th scope="row"><?php echo $item['id'] ?></th> <td><img src="<?php echo $item['avatar']; ?>" class="rounded" alt="<?php echo $item['name']; ?>"></td> <td><?php echo $item['name']; ?></td> <td><?php echo $item['gender'] == 0 ? '♀' : '♂'; ?></td> <td><?php echo $item['birthday']; ?></td> <td class="text-center"> <a class="btn btn-info btn-sm" href="edit.php?id=<?php echo $item['id'] ?>">编辑</a> <a class="btn btn-danger btn-sm" href="delete.php?id=<?php echo $item['id'] ?>">删除</a> </td> </tr> <?php endwhile ?> </tbody> </table> <ul class="pagination justify-content-center"> <li class="page-item"><a class="page-link" href="#">«</a></li> <li class="page-item"><a class="page-link" href="#">1</a></li> <li class="page-item"><a class="page-link" href="#">2</a></li> <li class="page-item"><a class="page-link" href="#">3</a></li> <li class="page-item"><a class="page-link" href="#">»</a></li> </ul> </main> </body> </html>
2. 增加数据
增加数据
<?php function add_user() { // 验证非空 if (empty($_POST['name'])) { $GLOBALS['error_message'] = '请输入姓名'; return; } if (!(isset($_POST['gender']) && $_POST['gender'] !== '-1')) { $GLOBALS['error_message'] = '请选择性别'; return; } if (empty($_POST['birthday'])) { $GLOBALS['error_message'] = '请输入日期'; return; } // 取值 $name = $_POST['name']; $gender = $_POST['gender']; $birthday = $_POST['birthday']; // 接收文件并验证 if (empty($_FILES['avatar'])) { $GLOBALS['error_message'] = '请上传头像'; return; } $ext = pathinfo($_FILES['avatar']['name'], PATHINFO_EXTENSION); // => jpg $target = '../uploads/avatar-' . uniqid() . '.' . $ext; if (!move_uploaded_file($_FILES['avatar']['tmp_name'], $target)) { $GLOBALS['error_message'] = '上传头像失败'; return; } $avatar = substr($target, 2); // var_dump($name); // var_dump($gender); // var_dump($birthday); // var_dump($avatar); // 保存 // 1. 建立连接 $conn = mysqli_connect('localhost', 'root', '123456', 'test'); if (!$conn) { $GLOBALS['error_message'] = '连接数据库失败'; return; } // var_dump("insert into users values (null, '{$name}', {$gender}, '{$birthday}', '{$avatar}');"); // 2. 开始查询 $query = mysqli_query($conn, "insert into users values (null, '{$name}', {$gender}, '{$birthday}', '{$avatar}');"); if (!$query) { $GLOBALS['error_message'] = '查询过程失败'; return; } $affected_rows = mysqli_affected_rows($conn); if ($affected_rows !== 1) { $GLOBALS['error_message'] = '添加数据失败'; return; } // 响应 header('Location: index.php'); } if ($_SERVER['REQUEST_METHOD'] === 'POST') { add_user(); } ?> <!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> <nav class="navbar navbar-expand navbar-dark bg-dark fixed-top"> <a class="navbar-brand" href="#">XXX管理系统</a> <ul class="navbar-nav mr-auto"> <li class="nav-item active"> <a class="nav-link" href="index.html">用户管理</a> </li> <li class="nav-item"> <a class="nav-link" href="#">商品管理</a> </li> </ul> </nav> <main class="container"> <h1 class="heading">添加用户</h1> <?php if (isset($error_message)): ?> <div class="alert alert-warning"> <?php echo $error_message; ?> </div> <?php endif ?> <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" enctype="multipart/form-data" autocomplete="off"> <div class="form-group"> <label for="avatar">头像</label> <input type="file" class="form-control" id="avatar" name="avatar"> </div> <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="1">男</option> <option value="0">女</option> </select> </div> <div class="form-group"> <label for="birthday">生日</label> <input type="date" class="form-control" id="birthday" name="birthday"> </div> <button class="btn btn-primary">保存</button> </form> </main> </body> </html>
3. 删除数据
删除数据
<?php // 接收要删除的数据 ID if (empty($_GET['id'])) { exit('<h1>必须传入指定参数</h1>'); } $id = $_GET['id']; // => 1,2,3 // 1. 建立连接 $conn = mysqli_connect('localhost', 'root', '123456', 'test'); if (!$conn) { exit('<h1>连接数据库失败</h1>'); } // 2. 开始查询 $query = mysqli_query($conn, 'delete from users where id in (' . $id . ');'); if (!$query) { exit('<h1>查询数据失败</h1>'); } $affected_rows = mysqli_affected_rows($conn); if ($affected_rows <= 0) { exit('<h1>删除失败</h1>'); } header('Location: index.php');
4. 修改数据
项目思路图
修改数据
<?php // 接收要修改的数据 ID if (empty($_GET['id'])) { exit('<h1>必须传入指定参数</h1>'); } $id = $_GET['id']; // 1. 建立连接 $conn = mysqli_connect('localhost', 'root', '123456', 'test'); mysqli_set_charset($conn,'utf8'); if (!$conn) { exit('<h1>连接数据库失败</h1>'); } // 2. 开始查询 // 因为ID 是唯一的 那么找到第一个满足条件的就不用在继续了 查询最后 limit 1就可以了 $query = mysqli_query($conn, "select * from users where id = {$id} limit 1;"); if (!$query) { exit('<h1>查询数据失败</h1>'); } // 已经查询到的当前数据 $user = mysqli_fetch_assoc($query); if (!$user) { exit('<h1>找不到你要编辑的数据</h1>'); } function edit () { global $user; // 验证非空 if (empty($_POST['name'])) { $GLOBALS['error_message'] = '请输入姓名'; return; } if (!(isset($_POST['gender']) && $_POST['gender'] !== '-1')) { $GLOBALS['error_message'] = '请选择性别'; return; } if (empty($_POST['birthday'])) { $GLOBALS['error_message'] = '请输入日期'; return; } // 取值 $user['name'] = $_POST['name']; $user['gender'] = $_POST['gender']; $user['birthday'] = $_POST['birthday']; // 有上传就修改 if (isset($_FILES['avatar']) && $_FILES['avatar']['error'] === UPLOAD_ERR_OK) { // 用户上传了新头像 -> 用户希望修改头像 $ext = pathinfo($_FILES['avatar']['name'], PATHINFO_EXTENSION); $target = '../uploads/avatar-' . uniqid() . '.' . $ext; if (!move_uploaded_file($_FILES['avatar']['tmp_name'], $target)) { $GLOBALS['error_message'] = '上传头像失败'; return; } $user['avatar'] = substr($target, 2); } // $user => 修改过后的信息 // TODO: 将数据更新回数据库 // $conn1 = mysqli_connect('localhost', 'root', '123456', 'test'); mysqli_set_charset($conn1,'utf8'); // var_dump("update users set name='{$user['name']}',gender={$user['gender']},birthday={$user['birthday']} where id={$_GET['id']};"); $query1 = mysqli_query($conn1,"update users set name='{$user['name']}',gender={$user['gender']},birthday='{$user['birthday']}' where id={$_GET['id']};"); $affected_rows1 = mysqli_affected_rows($conn1); var_dump("相应前面e行"); // var_dump($affected_rows1); // if ($affected_rows1 !== 1) { // $GLOBALS['error_message'] = '修改数据失败'; // return; // } var_dump("相应前面一行"); header('Location: index.php'); } if ($_SERVER['REQUEST_METHOD'] === 'POST') { edit(); } ?> <!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> <nav class="navbar navbar-expand navbar-dark bg-dark fixed-top"> <a class="navbar-brand" href="#">XXX管理系统</a> <ul class="navbar-nav mr-auto"> <li class="nav-item active"> <a class="nav-link" href="index.html">用户管理</a> </li> <li class="nav-item"> <a class="nav-link" href="#">商品管理</a> </li> </ul> </nav> <main class="container"> <h1 class="heading">编辑“<?php echo $user['name']; ?>”</h1> <form action="<?php echo $_SERVER['PHP_SELF']; ?>?id=<?php echo $user['id']; ?>" method="post" enctype="multipart/form-data"> <!-- <input type="hidden" id="id" value="<?php echo $user['id']; ?>"> --> <img src="<?php echo $user['avatar']; ?>" alt=""> <?php if (isset($error_message)): ?> <div class="alert alert-warning"> <?php echo $error_message; ?> </div> <?php endif ?> <div class="form-group"> <label for="avatar">头像</label> <!-- 文件域不能设置默认值 --> <input type="file" class="form-control" id="avatar" name="avatar"> </div> <div class="form-group"> <label for="name">姓名</label> <input type="text" class="form-control" id="name" name="name" value="<?php echo $user['name']; ?>"> </div> <div class="form-group"> <label for="gender">性别</label> <select class="form-control" id="gender" name="gender"> <option value="-1">请选择性别</option> <option value="1"<?php echo $user['gender'] === '1' ? ' selected': ''; ?>>男</option> <option value="0"<?php echo $user['gender'] === '0' ? ' 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 $user['birthday']; ?>"> </div> <button class="btn btn-primary">保存</button> </form> </main> </body> </html>