PHP development framework YiiFramework tutorial (25) database-QueryBuilder example

PHP development framework YiiFramework tutorial (25) database-QueryBuilder example

The previous article introduced how to access the database using DAO (database access object interface) in PHP. to use DAO, programmers need to write SQL statements. for some complicated SQL statements, yii provides Query Builder to help programmers generate SQL statements. Query Builder provides an object-oriented method to dynamically create SQL statements, making an inappropriate comparison between DAO and. net DAO interface is very type, and Query builder is a bit like LINQ, although it has much less functionality than LINQ. For some simple SQL queries, you usually do not need to use Query Builder, such as querying the Employee table in the previous article.

Compared with using SQL statements directly, using Query Builder has the following benefits:

Supports dynamic creation of complex SQL queries through programs.

Automatically adds quotation marks to the list of tables in the created SQL statement to avoid conflicts with the SQL reserved identifier.

Add quotation marks for parameters and bind parameters as much as possible to reduce the risk of SQL Injection ..

Using Query Builder does not directly write SQL statements, but provides database abstraction to a certain extent, which facilitates database type switching.

In this example, the Customer and Employee tables of Chinook are queried, and the contact information of all customers managed by EmployeeId = 4 is queried.

If you use SQL query, you can write:

SELECT c. FirstName, c. LastName, c. Address, c. Email
FROM customer c
INNER JOIN
Employee e
ON c. SupportRepId = e. EmployeeId
WHERE e. EmployeeId = 4 in this example, use Query Builder to create an SQL Query and modify the indexAction method of SiteController:

Public function actionIndex ()
{

$ Model = array ();
$ Connection = Yii: app ()-> db;
$ Command = $ connection-> createCommand ()
-> Select ('C. FirstName, c. LastName, c. Address, c. e-mail ')
-> From ('customer c ')
-> Join ('employee E', 'C. SupportRepId = e. EmployeeId ')
-> Where ('E. EmployeeId = 4 ');

$ DataReader = $ command-> query ();

// Each $ row is an array representing a row of data
Foreach ($ dataReader as $ row)
{
$ Customer = new DataModel ();

$ Customer-> firstName = $ row ['firstname'];
$ Customer-> lastName = $ row ['lastname'];

$ Customer-> address = $ row ['address'];
$ Customer-> email = $ row ['email '];

$ Model [] = $ customer;
}

$ This-> render ('index', array (
'Model' => $ model,

));
}

We can see that the Query Builder also uses CDbCommand, which provides the following methods to Query data:

Select ()

SelectDistinct ()

From ()

Where ()

Join ()

LeftJoin ()

RightJoin ()

CrossJoin ()

NaturalJoin ()

Group ()

Having ()

Order ()

Limit ()

Offset ()

Union ()

In addition, the data definition method is as follows:

CreateTable ()

RenameTable ()

DropTable ()

TruncateTable ()

AddColumn ()

RenameColumn ()

AlterColumn ()

DropColumn () is

CreateIndex ()

DropIndex ()

We can see that the methods supported by CDbCommand are basically one-to-one correspondence with the keywords of SQL statements, so that you can directly use SQL statements for simple SQL statements without using Query Builder, for complex queries, you can use Query Builder.

The result in this example is as follows: