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

This article mainly introduces what thinkphp query methods have, have a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

I. introduction

ThinkPHP has built-in very flexible query methods, which can quickly perform data query operations. Query conditions can be used for reading, updating and deleting operations, mainly involving coherent operations such as where methods. No matter what database you use, you use almost the same query method (individual databases, such as Mongo, will have differences in expression queries). The system helps you solve the differences between different databases. Therefore, we call this query mode of the framework query language. The query language is also the ORM highlight of the ThinkPHP framework, making query operations easier to understand.

Second, query mode

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

1. Use a string as a query condition

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

The final generated SQL statement is

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

When using string query, we can use the secure preprocessing mechanism of string conditions provided by the new version, without going into details for the time being.

two。 Use arrays as query criteria

This is the most commonly used query method, such as:

The final generated SQL statement is

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

If you do a multi-field query, the default logical relationship between fields is logical and AND, but the default logical judgment can be changed with the following rules, by using _ logic to define the query logic:

The final generated SQL statement is

SELECT * FROM think_user WHERE `name` = 'thinkphp' OR `roomt` =' thinkphp'

3. Use object method to query

Take the stdClass built-in object as an example:

The final generated SQL statement is the same as above

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

The effect of using an object query is the same as that of an array query, and it is interchangeable. In most cases, we recommend using an array method to be more efficient.

Expression query

The above query condition is only a simple equality judgment. You can use query expressions to support more SQL query syntax, which is also the essence of ThinkPHP query language.

Format of query expression: $map ['field name'] = array ('expression', 'query condition')

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

EQ equals (=)

NEQ is not equal to ()

GT is greater than (>)

EGT is greater than or equal to (> =)

LT less than (100)

4.EGT: greater than or equal to (> =)

For example: $map ['id'] = array (' egt',100); the query condition is id > = 100

5.LT: less than (

IV. Quick inquiry

Since version 3.0, shortcut queries have been added to further simplify the writing of query conditions, such as:

1. Implement the same query conditions for different fields

The query condition becomes

Name= 'thinkphp' OR title =' thinkphp'

two。 Implement different query conditions for different fields

'_ multi'= > true must be added to the end of the array to indicate that the current multiple conditions match, so that the query condition becomes status= 1 AND title =' thinkphp'

More query fields are supported, such as:

$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.

Fifth, interval query

ThinkPHP supports interval query 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')

VI. Combination query

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:

The final query condition is:

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

two。 Request string query mode

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

The query conditions are as follows:

`id` > 100 AND (`status` ='1' OR `score` = '100')

7. 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:

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.

VIII. Statistical inquiry

We often use some statistics in our applications, such as the current number of all users (or meet certain conditions), the maximum points of all users, the average score of users, and so on. ThinkPHP provides a series of built-in methods for these statistical operations, including:

Count counts the quantity. Parameter is the name of the field to be counted (optional)

Max gets the maximum value, and the parameter is the field name to be counted (required)

Min gets the minimum value, and the parameter is the name of the field to be counted (required)

Avg gets the average. The parameter is the name of the field to be counted (required)

Sum gets the total score. The parameter is the name of the field to be counted (required)

Usage example:

$User = M ("User"); / / instantiate the User object

Get the number of users: $userCount = $User- > count ()

Or according to the field statistics: $userCount = $User- > count ("id")

Get the user's maximum points: $maxScore = $User- > max ('score')

Get the minimum points for users whose credits are greater than 0: $minScore = $User- > where ('score > 0')-> min (' score')

Get the average points of the user: $avgScore = $User- > avg ('score')

Count the total scores of users: $sumScore = $User- > sum ('score')

And all statistical queries support the use of coherent operations.

9. SQL query

ThinkPHP's built-in ORM and ActiveRecord patterns achieve convenient data access operations, and the new version adds coherent operations to make this data operation clearer, but ThinkPHP still retains the native SQL query and execution operation support. In order to meet the needs of complex queries and some special data operations, the return value of SQL query does not do any processing because it is directly returned by Db query results. It mainly includes the following two methods:

1.query method: execute SQL query operation

Usage query ($sql,$parse=false)

Parameter sql (required): SQL statement to query

Parse (optional): whether you need to parse SQL

Return value returns false if the data is illegal or the query is incorrect.

Otherwise, the query result dataset is returned (same as the select method)

Examples of use:

If you currently use a distributed database and set read-write separation, the query method is always executed on the read server, so the query method corresponds to a read operation, regardless of your SQL statement.

2.execute method: execute sql operation for updating and writing data

Usage execute ($sql,$parse=false)

Parameter sql (required): the SQL statement to be executed

Parse (optional): whether you need to parse SQL

Return value returns false if the data is illegal or the query is incorrect. Otherwise, the number of records affected is returned.

Examples of use:

The dynamic query method of multiple data fields is not supported for the time being. Please use find method and select method to query.

2.getFieldBy dynamic query: queries for a field and returns the value of a field, for example

$userId = $User- > getFieldByName ('liu21st','id')

Indicates that the id value of the user is obtained based on the user's name.

11. Sub-query

Subquery support has been added since version 3.0, and can be used in two ways:

1. Use the select method

When the parameter of the select method is false, it means that no query is performed and only the construction SQL is returned, for example:

/ / first construct a subquery SQL

$subQuery = $model- > field ('id,name')-> table (' tablename')-> group ('field')-> where ($where)-> order (' status')-> select (false)

When the select method passes in the false parameter, it means that the current query is not executed, but only the query SQL is generated.

2. Use the buildSql method

$subQuery = $model- > field ('id,name')-> table (' tablename')-> group ('field')-> where ($where)-> order (' status')-> buildSql ()

After calling the buildSql method, the actual query operation is not performed, but the SQL statement of the query is generated (to avoid confusion, parentheses are added on both sides of the SQL), and then we call it directly in the subsequent query.

/ / query using subquery

$model- > table ($subQuery.' A')-> where ()-> order ()-> select ()

The constructed subquery SQL can be used in the coherent operation methods of ThinkPHP, such as table where and so on.

Thank you for reading this article carefully. I hope the article "what are the thinkphp query methods" shared by the editor will be helpful to you? at the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.