How to perform fuzzy query in PHP

Fuzzy query method: 1. Use SQL matching pattern, and the operator uses "LIKE" or "NOT LIKE". When matching, it is not case-sensitive; 2. Use regular expression matching pattern, and the operator uses "REGEXP" or "NOT REGEXP", the regular expression can appear anywhere in the matching field.

The operating environment of this tutorial: windows7 system, PHP7.1&&mysql8 version, Dell G3 computer.

When building a website, we often use the function of using keywords to find certain resources on the website. At this time, we often need to use fuzzy queries. Today I learned PHP fuzzy query, now let’s summarize it.

The above is the flow chart of fuzzy query.

In order to facilitate demonstration, a simple database table needs to be created.

KEY ‘username’(‘username’) is the index, very important.

Benefits of Index: If you retrieve data according to a certain condition, if the condition field is not indexed, the entire table will be traversed during the query. If you create an index, the query will Then the query will be performed based on the index, thereby improving query performance.

Precise query

The returned result is one and only one SQL query

Application scenario: User registration Login

Fuzzy query

Return The result is uncertain

Application scenario: Site search

Note: The results returned by the above two queries may be empty.

Fuzzy query technology supports 2 matching formats: 1.SQL matching mode (the most commonly used one in development); 2. Regular expression matching mode (not recommended)

SQL matching mode

1. When using SQL matching mode, you cannot use the operator = or !=, but use the operator LIKE or NOT LIKE;

2. Using SQL matching mode, MYSQL provides 2 types 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 mode, which is case-insensitive by default.

Five query scenarios

① #Query users whose username starts with a certain character

#Query users whose username starts with the character 'l'

# l%

SELECT * FROM user WHERE username LIKE 'l%';

② #Query the username with a certain character Users ending with

#Query users whose username ends with a certain character 'e'

#%e

SELECT * FROM user WHERE username LIKE '%e ';

③#Query users whose username contains a certain character

#Query users whose username contains the character 'o'

# % o%

SELECT * FROM user WHERE username LIKE '%o%'; (Commonly used)

## ④#The query username length is 3 User

#___

SELECT * FROM user WHERE username LIKE '___';

##⑤#Two wildcard characters Use in combination

#Query users whose second character is o

# _o%

SELECT * FROM user WHERE username LIKE '_o%' ;

Regular expression matching pattern (deprecated)
Wildcard

. Match any single character

* Match 0 or multiple characters in front of it

# x* indicate the x character that matches any quantity

[. .] Any character that matches the brackets

[abc] matching character A b or c

## [A-Z] matching any letter

[0-9] matching any number

[0-9]* match any number of any number

[A-Z]* match any number of letters

^ It means a character or string start

        ^a means it starts with the letter a

            $ means it ends with a certain character or string

          s$ means it ends with the letter s

Use regular expression matching pattern Operators used

REGEXP or NOT REGEXP(RLIKE or NOT RLIKE)

If you use a regular expression to match, the pattern is different from the SQL pattern

Explanation: As in the following example

#Query users whose username starts with the character l

SQL Match pattern l%

Regular expression ^l

SELECT * FROM user WHERE username REGEXP '^l';

#Query users whose username is exactly 3 characters

SQL matching pattern ___

Regular expression... ?Is this so

SELECT * FROM user FROM username WHERE username REGEXP '...';

Why are all users queried? Because a regular expression matches a pattern, the pattern matches if the regular expression appears anywhere in the matching field. So... matches usernames containing three letters or more, and all usernames in the table match, so all users are queried.

Note: If you only use the wildcard character . to match, there are N wildcard characters. Then the matching mode means that it is greater than or equal to N characters. If you want to express the exact number of characters, the format is as follows: ^...$

SELECT * FROM user WHERE username REGEXP '^...$';

Recommended learning: php video tutorial

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