Add, delete, modify and query operations in thinkPHP database

This article mainly introduces the operation methods of thinkPHP database addition, deletion, modification and query, and analyzes in detail the commonly used database operation functions and related usage skills of thinkPHP in the form of examples. Friends in need can refer to the following

The examples of this article describe thinkPHP Database operation method of adding, deleting, modifying and checking. Share it with everyone for your reference, the details are as follows:

thinkphp encapsulates the addition, deletion, modification and query of the database, making it more convenient to use, but not necessarily flexible.

It can be used in encapsulation. You need to write sql and you can execute sql.

1. Original

$Model = new Model(); // 实例化一个model对象 没有对应任何数据表
$insert_sql = "INSERT INTO sh_wxuser_collection (user_id,store_id,good_id,addtime) VALUES('".$user_id."','".$store_id."','".$good_id."','".$addtime."');";
$Model - >query($insert_sql);

2. For table instantiation , here The original name of the table is sh_wxuser_collection. sh is the prefix.

$model = M('wxuser_collection'); //自动省去sh
$insert_sql = "INSERT INTO __TABLE__ (user_id,store_id,good_id,addtime) VALUES('".$user_id."','".$store_id."','".$good_id."','".$addtime."');";
$model - >query($insert_sql);

Another way of writing, _ can be written in uppercase, it will automatically convert to_

$model = M('WxuserCollection'); //自动省去sh
$insert_sql = "INSERT INTO __TABLE__ (user_id,store_id,good_id,addtime) VALUES('".$user_id."','".$store_id."','".$good_id."','".$addtime."');";
$model - >query($insert_sql);

3. Encapsulated add statement

$model = M('WxuserCollection');
$data = array('user_id' = >$user_id, 'store_id' = >$store_id, 'good_id' = >$good_id, 'addtime' = >$addtime);
$model - >data($data) - >add();

4. Encapsulated modify edit statement

$model = M('WxuserCollection');
$data = array('user_id' = >$user_id, 'store_id' = >$store_id, 'good_id' = >$good_id, 'addtime' = >$addtime);
$model - >data($data) - >where('id=3') - >save();

It is indeed very convenient, but apart from convenience, don’t forget the original sql, the original sql, is the most interesting.

5.find()

$model = M('WxuserCollection');
$res1 = $model - >find(1);
$res2 = $model - >find(2);
$res3 = $model - >where('good_id=1105 AND store_id = 1 AND user_id = 20') - >find();

find gets a piece of data, find(1) gets the id of 1 Data, find(2) gets the data with id 2. The last one is to get the first piece of data with the condition where.

5.select()

$model = M('WxuserCollection');
$res = $model - >where('good_id=1105 AND store_id = 1 AND user_id = 20') - >field('id,good_id as good') - >select();

Get all data. The advantage here is that you don't have to consider the order of the SQL statements, you can just call the function as you like.

6.delete()

$model = M('WxuserCollection');
$res = $model - >where('id=1') - >delete(); // 成功返回1 失败返回0

Delete operation based on conditions

7.field()

$model = M('WxuserCollection');
$res = $model - >field('id,good_id as good') - >select();
$res = $model - >field(array('id', 'good_id' = >'good')) - >select();
$res = $model - >field('id', true) - >select();

There are two ways of string and array, the third one means getting Handle all fields except id.

8.order()

$model = M('WxuserCollection');
$res = $model - >order('id desc') - >select();
$res = $model - >order('id asc') - >select();
$res = $model - >order(array('id' = >'desc')) - >select();
$res = $model - >order(array('id')) - >select();

Two methods are string and array, the default is asc.

9.join()

$Model->join(' work ON artist.id = work.artist_id')->join('card ON artist.card_id = card.id')->select();
$Model->join('RIGHT JOIN work ON artist.id = work.artist_id')->select();
$Model->join(array(' work ON artist.id = work.artist_id','card ON artist.card_id = card.id'))->select();

The LEFT JOIN method is used by default. If you need to use other JOIN methods, It can be changed to the second method.

If the parameters of the join method are arrays, the join method can only be used once, and it cannot be mixed with the string method.

10.setInc()

$User = M("User"); // 实例化User对象
$User->where('id=5')->setInc('score',3); // 用户的积分加3
$User->where('id=5')->setInc('score'); // 用户的积分加1
$User->where('id=5')->setDec('score',5); // 用户的积分减5
$User->where('id=5')->setDec('score'); // 用户的积分减1

11.getField()

Get a field value

$User = M("User"); // 实例化User对象
// 获取ID为3的用户的昵称
$nickname = $User->where('id=3')->getField('nickname');

The returned nickname is a string result. That is, even if there are multiple fields that meet the condition, only one result will be returned.

Get a certain field column

If you want to return the field column (multiple results) that meets the requirements, you can use:

$User = M("User"); // 实例化User对象
// 获取status为1的用户的昵称列表
$nickname = $User->where('status=1')->getField('nickname',true);

The second parameter is passed in true, and the returned nickname is an array containing a list of all nicknames that meet the conditions.

If you need to limit the number of returned results, you can use:

$nickname = $User->where('status=1')->getField('nickname',8);

Get a list of 2 fields

$User = M("User"); // 实例化User对象
 // 获取status为1的用户的昵称列表
$nickname = $User->where('status=1')->getField('id,nickname');

If the getField method passes in multiple field names, an associative array will be returned by default, with the value of the first field as the index (so the first Try to select a field that will not be repeated).

Get multiple field lists

$result = $User->where('status=1')->getField('id,account,nickname');

If more than 2 fields are passed in field name, a two-dimensional array is returned (similar to the return value of the select method, the difference is that the key name of the index is the value of the first field in the two-dimensional array)

Comprehensive use case

$where = array('a.store_id' => $this->store_id, 'a.user_id' => $this->user_id);
$collects = $this->collectModel->table("sh_wxuser_collection a")->field(array('b.name','b.price','b.oprice','b.logoimg','a.goods_id'))->limit($start, $offset)->order('a.addtime DESC')->where($where)->join(' sh_goods b ON a.goods_id = b.id')->select();// 获取当前页的记录
echo M()->getLastSql(); // 调试sql语句用
$count = $this->collectModel->table("sh_wxuser_collection a")->where($where)->count(); // 获取总的记录数

Since two tables are combined here, the table method is used to redefine the table name, and the corresponding conditions and parameters are To add a prefix. a. Or b.

The field field is either a string or an array.

field('b.name', 'b.price', 'b.oprice', 'b.logoimg', 'a.goods_id') // 错误

I have written this before, and it’s a huge problem.

Using a framework, you cannot write sql flexibly. However, having a deep understanding of SQL will also help you to use the framework flexibly.

Method for debugging sql statements.

echo M()->getLastSql();

Very convenient.

The above is the entire content of this article. I hope it will be helpful to everyone's study. For more related content, please pay attention to the PHP Chinese website!

Related recommendations:

PHP uploads Excel files and imports data into the MySQL database

thinkphp3.2.3 version database additions, deletions and modifications Check the implementation code

thinkPHP5 implements the method of adding content to the database

##

The above is the detailed content of Add, delete, modify and query operations in thinkPHP database. For more information, please follow other related articles on the PHP Chinese website!