YII2 database query practices

I am a beginner of Yii2. I used Yii2 to establish a simple model to associate a y_user table used for testing in the database. However, an exception is thrown during access, and the exception code does not seem to have selected a database, however, I configured the correct database name in the configuration file, but the problem still exists. Exceptions and code... this article mainly introduces information about the YII2 database query practice. For more information, see

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
// Use findbysql to prevent SQL injection
$ 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.

Namespace app \ models;
Use Yii;
Use yii \ db \ ActiveRecord;
Class Test extends ActiveRecord
{
// None. corresponding table: the default class name matches the table name, so this function is not required.
Public static function tableName ()
{
Return 'test ';
}
// Optional; validators: used to verify fields
Public function rules (){
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 "invalid data ";
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 (['beten', '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
// Use obj-> array for query
$ Res = Test: find ()-> where (['beten', 'id', 1, 2])-> asArray ()-> all ();
Var_dump ($ res [0] ['id']); // 2
// Batch query: Batch query of large memory operations
Foreach (Test: find ()-> batch (1) as $ test ){
Var_dump (count ($ test ));
}

Delete operation

// Delete
// Select and delete
$ Res = Test: find ()-> where (['id' => 1])-> all ();
$ Res [0]-> delete ();
// Delete directly
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.

// Activity record modification
$ 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 scores of the corresponding students
$ Stu = Student: find ()-> where (['name' => 'xiaozhuai'])-> one ();
Var_dump ($ stu-> id );
// Basic retrieval
$ Scores_1 = $ stu-> hasMany ('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.

Namespace app \ models;
Use Yii;
Use yii \ db \ ActiveRecord;
Class Student extends ActiveRecord
{
Public static function tableName ()
{
Return 'student ';
}
// Obtain score information
Public function getScores ()
{
$ Scores = $ this-> hasales (Score: className (), ['Stu _ id' => 'id'])-> asArray ()-> all ();
Return $ scores;
}
}

Two call methods

// Called After function encapsulation
$ Scores = $ stu-> getScores ();
Var_dump ($ scores );
// 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.