Detailed explanation of thinkPHP database addition, deletion, modification and query operation method examples

The example in this article describes the operation method of add, delete, modify and query in thinkPHP database. 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. Instantiated for the table, the original name of the table here 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, and it will automatically be converted into_

$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();

is indeed very convenient, but convenient Besides, 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 data with id 1, find(2) gets the id 2 data. 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 methods: string and array. The third one means to get all fields except processing 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();

There are two methods: 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, you can change it to the second one,

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

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 nickname returned 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 a field column that meets the requirements (multiple results), 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 field should be chosen as non-duplicate as possible).

Get multiple field lists

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

If more than 2 field names are passed in, a two-dimensional array will be returned (similar to the return of the select method value, 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(); // 获取总的记录数

Here due to the combination of the two There is a table, so the table method is used to redefine the table name and prefix the corresponding conditions and parameters. 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 wrote this before, and it was a big problem.

If you use a framework, you cannot write sql flexibly. However, having a deep understanding of SQL is also conducive to using the framework flexibly.

Method for debugging sql statements.

echo M()->getLastSql();

I hope this article will be helpful to everyone’s PHP programming based on the ThinkPHP framework.

For more thinkPHP database addition, deletion, modification and query operation method examples and detailed explanations, please pay attention to the PHP Chinese website!

Related articles:

Ask for advice on how to write your own functions and classes in thinkphp, where to place them, and how to call them

thinkPHP is simple Sample code for calling functions and class library methods

ThinkPHP3.2 framework uses addAll() to batch insert data method sharing