Community Learn Tools Library Leisure
search
English
Home > List of blog posts > PHP MySQLi 增删查改（CURD）实例——面向过程方法
Blogger Information
Blog 33
fans 0
comment 2
visits 42385
Special Recommendation
More>
Related recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
PHP MySQLi 增删查改（CURD）实例——面向过程方法
hanyufeng的博客
Original
1106 people have browsed it

文档结构：

通用文件头部、底部文件保存在inc目录，css、js保存在static 文件

L01.02.png

数据库结构及数据：

创建数据库

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(40) NOT NULL,
  `memo` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

导入数据

INSERT INTO `user` (`id`, `name`, `email`, `password`, `memo`) VALUES
(1, '张三', 'zhangsan1@company.com', '7c4a8d09ca3762af61e59520943dc26494f8941b', NULL),
(2, '李四', 'lisi@company.com', 'dd5fef9c1c1da1394d6d34b248c51be2ad740840', NULL),
(3, '王五', 'wangwu@company.com', '601f1889667efaebb33b8c12572835da3f027f78', NULL);

示例源码：

通用文件：

header.php

<!doctype html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
 content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
<!--    注意：相对路径的写法，应该以调用header.php的文件为起始位置，不是header.php文件本身-->
<!--    或者使用相对于网站根目录的路径-->
 <link rel="stylesheet" href="static/bootstrap-3.3.7-dist/css/bootstrap.css">
    <script src="static/jquery-3.2.1.min.js"></script>
    <script src="static/bootstrap-3.3.7-dist/js/bootstrap.js"></script>
    <title><?php echo  isset($pageTitle)?$pageTitle:'注册'?></title>
</head>
<body>
<div class="container">
    <!--头部导航开始-->
 <div class="row">
        <div class="col-md-12">
            <div class="nav">
                <nav class="navbar navbar-inverse">
                    <div class="container-fluid">
                        <!-- Brand and toggle get grouped for better mobile display -->
 <div class="navbar-header">
                            <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#bs-example-navbar-collapse-1" aria-expanded="false">
                                <span class="sr-only">Toggle navigation</span>
                                <span class="icon-bar"></span>
                                <span class="icon-bar"></span>
                                <span class="icon-bar"></span>
                            </button>
                            <a class="navbar-brand" href="#">我的博客</a>
                        </div>
                        <div class="collapse navbar-collapse" id="bs-example-navbar-collapse-1">
                            <ul class="nav navbar-nav">
                                <li class="active"><a href="#">首页 <span class="sr-only">(current)</span></a></li>
                                <li><a href="#">学习心得</a></li>
                                <li><a href="#">编程技巧</a></li>
                                <li><a href="#">源码分享</a></li>
                                <li><a href="#">资料下载</a></li>
                            </ul>
                        </div><!-- /.navbar-collapse -->
 </div><!-- /.container-fluid -->
 </nav>
            </div>
        </div>
    </div>
    <!--头部导航结束-->

footer.php

<div class="row">
    <div class="col-md-12">
        <nav class="navbar navbar-inverse">
            <p class="text-center"><a href="">PHP中文网版权所有</a></p>
        </nav>
    </div>
</div>
<!--底部本身的内容结束-->

<!--与头部对应的结束标签-->
</div>
<!--脚本统一放在最后-->
<script>
//    给全部delete按钮添加click事件
    $('a#delete').each(function () {
        $(this).on('click', function () {
            if(!confirm("确认要删除吗？"))
            {
                return false;//取消跳转到链接
            }
        });
    });
</script>
</body>
</html>

主页面及代码：

list.php，显示数据列表

L01.02-1.png

<?php
$pageTitle = '用户信息列表';
include  'inc/header.php';

//连接数据库
require 'inc/connect.php';

//查询用户信息表user
$sql = "SELECT * FROM user";  //创建查询语句
$result = mysqli_query($db, $sql);  //执行查询
$rows = [];  //创建查询结果容器,初始为空数组,该数组最终会成为一个二维数组,与数组表对应
if ($result && mysqli_num_rows($result) > 0) {
    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
//        print_r($row);  //查看查询到的数据
        $rows[] = $row;  //将每一次查询到的结果放在数组$rows[]中,这是最终返回的结果
} else {
    echo '<h3>数据表为空,没有数据~~</h3>';
}
?>

<!--创建表格用来展示用户数据-->
<!--<div class="container">-->
    <div class="row">
        <div class="col-md-12 text-center">
            <h3>用户信息表</h3>
            <table class="table table-bordered">
                <tr class="h5 bg-info">
<!--                    <td>ID</td><td>姓名</td><td>邮箱</td>-->
                    <td>ID</td><td>姓名</td><td>邮箱</td><td>操作</td>
                </tr>
<!--
    1.下面循环输出用户信息,用foreach()的替代语法,请尽快熟悉这种写法,以后写模板就靠这个玩意了
    2. 替代语法的目标就是一个: 干掉php代码中的大括号,因为html与php混写的时候,不断的切换会有很多大括号
    3. 过多的大括号,即不利于代码整洁,也容易出错,不利于代码维护
-->
                <?php foreach ($rows as $row) : ?>
                    <tr>
                        <td><?php echo $row['id'] ?></td>
                        <td><?php echo $row['name'] ?></td>
                        <td><?php echo $row['email'] ?></td>
                <!-- 添加编辑和删除操作时,用链接式的按钮更合适-->
                        <td>
                            <a class="btn btn-success" href="view.php?id=<?php echo $row['id'] ?>" target="_blank" role="button">详细</a>
                            <a class="btn btn-primary" href="edit.php?id=<?php echo $row['id'] ?>" target="_blank" role="button">编辑</a>
                            <a id="delete" class="btn btn-danger"  href="delete.php?id=<?php echo $row['id'] ?>" target="_blank" role="button">删除</a>
                        </td>
                    </tr>
                <?php endforeach;?>
            </table>
        </div>
    </div>
<!--</div>-->

    <div class="row">
        <div class="col-md-12 text-right">
            <a class="btn btn-info btn-lg"  href="register.php" role="button">新建</a>
            <p></p>
        </div>
    </div>

<!--输出页尾-->
<?php include 'inc/footer.php' ?>

view.php，显示一条记录的详细信息

<?php
$pageTitle = '用户信息详情';
include 'inc/header.php';
$id = $_GET['id'];  //获取到要编辑的记录的id


require 'inc/connect.php';  //连接数据库
//准备查询语句
$sql = "SELECT * FROM `user` WHERE `id` = ".$id;
$res = mysqli_query($db, $sql);  //执行查询
if ($res && mysqli_num_rows($res) > 0) {
    //根据主键查询只会返回一条记录,不用循环处理
  $row = mysqli_fetch_array($res, MYSQLI_ASSOC);
}

?>
        <div class="row">
            <div class="col-md-12">
                <h3 class="text-center">用户信息详情</h3>
                <div class="form-group">
<!--                        用户名和密码是不允许修改的,因为这是登陆的重要信息,仅允许修改邮箱-->
                        <label for="name" class="col-md-1 col-sm-2 control-label">用户名</label>
                        <div class="col-md-11 col-sm-10">
                            <input type="text" class="form-control" id="name" placeholder="userName" name="name" value="<?php echo $row['name'] ?>" readonly>
                        </div>
                    </div>
                    <div class="form-group">
                        <label for="email" class="col-md-1 col-sm-2 control-label">邮箱</label>
                        <div class="col-md-11 col-sm-10">
                            <input type="email" class="form-control" id="email" placeholder="Email" name="email" value="<?php echo $row['email'] ?>" readonly>
                        </div>
                    </div>
                    <div class="form-group">
                        <label for="email" class="col-md-1 col-sm-2 control-label">备注</label>
                        <div class="col-md-11 col-sm-10">
                            <input type="email" class="form-control" id="memo" name="memo" value="<?php echo $row['memo'] ?>" readonly>
                            <p></p>
                        </div>
                    </div>
            </div>
        </div>

<?php include 'inc/footer.php' ?>

edit.php，编辑记录，提交到do_edit.php更新数据

<?php include 'inc/header.php';
$id = $_GET['id'];  //获取到要编辑的记录的id

require 'inc/connect.php';  //连接数据库
//准备查询语句
$sql = "SELECT * FROM `user` WHERE `id` = ".$id;
$res = mysqli_query($db, $sql);  //执行查询
if ($res && mysqli_num_rows($res) > 0) {
    //根据主键查询只会返回一条记录,不用循环处理
  $row = mysqli_fetch_array($res, MYSQLI_ASSOC);
}


?>

        <div class="row">
            <div class="col-md-12">
                <h3 class="text-center">用户信息编辑</h3>
<!--                修改处理的php脚本名称-->
                <form class="form-horizontal" action="do_edit.php?id=<?php echo $row['id'] ?>" method="post">
                    <div class="form-group">
<!--                        用户名和密码是不允许修改的,因为这是登陆的重要信息,仅允许修改邮箱-->
                        <label for="name" class="col-md-1 col-sm-2 control-label">用户名</label>
                        <div class="col-md-11 col-sm-10">
                            <input type="text" class="form-control" id="name" placeholder="userName" name="name" value="<?php echo $row['name'] ?>" readonly>
                        </div>
                    </div>
                    <div class="form-group">
                        <label for="email" class="col-md-1 col-sm-2 control-label">邮箱</label>
                        <div class="col-md-11 col-sm-10">
                            <input type="email" class="form-control" id="email" placeholder="Email" name="email" value="<?php echo $row['email'] ?>">
                        </div>
                    </div>


                    <div class="form-group">
                        <div class="col-md-2 col-md-offset-3 col-sm-offset-2 col-sm-10  text-center">
                            <button type="submit" class="btn btn-primary  btn-block">保存</button>
                        </div>
                    </div>
                </form>
            </div>
        </div>

<?php include 'inc/footer.php' ?>

do_edit.php

<?php
/**
 * 1.检测表单数据有效性
 * 2.更新数据表
 * 3.处理错误信息
 */

$pageTitle = '更新处理';  //设置当前页面标题
include 'inc/header.php';  //导入公共文件头部

//1.检测表单数据有效性
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $error = []; //创建一个数组,用来保存出错信息
    //用户名只读，跳过检查
    //检查邮箱信息
    if (empty($_POST['email'])) {
        $error[] = '没有输入邮箱,请检查';
    } else {
        $email = trim($_POST['email']);
    }
    //如果错误信息数组空，数据有效，更新到数据库
    //2.更新数据表
    if (empty($error)) {
        //连接数据库
        require 'inc/connect.php';
        //创建sql语句
        $sql = "UPDATE user SET  `email`='{$email}' WHERE `id`={$_GET['id']}";
        //更新数据
        if (mysqli_query($db, $sql)){
            echo '<h3 style="color: green">成功更新了'.mysqli_affected_rows($db).'条记录</h3>';
        } else {
            echo '<h3 style="color:red">更新失败:'.mysqli_error($db).'</h3>';
        }
        //关闭连接
        mysqli_close($db);
    }  else {
        //打印出错误信息
        foreach ($error as $message) {
            echo '<h3 style="color:red">'.$message.'</h3>';
        }
    }
}

include 'inc/footer.php';  //导入公共头部

delete.php，删除一条记录，删除前确认（在list.php页面）


L01.02-2.png

<?php
require 'inc/connect.php';

$sql = "DELETE FROM user WHERE id=".$_GET['id'];

if (mysqli_query($db, $sql)){
    echo '<script>alert("成功删除'.mysqli_affected_rows($db).'条记录")</script>';
} else {
    echo '<script>alert("删除失败'.mysqli_error($db).'")</script>';
}
mysqli_close($db);

register.php，添加新记录，提交到do_register.php处理数据

<?php
$pageTitle = '用户注册';
include 'inc/header.php'
?>

    <div class="row">
        <div class="col-md-12">
            <h3 class="text-center">用户注册</h3>
            <form class="form-horizontal" action="do_register.php" method="post">
                <div class="form-group">
                    <label for="name" class="col-sm-2 control-label">用户名</label>
                    <div class="col-sm-10">
                        <input type="text" class="form-control" id="name" placeholder="userName" name="name" value="<?php echo  isset($_POST['name'])?$_POST['name']:'' ?>">
                    </div>
                </div>
                <div class="form-group">
                    <label for="email" class="col-sm-2 control-label">邮箱</label>
                    <div class="col-sm-10">
                        <input type="email" class="form-control" id="email" placeholder="Email" name="email" value="<?php echo  isset($_POST['email'])?$_POST['email']:'' ?>">
                    </div>
                </div>
                <div class="form-group">
                    <label for="password1" class="col-sm-2 control-label">密码</label>
                    <div class="col-sm-10">
                        <input type="password" class="form-control" id="password1" placeholder="Password" name="password1" value="<?php echo  isset($_POST['password1'])?$_POST['password1']:'' ?>">
                    </div>
                </div>
                <div class="form-group">
                    <label for="password2" class="col-sm-2 control-label">重复密码</label>
                    <div class="col-sm-10">
                        <input type="password" class="form-control" id="password2" placeholder="Confirm Password" name="password2" value="<?php echo  isset($_POST['password2'])?$_POST['password2']:'' ?>">
                    </div>
                </div>

                <div class="form-group">
                    <div class="col-sm-offset-2 col-sm-10  text-center">
                        <button type="submit" class="btn btn-primary  btn-block">注册</button>
                    </div>
                </div>
            </form>
        </div>
    </div>

<?php include 'inc/footer.php' ?>

do_register.php

<?php
/**
 * 1.检测表单数据有效性
 * 2.添加记录到数据表
 * 3.处理错误信息
 */

$pageTitle = '用户注册';  //设置当前页面标题
include 'inc/header.php';  //导入公共文件头部

//1.检测表单数据有效性
if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $error = []; //创建一个数组,用来保存出错信息
    //检查用户名
    if (empty($_POST['name'])) {
        $error[] = '请输入用户名';
    } else {
        $name = trim($_POST['name']);
    }
    //检查邮箱
    if (empty($_POST['email'])) {
        $error[] = '请输入邮箱';
    } else {
        $email = trim($_POST['email']);
    }
    //检查密码。密码需要输入两遍,并且相同。
    if (empty($_POST['password1'])) {  //第一行密码如果为空
        $error[] = '请输入密码';
    } else {   //再检查第二行的校验密码
        if (empty($_POST['password2'])){  //如果没有输入第二行的校验密码
            $error[] = '请输入校验密码';
        } else {   //比较2个密码
            if ($_POST['password1'] == $_POST['password2']) {
                $password = trim($_POST['password1']); //如果相等,继续
            } else {  //否则重新输入
                $error[] = '两次输入的密码不一致';
            }
        }
    }

    //如果错误信息数组空，数据有效，添加到数据库
    //2.添加记录到数据表
    if (empty($error)) {
        //连接数据库
        require 'inc/connect.php';
        //创建sql语句
        $sql = "INSERT user SET `name`='{$name}', `email`='{$email}', `password`=sha1('{$password}')";
        //添加记录操作
        if (mysqli_query($db, $sql)){
            echo '<h3 style="color: green">成功添加了'.mysqli_affected_rows($db).'条记录</h3>';
        } else {
            echo '<h3 style="color:red">添加失败:'.mysqli_error($db).'</h3>';
        }
        //关闭连接
        mysqli_close($db);
    }  else {
        //打印出错误信息
        foreach ($error as $message) {
            echo '<h3 style="color:red">'.$message.'</h3>';
        }
    }
}

include 'inc/footer.php';  //导入公共头部


Statement of this Website
The copyright of this blog article belongs to the blogger. Please specify the address when reprinting! If there is any infringement or violation of the law, please contact admin@php.cn Report processing!
All comments Speak rationally on civilized internet, please comply with News Comment Service Agreement
0 comments
Author's latest blog post