php mysql增删改查

weixin_40793759

已于 2023-02-16 17:27:03 修改

阅读量121

点赞数

分类专栏： php 文章标签： mysql php 数据库

于 2023-02-16 16:19:07 首次发布

本文链接：https://blog.csdn.net/weixin_40793759/article/details/129064363

版权

php 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

虽然坎坷，但是写完了。。。
database.php：

<?php
    $servername="***";
    $username="***";
    $password="***";
    $dbname="***";

    $conn=mysqli_connect($servername, $username, $password, $dbname);
    if(mysqli_connect_errno()){
        echo "连接失败";
    }
?>

<?php
    require_once 'database.php'
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <?php
    $error_str=$firstname=$lastname=$email='';$id="";
    if($_SERVER["REQUEST_METHOD"] == "POST"){
        if($_POST['button2'] == '删除'){
            mysqli_query($conn, "DELETE FROM myGuests WHERE id=".$_POST['button1']);
        }else if($_POST['button2'] == '编辑'){
            $id=$_POST['button1'];
            $sql="SELECT * FROM myGuests WHERE id=".$id;
            $result = mysqli_query($conn, $sql);
            $row=mysqli_fetch_array($result);
            $firstname=$row['firstname'];
            $lastname=$row['lastname'];
            $email=$row['email'];
        }else{
            if(empty($_POST['firstname'])){
                $error_str='请完善表单';
                // die();
            }else{
                $firstname=$_POST['firstname'];
            }
            if(empty($_POST['lastname'])){
                $error_str='请完善表单';
                // die();
            }else{
                $lastname=$_POST['lastname'];
            }
            if(empty($_POST['email'])){
                $error_str='请完善表单';
                // die();
            }else{
                $email=$_POST['email'];
            }
            if(empty($error_str)){
                if($_POST['id']){
                    $sql = "UPDATE myGuests SET firstname='$firstname', lastname='$lastname', email='$email' WHERE id=".$_POST['id'];
                    mysqli_query($conn, $sql);
                }else{
                    $sql = "INSERT INTO myGuests(firstname, lastname, email) VALUES('$firstname', '$lastname', '$email')";
                    mysqli_query($conn, $sql);
                }
            }
        }
        
        
    }
    
    ?>
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method="post">
姓氏: <input type="text" name="firstname" value="<?php echo $firstname;?>">
名字: <input type="text" name="lastname" value="<?php echo $lastname;?>">
邮箱: <input type="text" name="email" value="<?php echo $email;?>">
<input type="hidden" name="id" value="<?php echo $id ?>">
<input type="submit" name="button2" value="确认">
</form>
<?php echo $error_str ?>
    <form action="" method="get">
        <input placeholder="搜索姓氏或名字" type="text" name="keyword" value="<?php $keyword ?>" />
        <input type="submit" value="搜索">
    </form>
    <table class="list" >
        <tr>
            <td>id</td>
            <td>名字</td>
            <td>姓氏</td>
            <td>邮箱</td>
            <td>操作</td>
        </tr>
    <?php
        $keyword='';
        $sql="SELECT * FROM myGuests";
        if($_SERVER["REQUEST_METHOD"] == "GET"){
            $keyword=$_GET['keyword'];
            if(!empty($_GET['keyword'])){
                $sql=$sql." WHERE firstname='".$keyword."' or lastname='".$keyword."'";
                
            }else{
                $sql = $sql;
            }
        }
        $result = mysqli_query($conn, $sql);
        renderList($result);
        
        function renderList($result){
            if (mysqli_num_rows($result) > 0) {
                // 输出数据
                while($row = mysqli_fetch_assoc($result)) {
                    ?>
                    <tr>
                        <td><?php echo $row['id'] ?></td>
                        <td><?php echo $row['firstname'] ?></td>
                        <td><?php echo $row['lastname'] ?></td>
                        <td><?php echo $row['email'] ?></td>
                        <td style="display: flex;">
                        <form action="" method="post">
                            <input type="hidden" name="button1"
                                    class="button" value="<?php echo $row['id'] ?>" />
                            
                            <input type="submit" name="button2"
                                    class="button" value="删除" />
                        </form>
                        <form action="" method="post">
                            <input type="hidden" name="button1"
                                    class="button" value="<?php echo $row['id'] ?>" />
                            
                            <input type="submit" name="button2"
                                    class="button" value="编辑" />
                        </form>
                        </td>
                    </tr>
                    <?php
                }
            }
        }
        
        
    ?>
    </table>
</body>
</html>

class实现：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <?php 
        $id=$error_str=$firstname=$lastname=$email=$keyword="";
        $table_data=[];

        // 实例化
        $myGuest=new Guests();
        if($_SERVER["REQUEST_METHOD"] == "POST"){
            if($_POST["button2"]=='确认'){
                if(empty($_POST['firstname']) || empty($_POST['lastname']) || empty($_POST['email'])){
                    $error_str="请完善表单";
                }
                if(!$error_str){
                    $firstname = $_POST["firstname"];
                    $lastname = $_POST["lastname"];
                    $email = $_POST["email"];
                    $id=$_POST["id"];
                    if($id){
                        $myGuest->update($id, $firstname, $lastname, $email);
                    }else{
                        $myGuest->add($firstname, $lastname, $email);
                    }
                    
                }
            }else if($_POST["button2"]=='删除'){
                if(isset($_POST["button1"])){
                    $myGuest->del($_POST["button1"]);
                }
            }else if($_POST["button2"]=='编辑'){
                $id=$_POST["button1"];
                if(isset($id)){
                    $row=$myGuest->getData('', $id)[0];
                    $firstname=$row['firstname'];
                    $lastname=$row['lastname'];
                    $email=$row['email'];
                }
            }
        }
        
        if($_SERVER["REQUEST_METHOD"] == "GET"){
            $keyword=$_GET["keyword"] ?? '';
        }
        $table_data=$myGuest->getData($keyword ?? '');
    ?>
    <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method="post">
        姓氏: <input type="text" name="firstname" value="<?php echo $firstname;?>">
        名字: <input type="text" name="lastname" value="<?php echo $lastname;?>">
        邮箱: <input type="text" name="email" value="<?php echo $email;?>">
        <input type="hidden" name="id" value="<?php echo $id ?>">
        <input type="submit" name="button2" value="确认">
    </form>
    <p><?php echo $error_str ?></p>
    <form action="" method="get">
        <input type="text" name="keyword" value="<?php echo $keyword ?>" >
        <input type="submit" name="button" value="查询">
    </form>
    <table>
        <tr>
            <td>ID</td>
            <td>姓氏</td>
            <td>名字</td>
            <td>邮箱</td>
            <td>添加时间</td>
        </tr>
        <?php 
        for($i=0; $i<count($table_data); $i++){
            ?>
            <tr>
                <td><?php echo $table_data[$i]['id'] ?></td>
                <td><?php echo $table_data[$i]['firstname'] ?></td>
                <td><?php echo $table_data[$i]['lastname'] ?></td>
                <td><?php echo $table_data[$i]['email'] ?></td>
                <td><?php echo $table_data[$i]['reg_date'] ?></td>
                <td style="display: flex">
                    <form action="" method="post">
                        <input type="hidden" name="button1"
                                class="button" value="<?php echo $table_data[$i]['id'] ?>" />
                        
                        <input type="submit" name="button2"
                                class="button" value="删除" />
                    </form>
                    <form action="" method="post">
                        <input type="hidden" name="button1"
                                class="button" value="<?php echo $table_data[$i]['id'] ?>" />
                        
                        <input type="submit" name="button2"
                                class="button" value="编辑" />
                    </form>
                </td>
            </tr>
            <?php
        } ?>
    </table>
    <?php
        // class四个方法
        // 表单按钮提交判断
        class Guests {
            private $conn;
            private $servername="***";
            private $username="***";
            private $password="***";
            private $db="***";
            public $datalist=[];
            // 连接数据库
            function __construct()
            {
                $this->conn=mysqli_connect($this->servername, $this->username, $this->password, $this->db);
                if(mysqli_connect_errno($this->conn)){
                    throw new Exception("连接错误：".mysqli_connect_errno());
                }
                mysqli_set_charset($this->conn, 'utf8');
            }
            private function query($sql){
                if(empty($sql)) throw new Exception('暂无执行的SQL');
                try{
                    return mysqli_query($this->conn, $sql);
                }catch(Exception $e){
                    throw new Exception('query Error：'.$e->getMessage());
                }
            }
            // 新增
            public function add($firstname, $username, $email){
                $sql="INSERT INTO myGuests(firstname, lastname, email) VALUES('$firstname', '$username', '$email')";
                $this->query($sql);
            }
            // 删除
            public function del($id){
                $sql="DELETE FROM myGuests WHERE id=".$id;
                $this->query($sql);
            }
            // 编辑
            public function update($id, $firstname, $lastname, $email){
                $sql="UPDATE myGuests SET firstname='$firstname', lastname='$lastname', email='$email' WHERE id=".$id;
                $this->query($sql);
            }
            // 查询
            public function getData($keyword="", $id=""){
                $sql="SELECT * FROM myGuests";
                if($id){
                    $sql.=" WHERE id=".$id;
                    return mysqli_fetch_all(mysqli_query($this->conn, $sql), MYSQLI_ASSOC);
                }
                if($keyword){
                    $sql.=" WHERE firstname='$keyword' or lastname='$keyword'";
                }
                return mysqli_fetch_all($this->query($sql), MYSQLI_ASSOC);
            }
            
            function __destruct()
            {
                mysqli_close($this->conn);
            }
        }

        
        
        
    ?>
</body>
</html>