YII2 database Query Practice

This article mainly introduces the YII2 database query practice related information, the need for friends can refer to the following

The simple practice of basic operation of database such as yii2 frame, adding and deleting, correlation query and so on.

Database configuration.

/config/db.php the database configuration

In the course of practice there is a test library-"Test table-" two records are as follows

Mysql> select * from test;
+----+--------+
| ID | name |
+----+--------+
| 1 | Zhuai |
| 2 | Heng |
+----+--------+
Rows in Set (0.00 sec)

SQL Query method

YII2 provides the raw database query method Findbysql, and the basic SQL injection defense is done automatically through the placeholder method. On the Code

The most basic way to query
$sql = "SELECT * FROM Test where 1";
$res = Test::findbysql ($sql)->all ();
Var_dump (Count ($res)); Res->2
Findbysql Preventing SQL injection methods
$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";
Locators automatically prevent SQL injection
$res = Test::findbysql ($sql, Array (": id" = + $id))->all ();
Var_dump (Count ($res)); Res->1

ActiveRecord Query method

Each framework, in addition to the original SQL, will provide a corresponding package of query methods, Yii2.

Create model

Yii Model Basic mode is as follows, the code below does not repeat.

<?php
namespace App\models;
Use Yii;
Use Yii\db\activerecord;
Class Test extends ActiveRecord
{
None, corresponding table: default class name and table name match, no need for this function
public static function TableName ()
{
return ' test ';
}
None, Authenticator: used primarily for verifying individual fields
Public Function rules () {
return [
[' id ', ' integer '],
[' Name ', ' string ', ' length ' = [0, 100]],
];
}
}

When you use it, you need to introduce model

Use App\models\test;
Add action
Add operation
$test = new test ();
$test->name = ' Test ';
Legality check
$test->validate ();
if ($test->haserrors ()) {
echo "Data is not legal";
Die
}
$test->save ();

Query operations

Query operations first on official documents

ActiveRecord doc

where doc

It should be emphasized that the YII query provides a particularly rich library, such as batch query processing in code, and so on, the details can be seen in the document.

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 ',])->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 queries
$res = Test::find ()->where ([' Between ', ' ID ', up])->asarray ()->all ();
Var_dump ($res [0][' id ']); 2
Bulk queries, bulk queries for large memory operations
foreach (Test::find ()->batch (1) as $test) {
Var_dump (Count ($test));
}

Delete operation

Delete
Elect to delete
$res = Test::find ()->where ([' ID ' =>1])->all ();
$res [0]->delete ();
Delete directly
Var_dump (Test::d eleteall (' Id>:id ', Array (': id ' = 2)));

Modify Operation

In addition to the way in the code, YII2 directly provides an update operation.

Activity Record Modification
$res = Test::find ()->where ([' ID ' =>4])->one ();
$res->name = "Update";
$res->save ();

Associating query Operations

Two tables in the association query example:

A student table (student): ID, name;

A score table (score): Id,stu_id,score

All score of the corresponding student
$stu = Student::find ()->where ([' name ' = ' Xiaozhuai '])->one ();
Var_dump ($stu->id);
Basic access
$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 related query methods; However, when the controller is involved in the operation, the code is too confusing to encapsulate the call in the model

First, encapsulate the related associated call function in the student model

<?php
namespace App\models;
Use Yii;
Use Yii\db\activerecord;
Class Student extends ActiveRecord
{
public static function TableName ()
{
Return ' student ';
}
Get the score information
Public Function Getscores ()
{
$scores = $this->hasmany (score::classname (), [' stu_id ' = ' id '])->asarray ()->all ();
return $scores;
}
}

Then call directly, two methods of invocation

Called after the function is encapsulated
$scores = $stu->getscores ();
Var_dump ($scores);
Using __get's automatic invocation method
$scores = $stu->scores;
Var_dump ($scores);

At last

The above in the deployment and use of YII2 in the process of some basic additions and deletions, related queries and other operations.

