How to implement fuzzy query in PHP (graphic code)
The content of this article is about how to implement fuzzy query (graphic and text code) in PHP. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.
Introduction
Revealing the essence of PHP fuzzy query technology
Function
-
Find relevant users based on the entered keywords
PHP User Queryer Case Analysis
Course Objectives
Master the application of PHP fuzzy technology
Key points of the course
The syntax of PHP fuzzy query
PHP fuzzy query application
Course case (rendering)
Database design
User table ( user):
CREATE TABLE user( `uid` int(10) AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID', `username` varchar(30) NOT NULL DEFAULT '' COMMENT '用户名', `password` varchar(6) NOT NULL DEFAULT '' COMMENT '密码', `sex` char(2) NOT NULL DEFAULT '保密' COMMENT '性别', `email` varchar(40) NOT NULL DEFAULT '' COMMENT '邮箱', `hobby` varchar(255) NOT NULL DEFAULT '' COMMENT '兴趣爱好', KEY `username`(`username`)//索引 )ENGINE=MyISAM DEFAULT CHARSET=UTF8 COMMENT='用户表'
Benefits of indexing:
If you retrieve data according to a certain condition, if the condition field is not indexed, the entire table will be traversed when querying. If you create an index, the query will be based on the index, thereby improving query performance
Mysql fuzzy query syntax
SQL matching mode (the most commonly used one in development)
Regular expression matching Pattern
SQL matching pattern
#Use SQL matching pattern, you cannot use operator = or !=, but use operator LIKE or NOT LIKE
Use SQL matching mode, MYSQL provides two wildcard characters:
①% represents any number of any characters (including 0)
② Any single character represented by _- ## uses the SQL matching mode. If the matching format does not contain either of the above two wildcard characters, the query effect is equivalent to = or !=
- Use SQL matching pattern, case insensitive by default
代码实现: SELECT * FROM user WHERE username like 'l%'; SELECT * FROM user WHERE username like '%e'; SELECT * FROM user WHERE username like '%o%'; SELECT * FROM user WHERE username like '___';//三个_，表示username为三个字符的结果集 SELECT * FROM user WHERE username like '_o%';//第二个字符为o
. Matches any single character
* Matches 0 or more characters before it
eg:x* 表示匹配任何数量的x字符
- [] matches any characters in brackets
eg:[abc] 匹配字符a、b后者c [a-z] 匹配任何字母 [0-9] 匹配任何数字 [0-9]* 匹配任何数量的任何数字 [a-z]* 匹配任何数量的任何字母
- ^ means it starts with a certain character or string
eg:^a 表示以字母a开头
- $ represents a certain character or string result
eg：s$ 表示以字母s结尾
- Use regular expressions Operators used in matching patterns: REGEXP(RLIKE) or NOT REGEXP(NOT RLIKE)
code:SELECT * FROM user WHERE username REGEXP '^l'; SELECT * FROM user WHERE username REGEXP '...';
PS: If only the . wildcard is used, there are several dot wildcards, assuming N , then the matching pattern means greater than or equal to N
- ##The exact number of characters
^...$ //表示只能为三个字符 SELECT * FROM user WHERE username REGEXP '^...$';
Copy after login
Case
Development process
Source code analysis
<?php
//关键字
$keywords = isset($_POST['keywords'])?$_POST['keywords']:'';
//连接数据库，php7废弃了mysql_connect推荐使用
mysqli_connect$link = mysqli_connect( "localhost:3306", "root", "root", "mook");
if(!empty($keywords)){
$sql = "SELECT * FROM user WHERE username like '%{$keywords}%' ";
}else{
$sql = "SELECT * FROM user";
}$usersArr = [];$result = $link->query($sql);while($row = $result->fetch_assoc())
{ //简单高亮显示
// $row['username'] = str_replace($keywords, "<font color='red'>".$keywords."</font>",$row['username']);
//高亮显示，不区分关键字的大小写
$usernameArr = preg_split('/(?<!^)(?!$)/u',$row['username']);
foreach ($usernameArr as $key => $value) {
if(strtoupper($keywords) == strtoupper($value)){
$usernameArr[$key] = "<font color='red'>".$value."</font>";
}
}
$row['username'] = join($usernameArr);
$usersArr[] = $row;
}?><!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>php用户查询器</title>
</head>
<body>
<h1 id="php模糊查询">php模糊查询</h1>
<form action="index.php" method="post">
用户名:<input type="text" name="keywords" value="" />
<input type="submit" value="提交查询" />
</form>
<?php
if(!empty($keywords)){
echo "查询关键词:<font color='red'> ".$keywords." </font>结果!";
}
$tableString = "<table width='500' border='1' cellpadding='5'>";
$tableString .= "<tr bgcolor='orange'><th>用户名</th><th>邮箱</th><th>性别</th></tr>";
if(!empty($usersArr)){
foreach ($usersArr as $key => $value) {
$tableString .= "<tr><td>" . $value['username']. "</td><td>" . $value['email'] . "</td><td>".$value['sex']."</td></tr>";
}
}else{
$tableString .="<tr><td colspan='3'>没有数据</td></tr>";
}
$tableString .= "</table>";
echo $tableString;
?>
</body>
</html>Source code download address:
Link ：https://pan.baidu.com/s/1F8duu8G2iCa4xgNLBbiLCg Password: 96fn
Related recommendations:
PHP code example to realize that member account can only be logged in as the only onePHP code example to realize that member account can only be logged in as the only oneThe above is the detailed content of How to implement fuzzy query in PHP (graphic code). For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
PHP 8.4 Installation and Upgrade guide for Ubuntu and Debian
Dec 24, 2024 pm 04:42 PM
PHP 8.4 brings several new features, security improvements, and performance improvements with healthy amounts of feature deprecations and removals. This guide explains how to install PHP 8.4 or upgrade to PHP 8.4 on Ubuntu, Debian, or their derivati
7 PHP Functions I Regret I Didn't Know Before
Nov 13, 2024 am 09:42 AM
If you are an experienced PHP developer, you might have the feeling that you’ve been there and done that already.You have developed a significant number of applications, debugged millions of lines of code, and tweaked a bunch of scripts to achieve op
How To Set Up Visual Studio Code (VS Code) for PHP Development
Dec 20, 2024 am 11:31 AM
Visual Studio Code, also known as VS Code, is a free source code editor — or integrated development environment (IDE) — available for all major operating systems. With a large collection of extensions for many programming languages, VS Code can be c
Explain JSON Web Tokens (JWT) and their use case in PHP APIs.
Apr 05, 2025 am 12:04 AM
JWT is an open standard based on JSON, used to securely transmit information between parties, mainly for identity authentication and information exchange. 1. JWT consists of three parts: Header, Payload and Signature. 2. The working principle of JWT includes three steps: generating JWT, verifying JWT and parsing Payload. 3. When using JWT for authentication in PHP, JWT can be generated and verified, and user role and permission information can be included in advanced usage. 4. Common errors include signature verification failure, token expiration, and payload oversized. Debugging skills include using debugging tools and logging. 5. Performance optimization and best practices include using appropriate signature algorithms, setting validity periods reasonably,
PHP Program to Count Vowels in a String
Feb 07, 2025 pm 12:12 PM
A string is a sequence of characters, including letters, numbers, and symbols. This tutorial will learn how to calculate the number of vowels in a given string in PHP using different methods. The vowels in English are a, e, i, o, u, and they can be uppercase or lowercase. What is a vowel? Vowels are alphabetic characters that represent a specific pronunciation. There are five vowels in English, including uppercase and lowercase: a, e, i, o, u Example 1 Input: String = "Tutorialspoint" Output: 6 explain The vowels in the string "Tutorialspoint" are u, o, i, a, o, i. There are 6 yuan in total
How do you parse and process HTML/XML in PHP?
Feb 07, 2025 am 11:57 AM
This tutorial demonstrates how to efficiently process XML documents using PHP. XML (eXtensible Markup Language) is a versatile text-based markup language designed for both human readability and machine parsing. It's commonly used for data storage an
Explain late static binding in PHP (static::).
Apr 03, 2025 am 12:04 AM
Static binding (static::) implements late static binding (LSB) in PHP, allowing calling classes to be referenced in static contexts rather than defining classes. 1) The parsing process is performed at runtime, 2) Look up the call class in the inheritance relationship, 3) It may bring performance overhead.
What are PHP magic methods (__construct, __destruct, __call, __get, __set, etc.) and provide use cases?
Apr 03, 2025 am 12:03 AM
What are the magic methods of PHP? PHP's magic methods include: 1.\_\_construct, used to initialize objects; 2.\_\_destruct, used to clean up resources; 3.\_\_call, handle non-existent method calls; 4.\_\_get, implement dynamic attribute access; 5.\_\_set, implement dynamic attribute settings. These methods are automatically called in certain situations, improving code flexibility and efficiency.
