YII2 database query practices and YII2 database practices

Source: Internet
Author: User
Tags basic sql injection

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.