How to implement PHP query and how to implement queries in thinkphp

Query function is often used in the daily development, today we PHP Chinese network with you to comb the query function implementation method, as well as a brief introduction of how to use the query function in thinkphp.

SQL match mode

1. Use SQL match mode, cannot use operator = or! =, but using the operator like or not like;

2. Using SQL match mode, MySQL provides 2 wildcard characters.

% denotes any number of characters (including 0)

_ Denotes any single character

3. Using SQL match mode, if the matching format does not contain any of the above 2 wildcard characters, its query effect is equal to = or! =

4. Use SQL match mode when matching, case insensitive

#查询用户名以某个字符开头的用户 # Query User name begins with the character ' L ': l%select * from user WHERE username like ' l% '; #查询用户名以某个字符结尾的用户 # query user whose name ends with the character ' E ': e% SELECT * from user where username like ' e% '; #查询用户名包含某个字符的用户 # Query user name contains the character ' o ' User:%o%select * from user where username like '%o% '; #查询包含三个字符的用户SELECT * from the user where username like ' _ '; #查询用户名第二个字符为o的用户: _o%select * from the user where username like ' _o% ';

Regular expression matching pattern

wildcard character (regular expression)

. Match any single character

* Match 0 or more characters in front of it

x* means matching any number of x characters

[..] Match any of the characters in brackets
[ABC] match character AB or C
[A-z] matches any letter
[0-9] Match any number
[0-9]* matches any number of numbers
[a-z]* matches any number of letters

^ denotes starting with a character or string

^a that begins with the letter A

$ means ending with a character or string

S $ means ending with the letter S

The operators used with regular expression matching patterns are:

REGEXP or not REGEXP (rlike or not rlike)

Note: The regular expression matches the pattern, and its regular expression appears anywhere in the matching field,

Even if the pattern is matched, it is not necessary to put a wildcard character on both sides to match it;

If only wildcard characters are used. To match, assuming N, then its matching pattern is expressed, greater than or equal to N;

How do you understand the above sentence?

So

... Match data greater than or equal to 3 characters
.... Match data greater than or equal to 4 characters
#查询用户名以字符 l User: ^l;
#正则表达式写法

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 ' ^...$ ';

thinkphp like fuzzy query

At present, there are more and more people using thinkphp framework for project development, because of its good encapsulation, which leads to a lot of pure PHP development, the example of this paper is illustrated with like Fuzzy query.

Here are the main examples of usage:

Thinkphp can support direct use of strings as query criteria, but most of the cases recommend using indexed arrays or objects as query criteria because they are more secure.

I. Using strings as query criteria

This is the most traditional way, but the security is not high,
For example:

$User = M ("User"); Instantiate the User object $user->where (' type=1 and Status=1 ')->select ();

The last SQL statement generated is

SELECT * from Think_user WHERE type=1 and Status=1

If you make a multi-field query, the default logical relationship between fields is logical and and, but with the following rules you can change the default logical judgments by using _logic to define the query logic:

$User = M ("User"); Instantiate the User object $condition[' name '] = ' thinkphp '; $condition [' account '] = ' thinkphp '; $condition [' _logic '] = ' OR ';// Pass query condition into Query method $user->where ($condition)->select ();

The last SQL statement generated is

SELECT * from Think_user WHERE ' name ' = ' thinkphp ' OR ' account ' = ' thinkphp '

Second, array mode as a query condition

So much has been said about how the like query is implemented, see below

$userForm =m (' user '); $where [' Name ']=array (' like ', ' php% '); $userForm->where ($where)->select ();

The like query here is:

Name like ' php% '

Query statement:

$where [' Name ']=array (' like ', Array ('%php% ', '%.com '), ' OR ');