This is the 18th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021

Database operations for Yii2.0.

Since I have installed yiI 2.0-advanced version here. This piece of crap comes with its own database, which was installed with it in the previous installment. You’ve configured everything, you’ve configured everything.

No matter what the framework, the database is its own set of model operations class encapsulation.

But I generally do not use, the cost of learning is too high, and he encapsulated the method of what generally do not touch the whole. It’s too hard to learn. Anyway, the underlying execution is SQL.

So I’ll skip over the database-related model operations that Yii2.0 encapsulates itself here.

In general, I used to use native SQL statements to add, delete, change and check the database. And this method, I think the maintenance of later is also a good choice, after all, native Sql statements, should be able to understand. Less time to learn the framework.

Official start:

In Yii2.0, all methods that are externally accessible need to be action.

Yii2.0 encapsulates roughly two general methods for performing database operations:

1: Query () and queryXXX() methods. The query() method returns an object and is generally used less often, although this is for me, depending on your needs.

(1) : query method

$dataReader=$command->query();
// Return the CDbDataReader object. Note that this represents a result set and not a record. The record can be obtained by:
// CDbDataReader::read() retrieves data one row ata time and returns false at the end
while(($row=$dataReader->read())! = =false) 
// CDbDataReader implements the iterator interface so foreach traversal can be used
foreach($dataReader as $row)
// Return all records at once (array)
    $rows=$dataReader->readAll();
Copy the code

Let’s write a few pieces of code to test the methods that the Yii2.0 framework provides for us to execute native SQL.

(2) : queryXXX method

(1) queryAll data :(2d array) queryAll

/ / SQL statements
        $sql_queryAll = "SELECT * FROM art_category";
        // Create a query object
        $command=Yii::$app->db->createCommand($sql_queryAll);
        // Execute query for all data
        $result = $command->queryAll();
  
        echo "<pre>";
        var_dump($result);
Copy the code

(2) : Query the first row of data: (one-dimensional array) queryOne

/ / SQL statements
        $sql_queryAll = "SELECT * FROM art_category ";
        // Create a query object
        $command=Yii::$app->db->createCommand($sql_queryAll);
        // Execute query for all data
        // $result = $command->queryAll();
        // Perform a query for a row of data
        $result = $command->queryOne();
     
        echo "<pre>";
        var_dump($result);
Copy the code

The rest of the method usually used not much, with the words are also in THE SQL statement inside the implementation

2: execute method

Used for data UPDATE (non-query) operations (INSERT, UPDATE, and DELETE) that return the number of record rows affected by the operation

(1) add insert()->execute

// Batch write
        $insertSql = "INSERT INTO art_category (cat_name,cat_pid) values ('xxx','32'), ('aaaaaaa','963')";
  
        $command=Yii::$app->db->createCommand($insertSql);
  
        // Returns the number of successful writes.
        $result = $command->execute();
  
        echo "<pre>";
        var_dump($result);
Copy the code

Update ()->execute

// Modify the statement
        $sql = "UPDATE art_category SET cat_name = 'camellia' WHERE id = 10";
  
        $command=Yii::$app->db->createCommand($sql);
  
        // Returns the number of successful modifications.
        $result = $command->execute();
  
        echo "<pre>";
        var_dump($result);
Copy the code

(3) delete delete()->execute

// Delete the statement
        $sql = "DELETE FROM art_category WHERE id = '10'";
  
        $command=Yii::$app->db->createCommand($sql);
  
        // Returns the number of successfully deleted items.
        $result = $command->execute();
  
        echo "<pre>";
        var_dump($result);
Copy the code

These are the general methods for executing native SQL, and we’ll see what happens later.

Then take a look at what the Yii2.0 framework does for database transactions:

$db = Yii::app()->db;
$dbTrans = $db->beginTransaction();      // Start things
$dbTrans->commit();                      / / submit
$dbTrans->rollback();                    / / rollback
Copy the code

Very simple.

The last

Data is priceless, so exercise caution.

For good suggestions, please enter your comments below.

Welcome to my blog guanchao.site

Welcome to applets: