Details about how to add, delete, modify, and query operations for the thinkPHP database

This article mainly introduces the methods for adding, deleting, modifying, and querying thinkPHP databases, and analyzes the common database operation functions and related usage skills of thinkPHP in detail based on the instance form, for more information about how to add, delete, modify, and query thinkPHP databases, see the example in this article. We will share this with you for your reference. The details are as follows:

Thinkphp encapsulates the addition, deletion, modification, and query operations on the database, making it easier to use, but not necessarily flexible.

It can be used for encapsulation. you need to write SQL statements and execute SQL statements.

1. Original

$ Model = new Model (); // instantiate a model object without 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 );

2. for table instantiation, the table name here is sh_wxuser_collection. Sh is the prefix.

$ Model = M ('wxuser _ collect'); // automatically saves 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 _

$ Model = M ('wxusercollect'); // automatically saves 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 edit statements

$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 really convenient, but it is convenient. 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 data with id 2. The last one is to obtain the first data in 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();

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

6. delete ()

$ Model = M ('wxusercollect'); $ res = $ model-> where ('Id = 1')-> delete (); // if the call succeeds, 1 is returned. if the call fails, 0 is returned.

Delete objects 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();

String, array. The third method is to obtain all fields other than the 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();

String, array two methods, 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();

The left join method is used by default. if you need other JOIN methods, you can change it to the second one,

If the parameters of the join method use arrays, the join method can only be used once and cannot be used together with the string method.

10. setInc ()

$ User = M ("User"); // instantiate the User object $ User-> where ('Id = 5')-> setInc ('score ', 3 ); // add 3 $ User-> where ('Id = 5')-> setInc ('score '); // add 1 $ User-> where ('Id = 5')-> setDec ('score ', 5 ); // reduce the User's points by 5 $ User-> where ('Id = 5')-> setDec ('score '); // reduce the User's points by 1

11. getField ()

Obtain a field value

$ User = M ("User"); // instantiate the User object // Obtain the nickname of a User whose ID is 3 $ nickname = $ User-> where ('Id = 3 ') -> getField ('nickname ');

The returned nickname is a string. That is to say, even if multiple fields meet the conditions, only one result is returned.

Obtain a field column

If you want to return a qualified field column (multiple results), you can use:

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

If the second parameter is set to true, the returned nickname is an array that contains a list of all nicknames that meet the conditions.

To limit the number of returned results, you can use:

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

Obtain the list of two fields

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

If the getField method is used to input multiple field names, an associated array is returned by default, and the value of the first field is the index (so the first field should be selected as unique as possible ).

Obtain the list of multiple fields

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

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

Comprehensive Use cases

$ 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. pric', 'B. oprice', 'B. logoimg ', 'A. goods_id ')-> limit ($ start, $ offset)-> order ('A. addtime DESC ')-> where ($ where)-> join ('sh_goods B ON. goods_id = B. ID')-> select (); // Obtain 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 (); // obtain the total number of records

Because two tables are combined, the table method is used to redefine the table name. The corresponding conditions and parameters must be prefixed. A. or B.

The field is either a string or an array.

Field ('B. name',' B. price', 'B. oprice',' B. logoimg ', 'A. goods_id') // error

I wrote this before, which is a big problem.

With the framework, you cannot write SQL statements flexibly. However, a deep understanding of SQL is also conducive to flexible use of the framework.

The method used to debug SQL statements.

echo M()->getLastSql();

I hope this article will help you design PHP programs based on the ThinkPHP framework.

For more details about how to add, delete, modify, and query instances for the thinkPHP database, please follow the PHP Chinese network!

Related articles:

How to write your own functions and classes in thinkphp, and how to call them?

Example code of thinkPHP simple function call and class library method

Share ThinkPHP3.2 using addAll () to insert data in batches