YII2 Database Query Practice _ database Other

The simple practice of basic operation of database, such as the YII2 frame, the search for additions and deletions, and related queries.

Database configuration.

/config/db.php for database configuration

In practice, there is a test library-"Test table-" two records as follows

Mysql> select * from test;
+----+--------+
| id | name |
+----+--------+
| 1 | zhuai |
| 2 | Heng | 
+----+--------+
rows in Set (0.00 sec)

SQL Query method

YII2 provides the original database query method Findbysql and, by way of placeholders, automates the basic SQL injection defenses. Up 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 prevents SQL injection mode
$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

Each framework, in addition to its original SQL, provides the appropriate encapsulated query, Yii2.

Create model

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

<?php
namespace App\models;
Use Yii;
Use Yii\db\activerecord;
Class Test extends ActiveRecord
{
//Can not, corresponding table: The default class name and table name match, you do not need this function public
static function tablename ()
{ Return
' Test ';
}
No, validator: primarily used to validate fields public
function rules () {return
[
' id ', ' integer '],
[' Name ', ' string ', '] Length ' => [0]],
];
}

Need to introduce model when using

Use App\models\test;
Add action
//Add operation
$test = new test ();
$test->name = ' Test ';
Verification of legality
$test->validate ();
if ($test->haserrors ()) {
echo "data is not valid";
Die;
}
$test->save ();

Query operations

Query operations first on official documents

ActiveRecord doc

where doc

It needs to be emphasized that YII queries provide a very rich library, such as batch query processing in code, and so on, and 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 ', 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 use Obj->array
$res = Test::find ()->where ([' Between ', ' id ', 1,2])->asarray ()->all ();
Var_dump ($res [0][' id ']); 2
//Bulk query for large memory operation of the bulk query
foreach (Test::find ()->batch (1) as $test) {
var_dump (count ($test));
}

Delete operation

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

Modify Operation

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

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

Associating query Operations

Two tables in the associated 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 acquisition
$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 methods of associative query; however, in the Controller to do the related operations, the code is too confusing, in model to encapsulate the call

First, encapsulate the associated call function in student model

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

Call directly after, two ways of calling

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

At last

The above in the deployment of YII2 and the use of some of the basic additions and deletions to check, associated queries and other operations.