PHP案例：实现数据库增删改查功能

一、创建数据库与表

二、创建PHP项目db_demo

1、Student.php

<?php

class Student
{
    private $id;
    private $name;
    private $gender;
    private $age;
    private $telephone;

    /**
     * @return mixed
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * @param mixed $id
     */
    public function setId($id)
    {
        $this->id = $id;
    }

    /**
     * @return mixed
     */
    public function getName()
    {
        return $this->name;
    }

    /**
     * @param mixed $name
     */
    public function setName($name)
    {
        $this->name = $name;
    }

    /**
     * @return mixed
     */
    public function getGender()
    {
        return $this->gender;
    }

    /**
     * @param mixed $gender
     */
    public function setGender($gender)
    {
        $this->gender = $gender;
    }

    /**
     * @return mixed
     */
    public function getAge()
    {
        return $this->age;
    }

    /**
     * @param mixed $age
     */
    public function setAge($age)
    {
        $this->age = $age;
    }

    /**
     * @return mixed
     */
    public function getTelephone()
    {
        return $this->telephone;
    }

    /**
     * @param mixed $telephone
     */
    public function setTelephone($telephone)
    {
        $this->telephone = $telephone;
    }

    function __toString()
    {
        return "Student{id=$this->id, name='$this->name',
        gender='$this->gender', age=$this->age,
        telephone='$this->telephone'}";
    }
}

?>

2、DataSource.php

<?php
    function getConn() {
       $host = "localhost";
       $user = "root";
       $password = "root";
       $database = "student";

       $conn = mysqli_connect($host, $user, $password, $database);

       if ($conn->error) {
           die("连接数据库[$database]失败！");
       }
       return $conn;
    }
?>

3、StudentDao.php

<?php
require("Student.php");
require("DataSource.php");

/**
 * 查询全部学生
 *
 * @return array
 */
function findAllStudents() {
    // 获得数据库连接
    $conn = getConn();
    // 定义SQL字符串
    $sql = "SELECT * FROM student";
    // 执行查询，返回结果集
    $result = $conn->query($sql);
    // 定义学生数组
    $students = array();
    // 定义数组下标
    $i = 0;
    // 遍历结果集
    while($row = $result->fetch_assoc()) {
        $students[$i] = new Student();
        $students[$i]->setId($row['id']);
        $students[$i]->setName($row['name']);
        $students[$i]->setGender($row['gender']);
        $students[$i]->setAge($row['age']);
        $students[$i]->setTelephone($row['telephone']);
        $i++;
    }
    return $students;
}

/**
 * 按学号查询学生
 *
 * @param $id
 * @return null|Student
 */
function findStudentById($id)
{
    // 获得数据库连接
    $conn = getConn();
    // 定义SQL字符串
    $sql = "SELECT * FROM student WHERE id = $id";
    // 执行查询，返回结果集
    $result = $conn->query($sql);
    // 判断是否有查询记录
    if ($result->num_rows > 0) {
        // 将查询结果放进关联数组
        $row = $result->fetch_assoc();
        // 创建学生对象
        $student = new Student();
        // 设置学生对象属性
        $student->setId($row['id']);
        $student->setName($row['name']);
        $student->setGender($row['gender']);
        $student->setAge($row['age']);
        $student->setTelepone($row['telephone']);
    } else {
        $student = null;
    }
    // 返回学生对象
    return $student;
}

/**
 * 添加学生记录
 * @param $student
 * @return bool|mysqli_result
 */
function insertStudent($student)
{
    $id = $student->getId();
    $name = $student->getName();
    $gender = $student->getGender();
    $age = $student->getAge();
    $telephone = $student->getTelephone();

    // 获得数据库连接
    $conn = getConn();
    // 定义SQL字符串
    $sql = "INSERT INTO student VALUES ('$id', '$name', '$gender', '$age', '$telephone')";
    // 执行插入操作
    $retval = $conn->query($sql);

    return $retval;
}

/**
 * 按学号删除学生
 *
 * @param $id
 */
function deleteStudentById($id) {
    // 获得数据库连接
    $conn = getConn();
    // 定义SQL字符串
    $sql = "DELETE FROM student WHERE id = $id";
    // 执行删除操作
    $retval = $conn->query($sql);

    return $retval;
}
?>

4、FindStudentById.php

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>按学号查询学生</title>
</head>
<body>
<h3>按学号查询学生</h3>
<form action="doFindStudentById.php" method="post">
    学号：<input type="text" name="id">
    <input type="submit" value="查询"><br>
</form>
</body>
</html>

5、doFindStudentById.php

<?php
    require("StudentDao.php");

    $id = $_POST['id'];
    $student = findStudentById($id);
    if ($student == null) {
        echo "你要查询的学生不存在！";
    } else {
        echo $student;
    }
?>

6、InsertStudent.php

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>添加学生记录</title>
</head>
<body>
<h3>添加学生记录</h3>
<form action="doInsertStudent.php" method="post">
    学号：<input type="number" name="id"><br>
    姓名：<input type="text" name="name"><br>
    性别：<input type="text" name="gender"><br>
    年龄：<input type="number" name="age"><br>
    电话：<input type="tel" name="telephone"><br>
    <input type="submit" value="确定">
    <input type="reset" value="重置">
</form>
</body>
</html>

7、doInsertStudent.php

<?php
    require("StudentDao.php");

    $id = $_POST['id'];
    $name = $_POST['name'];
    $gender = $_POST['gender'];
    $age = $_POST['age'];
    $telephone = $_POST['telephone'];

    $student = new Student();

    $student->setId($id);
    $student->setName($name);
    $student->setGender($gender);
    $student->setAge($age);
    $student->setTelephone($telephone);

    $retval = insertStudent($student);

    if ($retval) {
        echo "记录插入成功！";
    } else {
        echo "记录插入失败！";
    }

?>

8、deleteStudentById.php

<?php
    require("StudentDao.php");

    $id = $_GET['id'];
    $retval = deleteStudentById($id);
    $location="StudentList.php?retval=$retval";
    header("location:$location");
?>

9、StudentList.php

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>学生列表</title>
</head>
<body>
<h3>学生列表</h3>
<?php
    require("StudentDao.php");

    @$retval = $_GET['retval'];
    if ($retval) {
        echo "<script type='text/javascript'>alert('记录删除成功！');</script>";
    }

    $students = findAllStudents();
    if (count($students) > 0) {
        echo "<table border='1' cellpadding='5' cellspacing='0'>";
        echo "<tr><td>学号</td><td>姓名</td><td>性别</td>"
            ."<td>年龄</td><td>电话号码</td><td>操作</td><td>操作</td></tr>";
        foreach ($students as $student) {
            $id = $student->getId();
            $name = $student->getName();
            $gender = $student->getGender();
            $age = $student->getAge();
            $telephone = $student->getTelephone();
            echo "<tr><td>$id</td><td>$name</td><td>$gender</td>"
                ."<td>$age</td><td>$telephone</td>"
                ."<td><a href='#'>编辑</a></td>"
                ."<td><a href='DeleteStudentById.php?id=$id' οnclick='return deleteStudent();'>删除</a></td></tr>";
        }
        echo "</table>";
    } else {
        echo "学生表里没有记录！";
    }
?>
<script type="text/javascript">
    function deleteStudent() {
        var choice = confirm("你是否要删除该记录？");
        return choice;
    }
</script>
</body>
</html>

10、TestStudent.php

<?php
    // 包含文件Student.php
    require("Student.php");

    // 创建学生对象
    $student = new Student();

    // 设置学生对象属性
    $student->setId(1);
    $student->setName("张小强");
    $student->setGender("男");
    $student->setAge(18);
    $student->setTelephone("15890904567");

    // 输出学生对象
    echo $student;
?>

11、index.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>首页</title>
</head>
<body>
<h1>PHP数据库操作案例演示</h1>
<ul>
    <li><a href="TestStudent.php">测试学生实体类</a></li>
    <li><a href="FindStudentById.php">按学号查询学生</a></li>
    <li><a href="InsertStudent.php">添加学生记录</a></li>
    <li><a href="StudentList.php">查看学生列表</a></li>
</ul>
</body>
</html>

三、运行程序

1、测试学生实体类

2、按学号查询学生

3、添加学生记录

4、查看学生列表

5、删除学生记录