Thinkphp5 operation database (add delete change check), see this article is enough

Thinkphp Standard Data sheet design:

Create time field: create_time

Update time field: update_time

Delete time: delete_time

Select int as shown in the figure below:

Create a model folder

Create a new folder named Model in the secondary object directory under the Application folder. This folder is at the same level as the corresponding Controller and View directories.

If there are multiple modules (such as index, admin) that operate the same database, then you can put the model into the common module, as follows:

Create a model class

Create a model object file in the model directory. Create a model object file in the model directory.

Table name pre_user -- -- -- -- -- -- -- -- -- -- -- -- -- -- - > model of User. PHP table name pre_user_info -- -- -- -- -- -- -- -- -- -- -- -- -- -- - > model of the UserInfo. PHPCopy the code

2. Define the model

      
namespace app\index\model;
use think\Model;
use think\Db;

class User extends Model{
	/** * Define variables * 1. The variable name should be the same as the field name in the data table * 2. This can be omitted as required, because if not, ThinkPHP will automatically look for the corresponding field name */ in the data table
	public $username;
	public $password;
}
? >
Copy the code

If the data model definition name and the table name do not match, then additional definitions and declarations are required, as follows:

      
namespace app\index\model;
use think\Model;
use think\Db;

class User extends Model
{
	protected $table = "admin_user";// Specify the name of the data table
    protected $pk = 'id';           // Specify the fields of the primary key
}
? >
Copy the code

Call the method of the model

// Import the defined data model classes
use \app\index\model\User;

// Method 1:
$res = User::get(1);

// Method 2:
$user = new User;
$res = $user::get(1);	

// Method 3:
use think\Loader;
$user = Loader::model("User");
$res = $user::get(1);

// Method 4:
$user = model("User");       
$res = $user::get(1);
Copy the code

4. Query operations

Get Gets a record

$res = User::get(1);
Copy the code

All Obtains multiple records

1, no parameter

$result = User::all(); // Query all records
Copy the code

2. The parameter is n, and n is a positive integer

$result = User::all(1); // Query the record whose ID is 1
Copy the code

3. Parameter ‘n1, n2, n3… ‘

$result = User::all('7, 8, 9, 10'); // Query 4 records whose ids are 7, 8, 9 and 10
Copy the code

4. The parameters are [n1, n2, n3…]

$result = User::all([7.8.9.10]); // Query 4 records whose ids are 7, 8, 9 and 10
Copy the code

Find Queries an item

 $res = User::where('id'.'1')->field('name')->find();
Copy the code

Is not equal to

->where(‘id’,’neq’,1)

Select Multiple query

$res = User::where('id'.'1')->field('name')->limit(2)->order('id DESC')->select();
Copy the code

Value Queries one field

$res = User::where('id'.'1')->value('name');
Copy the code

Convert the result to an array

$res = $res->toArray();
Copy the code

Query the number

// Query the total number of entries
$res = User::count();
// Count the number of items based on conditions
$res = User::where('id'.'>'.3)->count();
Copy the code

Query whereTime() time condition

1. Get today’s information

db('table')->whereTime('c_time'.'today')->select();
// This can be simplified as follows
db('table')->whereTime('c_time'.'d')->select();
Copy the code

2. Get yesterday’s information

db('table')->whereTime('c_time'.'yesterday')->select();
Copy the code

3. Get the week’s information

db('table')->whereTime('c_time'.'week')->select();   
// This can be simplified as follows
db('table')->whereTime('c_time'.'w')->select(); 
Copy the code

4. Get this month’s information

db('table')->whereTime('c_time'.'month')->select();   
// This can be simplified as follows
db('table')->whereTime('c_time'.'m')->select();   
Copy the code

5. Get last month’s information

db('table')->whereTime('c_time'.'last month')->select();    
Copy the code

6. Get information about the year

db('table')->whereTime('c_time'.'year')->select();    
// This can be simplified as follows
db('table')->whereTime('c_time'.'y')->select();    
Copy the code

7. Get last year’s information

db('table')->whereTime('c_time'.'last year')->select();     
Copy the code

8. Query the date range

// Query from today to the day after tomorrow
db('table')->whereTime('time'.'between', [strtotime(date('Y-m-d')), strtotime(date('Y-m-d', strtotime('+2 day')))])->select(); Db from today to the day after tomorrow'table')->whereTime('time'.'between'['2020-3-28'.'2020-3-30'])->select();
Copy the code

5. Add operations

1. Add using the create() method

$res = User::create([
     'name'= >'anyang'.'age'= >23.'sex'= >1.'password'= >'123456'
 ]);
Copy the code

2, add data and return the added primary key

$uid=UserModel::create([
     'name'= >'anyang'.'age'= >23.'sex'= >1.'password'= >'123456'
 ])->id;
Copy the code

You can also use the insertGetId method of the DB class, as follows:

$uid = User::insertGetId([
     'name'= >'anyang'.'age'= >23.'sex'= >1.'password'= >'123456'
 ]);
Copy the code

3. Add the instantiation mode

 

 $user = new User;
 $user->name =  'anyang';
 $user->age =  23;
 $user->save();
Copy the code

4. The instantiation mode filters the inserted fields and returns the number of inserted rows

 $user = new User;
 $data = [
     'name'= >'anyang'.'age'= >23.'email'= >'[email protected]'
 ];
 // Only the name and age fields are written
 $res = $user->allowField(['name'.'age'])->save($data);
Copy the code

5. The model uses allowField() to filter data for non-table fields

// Define the model object and pass in post data
$user = new User($_POST);
// Filter non-table field data in the POST array
$user->allowField(true)->save();
Copy the code

6. The model uses allowField() to specify certain fields to write

$user = new User;
// Only the name and email fields are written to the POST array
$user->allowField(['name'.'email'])->save($_POST['id'= >1]);
Copy the code

SaveAll () saveAll()

user = new User;
$list= [['name'= >'anyang'.'email'= >'[email protected]'],
    ['name'= >'a small retailer,.'email'= >'[email protected]']].$user->saveAll($list);
Copy the code

You can also use the insertAll() method of the DB class to return the number of successful entries

$res = User::insertAll([
     'name'= >'anyang'.'age'= >23.'sex'= >1.'password'= >'123456'
 ]);
Copy the code

Additional, other ways to filter fields:

1. In the DB operation, you can use strict to disable the strict checking of fields

Db: : name (" user ") - > strict (false)->insert($data);
Copy the code

2. Destroy variables using PHP’s unset() method

unset($data[' file ']);Copy the code

6. SaveAll Adds multiple pieces of data and returns the object list

 $user = new User;
 $data= [['name'= >'anyang'.'age'= >20.'email'= >'[email protected]'
     ],
     [
         'name'= >'a small retailer,.'age'= >25.'email'= >'[email protected]']].$res = $user->allowField(['name'.'age'])->saveAll($data);
Copy the code

Update operation

1. Update returns the number of affected rows

 $res = User::where(['id'= >1])->update(['name'= >'anyang']);
Copy the code

2. SetField updates a single field

User::where('id'.1)->setField('name'.'anyang');
Copy the code

3, setInc

//setInc('money',10)
User::where(['id'= >1])->setInc('money'.10);
Copy the code

4, setDec

//setDec('money',10) subtracts 10 from the money field
User::where(['id'= >1])->setDec('money'.10);
Copy the code

5, batch update, require data to contain the primary key, return the list of updated objects

$user = new User;
$res = $user->saveAll([
     ['id'= >1.'name'= >'anyang'],
     ['id'= >2.'name'= >'a small retailer,]]);Copy the code

7. Delete operations

1, pass in the primary key, return the number of rows affected

$res = User::destroy(1);
Copy the code

2, pass in a condition that returns the number of rows affected

 $res = User::destroy(['name'= >'anyang']);
Copy the code

3. Conditional deletion returns the number of affected rows

 $res = User::where(['id'= >1])->delete();
Copy the code

Eight, the transaction

1. Automatic control of transaction processing

Db::transaction(function(){ 
    Db::table('order')->where(['id'= >1])->delete(); 
	Db::table('user')->where('id'= >1)->setInc('money'.10);	
});
Copy the code

2. Manually control transactions

Db::startTrans();// Start the transaction
try {
    Order::where(['id'= >1])->delete();
	User::where('id'= >1)->setInc('money'.10);
	Db::commit();// Commit the transaction
} catch (Exception $e) {
	Db::rollback();	/ / rollback
}
Copy the code

Model = model = model

The naming convention for the reader is: -> GET + the hump of the property name + Attr

      
namespace app\index\model;
use think\Model;
class User extends Model
{		    
    // Acquirer: change gender 012 to male, female, unknown return
	public function getSexAttr($val)
	{
		switch ($val) {
            case 1:
                return 'male';
            case 2:
                return 'woman';
            default:
            return 'unknown'; }}// Fetch: return after formatting the timestamp
    public function getUpdateTimeAttr($val){
        if(!empty($val)) {// If it is a timestamp, format it
			if(! strtotime($val)) {
				return date('Y-m-d H:i:s'.$val);
			}else{
				return $val; }}else{
			return ' '; }}}Copy the code

Additional note: StrtoTime () parses any English text date-time description as a Unix timestamp, returning the timestamp on success or FALSE otherwise (prior to PHP 5.1.0, this function returned -1 on failure)

Model modifiers

      
namespace app\index\model;
use think\Model;
class User extends Model
{
	/ / modifier
	public function setTimeAttr()
	{
        return time();
	}
    /** * modifiers: encrypt the password field and store it * $val the first argument is the password * $data the second argument is the added data (optional) */
    public function setPasswordAttr($val.$data){
        if($val= = =' ') {
            return $val;
        }else{
            return md5($val.$data['email']); }}}Copy the code

Automatic completion of model

Auto adds and updates, auto-complete property array insert only adds, auto-complete property array update only updates

1. Automatic completion

      
namespace app\index\model;
use think\Model;
class User extends Model
{
	// Add and modify fields automatically
    protected $auto = ['addtime'];

    public function setAddtimeAttr(){
        returntime(); }}Copy the code

2. Data is added automatically

      
namespace app\index\model;
use think\Model;
class User extends Model
{
	// Add autocomplete
    protected $insert = ['addtime'];

    public function setAddtimeAttr(){
        returntime(); }}Copy the code

3, data update, automatically complete:

      
namespace app\index\model;
use think\Model;
class User extends Model
{
	// Update is done automatically
    protected $update = ['addtime'];

    public function setAddtimeAttr(){
        returntime(); }}Copy the code

Autocomplete timestamp

In the database configuration file database.php, there is the following configuration:

// Write the timestamp field automatically
'auto_timestamp'= >false.// If enabled (set to true), all tables will be timestamped automatically, but this is not recommended. It is safer to set it only where needed.
Copy the code

For example, for the User table, the timestamp is automatically completed, which is set in the User model:

      
namespace app\index\model;
use think\Model;

class User extends Model{
    // Enable the autocomplete timestamp function
    protected $autoWriteTimestamp = true;
    // After opening,
    // When adding data, the default autocomplete fields are: create_time and update_time
    // When modifying data, the default autocomplete field is update_time
    
    // If the table contains neither of these fields, an error will be reported. The following changes are required:
    protected $createTime = 'addtime';// Change the default add time field
    protected $updateTime = 'updtime';// Change the default change time field
    protected $updateTime = false;// Set to false when this field is not needed
}
Copy the code

Update_time = update_time;

1. Use update

User::update(['name'= >'anyang'], ['id'= >1]);
Copy the code

Update method in Thinkphp source code is as follows:

/** * Update data *@access public
    * @paramArray $data Specifies the data array *@paramArray $WHERE update condition *@paramArray | true $* field allows field@return $this
    */
   public static function update($data= [].$where= [].$field = null)
   {
       $model = new static(a);if (!empty($field)) {
           $model->allowField($field);
       }
       $result = $model->isUpdate(true)->save($data.$where);
       return $model;
   }
Copy the code

2, use save

$user=new User;
$user->isUpdate(true)->save(['name'= >'anyang'], ['id'= >1]);
Copy the code

 

 

13. Soft Delete

What is soft delete?

When some records are deleted, sometimes we need to fake the deletion, just by changing the state of a field to mark that the record has been deleted, but in fact, the records still exist in the database. The application scenario of fake delete is still more, such as the payment record of Alipay, we delete in the APP, it will not be displayed again, do you think really deleted, will not leave any trace? No, no, the deletion of Alipay collection records is only soft deletion, in the alipay database, in fact, still retain these collection records, if your collection is suspected of violation or violation of the law, the police can still check through the alipay network police background.

1. Enable soft delete

      
namespace app\index\model;
use think\Model;
use traits\model\SoftDelete;// Introduce soft deleted classes

class Order extends Model{
    // Use soft delete
    // When deleting, the default field is updated with delete_time
    use SoftDelete;
    // If the table does not contain the delete_time field, an error will be reported. The following changes are required:
    protected $deleteTime = 'deltime';
}
Copy the code

2, controller soft delete, return the number of rows affected

 $res = Order::destroy(1);
    
Copy the code

After a delete is performed, the delete_time field is updated, and if auto-complete is enabled for the update_time field, the update_time field will also be updated.

3. If soft delete is enabled and you need to delete data without soft delete, use the following method

// DeStory () Passes true as the second argument
$res = Order::destroy(1.true);

// The delete() argument passes true
$orderData = Order::get(1);
$orderData ->delete(true);
Copy the code

4. Query the data that has been deleted

$res = Order::withTrashed(true)->find(1);
Copy the code

5. Query only the data that has been soft deleted

$res = Order::onlyTrashed()->select();
Copy the code