13, Other query methods (focus: where method)
1. where method (query syntax)
See the manual -- Database -- Query constructor -- query syntax
where('Field name','expression','query criteria'); whereOr('Field name','expression','query criteria');
expression | meaning |
---|---|
EQ,= | Equal to (=) |
NEQ,<> | Not equal to (< >) |
GT,> | Greater than (>) |
EGT,>= | Greater than or equal to (> =) |
LT,< | Less than (<) |
ELT,<= | Less than or equal to (< =) |
LIKE | Fuzzy query |
[NOT] BETWEEN | (not in) interval query |
[NOT] IN | (not IN) IN query |
[NOT] NULL | Whether the query field is (not) NULL |
[NOT] EXISTS | EXISTS query |
EXP | Expression query, support SQL syntax |
> time | Time comparison |
< time | Time comparison |
between time | Time comparison |
notbetween time | Time comparison |
Note: in the where method, if the comparison expression is equal to, this parameter can be omitted.
Application example:
$info = User::where('id','=','1')->find(); $info = User::where('id','1')->find(); $info = User::where('name','like','%thinkphp%')->select();
Note: after the where method is used, the find method and select method can be used to Query data, but the get method and all method cannot be used. (reason: the where method returns a Query object, not a model object)
Extension: batch condition query (manual - Database - Query Builder - advanced query)
2. Continuous operation (chain operation)
(see manual - Database - Query Builder - chain operation)
Principle: for the method called in the middle, the return value is the object, and you can continue to call other methods of the object.
The composition of an sql statement can be very complex, including where, order by, group by, fields restricting query, limit, having restrictions, etc. these complex conditions are encapsulated in the tp framework
Note: in the TP framework, there is no order requirement for the intermediate methods. At that time, the find and select methods used to obtain the final results must be at the end.
Common chain operation methods
$model = new Address(); //Specify the fields to query, including the fields after select in the native sql $model->field('id,name')->select(); //It is equivalent to order by in native sql $model->order('id desc,time desc')->select(); //It is equivalent to the limit condition in the native sql $model->limit(3)->select(); $model->limit(0,3)->select(); $model->limit('0,3')->select(); //It is equivalent to group by in native sql $model->group('cate_id')->select(); //Equivalent to having condition in native sql $model->having('id>3')->select(); //Alias method is used to set alias, and join method is used to query linked tables $model->alias('a') ->join('think_user_type t','a.id=t.user_id', 'left') ->select(); //Address::alias('a')->join('tpshop_user u','a.user_id = u.id', 'left')->select();
Join table query: SELECT * FROM tpshop_goodspics g LEFT JOIN tpshop_goods gs on g.goods_id = gs.id;
$data = \app\admin\model\Goods::where('id', '>', 32) ->field('id, goods_name') ->order('id desc') ->limit(5) ->select(); dump($data);
3. Statistical query (aggregate query)
See manual - Model - aggregation, or manual - Database - query constructor - aggregate query
In the model, you can also call the aggregation method of the database to query:
method | explain |
---|---|
count | Count quantity. The parameter is the field name to be counted (optional) |
max | Gets the maximum value. The parameter is the name of the field to be counted (required) |
min | Get the minimum value. The parameter is the field name to be counted (required) |
avg | Get the average value. The parameter is the field name to be counted (required) |
sum | Get the total score. The parameter is the field name to be counted (required) |
The return values of these methods are specific data. In a coherent operation, it can only be placed last, and there is no need to use find and select.
The count method is equivalent to select count(*) from tpshop_user;
4. Data field query
① Query the value of a field of a record
Goods::where('id', 2)->value('goods_name');
② Query the value of a field of multiple records and return a one-dimensional index group
Goods::where('id', 'GT', 2)->column('goods_name');
③ Query the value of a field of multiple records, and take the id field value as the array index
Goods::where('id', 'GT', 2)->column('goods_name', 'id');
5. trace debugging
Trace debugging function is an auxiliary tool for development and debugging provided by ThinkPHP to developers. It can display the request information, operation status, SQL execution, error prompt, etc. of the operation of the current page in real time
Start trace debugging:
Modify application/config.php and configure the following parameters:
'app_trace' => true
Summary exercise:
14, Display of data in the template (understand)
1. Conditional judgment tag if
The if tag is built in the TP framework to judge if in the template file. The syntax is as follows:
{if condition="($name == 1) OR ($name > 100) "} value1 {elseif condition="$name eq 2"/}value2 {else /} value3 {/if} It can also be written in the following form: {if ($name == 1)}value1 {/if}
2. Range judgment label in
The range judgment tag includes four Tags: in not in between not between, which are used to judge whether a variable has a certain range.
{in name="id" value="1,2,3"} id Within range {/in} {in name="id" value="$range"} id Within range {/in}
The value of the Name property is the variable Name without the $sign
The Value property Value can be a string range, an array variable or a string variable.
Equivalent to if(in_array()) in native php
3. Use function
(see manual - templates - using functions)
Output variables in the template file. You can use functions to process variables
Syntax: there are two kinds of syntax:
{$variable name | function name = arg1,arg2, ###}
Parameters, you can use #### instead of the output variable itself. If the variable itself is the first parameter, it can be omitted.
Example:
//Format time {$v.goods_create_time|date='Y-m-d H:i:s', ###} //md5 encrypt string {$name|md5}
{: function name (parameter)}
{:date("Y-m-d H:i:s", $v['goods_create_time'])}
All php functions or functions defined in the framework can be called directly in the template.
4. System variable
(see manual - template - system variable)
The TP framework comes with a $Think variable to output system variables in the template.
$Think.server : amount to $_SERVER $Think.get : amount to $_GET $Think.post : amount to $_POST $Think.request : amount to $_REQUEST $Think.cookie : amount to $_COOKIE $Think.session : amount to $_SESSION $Think.config.Name: output configuration parameters $Think.const.Name: output constant The equivalent framework helps us do the following things $Think = [ 'get' => $_GET, 'post'=>$_POST ]; $this->assign('Think', $Think);
Example: url address: People.com | Celebrity News, Exclusives, Photos and Videos
Display page parameter value: {$Think.get.page}
5. Use operator
When outputting variables in the template file, operators can be used first.
+ {$a+$b} - {$a-$b} * {$a*$b} / {$a/$b} % {$a%$b} Remainder or mold ++ {$a++} or {++$a} -- {$a--} or {--$a}
15, Adding data
(see manual - Model - add) curd: create update read delete
1. Add a piece of data (save method)
The return value of the Save method is the number of records written
2. Static method addition (create method)
Add data usage example:
3. Add multiple pieces of data (saveAll method)
4. Filter non datasheet fields
Save method: call allowField method before calling save method
create method: pass the second parameter to the create method, true
User::create($_POST, true);
16, Case: adding function of background goods
Demand: only the most basic information (commodity name, commodity price and commodity quantity) is added
The form shows the create method of the Goods controller (integration template)
Form submission Goods controller save method
Idea: display the form, submit the form, receive and save the data to the data table.
1) Function realization
① Confirm the integrity of the form and improve the submission address, name attribute value and other information
② Modify the save method of the Goods controller to receive the form submission and save the data.
2) Knowledge points
1. Timestamp function of the model
(see manual - Model - timestamp)
Note: the timestamp field is automatically written on the premise that there must be a corresponding field in the data table.
2. Page Jump and redirection
(see manual -- controller -- jump and redirect)
Jump on success
$this -> success("Prompt information "," jump address "," return data "," waiting time "“ header "Information");
Jump on failure
$this -> error("Prompt information "," jump address "," return data "," waiting time "“ header "Information");
The waiting time is 3s by default. The jump address can not be written. It will jump back to the previous page by default.
Redirect jump: used to jump directly without displaying prompt information.
$this -> redirect("Jump address "," request parameters "“ http code");
17, Ueditor rich text editor
1. Download of Ueditor editor
The detailed description of goods is usually mixed with pictures and text. For the mixed content, you need to use a rich text editor.
Download address of Ueditor rich text editor:
GitHub - Fex team / ueeditor: rich text rich text editor
After downloading:
Decompression:
2. Use of Ueditor editor
Use steps:
Bring the editor's code into the project
Import js file
Specify the label (carrying the id attribute) and the display position of the editor
Instantiate editor in js
Note: when in use, the rich text editor is divided into a common input interface and a source code input interface.
If you want to write your own html code and set the style, you must click the html button in the upper left corner to switch to the source code interface. After entering, you must click the html button at least once, otherwise you will not receive data in php.
Case implementation: the product addition page uses ueeditor rich text editor
Integrate editor into project
Introduce an editor on the page (refer to the demo file index.html)
① Import js file
② Define the tag, id attribute and name attribute on the page
③ Instantiate the editor with js code
Addition of Product Description:
The product description field value received in the controller is an html code string.
Example:
<p>test</p> <p> <img src="/ueditor/php/upload/image/20180804/1533373686.png" title="1533373686.png" alt="b1.png"/> </p>