How to use the Where () in thinkphp

This article mainly introduced about the thinkphp in the Where () use of the method is detailed, has a certain reference value, now share to everyone, the need for friends can refer to

The Where method can be used to filter the results of a database operation. That is, the WHERE clause in the SQL query statement. This article introduces you to the thinkphp in the Where () use of the detailed, interested friends reference

This article describes the use of the Where () method of thinkphp. The Where method can be used to filter the results of a database operation. That is, the WHERE clause in the SQL query statement.

Today to tell you the most common but also the most complex where method, where method is also part of the model class of coherent operation method, mainly for the query and operation conditions of the settings.

The use of the Where method is the essence of thinkphp query Language, is also an important part of thinkphp ORM and highlights, can be completed including ordinary query, expression query, quick query, interval query, combined query, including query operations. The arguments of the Where method support strings and arrays, although objects can also be used but are not recommended.

String condition

Use string conditions to query and manipulate directly, 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 use more than 3.1 versions, it is recommended that you use a preprocessing mechanism to ensure more security when using string conditions, for example:

$Model->where ("id=%d and Username= '%s ' andxx= '%f '", Array ($id, $username, $xx))->select ();

or use:

$Model->where ("id=%d and Username= '%s ' and xx= '%f '", $id, $username, $xx)->select ();

If the $id variable comes from a user commit or URL address, if the incoming non-numeric type is forced to be formatted as a number format, the query operation is performed.
String preprocessing format types support specifying numbers, strings, and so on, so you can refer to the parameter descriptions of the vsprintf method.

Array condition

The where usage of the array condition is the recommended usage for thinkphp.

General Query

The simplest way to query an array is as follows:

$User = M ("User"); Instantiate the User object $map[' name '] = ' thinkphp '; $map [' status '] = 1;//to pass the query condition into the Query method $user->where ($map)->select ();

The last SQL statement generated is

SELECT * from Think_user WHERE ' name ' = ' thinkphp ' and Status=1

An expression query

The above query condition is just a simple equality judgment, you can use the query expression to support more SQL query syntax, the use of the query expression format:

$map [' field 1 '] = Array (' expression ', ' query Condition 1 '), $map [' field 2 '] = Array (' expression ', ' query Condition 2 '), $Model->where ($map)->select (); also supports

Expressions are not case-sensitive, and the supported query expressions are in the following ways, meaning that:

Meaning of an expression

EQ Equals (=)
NEQ not equal to (<>)
GT greater than (>)
EGT greater than or equal to (>=)
LT is less than (<)
ELT less than or equal (<=)
Like fuzzy query
[NOT] Between (not) interval query
[NOT] In (not) in query
EXP expression query, support SQL syntax

Examples are as follows:

EQ: Equals (=)

For example:

$map [' id '] = array (' eq ', 100);

Equivalent to the following query

$map [' id '] = 100;

The query condition represented is ID = 100

NEQ: Not equal to (<>)

For example:

$map [' id '] = array (' NEQ ', 100);

The query condition represented is ID <> 100

GT: Greater Than (>)

For example:

$map [' id '] = array (' GT ', 100);

The query condition represented is ID > 100

EGT: greater than or equal to (>=)

For example:

$map [' id '] = array (' EGT ', 100);

The query condition represented is ID >= 100

LT: Less Than (<)

For example:

$map [' id '] = array (' lt ', 100);

The query condition represented is ID < 100

ELT: Less than or equal (<=)

For example:

$map [' id '] = array (' ELT ', 100);

The query condition represented is ID <= 100

[NOT] Like: similar to SQL

For example:

$map [' name '] = Array (' Like ', ' thinkphp% ');

Query condition becomes name like ' thinkphp% '

If the Db_like_fields parameter is configured, some fields are also automatically fuzzy-queried. For example, set up:

' Db_like_fields ' = ' title|content '

words, use

$map [' title '] = ' thinkphp ';

Query condition becomes name like '%thinkphp% '
Array methods are supported, such as

$map [' A '] =array (' Like ', Array ('%thinkphp% ', '%tp '), ' OR '), $map [' B '] =array (' Notlike ', Array ('%thinkphp% ', '%tp '), ' and ');

The resulting query condition is:

(A like '%thinkphp% ' OR a Like '%tp ') and (b not like '%thinkphp% ' and b ' = '%TP ')

[NOT] Between: With SQL [not] between, the query condition supports strings or arrays, for example:

$map [' id '] = array (' Between ', ' 1,8 ');

and the following equivalent:

$map [' id '] = array (' Between ', Array (' 1 ', ' 8 '));

Query condition becomes ID between 1 and 8

[NOT] In: With SQL [NOT] in, the query condition supports strings or arrays, for example:

$map [' id '] = array (' Not ', ' 1,5,8 ');

and the following equivalent:

$map [' id '] = array (' Not in ', Array (' 1 ', ' 5 ', ' 8 '));

Query condition becomes ID not in (1, 5, 8)

EXP: Expression to support more complex query conditions

For example:

$map [' id '] = array (' In ', ' 1,3,8 ');

Can be changed to:

$map [' id '] = Array (' exp ', ' in (1,3,8) ');

The conditions of the EXP query are not treated as strings, so the following query criteria can use any SQL-supported syntax, including the use of functions and field names.

Query expressions can be used not only for query criteria, but also for data updates, such as:

$User = M ("User"); Instantiate the User object//To modify the data object property assignment $data[' name '] = ' thinkphp '; $data [' score '] = Array (' exp ', ' score+1 ');//user's integration plus 1$user-> Where (' id=5 ')->save ($data); Save modified data based on conditions

Quick Query

The Where method supports shortcut queries, which further simplifies the writing of query conditions, such as:

First, the implementation of different fields of the same query criteria

$User = M ("User"); Instantiate the User object $map[' name|title ' = ' thinkphp ';//pass query condition into Query method $user->where ($map)->select ();

Query condition becomes name= ' thinkphp ' OR title = ' thinkphp '

Second, the implementation of different fields and different query conditions

$User = M ("User"); Instantiate the User object $map[' Status&title ' =array (' 1 ', ' thinkphp ', ' _multi ' =>true);//Pass the query condition into the Query method $user->where ($MAP) ->select ();

The ' _multi ' =>true must be added at the end of the array, indicating that the current is a multi-conditional match so that the query condition becomes status= 1 and title = ' thinkphp ', and the query field supports more, for example:

$map [' Status&score&title '] =array (' 1 ', Array (' GT ', ' 0 '), ' thinkphp ', ' _multi ' =>true);

The query condition becomes status= 1 and score >0 and title = ' thinkphp '

Note: "|" in quick query mode and "&" cannot be used at the same time.

Interval query

The Where method supports interval queries for a field, for example:

$map [' id '] = Array (array (' GT ', 1), Array (' LT ', 10));

The resulting query criteria are: (' id ' > 1) and (' ID ' < 10)

$map [' id '] = array (' GT ', 3), array (' LT ', ten), ' or ');

The resulting query criteria are: (' ID ' > 3) OR (' ID ' < 10)

$map [' id '] = Array (Array (' NEQ ', 6), Array (' GT ', 3), ' and ');

The resulting query criteria are: (' id '! = 6) and (' ID ' > 3)
The last one can be an and, or or XOR operator, and if not, the and operation is the default.

The conditions of the interval query can support all expressions of a normal query, which means expressions such as like, GT, and exp can be supported. In addition, the interval query can also support more conditions, as long as the conditions for a field can be written together, for example:

$map [' name '] = Array (' Like ', '%a% '), an array (' like ', '%b% '), an array (' like ', '%c% '), ' thinkphp ', ' or ');

The final query condition is:

(' name ' like '%a% ') or (' name ' like '%b% ') or (' name ' like '%c% ') or (' name ' = ' thinkphp ')

Combination Query

Combined queries are used for complex query conditions, and you might consider using a composite query if you need to use strings at the same time in a query and do not want to lose the flexibility of array methods.

The body of the combined query is queried in an array way, except for the addition of special query support, including string pattern query (_string), compound query (_complex), request string Query (_query), and a special query in mixed queries can only define one per query. Special queries with the same index will be overwritten because of the array index.

One, string pattern query (using _string as the query condition)

Array conditions can also be mixed with string conditions, for example:

$User = M ("User"); Instantiate the User object $map[' id ' = Array (' NEQ ', 1), $map [' name '] = ' OK ', $map [' _string '] = ' status=1 and score>10 '; $User where ($map)->select ();

The final query conditions are as follows:

(' id '! = 1) and (' name ' = ' OK ') and (Status=1 and SCORE>10)

Second, request string Query method

Request string queries are similar to URL parameters, and can support simple conditional equality judgments.

$map [' id '] = array (' GT ', ' n '); $map [' _query '] = ' status=1&score=100&_logic=or ';

The resulting query condition is: ' ID ' >100 and (' status ' = ' 1 ' OR ' score ' = ' 100 ')

Three, compound query

Compound query is equivalent to encapsulate a new query condition, and then merge into the original query condition, so it can complete complex query condition assembly.
For example:

$where [' name '] = Array (' Like ', '%thinkphp% '); $where [' title '] = Array (' Like ', '%thinkphp% '); $where [' _logic '] = ' or '; $ map[' _complex ' = $where; $map [' id '] = array (' GT ', 1);

The query condition is

(ID > 1) and ((name like '%thinkphp% ') OR (title like '%thinkphp% '))

Compound query using _complex as a sub-query criteria to define, with the previous query method, can be very flexible to develop more complex query conditions.

Many query methods can be converted to each other, such as the above query conditions can be changed to:

$where [' id '] = array (' GT ', 1); $where [' _string '] = ' (Name like "%thinkphp%") OR (title like "%thinkphp") ';

The last generated SQL statement is consistent.

Multiple calls

Starting with the 3.1.3 version, the Where method supports multiple invocations, but the string condition can occur only once, for example:

$map [' a '] = array (' GT ', 1), $where [' b '] = 1; $Model->where ($map)->where ($where)->where (' Status=1 ') Select ();

Multiple array conditional expressions are eventually merged, but string conditions are supported only once.

About the use of the Where () method in thinkphp, this article introduces so much, I hope you have some help, thank you!