Shulou(Shulou.com)06/03 Report--

This article mainly introduces how to add, delete, modify and query the database in thinkPHP, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

The details are as follows:

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

Can use encapsulation, need to write sql, you can execute sql.

1. Primitive

$Model = new Model (); / / instantiating a model object does not correspond to any data table $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)

two。 For table instantiation, the table's original name here is sh_wxuser_collection. Sh is a prefix.

$model = M ('wxuser_collection'); / / automatically omit 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 be automatically converted to _

$model = M ('WxuserCollection'); / / automatically omit 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's really convenient, but when it's convenient, don't forget that 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 acquires a piece of data, find (1) acquires data with id 1, and find (2) acquires data with id 2. The last one is to get the first piece of data in 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 the data. The advantage here is that you don't have to worry about the order of sql statements, and you can just call functions at will.

6.delete ()

$model = M ('WxuserCollection'); $res = $model-> where (' id=1')-> delete (); / / return 1 for success and 0 for failure

Delete operation according to condition

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

String, array two ways, the third is to get all the 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 ()

String, array two ways, the default 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 ()

LEFT JOIN 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 use an array, the join method can only be used once and cannot be mixed with a string.

10.setInc ()

$User = M ("User"); / / instantiate User object $User- > where ('id=5')-> setInc (' score',3); / / user's credit plus 3$ User- > where ('id=5')-> setInc (' score'); / / user's credit plus 1$ User- > where ('id=5')-> setDec (' score',5); / / user's credit minus 5$ User- > where ('id=5')-> setDec (' score'); / / user's credit minus 1

11.getField ()

Get a field value

$User = M ("User"); / / instantiate the User object / / get the nickname of the user whose ID is 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 criteria, only one result will be returned.

Get a field column

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

$User = M ("User"); / / instantiate the User object / / get the list of nicknames of users whose status is 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 criteria.

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

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

Get a list of 2 fields

$User = M ("User"); / / instantiate the User object / / get the list of nicknames of users whose status is 1$ nickname = $User- > where ('status=1')-> getField (' id,nickname')

If more than one field name is passed in by the getField method, an associative array is returned by default, indexed by the value of the first field (so try not to repeat the first field).

Get a list of multiple fields

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

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

Comprehensive use case

$where = array ('a.storestoreroomid' = > $this- > store_id,' a.userSecretid' = > $this- > user_id) $collects = $this- > collectModel- > table ("sh_wxuser_collection a")-> field (array) ('b.namestamentwayb.pricewords))-> limit ($start, $offset)-> order (' a.addtime DESC')-> where ($where)-> join ('sh_goods b ON a.goods_id = b.id')-> select (); / / get the record of the current page echo M ()-> getLastSql () / / debug the sql statement with $count = $this- > collectModel- > table ("sh_wxuser_collection a")-> where ($where)-> count (); / / get the total number of records

Because of the combination of two tables, the table method is used to redefine the table name, and the corresponding conditions and parameters are prefixed. a. Or b.

Where the field field is either a string or an array.

Field ('b. Nameplate,'b. Pricekeeper,'b. Opricegoat,'b. Logoimg, 'a.goodsroomid') / / error

That's what I wrote before. It's a big problem.

Using the framework, you can't write sql flexibly. However, having a deep understanding of sql is also conducive to the flexible use of the framework.

The method used to debug sql statements.

Echo M ()-> getLastSql (); Thank you for reading this article carefully. I hope the article "how to add, delete, modify and search the database in thinkPHP" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.