Thinkphp model operation

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.