PHP模糊查询练习

╰+哭是因爲堅強的太久メ 2022-11-19 09:42 141阅读 0赞

PHP连接数据库及简单模糊查询功能实现。

--------------------

## 1.建库建表sql ##

DROP DATABASE IF EXISTS `test`;
    CREATE DATABASE IF NOT EXISTS `test` DEFAULT CHARSET = `utf8`;
    USE `test`;
    
    -- ----------------------------
    -- Table structure for user
    -- ----------------------------
    DROP TABLE IF EXISTS `user`;
    CREATE TABLE `user`  (
      `uid` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
      `username` varchar(32) NOT NULL COMMENT '名字',
      `password` char(32) NOT NULL COMMENT '密码',
      `sex` char(2) NOT NULL COMMENT '性别',
      `email` varchar(40) NOT NULL COMMENT '邮箱',
      `hobby` varchar(255) NOT NULL COMMENT '兴趣爱好',
      PRIMARY KEY (`uid`) USING BTREE
    ) ENGINE = InnoDB AUTO_INCREMENT = 7 DEFAULT CHARSET=utf8 COMMENT='用户表';
    
    -- ----------------------------
    -- Records of user
    -- ----------------------------
    INSERT INTO `user` VALUES (1, '张三', '123456', '男', '123456@qq.com', '读书');
    INSERT INTO `user` VALUES (2, '李四', '123456', '女', '123@163.com', '舞蹈');
    INSERT INTO `user` VALUES (3, '汪芜', '123', '女', '369@sina.com', '琵琶');
    INSERT INTO `user` VALUES (4, '赵六', '12345', '男', '10086@360.com', '武术');
    INSERT INTO `user` VALUES (5, '田七', '1234', '女', '8848@qq.com', '排球');
    INSERT INTO `user` VALUES (6, '赵二', '1234', '男', '654321@qq.com', '网球');

## 2.PHP连接数据库及简单模糊查询 ##

<?php
    $keywords = isset($_GET['keywords']) ? trim($_GET['keywords']) : '';
    //echo "查询关键词为：".$keywords;
    
    // 连接数据库，设置字符集
    $conn = mysqli_connect("localhost", "root", "123456", "test", "3306") or die("数据库连接失败！");
    mysqli_set_charset($conn, "utf-8");
    
    //模糊查询语句
    $sql = "SELECT * FROM user WHERE username LIKE '%{ $keywords}%'";
    $res = mysqli_query($conn, $sql);
    $user = array();
    if (!empty($keywords)) { 
        while($row=mysqli_fetch_array($res)) { 
            $row['username'] = str_replace($keywords, '<font color="red">'.$keywords.'</font>', $row['username']);
            $user[] = $row;
        }
    }
    //print_r($user);
    ?>
    
    <!DOCTYPE html>
    <html>
    <head>
        <meta charset="utf-8">
        <title>查询器</title>
        <style>
            .textbox{ 
                width:355px;
                height: 40px;
                border-radius: 3px;
                border: 1px solid #e2b709;
                padding-left: 10px;
            }
            .search{ 
                width: 355px;
                height: 40px;
                background-color: #7fbdf0;
                color: whitesmoke;
                border: 1px solid #888888;
            }
            table{ 
                background-color: #7fbdf0;
                line-height: 25px;
            }
            th{ 
                background-color: #fff2fb;
            }
            td{ 
                background-color: #fff2fb;
                text-align: center;
            }
        </style>
    <body>
    <form action="" method="get">
        <p><input type="text" name="keywords" class="textbox" value="" placeholder="请输入关键词"/></p>
        <p><input type="submit" value="查询" class="search"/></p>
    </form>
    </body>
    </head>
    </html>
    
    <?php
    if ($keywords) { 
        echo '<h3>查询关键词为：<font COLOR="red">'.$keywords.'</font></h3>';
    }
    
    if ($user) { 
        echo '<table width="500" cellpadding="5">';
        echo '<tr><th>用户名</th><th>密码</th><th>性别</th><th>邮箱</th><th>爱好</th></tr>';
        foreach ($user as $key=>$value) { 
            echo '<tr>';
            echo '<td>'.$value['username'].'</td>';
            echo '<td>'.$value['password'].'</td>';
            echo '<td>'.$value['sex'].'</td>';
            echo '<td>'.$value['email'].'</td>';
            echo '<td>'.$value['hobby'].'</td>';
            echo '</tr>';
        }
    } else { 
        echo "没有查询到相关用户！";
    }
    ?>

效果截图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VzZXJuYW1lNjY2_size_16_color_FFFFFF_t_70]

--------------------

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3VzZXJuYW1lNjY2_size_16_color_FFFFFF_t_70]: /images/20221119/7285826566684b51a5c00dd30011ba11.png