ThinkPHP6.0 database chain operation

ThinkPHP6 database chain operation

The chain operation method provided by the database can effectively improve data access code clarity and development efficiency, and supports all CURD operations
marked with *, it supports multiple calls

Continuous operation	Function	Supported parameter types
where*	For AND queries	Strings, arrays and objects
table	Used to define the name of the data table to be operated	Strings and arrays
name	Used to define the name of the data table to be operated Name	String
field*	is used to define the field to be queried (supports field exclusion)	String and Array
order*	is used to sort the results	String and array
limit	Used to limit the number of query results	Strings and numbers
page	Used for query paging (will be converted to limit internally )	Strings and numbers

1. Expression query

The expression is the condition of the SQL statement
Expressions are not case-sensitive
Expression is written in where

##ExpressionMeaningQuery Method = is equal to ##<>#> is greater than greater than or equal toless than##<=whereLike/whereNotLike

is not equal to

##>=

less than or equal to

##[ NOT] LIKE Fuzzy query

##[NOT] BETWEEN (Not)Interval query whereBetween/whereNotBetween

[NOT] IN （Not in）IN query whereIn/whereNotIn

[NOT] NULL Query whether the field is (not) NULL whereNull/whereNotNull

whereQuery

The where method is the most commonly used method in the chain operation method. It can complete normal query, expression query, quick query, Conditional query operations including interval query and combined query

# Equal to (=)
$select = Db::table('shop_goods')- >where('id','=','1')->select();
print_r($select->toArray());

# Not equal to (<>)
$select = Db::table('shop_goods')->where('id','<>',' 2')->select();
print_r($select->toArray());

Greater than (>)
$select = Db::table('shop_goods')->where('id','>','3')->select();
print_r( $select->toArray());

# Greater than or equal to (>=)
$select = Db::table('shop_goods' )->where('id','>=','4')->select();
print_r($select->toArray());

# Less than (<)
$select = Db::table('shop_goods')->where('id','<','5 ')->select();
print_r($select->toArray());

Less than or equal to (<=)
$select = Db::table('shop_goods')->where('id','<=','6')->select();
print_r($select->toArray());

# where
$select = Db::table('shop_goods')
->where('id','>','3')
->where('id','<','8')
->select();
print_r($select->toArray());

LIKE
$select = Db::table('shop_goods')->where('title','like','%dress%')->select();
print_r($select-> ;toArray());

# NOT LIKE
$select = Db::table('shop_goods')->where('title' ,'not like','%dress%')->select();
print_r($select->toArray());

# BETWEEN
$select = Db::table('shop_goods')->where('id','between','6,10')->select();
print_r($select->toArray());

NOT BETWEEN
$select = Db::table('shop_goods ')->where('id','not between',[6,10])->select();
print_r($select->toArray());

# IN
$select = Db::table('shop_goods')->where('id','in','4,7, 10')->select();
print_r($select->toArray());

# NOT IN
$select = Db::table('shop_goods')->where('id','not in',[4,7,10])->select();
print_r($select->toArray());

2. Data table

1, table and name

# must be the complete database name
$select = Db::table('shop_goods')->where('id','10')->select();
print_r($select-> ;toArray());
# The database does not set the prefix
$select = Db::name('shop_goods')->where('id','11')-> ;select();
print_r($select->toArray());
# Database setting prefix, no prefix access
$select = Db::name ('list')->where('id','12')->select();
print_r($select->toArray());

2. Database prefix

Database configurationdatabase.php

return [
'connections' => [

## ]
];
];
3. Return value
1,
field

The main function of the field method is to identify the fields to be returned or operated. It can be used for query and write operations.

All query methods can be used Use field method
# String

->field( 'title,price,discount as d')
->where('status',1)
->select();
print_r($select ->toArray());
Array
$select = Db::table('shop_goods')
- & gt; field ([
# Title ',
' Price ',
' Discount '= & GT
#                                                                                                                                                                                                           .
Add, only these fields can be added
# Multiple fields
$data = [
'title' = > 'New item',
'price' => 50,
'discount' => 8,
'add_time' => 1576080000
];
$insert = Db::table('shop_goods')
                                                                                                                                                                                                                                                                                                    other ;field('add_time')
->insert($data);
print_r($insert);
Query all fields, faster
$select = Db::table('shop_goods')

                                                                                                                                                                                                                                  ('*')
                                                                                                                                                                                                                                                          . ));
2,
withoutField
withoutField method is used to exclude fields in the data table
Db::table('shop_goods')->withoutField('id')->select();
3,

fieldRaw

Db::table('shop_goods')->fieldRaw('id,sum(price)')->select();

4. Sorting

method is used to sort the results of the operation or limit the priority

Default positive order

asc forward order

desc reverse order

$select = Db::table ('shop_goods')
. order('price','DESC')
. $select->toArray());
2,
orderRaw

$select = Db::table( 'shop_goods')
-& GT; Field ('Title, Price, ID')
## -& GT; where ('Status', 1)
## -& GT; Orderraw; ("field(title,'price','discount','stock')")
->select();
print_r($select->toArray()) ;
5. Paging

limit method is mainly used to specify the number of queries and operations

$select = Db::table('shop_goods')
->field('title,price,id')
->where(' status',1)
->order('price','DESC')
-& gt; limit (3)
& gt; select ();
## ($ select- & gt; toarray ())
$select = Db::table('shop_goods')

',1)
->order('price','DESC')
->limit(0,5)
->select() ;
print_r($select->toArray());
##page
The method is mainly used for paging queries

$select = Db::table('shop_goods') ->field('title,price,id')

& gt; order ('price', 'desc')
-& gt (1,5)
->select();
print_r($select->toArray());
6. Aggregation query

If there is no data in the aggregation method, the default value is 0. Aggregation queries can be matched with other query conditions

Functioncount The number of statistics, the parameter is the field name to be counted (optional) max Get the maximum value. The parameter is the name of the field to be counted (required) min Get the minimum value. The parameter is the name of the field to be counted (required) avg Get the average value, the parameter is the field name to be counted (required) sumGet Total number, the parameter is the field name to be counted (required)

// Count the quantity, the parameter is the field name to be counted (optional)
$select = Db::table('shop_goods')->count();

print_r($select);

// Get the maximum value, the parameter is the field name to be counted (required)
$select = Db ::table('shop_goods')->max('id');
print_r($select);

// Get the minimum value , the parameter is the field name to be counted (required)
$select = Db::table('shop_goods')->min('id');
print_r($select) ;

// Get the average value, the parameter is the field name to be counted (required)
$select = Db::table('shop_goods') ->avg('id');
print_r($select);

// Get the total number, the parameter is the field name to be counted ( Required)
$select = Db::table('shop_goods')->sum('id');
print_r($select);

7. Search and sorting examples

controller code

public function index(){
$title = 'Mall';
$ login = 'Ouyang Ke';
# Left menu
$menu = Db::table('shop_menu')->where('fid',0)->select ();
$left = $menu->toArray();
foreach($left as &$left_v){
$left_v['lists'] = Db::table('shop_menu')->where('fid',$left_v['id'])->select();
}
# Right side List
$param = Request::param();
if(isset($param['status']) && $param['status'] == 1){
$where['status'] = 1;
}else if(isset($param['status']) && $param['status'] == 2){

$where['status'] = 2;
}else{
$where = true;
}
$list = Db :: table ('shop_goods')
& GT; where ($ where)
## -& gtr ('add_time desc'
## -& gter ('' id desc ')
-& gt; select ();
## $ right = $ list -& gt; toarray ();
## Foreach ($ right as & $ right_v) {
$right_v['cat'] = Db::table('shop_cat')->where('id',$right_v['cat'])->value('name') ;
}
View::assign([
'title' => $title,
'login' => $login,
'left' => $left,
'right' => $right,
'status' => isset($param['status']) ? $param['status'] : null
]);
return View::fetch();
}

view代码

<form class="layui-form" method="post">
<div class="layui-form-item" style="margin-top:10px;">
<div class="layui-input-inline">
<select name="status">
<option value="0" {if $status==0}selected{/if}>全部</option>
<option value="1" {if $status==1}selected{/if}>开启</option>
<option value="2" {if $status==2}selected{/if}>关闭</option>
</select>
</div>
<button class="layui-btn layui-btn-primary"><i class="layui-icon"></i>搜索</button>
</div>
</form>

8. Paging example

controller code

public function index(){
$title = 'Mall';
$login = 'Ouyang Ke';
# Left menu
$menu = Db::table(' shop_menu')->where('fid',0)->select();
$left = $menu->toArray();
foreach($left as &$left_v){
$left_v['lists'] = Db::table('shop_menu')->where('fid',$left_v['id'])->select( );
}
# Right list
$param = Request::param();
if(isset($param['status ']) && $param['status'] == 1){
$where['status'] = 1;
}else if(isset($param['status' ]) && $param['status'] == 2){
$where['status'] = 2;
}else{
$where = true ;
}
$p = isset($param['p']) ? $param['p'] : 1;
// Total statistics
$count = Db::table('shop_goods')->where($where)->count();
$list = Db::table('shop_goods')
-& gt;; $ when)
-& gt; order ('add_time desc')
## -& gt ('ID desc')
# #                                                                                                                                                                                                                                       through $right as &$right_v){
$right_v['cat'] = Db::table('shop_cat')->where('id',$right_v['cat'])-> ;value('name');
}
View::assign([
'title' => $title,
'login ' => $login,
'left' => $left,
'right' => $right,
'count' => ceil ($count/10),
'p' => $p,
'status' => isset($param['status']) ? $param['status '] : 0
]);
return View::fetch();
}
view code
<div class="layui-box layui-laypage layui-laypage-default">
<a href="/index.php/Index/index?p={$p-1}&status={$status}" class="layui-laypage-prev {if $p<=1}layui -disabled{/if}">Previous page</a>
{for start="0" end="$count"}
{if $p == $ i 1)

#                                                                                                                                                "/index.php/Index/index?p={$i 1}&status={$status}">{$i 1}</a>
                                                                          ## {/for}
<a href="/index.php/Index/index?p={$p 1}&status={$status}" class="layui-laypage-next { if $p>=$count}layui-disabled{/if}">Next page</a>
</div>
9. Template paging
paginate
Built-in paging implementation, it is very simple to add pagination output function to data

render

Get the page turning html code

##total Get the total quantity
controller code
$select = Db::table('shop_goods')->paginate(10);
print_r($select);echo '<hr>';
foreach($select as $v){
print_r($v);echo '<hr>';

}

print_r($select->render ());echo '<hr>';

print_r('Total:'.$select->total());echo '<hr>';
View ::assign([
'select' => $select
]);
return View::fetch();
viewcode
<div>{$select|raw}</div>

css代码

.pagination {
display: inline-block;
padding-left: 0;
margin: 20px 0;
border-radius: 4px;
}
.pagination > li {
display: inline;
}
.pagination > li > a,
.pagination > li > span {
position: relative;
float: left;
padding: 6px 12px;
margin-left: -1px;
line-height: 1.42857143;
color: #337ab7;
text-decoration: none;
background-color: #fff;
border: 1px solid #ddd;
}
.pagination > li:first-child > a,
.pagination > li:first-child > span {
margin-left: 0;
border-top-left-radius: 4px;
border-bottom-left-radius: 4px;
}
.pagination > li:last-child > a,
.pagination > li:last-child > span {
border-top-right-radius: 4px;
border-bottom-right-radius: 4px;
}
.pagination > li > a:hover,
.pagination > li > span:hover,
.pagination > li > a:focus,
.pagination > li > span:focus {
z-index: 2;
color: #23527c;
background-color: #eee;
border-color: #ddd;
}
.pagination > .active > a,
.pagination > .active > span,
.pagination > .active > a:hover,
.pagination > .active > span:hover,
.pagination > .active > a:focus,
.pagination > .active > span:focus {
z-index: 3;
color: #fff;
cursor: default;
background-color: #337ab7;
border-color: #337ab7;
}
.pagination > .disabled > span,
.pagination > .disabled > span:hover,
.pagination > .disabled > span:focus,
.pagination > .disabled > a,
.pagination > .disabled > a:hover,
.pagination > .disabled > a:focus {
color: #777;
cursor: not-allowed;
background-color: #fff;
border-color: #ddd;
}

10. Template paging example

##list_rows Number per pagepage Current pagepathurl pathquery url extra parametersfragment url anchorvar_page Page variable

controller code

public function index(){
$title = 'Mall';
$login = 'Ouyang Ke';

# Left menu
$menu = Db::table('shop_menu')->where('fid',0)->select();
$ left = $menu->toArray();
foreach($left as &$left_v){
$left_v['lists'] = Db::table('shop_menu') ->where('fid',$left_v['id'])->select();
}
# Right list
$param = Request::param();
if(isset($param['status']) && $param['status'] == 1){
$where['status' ] = 1;
}else if(isset($param['status']) && $param['status'] == 2){
$where['status'] = 2;
}else{
$where = true;
}
$p = isset($param['p']) ? $param['p'] : 1;
# thinkphp comes with paging
$list = Db::table('shop_goods')
- ->where( $where)
->order('add_time DESC')
->order('id DESC')
->paginate([

'list_rows'=> 10,
'query' => Request::param()
>toArray();
foreach($right as &$right_v){
$right_v['cat'] = Db::table('shop_cat')->where( 'id',$right_v['cat'])->value('name');
}
View::assign([
'title' => $title,
'login' => $login,
'left' => $left,
'right' => $right ,
'list' =>
# ]);
return View::fetch();
}
viewcode
< div>{$paginate|raw}</div>

11. SQL debugging

getLastSql Output the last executed sql statement
getLastSql The method can only get the last executed SQL record

$select = Db::table('shop_goods')->select();
echo Db::getLastSql();

fetchSql The method directly returns the current SQL without executing

$select = Db::table('shop_goods')->fetchSql()->select();
echo $select;

12. Dynamic configuration database

config directory database.php file

return [
'connections' =>
                                                                                                                                                                                                               => get('database.hostname', '127.0.0.1'),
                                                                                                                                                                                                                                  . Name
'username' => Env::get('database.username', 'root'),
## Env::get('database.password', 'root'),
                                                                                                   'hostport'                                        '),
// The database connection parameter
' Params '= & gt; [],
## // Database coding defaults UTF8
' Charset '= & Gt; env :: get (' database.charset ',' utf8 '),
## // Database table prefix
' prefix '= & gt; env :: GET (' database .prefix ',' shop _ '),
// Database deployment method: 0 concentrated (single server), 1 distributed (main server)
' deploy '= & gt; 0 ,
                                                                                                                                                                                                                             　　　　　　　　　　　　　　　　　 master_num' => 1,
// Specify the slave server serial number

#
to ’ ’ s to ’ s ’ ‐ ‐ ‐ ‐ to

shop_user table in ouyangke database
CREATE TABLE `shop_user` (
`uid ` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'User ID',
`account` varchar(50) NOT NULL COMMENT 'Account',
`password` char(32) NOT NULL COMMENT 'Password',
`name` varchar(50) NOT NULL COMMENT 'Name',
`status` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT 'Status 1 is on 2Close',
`add_time` int(10) unsigned NOT NULL COMMENT 'Add time',
PRIMARY KEY (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT =3 DEFAULT CHARSET=utf8mb4 COMMENT='Backend Administrator';

##connect
Method to dynamically configure database connection information

Db::connect('ouyangke')->table('shop_user')->select();
connect
method is required Called at the beginning of the query, and must be followed by the query method, otherwise some queries may fail or the default database connection may still be used
13. WHRER chain operation (not commonly used)
The same function as query expressions, ThinkPHP provides the following quick query methods

Parameters	Description

## whereNotNull*Query whether the field is not NULLStringwhereExists*EXISTSQueryCharacter string##whereFindInSet*FIND_IN_SET queryStringwhereRaw* is used for direct query and operation of string conditionsString

14. Other chain operations (not commonly used)

Continuous operation	Function	Supported parameter types
whereOr *	For OR queries	Strings, arrays and objects
whereLike*	Fuzzy queries	String
whereNotLike*	Fuzzy query	String
whereBetween*	Interval query	String
whereNotBetween*	Not in the interval query	String
whereIn*	IN query	String
whereNotIn*	Not in IN query	String
whereNull*	Query whether the field is NULL	String


whereNotExists*	Not in EXISTS query	String
whereBetweenTime*	Time interval comparison	String
whereTime*	Quick query for time and date	String
whereExp*	Expression query, supports SQL syntax	String

##having Used for having support for queries String join* Used to join queries to support Strings and arraysunion*Used to join queries to union supportsStrings, arrays and objectsdistinct Distinct support for queriesBoolean valueslock Lock mechanism for databaseBoolean valuecache Use For query cacheSupport multiple parameterscomment For SQL commentsString

Continuous operations	Function	Supported parameter types
alias	Used to define aliases for the current data table	String
strict	Used to set whether to strictly detect whether the field name exists	Boolean value
group	Used to support group for query	String







force	Forced indexing of the dataset	String
partition	For settings Partition information	Array string
failException	Used to set whether to throw an exception if no data is queried	Boolean value
sequence	Used to set the auto-increment sequence name	String
replace	Used to set the REPLACE method to write	Boolean value
extra	Used to set additional query rules	characters String
duplicate	Used to set DUPLCATE information	Array string
procedure	Used to set whether the current query is a stored procedure query	Boolean value
master	Used to set the master server to read data	Boolean value
view*	For view query	String, array

15. Transaction operation

##InnoDB The engine supports transaction processing, MyISAM does not support transaction processing

// 启动事务
Db::startTrans();
$data = ['cat'=>'1','title'=>'日系小浪漫与温暖羊毛针织拼接网纱百褶中长收腰连衣裙','price'=>'1598.35','add_time'=>1576080000];
$insert = Db::table('shop_goods')->insert($data);
if(empty($insert)){
    // 回滚事务
    Db::rollback();
}else{
    // 提交事务
    Db::commit();
}

transaction method operates database transactions. When an exception occurs in the code in the closure, it will automatically roll back

Db::transaction(function () {
    $data = ['cat'=>'1','title'=>'日系小浪漫与温暖羊毛针织拼接网纱百褶中长收腰连衣裙','price'=>'1598.35','add_time'=>1576080000];
    $insert = Db::table('shop_goods')->insert($data);
});

16. Data set

The database is queried through select, and the obtained data set object
The returned data set object is
think\Collection, provides the same usage as arrays, and also encapsulates some additional methods

##1 isEmptyIs it empty2 toArray Convert to array3 all All data4 merge Merge other data5 diff Compare arrays and return the difference set6 flip Exchange the keys and values in the data7 intersect Compare the arrays and return the intersection8 keys Return all key names in the data9 pop Delete the last element in the data##10 ##20 order Specify field sorting##27 whereIn IN query the elements in the filter array28 whereNotIn Not IN query the elements in the filter array 29whereBetween Between query filters the elements in the array30whereNotBetweenNot Between query elements in the filter array

$select = Db::table('shop_goods')
            ->field('title,price,id')
            ->where('status',1)
            ->order('price','DESC')
            ->select();
if($select->isEmpty()){
    echo '未查询到数据';
}else{
    print_r($select->toArray());
}

Preparation: Perform data set query in the model, all data set objects are returned, but the think\model\Collection class (inherited think\Collection) is used, but the usage is consistent.

← ThinkPHP6.0 request

ThinkPHP6.0 model →

submit

php.cn

Number	Method	Description









shift	Delete the first element in the data
11	unshift	Insert an element at the beginning of the data
12	push	Insert an element at the end
13	reduce	Return the array as a string by using a user-defined function
14	reverse	Reverse data rearrangement
15	chunk	Data is separated into multiple data blocks
16	each	Execute callbacks for each element of the data
17	filter	Use callback function to filter elements in the data
18	column	Return the specified column in the data
19	sort	Sort the data

21	shuffle	Shuffle the data
22	slice	Intercept a part of the data
23	map	Use the callback function to process the elements in the array
24	where	Filter elements in the array based on field conditions
25	whereLike	Like Query filter element
26	whereNotLike	Not Like filter element