Related knowledge about php+mysql fuzzy query function

php MySQL fuzzy query function is very important for PHP to operate the database. This article will explain its related knowledge in detail.

General fuzzy query statements are as follows:

SELECT field FROM table WHERE certain field Like condition

Regarding conditions, SQL provides four matching modes:

1, %: represents any 0 or more characters. Can match characters of any type and length. In some cases, if it is Chinese, please use two percent signs (%%) to express it.
For example, SELECT * FROM [user] WHERE u_name LIKE '%三%'
will treat u_name as "Zhang San", "Zhang Cat San", "Three-legged Cat", "Tang Sanzang" and so on. Find out all the records of "three".
In addition, if you need to find records with both "三" and "cat" in u_name, please use the and condition
SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE '% Cat%'
If you use SELECT * FROM [user] WHERE u_name LIKE '%三%cat%'
Although you can search for "three-legged cat", you cannot search for "Zhang Cat San" that meets the conditions.

2, _: represents any single character. Matches a single arbitrary character. It is often used to limit the character length of expression statements:

For example, SELECT * FROM [user] WHERE u_name LIKE '_三_'
Only find " "Tang Sanzang" such that u_name has three characters and the middle character is "三";

Another example is SELECT * FROM [user] WHERE u_name LIKE '三';
Only find "three-legged cat" In this way, the name has three characters and the first character is "三";

3, [ ]: represents one of the characters listed in brackets (similar to regular expression). Specify a character, string or range, and require the matched object to be any one of them.

For example, SELECT * FROM [user] WHERE u_name LIKE '[Zhang Li Wang] San'
will find "Zhang San", "Li San", "Wang San" (instead of "Zhang Li Wang] Wang San");

If [ ] contains a series of characters (01234, abcde, etc.), it can be abbreviated as "0-4", "a-e"
SELECT * FROM [user] WHERE u_name LIKE 'Old [1-9]'
will find "Old 1", "Old 2",..., "Old 9";

4, [^]: means not in the brackets A single character within a column. Its value is the same as [], but it requires that the matched object is any character other than the specified character.

For example, SELECT * FROM [user] WHERE u_name LIKE '[^Zhang Li Wang] San'
will find "Zhao San", "Sun San" etc.;

SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]';
will exclude "Old 1" to "Old 4" and search for "Old 5" ", "Old 6",...

5, when the query content contains wildcard character ,

due to the wildcard character, we query special characters Statements such as "%", "_", and "[" cannot be implemented normally, but special characters can be queried normally if they are enclosed in "[ ]". Based on this, we wrote the following function:

function sqlencode(str)
str=replace(str,"[","[[]") '此句一定要在最前
str=replace(str,"_","[_]")
str=replace(str,"%","[%]")
sqlencode=str
end function

This article explains the fuzzy query function of php mysql. For more learning materials, please pay attention to the php Chinese website.

Related recommendations:

How to copy and move files through php

About jQuery effects - related to hiding and displaying Knowledge

Related knowledge points about SQL NULL values

The above is the detailed content of Related knowledge about php+mysql fuzzy query function. For more information, please follow other related articles on the PHP Chinese website!