Shulou(Shulou.com)06/03 Report--

This article mainly introduces "the specific usage of where method in ThinkPHP". In daily operation, I believe that many people have doubts about the specific usage of where method in ThinkPHP. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "the specific usage of where method in ThinkPHP". Next, please follow the editor to study!

The most common but complex query method for ThinkPHP CURD operations is the where method. The where method is also one of the coherent operation methods of the model class, which is mainly used for querying and setting operation conditions.

The usage of where method is not only the essence of ThinkPHP query language, but also an important part and highlight of ThinkPHP ORM (object Relational Mapping). It can complete query operations including general query, expression query, shortcut query, interval query and combination query. The parameters of the where method support strings and arrays, and although you can also use objects, it is not recommended.

1. String condition

Use string conditions to query and operate directly, for example:

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

The final generated SQL statement is

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

If version 3.1 or above is used, it is recommended to cooperate with the preprocessing mechanism to ensure more security when using string conditions, for example:

$Model- > where ("id=%d and username='%s' and xx='%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 submission or URL address, and if a non-numeric type is passed in, the query operation will be forced to be formatted into a numeric format.

The format type of string preprocessing supports specifying numbers, strings, etc. For more information, please see the parameter description of vsprintf method.

two。 Array condition

The where use of array conditions is recommended by ThinkPHP.

General query

The simplest array query is as follows:

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

The final generated SQL statement is

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

Expression query

The above query condition is only a simple equality judgment. You can use query expressions to support more SQL query syntax and the format of query expressions:

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

Expressions, regardless of case, support the following query expressions, which mean:

The expression means that EQ is equal to (=) NEQ is not equal to () GT is greater than (>) EGT is greater than or equal to (> =) LT is less than

EGT: greater than or equal to (> =)

For example:

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

The query condition represented is id > = 100

LT: less than (save ($data); / / Save modified data according to conditions

Quick query

The where method supports shortcut queries, which can further simplify the writing of query conditions, such as:

1. Implement the same query conditions for different fields

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

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

Second, realize different query conditions for different fields.

$User = M ("User"); / instantiate the User object $map ['status&title'] = array (' 1instantiation method $User- > where ($map)-> select ()

'_ multi'= > true must be added to the end of the array to indicate that multiple conditions are currently matched, 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 gt','0'), 'thinkphp','_multi'= > true)

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

Note: "|" and "&" cannot be used at the same time in shortcut queries.

Interval query

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

$map ['id'] = array (array (' gt',1), array ('lt',10))

The query condition obtained is (`id` > 1) AND (`id`)

< 10) $map['id'] = array(array('gt',3),array('lt',10), 'or') ; 得到的查询条件是： (`id` >

3) OR (`id`

< 10) $map['id'] = array(array('neq',6),array('gt',3),'and'); 得到的查询条件是：(`id` != 6) AND (`id` >

3)

The last one can be the AND, OR, or XOR operator. If you don't write it, the default is the AND operation.

The conditions of interval queries can support all expressions of ordinary queries, that is, expressions such as LIKE, GT, and EXP can be supported. In addition, interval queries can support more conditions, as long as the conditions for a field can be written together, such as:

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

The final query conditions are:

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

Combined query

Composite queries are used for complex query conditions. If you need to use strings occasionally but do not want to lose arrays, you can consider using composite queries.

The main body of the combined query is still an array query, but some special query support is added, including string pattern query (_ string), compound query (_ complex) and request string query (_ query). The special query in the hybrid query can only be defined one at a time. Because the array index is used, special queries with the same index will be overwritten.

1. String mode query (using _ string as query condition)

Array conditions can also be mixed with string conditions, such as:

$User = M ("User"); / / instantiate the User object $map ['id'] = array (' neq',1); $map ['name'] =' ok';$map ['_ string'] = 'status=1 AND score > 10 alternate user-> where ($map)-> select ()

The final query condition is:

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

Second, the query mode of request string

Request string query is a way of passing parameters similar to URL, which can support simple conditional equality judgment.

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

The query condition obtained is: `id` > 100 AND (`status` ='1' OR `score` = '100')

3. Compound query

The composite query is equivalent to encapsulating a new query condition and then merging it into the original query condition, so it can complete the more 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%'))

Composite query uses _ complex as a subquery condition to define. With the previous query method, more complex query conditions can be made very flexibly.

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

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

The resulting SQL statement is consistent.

3. Multiple calls

Since version 3.1.3, the where method supports multiple calls, but string conditions can only occur once, for example:

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

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

At this point, the study of "the specific use of the where method in ThinkPHP" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.