YII2 database query practices and YII2 database practices
This article explores the yii2 framework and provides simple practices for adding, deleting, modifying, and querying, and associated queries and other basic database operations.
Database Configuration.
/Config/db. php for Database Configuration
In practice, there is a test database-test table-two records are as follows:
mysql> select * from test;+----+--------+| id | name |+----+--------+| 1 | zhuai || 2 | heng | +----+--------+18 rows in set (0.00 sec)
SQL query method
Yii2 provides the original database query method findBySql, and uses placeholders to automatically defend against basic SQL injection. Above code
// The most basic query method $ SQL = "select * from test where 1"; $ res = Test: findBySql ($ SQL)-> all (); var_dump (count ($ res); // res-> 2 // findbysql prevents the SQL Injection Method $ id = '1 or 1 = 1 '; $ SQL = "select * from test where id = ". $ id; $ res = Test: findBySql ($ SQL)-> all (); var_dump (count ($ res )); // res-> 2 $ SQL = "select * from test where id =: id"; // The locator automatically prevents SQL injection $ res = Test :: findBySql ($ SQL, array (": id" => $ id)-> all (); var_dump (count ($ res); // res-> 1
ActiveRecord Query Method
In addition to the original SQL method, each framework provides the corresponding encapsulation query method, and yii2 also does.
Create model
The basic method of yii model is as follows. The code is not described in detail below.
<? Phpnamespace app \ models; use Yii; use yii \ db \ ActiveRecord; class Test extends ActiveRecord {// No; corresponding table: the default class name matches the table name, this function is not required. public static function tableName () {return 'test';} // optional. validators: used to verify the public function rules () of each field () {return [['id', 'integer'], ['name', 'string', 'length' => [0,100],] ;}}
You need to introduce the model when using it.
Use app \ models \ Test; add operation // add operation $ test = new Test (); $ test-> name = 'test '; // validity verification $ test-> validate (); if ($ test-> hasErrors () {echo "data is invalid"; die ;} $ test-> save ();
Query operations
Query operations first in the official documentation
ActiveRecord doc
Where doc
It should be emphasized that yii queries provide a lot of libraries, such as batch query processing in code. For details, see the documentation.
// Select // id = 1 $ res = Test: find ()-> where (['id' => 1])-> all (); var_dump (count ($ res); // 1 // id> 0 $ res = Test: find ()-> where (['>', 'id ', 0])-> all (); var_dump (count ($ res); // 2 // id> = 1 id <= 2 $ res = Test: find () -> where (['between', 'id', 1, 2])-> all (); var_dump (count ($ res )); // 2 // name field like $ res = Test: find ()-> where (['like', 'name', 'cuihuan ']) -> all (); var_dump (count ($ res); // 2 // query using obj-> array $ res = Test: find () -> where (['between', 'id', 1, 2])-> asArray ()-> all (); var_dump ($ res [0] ['id']); // 2 // batch query, for batch query of large memory operations foreach (Test: find () -> batch (1) as $ test) {var_dump (count ($ test ));}
Delete operation
// Delete // select to delete $ res = Test: find ()-> where (['id' => 1])-> all (); $ res [0]-> delete (); // directly delete var_dump (Test: deleteAll ('Id>: id', array (': id' => 2 )));
Modify operation
In addition to the method in the code, yii2 provides the update operation directly.
// Modify the activity record $ res = Test: find ()-> where (['id' => 4])-> one (); $ res-> name = "update"; $ res-> save ();
Associated query operations
Two tables in the join query example:
Student: id, name;
A score table (score): id, stu_id, score
// All score $ stu = Student: find ()-> where (['name' => 'xiaozhuai'])-> one (); var_dump ($ stu-> id); // obtain $ scores_1 = $ stu-> hasu ('app \ model \ Score ', ['stu _ id' => $ stu-> id])-> asArray ()-> all (); $ scores_2 = $ stu-> hasMany (Score :: className (), ['stu _ id' => 'id'])-> asArray ()-> all (); var_dump ($ scores_1); var_dump ($ scores_2 );
Two associated query methods. However, when performing related operations on the controller, the code is too messy and the call is encapsulated in the model.
First, encapsulate related association call functions in the student model.
<? Phpnamespace app \ models; use Yii; use yii \ db \ ActiveRecord; class Student extends ActiveRecord {public static function tableName () {return 'student ';} // obtain the Score information. public function getScores () {$ scores = $ this-> hasscores (Score: className (), ['stu _ id' => 'id']) -> asArray ()-> all (); return $ scores ;}}
Two call Methods
// Call $ scores = $ stu-> getScores (); var_dump ($ scores) after function encapsulation ); // use _ get's automatic call method $ scores = $ stu-> scores; var_dump ($ scores );
Last
The preceding operations include addition, deletion, modification, query, and association query during the deployment and use of yii2.
Articles you may be interested in:
- Yii Query Builder usage example tutorial
- Yii framework associated query with Usage Analysis
- Example of Yii CDbCriteria query condition usage
- Yii database query method
- Yii provides an example of array and Object-based Model Query Techniques.
- Detailed description of YII association query