ThinkPHP where () usage details, thinkphpwhere_PHP tutorial

Thinkphpwhere. Thinkphpwhere describes how to use where () in ThinkPHP. This article describes how to use where () in ThinkPHP. The where method can be used to filter database operation results. That is, how to use where () in the SQL query statement ThinkPHP, thinkphpwhere

This article describes how to use the where () method of ThinkPHP. The where method can be used to filter database operation results. That is, the where clause in the SQL query statement.

Today, I would like to explain to you the most common but complex where method for queries. The where method is also one of model-class coherent operation methods and is mainly used to set query and operation conditions.

The usage of the where method is the essence of ThinkPHP query language and an important component and highlight of ThinkPHP ORM, you can perform common queries, expression queries, quick queries, interval queries, and combined queries. The where method supports strings and arrays. Although objects can be used, it is not recommended.

String condition

Directly query and operate using string conditions, for example:

$ User = M ("User"); // instantiate the User object $ User-> where ('Type = 1 AND status = 1')-> select ();

The last generated SQL statement is

SELECT * FROM think_user WHERE type=1 AND status=1

If you use version 3.1 or later, we recommend that you use the preprocessing mechanism to ensure better 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 is submitted by the user or the URL address, if it is not of the numeric type, it is forcibly formatted as a number for query.
String preprocessing formats support specifying numbers and strings. for details, refer to the parameter description of the vsprintf method.

Array conditions

The where usage of array conditions is recommended by ThinkPHP.

Common Query

The simplest way to query arrays is as follows:

$ User = M ("User"); // instantiate the User object $ map ['name'] = 'thinkphp'; $ map ['status'] = 1; // input the query condition into the query method $ User-> where ($ map)-> select ();

The last generated SQL statement is

SELECT * FROM think_user WHERE `name`='thinkphp' AND status=1

Expression query

The preceding query condition is just a simple equality judgment. you can use a query expression to support more SQL query syntaxes. the format of the query expression is as follows:

$ Map ['Field 1'] = array ('expression', 'query condition 1'); $ map ['Field 2'] = array ('expression ', 'query condition 2'); $ Model-> where ($ map)-> select (); // also supported

The expressions are case-insensitive and support the following query expressions:

Expression meaning

EQ equals (=)
NEQ is not equal to (<>)
GT greater than (>)
EGT greater than or equal to (> =)
LT is less than (<)
ELT less than or equal to (<=)
LIKE fuzzy query
[NOT] BETWEEN (NOT) interval query
[NOT] IN (NOT) IN query
EXP expression query, supporting SQL syntax

Example:

EQ: equal to (=)

For example:

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

It is equivalent to the following query:

$map['id'] = 100;

The query condition is id = 100.

NEQ: not equal to (<>)

For example:

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

The query condition is id <> 100

GT: greater than (>)

For example:

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

Indicates that the query condition is id> 100.

EGT: greater than or equal to (> =)

For example:

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

The query condition is id> = 100.

LT: less than (<)

For example:

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

The query condition is id <100

ELT: less than or equal to (<=)

For example:

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

The query condition is id <= 100

[NOT] LIKE: LIKE of SQL

For example:

$map['name'] = array('like','thinkphp%');

The query condition becomes name like 'thinkphp %'

If the DB_LIKE_FIELDS parameter is configured, some fields are automatically fuzzy queried. For example:

'DB_LIKE_FIELDS'=>'title|content'

Use

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

The query condition will change to name like '% thinkphp %'
Supports arrays, such

$map['a'] =array('like',array('%thinkphp%','%tp'),'OR');$map['b'] =array('notlike',array('%thinkphp%','%tp'),'AND');

The generated query conditions are:

(a like '%thinkphp%' OR a like '%tp') AND (b not like '%thinkphp%' AND b not like '%tp')

[NOT] BETWEEN: same as SQL [not] between. query conditions support strings or arrays, for example:

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

It is equivalent to the following:

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

The query condition becomes id BETWEEN 1 AND 8.

[NOT] IN: same as SQL [not] in. query conditions support strings or arrays, for example:

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

It is equivalent to the following:

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

The query condition becomes id not in (, 8)

EXP: expression that supports more complex queries

For example:

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

You can change it:

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

Exp query conditions are not treated as strings, so the following query conditions can use any syntax supported by SQL, including using functions and field names.

A query expression can be used not only for query conditions, but also for data updates. for example:

$ User = M ("User"); // instantiate the User object // assign a value to the attribute of the data object to be modified $ data ['name'] = 'thinkphp '; $ data ['score '] = array ('Exp', 'Score + 1'); // add 1 $ User points> where ('Id = 5 ') -> save ($ data); // save the modified data according to the conditions.

Quick Query

The where method supports the quick query method, which can further simplify the writing of query conditions, for example:

1. implement the same query conditions for different fields

$ User = M ("User"); // instantiate the User object $ map ['name | title'] = 'thinkphp '; // input the query condition into the query method $ User-> where ($ map)-> select ();

The query condition becomes name = 'thinkphp' OR title = 'thinkphp'

2. implement different query conditions for different fields

$ User = M ("User"); // instantiate the User object $ map ['status & title'] = array ('1', 'thinkphp ', '_ multi' => true); // input the query condition to the query method $ User-> where ($ map)-> select ();

'_ Multi' => true must be added at the end of the array, indicating that the current multi-condition match is performed. in this way, the query condition becomes status = 1 AND title = 'thinkphp ', more query fields are supported, for example:

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

The query condition changes to status = 1 AND score> 0 AND title = 'thinkphp'

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

Interval query

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

$ Map ['id'] = array ('GT ', 1), array ('Lt', 10 ));

The query conditions are as follows: ('id'> 1) AND ('id' <10)

$ Map ['id'] = array ('GT ', 3), array ('Lt', 10), 'OR ');

The query conditions are as follows: ('id'> 3) OR ('id' <10)

$ Map ['id'] = array ('neq', 6), array ('GT ', 3), 'and ');

The query condition is: ('id '! = 6) AND ('id'> 3)
The last one can be the AND, OR, or xor operator. If no value is specified, it is the AND operation by default.

The condition of the interval query can support all expressions of a common query, that is, expressions LIKE, GT, and EXP are supported. In addition, range query supports more conditions, as long as the conditions for a field can be written together, for example:

$map['name'] = array(array('like','%a%'), array('like','%b%'), array('like','%c%'), 'ThinkPHP','or'); 

The final query condition is:

(`name` LIKE '%a%') OR (`name` LIKE '%b%') OR (`name` LIKE '%c%') OR (`name` = 'ThinkPHP')

Combined query

Composite query is used for complex query conditions. if you need to use strings occasionally while querying but do not want to lose arrays flexibly, you can use composite query.

The subject of the combined query is still queried in array mode, but some special query support is added, including string mode query (_ string) and composite query (_ complex), request string query (_ query). only one special query can be defined for each query in a hybrid query. because of the array index method, special queries with the same index will be overwritten.

1. query in string mode (using _ string as the query condition)

Array conditions can also be used together 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 condition is:

( `id` != 1 ) AND ( `name` = 'ok' ) AND ( status=1 AND score>10 )

II. request string query method

Request string query is similar to passing parameters through a URL. it supports simple equality criteria.

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

The obtained query conditions are: 'id'> 100 AND ('status' = '1' OR 'score '= '123 ')

III. Composite query

A composite query encapsulates a new query condition and is then incorporated into the original query condition. Therefore, complex query condition assembly can be completed.
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%') )

The composite query uses _ complex as the subquery condition. in combination with the previous query method, you can flexibly define more complex query conditions.

Many query methods can be converted to each other. for example, the preceding query conditions can be changed:

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

The final SQL statements are consistent.

Multiple calls

Starting from version 3.1.3, the where method supports multiple calls, but the string condition can only appear once. for example:

$map['a'] = array('gt',1);$where['b'] = 1;$Model->where($map)->where($where)->where('status=1')->select();

Multiple array condition expressions are merged, but the string condition is only supported once.

This article describes how to use the where () method in ThinkPHP. I hope it will help you. thank you!

For more information about how to use thinkphpwhere, this article describes how to use the where () method of ThinkPHP. The where method can be used to filter database operation results. SQL query statement...