How to implement fuzzy query in php

How to implement fuzzy query in php: 1. Use SQL matching mode, the operator must use LIKE or NOT LIKE, when matching, it is not case sensitive; 2. Use regular expression matching mode, its regular expression The formula appears anywhere in the matching field.

[Related learning recommendations: php programming (video)]

php achieves blurring Query method:

Method 1: SQL matching pattern

1. Use sql matching pattern. You cannot use the operator = or ! =, but use the operator LIKE or NOT LIKE;

2. Using sql matching mode, MYSQL provides 2 kinds of wildcard characters.

• % represents any number of any characters (including 0)

• _ Represents any single character

3. Use sql matching mode. If the matching format does not contain any of the above two wildcard characters, the query effect is equivalent to = or! =

4. Use sql matching pattern. When matching, it is case-insensitive

#查询用户名以某个字符开头的用户
#查询用户名以字符'l'开头的用户： l%
SELECT * FROM user WHERE username LIKE 'l%';
#查询用户名以某个字符结尾的用户
#查询用户名以字符'e'结尾的用户：e%
SELECT * FROM user WHERE username LIKE 'e%';
#查询用户名包含某个字符的用户
#查询用户名包含字符'o'的用户：%o%
SELECT * FROM user WHERE username LIKE '%o%';
#查询包含三个字符的用户
SELECT * FROM user WHERE username LIKE '___';
#查询用户名第二个字符为o的用户：_o%
SELECT * FROM user WHERE username LIKE '_o%';

Method 2: Regular expression matching pattern

Wildcard ( Regular expression)

.matches any single character

*matches 0 or more characters before it

x* means matching any number of x characters

• [..] Matches any character in square brackets

• [abc] Matches the characters ab or c

• [a-z] Matches any letter

• [0-9] Matches any number

• [0-9]*Match any number of any digits

• [a-z]*Match any number of letters

^ means starting with a certain character or string

^a means starting with the letter a

$ means ending with a certain character or string

s$ means ending with the letter s

The operator used to match the pattern using regular expressions is:

REGEXP or NOT REGEXP(RLIKE or NOT RLIKE)

Note: Regular expression matching pattern, its regular expression appears anywhere in the matching field ,

Even if the pattern matches, there is no need to put a wildcard character on both sides to make it match;

If only the wildcard character . is used to match, assuming N, then the matching pattern means that it is greater than Equal to N;

How do you understand the above sentence?

That is to say

... Matches data that is greater than or equal to 3 characters

.... Matches data that is greater than or equal to 4 characters

# Query users whose username starts with the character l: ^l;

#Regular expression writing

SELECT * FROM user WHERE username REGEXP '^l';
#sql匹配模式写法：
SELECT * FROM user WHERE username LIKE 'l%';
#查询用户名正好是三个字符的用户：^...$;
#sql匹配模式写法：
SELECT * FROM user WHERE username LIKE '___';
#正则表达式写法
SELECT * FROM user WHERE username REGEXP '^...$';

Related recommendations:Programming video course

The above is the detailed content of How to implement fuzzy query in php. For more information, please follow other related articles on the PHP Chinese website!