Decryption PHP Fuzzy Query technology

Mu class network decryption PHP fuzzy Query technology Learning notes!

For the previous knowledge of the consolidation, you can jump-type selective viewing!

/**
* Decryption PHP Fuzzy query technology
*
* This lesson is the introduction of PHP Fuzzy Query Technology Special Video Course
Outline
* The usefulness of fuzzy query, application scenarios, site search technology;
*
*
*php User Finder Case Demo
* (keyword highlighting technology)
*
* Program Flow:
* Client Submit keyword,
* The terminal obtains the keyword, through the PHP fuzzy query technology to the user Information base query related user,
* Return the query results to the client.
*
* Course Objectives:
Learn the application of PHP fuzzy query technology
* * Independent completion of the site news search function
* 3 multi-keyword fuzzy query
*
*
* Section II Video: Building of basic data
*
* User ID, user name, login password, gender, other
*
CREATE TABLE ' user ' (
' UID ' int (ten) auto_increment PRIMARY KEY COMMENT ' user ID ',
' username ' varchar (+) not NULL DEFAULT ' COMMENT ' username ',
' Password ' varchar (6) Not NULL DEFAULT ' COMMENT ' password ',
' Sex ' char (2) not NULL DEFAULT ' confidential ' COMMENT ' gender ',
' Email ' varchar (+) not NULL DEFAULT ' COMMENT ' mailbox ',
' Hobby ' varchar (255) Not NULL DEFAULT ' COMMENT ' hobby ',
KEY ' username ' (' username ')
ENGINE = MyISAM DEFAULT CHARSET = UTF8 COMMENT = ' user table ';
*
* Build Index:
* Data volume is very small reflected;
* If you follow a certain criteria to retrieve the data,
* If this condition field is not indexed, the query is traversed by the entire table,
* If an index is established, queries are queried against the index to improve query performance
* Insert some data
*
INSERT into user
Values
(NULL, ' Apple ', ' 123321 ', ' Male ', ' [email protected] ', ' basketball '),
(NULL, ' orange ', ' 463546 ', ' Male ', ' [email protected] ', ' Football '),
(null, ' pea ', ' 5465 ', ' female ', ' [email protected] ', ' table tennis '),
(NULL, ' Bananer ', ' 87957 ', ' female ', ' [email protected] ', ' volleyball '),
(null, ' Coco ', ' 1254 ', ' Male ', ' [email protected] ', ' ice hockey '),
(null, ' Hello ', ' 7542 ', ' female ', ' [email protected] ', ' Badminton '),
(NULL, ' hair ', ' 345464 ', ' Male ', ' [email protected] ', ' Yo-yo '),
(null, ' Lucy ', ' 341545 ', ' Male ', ' [email protected] ', ' rugby '),
(NULL, ' Hanmeimei ', ' 57653 ', ' female ', ' [email protected] ', ' billiards '),
(NULL, ' Lilei ', ' 353535 ', ' Male ', ' [email protected] ', ' ball '),
(NULL, ' Zhangsan ', ' 976854 ', ' female ', ' [email protected] ', ' balloon ');
*
* Get started with query syntax: Exact query, fuzzy query
*
* Return results with only one SQL query is an exact query
* Application Scenario:
* User Registration Login, user registration to determine whether the user name has been registered, log in when the account password query
* Update of heads-up data
*
* The results returned by the fuzzy query are indeterminate
* Application Scenario:
* Search within the station.
*
* The results returned by both of these queries are likely to be empty
*
* Introduction to Grammar
*mysql fuzzy query syntax supports two types of matching formats
*1.sql matching mode (one of the most used in development)
Regular expression matching mode (not recommended)
*
*sql Matching mode:
Use SQL match mode without using operator = or! =
* But with operator like or not
* *. Using SQL match mode, MySQL provides two wildcard characters.
*% represents any number of arbitrary characters (including 0)
*_ represents any single character
Using SQL match mode, if the matching format does not contain any of the two wildcard characters,
* The query effect is equal to = or! =
The use of SQL match mode, by default, is case insensitive.
*
*
*
* Use of two wildcard characters
* Query user whose name begins with a character
*
SELECT * FROM user WHERE username like ' 0% ';
* User name is queried at the end of a character
SELECT * FROM user WHERE username like '%o ';
* Query user name contains a character of the user
SELECT * FROM user WHERE username like '%o% ';
*
* Query user name length of three users
SELECT * FROM user WHERE username like ' ____ ';
*
* Query user name the second character is O user
SELECT * FROM user WHERE username like ' _o% ';
*
*
*
*
* Regular expression matching pattern
* . Match any single character
* * Match 0 or more characters in front of it
* x* denotes any number of X characters
* [...] matches any of the characters in brackets
* [ABC] match character A or B or C
* [A-z] matches any letter
* [0-9] any number
* [0-9]* matches any number of any numbers
* ^ means to start with a word or a string
* ^a denotes the beginning of the letter A
*% indicates a character or end of string
* S $ means ending with the letter S
*
* Using the regular expression matching pattern using the operator is
*regexp or not REGEXP (rlike or not rlike)
*
* If a regular expression is used to match, its pattern differs from SQL mode
*
* Query user name beginning with character L
SQL Match mode l%
Regular Expression ^1
SELECT * FROM user WHERE username REGEXP ' ^l '
*
* Query user name is exactly 4 characters
SQL Match pattern ____
Regular expressions ....
SELECT * FROM user WHERE username REGEXP ' .... '
All user names four and more than four characters of the user are queried out of the!!!!!

The regular expression matches the pattern, and its regular expression appears anywhere in the matching field, even if the pattern matches.

Note: If you use wildcard characters only. To match, the matching pattern represents a character greater than or equal to N;
This will do the following:
SELECT * FROM user WHERE username REGEXP ' ^.....$ '
*
*
*
*php Fuzzy query technology case development;
* Get Keywords
* Implement user Information retrieval
* Keyword highlighting: Technical point is the use of PHP string replacement function;
Highlight the user name keyword while traversing
while ($row = Mysql_fetch_assoc ($res)) {
$row [' username '] = str_replace ($keyword, ' <font color= ' red '. $keywords. ' </font> ', $row [' username ']);
$user []= $row;
}
*
* Basic job: Develop a site news search function (according to Yo transfer input title query related news)
* Expand the job, the site news search function (users can enter multiple keywords to search, multiple keywords separated by a space or comma)
*
*
*
*fulltext Full-Text index
* When the data volume is huge, millions above;
*
* Full-text indexing is not used in most websites;
* Data volume is huge, millions of times, the use of PHP fuzzy query technology like efficiency is relatively low, but also more cost-effective performance;
*
* Note When using:
MySQL use full-text search function, the data table engine must be myisam;
* *) MySQL full text Search feature does not support Chinese, if need to support the Chinese need to pass special processing;
*
*
*
*
*
*/

Decryption PHP Fuzzy Query technology