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连接数据库及简单模糊查询
login.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 "没有查询到相关用户!";
}
?>
效果截图: