1. 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 almost use the same query method (individual databases such as Mongo will have differences in expression query). The system helps you solve the differences between different databases, so we call this query method of the framework a query language. The query language is also the ORM highlight of the ThinkPHP framework, making query operations easier and easier to understand. Let’s explain the connotation of query language one by one.
2. Query method
ThinkPHP can support the use of strings directly as query conditions, but in most cases it is recommended to use index arrays or objects as query conditions because it will be safer.
1. Use strings as query conditions
This is the most traditional way, but it is not very safe, for example:
<?php
$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
When using string query, we can use the secure preprocessing mechanism of string conditions provided by the new version, and we will not go into details for now.
2. Use arrays as query conditions
This method is the most commonly used query method, 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 generated SQL statement is
SELECT * FROM think_user WHERE `name`='thinkphp' AND status=1
If you perform a multi-field query, the default logical relationship between fields is logic and AND, but the following rules can be used to change the default logical judgment, and define the query logic by using _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 generated SQL statement is
SELECT * FROM think_user WHERE `name`='thinkphp' OR `account`='thinkphp'
3. Use object method to query
Here we take the built-in object in stdClass as an example:
<?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 final generated SQL statement is the same as above
SELECT * FROM think_user WHERE `name`='thinkphp' AND status=1
The effect of using object-based query and using array-based query is the same and interchangeable. In most cases, we recommend that the array-based method be more efficient.
3. Expression query
The above query conditions are just a simple equality judgment. You can use query expressions to support more SQL query syntax, which is also the essence of ThinkPHP query language.
The format of query expression usage: $map['field name'] = array('expression','query condition');
Expressions are case-free. The supported query expressions have the following types, and their meanings are:
EQ equals (=)
NEQ does not equal (<>)
GT is greater than (>)
EGT greater than or equal to (>=)
LT is less than (<)
ELT is less than or equal to (<=)
LIKE Fuzzy Query
[NOT] BETWEEN (not in) interval query
[NOT] IN (not in) IN query
EXP expression query, supports SQL syntax
Examples are as follows:
: equal to (=)
For example: $map['id'] = array('eq',100); is equivalent to the following query $map['id'] = 100; the query condition represented is id = 100
: Not equal to (<>)
For example: $map['id'] = array('neq',100); represents the query condition that is id <> 100
: greater than (>)
For example: $map['id'] = array('gt',100); represents the query condition that is id > 100
: greater than or equal to (>=)
For example: $map['id'] = array('egt',100); represents the query condition that is id >= 100
: less than (<)
For example: $map['id'] = array('lt',100); the query condition represented is id < 100
: less than or equal to (<=)
For example: $map['id'] = array('elt',100); the query condition represented is id <= 100
7.[NOT] LIKE: LIKE of the same 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 will also automatically perform fuzzy queries. For example, if you set: 'DB_LIKE_FIELDS'=>'title|content', use $map['title'] = 'thinkphp'; the query condition will become name like '%thinkphp%'
Support array mode:
For example $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')
8.[NOT] BETWEEN: [not] between the same SQL, query conditions support strings or arrays,
For example: $map['id'] = array('between','1,8'); and the following equivalent: $map['id'] = array('between',array('1','8'));
The query condition becomes id BETWEEN 1 AND 8
9.[NOT] IN: [not] in the same SQL, query conditions support strings or arrays.
For example: $map['id'] = array('not in','1,5,8'); and the following equivalent: $map['id'] = array('not in',array('1','5','8'));
The query condition becomes id NOT IN (1,5, 8)
: Expressions, support more complex query situations
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 conditions can be used in any SQL-supported syntax, including the use of function and field names. Query expressions can be used not only for query conditions, but also for data updates, for example:
<?php
$User = M("User"); // Instantiate the User object// Assign value of the data object attribute to be modified$data['name'] = 'ThinkPHP';
$data['score'] = array('exp','score+1');// Add 1 to the user's points$User->where('id=5')->save($data); //Save modified data according to conditions?>
4. Quick query
Starting from version 3.0, a quick query method has been added, which can further simplify the writing of query conditions, such as:
1. Implement the same query conditions 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 conditions become
name= 'thinkphp' OR title = 'thinkphp'
2. Implement different query conditions 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();
?>
'_multi'=>true must be added at the end of the array, indicating that there is currently a multi-condition match, so the query condition becomes status= 1 AND title = 'thinkphp',
Query fields support more, for example:
$map['status&score&title'] =array('1',array('gt','0'),'thinkphp','_multi'=>true);
The query conditions become
status= 1 AND score >0 AND title = 'thinkphp'
Note: "|" and "&" cannot be used at the same time in the shortcut query method.
5. Interval query
ThinkPHP supports interval query for a certain field.
For example: $map['id'] = array(array(array('gt',1),array('lt',10)));
The query conditions obtained are: (`id` > 1) AND (`id` < 10)
For example: $map['id'] = array(array(array('gt',3),array('lt',10), 'or');
The query conditions obtained are: (`id` > 3) OR (`id` < 10)
For example: $map['id'] = array(array(array('neq',6),array('gt',3),'and');
The query conditions obtained are: (`id` != 6) AND (`id` > 3)
The last one can be AND, OR, or XOR operators. If not written, the default is AND operation.
The conditions of interval query can support all expressions of ordinary queries, that is, expressions such as LIKE, GT and EXP can support them. In addition, interval query can support 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 conditions are:
(`name` LIKE '%a%') OR (`name` LIKE '%b%') OR (`name` LIKE '%c%') OR (`name` = 'ThinkPHP')
6. Combination query
The body of the combined query is still querying in an array, but some special query support is added, including string pattern query (_string), compound query (_complex), and request string query (_query). Only one special query in mixed queries can be defined for each query. Since the indexing method of arrays is adopted, special queries with the same index will be overwritten.
1. String pattern query (using _string as 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 query conditions obtained in the end are:
( `id` != 1 ) AND ( `name` = 'ok' ) AND ( status=1 AND score>10 )
2. Request string query method
Request string query is a method similar to URL parameter transfer, which can support simple conditional equality judgment.
<?php
$map['id'] = array('gt','100');
$map['_query'] = 'status=1&score=100&_logic=or';
?>
The query conditions obtained are:
`id`>100 AND (`status` = '1' OR `score` = '100')
7. Compound query
Composite query is equivalent to encapsulating a new query condition and then incorporated into the original query condition, so relatively complex query condition assembly can be completed.
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 conditions are
( id > 1) AND ( ( name like '%thinkphp%') OR ( title like '%thinkphp%') )
Compound queries use _complex as subquery conditions to define them. In conjunction with the previous query methods, more complex query conditions can be formulated very flexibly.
Many query methods can be converted to each other, for example, the query conditions above can be changed to:
$where['id'] = array('gt',1);
$where['_string'] = ' (name like "%thinkphp%") OR ( title like "%thinkphp") ';
The final generated SQL statement is consistent.
8. Statistical Query
In applications, we often use some statistics, such as the number of users currently (or meets certain conditions), the maximum points of all users, the average score of users, etc. ThinkPHP provides a series of built-in methods for these statistical operations, including:
Count The number of statistics, the 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 value, the 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 example:
$User = M("User"); // Instantiate the User object
Get the number of users: $userCount = $User->count();
Or according to field statistics: $userCount = $User->count("id");
Get the maximum points of the user: $maxScore = $User->max('score');
Get the minimum points for users whose points are greater than 0: $minScore = $User->where('score>0')->min('score');
Get the average score of the user: $avgScore = $User->avg('score');
Statistics the total score 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 modes implement convenient data access operations, and the new version adds coherent operation functions to make this data operation clearer. However, ThinkPHP still retains 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 the SQL query is directly returned by Db class query results, and no processing is done. It mainly includes the following two methods:
Method: Perform SQL query operation
Usage query($sql,$parse=false)
Parameters sql (must): SQL statement to query
parse (optional): Whether SQL parsing is required
Return value If the data is illegal or the query is incorrect, it returns false.
Otherwise, return the query result dataset (same as select method)
Example of usage:
<?php
$Model = new Model() // Instantiate a model object without any data table$Model->query("select * from think_user where status=1");
?>
If you currently use a distributed database and set up read and write separation, the query method is always executed by the read server, so the query method corresponds to read operations, regardless of what your SQL statement is.
Method: execute SQL operations for updating and writing data
Usage execute($sql,$parse=false)
Parameters sql (must): SQL statement to be executed
parse (optional): Whether SQL parsing is required
Return value If the data is illegal or the query is incorrect, return false. Otherwise, return the number of records affected.
Example of usage:
<?php
$Model = new Model() // Instantiate a model object without any data table$Model->execute("update think_user set name='thinkPHP' where status=1");
?>
If you currently use a distributed database and set up read and write separation, the execute method is always executed by the write server, so the execute method corresponds to the write operation, regardless of what your SQL statement is.
10. Dynamic query
With the help of the features of PHP5 language, ThinkPHP implements dynamic query. The dynamic query methods of the core model include the following:
getBy query data based on the value of the field, for example, getByName, getByEmail
getFieldBy query based on the field and return the value of a certain field. For example, getFieldByName
Dynamic query: This query method checks and records the fields of the data table.
For example, if the User object has properties such as id, name, email, address, etc., then we can use the following query method to directly query records that meet the conditions based on a certain property.
<?php
$user = $User->getByName('liu21st');
$user = $User->getByEmail('liu21st@');
$user = $User->getByAddress('Shenzhen, China');
?>
Dynamic query methods for multiple data fields are not supported for the time being. Please use the find method and the select method for querying.
Dynamic query: Query for a field and return the value of a field, for example
$userId = $User->getFieldByName('liu21st','id');
Indicates that the user's id value is obtained based on the user's name.
11. Sub-query
Since version 3.0, subquery support has been added, and there are two ways to use it:
1. Use the select method
When the parameter of the select method is false, it means that no query is performed but just returns to build SQL, for example:
// First construct the 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 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 will not be performed, but only generates the SQL statement for the query (to avoid confusion, brackets will be added on both sides of the SQL), and then we call it directly in subsequent queries.
//Use subquery for query
$model->table($subQuery.' a')->where()->order()->select()
The constructed subquery SQL can be used for coherent operation methods of ThinkPHP, such as table where, etc.
The above thinkphp query, 5.0 method (it is feasible for personal trial) is all the content I share with you. I hope you can give you a reference and I hope you can support me more.