thinkphp Query, 3.X 5.0 method

The following small series for everyone to bring a thinkphp query, 3.X 5.0 method (pro-test feasible). Small series feel very good, now share to everyone, also for everyone to make a reference. Let's take a look at it with a little knitting.

First, Introduction

Thinkphp built-in a very flexible query method, you can quickly do data query operations, query conditions can be used for reading, update and delete operations, mainly related to the where method and other coherent operations, regardless of the database used, You almost use the same query method (individual databases such as MONGO in the expression query will be different), the system to help you solve the differences in the database, so we call the framework of this query is called the query language. The query language is also the ORM highlight of the thinkphp framework, making query operations easier to understand. The following one by one explains the meaning of the query language.

Second, inquiry method

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.

1. Use strings as query criteria

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

<? php
$ 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

When using string queries, we can use the security preprocessing mechanism of the string condition provided by the new version, and we will not dwell on it.

2. Using Arrays as query criteria

This is the most common way to query, for example:

<? php
$ User = M ("User"); // Instantiate the User object
$ condition ['name'] = 'thinkphp';
$ condition ['status'] = 1;
// Pass the query conditions into the query method
$ User-> where ($ condition)-> select ();
?>

The last SQL statement generated is

SELECT * from Think_user WHERE ' name ' = ' thinkphp ' 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:

<? php
$ User = M ("User"); // Instantiate the User object
$ condition ['name'] = 'thinkphp';
$ condition ['account'] = 'thinkphp';
$ condition ['_ logic'] = 'OR';
// Pass the query conditions into the query method
$ User-> where ($ condition)-> select ();
?>

The last SQL statement generated is

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

3. Use object mode to query

Here is an example of a stdclass built-in object:

<? php
$ User = M ("User"); // Instantiate the User object
// define query conditions
$ condition = new stdClass ();
$ condition-> name = 'thinkphp';
$ condition-> status = 1;
$ User-> where ($ condition)-> select ();
?>

The last SQL statement generated is the same as above

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

The effect of querying and using an array query using object mode is the same, and is interchangeable, and in most cases we recommend an array approach to be more efficient.

Third, expression query

The above query condition is just a simple equality judgment, you can use the query expression to support more SQL query syntax, is also the essence of thinkphp query language,

Use format for query expression: $map [' field name '] = Array (' expression ', ' query condition ');

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

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:

1.EQ: Equals (=)

For example: $map [' id '] = array (' eq ', 100), and the following query is equivalent $MAP [' id '] = 100; the query condition is ID = 100

2.NEQ: Not equal to (<>)

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

3.GT: Greater Than (>)

For example: $map [' id '] = array (' GT ', 100); the query condition is ID > 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 (<)

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

6.ELT: Less than equals (<=)

For example: $map [' id '] = array (' ELT ', 100); the query condition represented by ID <= 100

7.[not] Like: similar to SQL

For example: $map [' name '] = Array (' Like ', ' thinkphp% '); the query condition becomes the name "thinkphp%"

If the Db_like_fields parameter is configured, some fields are also automatically fuzzy-queried. For example set: ' Db_like_fields ' + ' title|content ' words, use $map [' title '] = ' thinkphp '; the query will become the name like '%thinkphp% '

Array mode supported:

For example $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 ')

8.[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

9.[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)

10.EXP: Expressions that support more complex query scenarios

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:

<? php
$ User = M ("User"); // Instantiate the User object
// Assignment of data object properties to be modified
$ data ['name'] = 'ThinkPHP';
$ data ['score'] = array ('exp', 'score + 1'); // user's points plus 1
$ User-> where ('id = 5')-> save ($ data); // Save the modified data according to the conditions
?>

Fourth, quick query

Starting with version 3.0, the Quick Query method is added to further simplify the wording of the query criteria, such as:

1. Implement the same query criteria for different fields

<? php
$ User = M ("User"); // Instantiate the User object
$ map ['name | title'] = 'thinkphp';
// Pass the query conditions into the query method
$ User-> where ($ map)-> select ();
?>

The query condition becomes

Name= ' thinkphp ' OR title = ' thinkphp '

2. Implement different query criteria for different fields

<? php
$ User = M ("User"); // Instantiate the User object
$ map ['status & title'] = array ('1', 'thinkphp', '_ multi' => true);
// Pass the query conditions 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-condition match so that the query condition becomes status= 1 and title = ' thinkphp ',

Query fields support more, such as:

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

Fifth, interval query

Thinkphp supports interval queries for a field,

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

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

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

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

For example: $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 ')

Sixth, combination query

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.

1. String pattern query (using _string as the query condition)

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

<? php
$ 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)

2. Request String Query method

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

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

The resulting query conditions are:

' ID ' >100 and (' status ' = ' 1 ' OR ' score ' = ' 100 ')

Seventh, 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:

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

Eighth, statistical inquiry

In the application we often use some statistics, such as all current (or meet certain conditions) of the number of users, the maximum points for all users, the average score of users, and so on, thinkphp for these statistical operations to provide a series of built-in methods, including:

Count statistic number, parameter is the field name to be counted (optional)
Max Gets the maximum value, the parameter is the field name to be counted (must)
Min Gets the minimum value, the parameter is the field name to be counted (must)
Avg Gets the average, parameter is the field name to be counted (must)
Sum gets the total score, the parameter is the field name to be counted (must)

Usage examples:

$User = M ("User"); Instantiating a User object

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

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

Get the maximum number of points for a user: $maxScore = $User->max (' score ');

Minimum points for users with points greater than 0: $minScore = $User->where (' score>0 ')->min (' score ');

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

Statistical user's total: $sumScore = $User->sum (' score ');

And all statistical queries support the use of coherent operations.

Ninth, SQL query

Thinkphp's built-in ORM and ActiveRecord modes enable easy data access operations, and the new version adds a more coherent operation to make this data operation clearer, but thinkphp still retains native SQL queries and performs operational support. In order to meet the needs of complex queries and some special data operations, the return value of the SQL query is returned as a result of the query of the DB class directly, without any processing. The following two methods are mainly included:

1.query method: Execute SQL query operation

Usage query ($sql, $parse =false)

Parameter SQL (required): SQL statement to query

Parse (optional): Whether to parse SQL

Return value FALSE if data is illegal or query error

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

Examples of Use:

<? php
$ Model = new Model () // Instantiate a model object without corresponding data table
$ Model-> query ("select * from think_user where status = 1");
?>

If you are currently using a distributed database and have read-write separation, the query method is always executed at 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): SQL statement to execute

Parse (optional): Whether to parse SQL

Return value if the data is illegal or the query error returns false otherwise the number of records affected is returned

Examples of Use:

<? php
$ Model = new Model () // Instantiate a model object without corresponding data table
$ Model-> execute ("update think_user set name = 'thinkPHP' where status = 1");
?>
If you are

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

Tenth, dynamic query

With the feature of PHP5 language, thinkphp implements dynamic query, and the dynamic query method of core model includes the following:

Getby querying data based on the value of a field for example, Getbyname,getbyemail
Getfieldby Query and return the value of a field based on a field for example, Getfieldbyname

1.getBy Dynamic Query: The query records the fields of the data table.

For example, if the user object has properties such as id,name,email,address, then we can use the following query method to query the qualifying records directly based on a property.

<? php
$ user = $ User-> getByName ('liu21st');
$ user = $ User-> getByEmail ('liu21st@gmail.com ');
$ user = $ User-> getByAddress ('Shenzhen, China');
?> 

Dynamic query methods that do not support multiple data fields temporarily, use the Find method and the Select method to query.

2.getFieldBy Dynamic Query: Queries for a field and returns the value of a field, such as

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

Represents the ID value of the user that is obtained based on the user's name.

Eleventh. Sub-query

New subquery support has been added since version 3.0 and is available in two ways:

1. Using the Select method

When the parameter of the Select method is false, it means that no query is simply returned to the build SQL, 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 the false parameter, it means that the current query is not executed, but only the query SQL is generated.

2. Using the BuildSQL method

$subQuery = $model->field (' Id,name ')->table (' tablename ')->group (' field ')->where ($where)->order (' Status ')->buildsql ();

When the BuildSQL method is called, the actual query operation is not performed, but only the SQL statement that generated the query (in order to avoid confusion, the parentheses are added to the SQL), and then we call directly in subsequent queries.

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

The constructed subquery SQL can be used for thinkphp's coherent operation methods, such as table where and so on.

The above thinkphp query, 3.X 5.0 method (pro-test feasible) is small part of the whole content to share to everyone, hope to give you a reference, but also hope that we support a lot of script home.