PHP development framework Yii Framework tutorial (25) Database-Query Builder example

The previous article introduced how PHP uses DAO (Database Access Object Interface) to access the database. Using DAO requires programmers to write SQL statements. For some complex SQL statements, Yii provides Query Builder to help programmers generate SQL statements. Query Builder provides an object-oriented method to dynamically create SQL statements. To make an inappropriate comparison, PHP's DAO and .Net's DAO interface are very similar. Query Builder is a bit like LINQ, although it has smaller functions than LINQ. a lot of. For some simple SQL queries, there is usually no need to resort to 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 the dynamic creation of more complex SQL queries through programs.

Automatically add quotation marks to the table names and lists in the created SQL statements to avoid conflicts with SQL reserved identifiers.

Specify to add quotes for parameters and use parameter binding as much as possible to reduce the risk of SQL Injection.

Using Query Builder does not directly write SQL statements, but provides a certain degree of database abstraction, thus providing convenience for switching database types.

This example queries Chinook's two tables Customer and Employee, and queries the contact information of all customers managed by EmployeeId=4.

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 This example uses Query Builder to create a 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.Email')
->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,
));
}

You can see that Query Builder also uses CDbCommand. CDbCommand provides the following methods for querying data:

select()
selectDistinct()
from()
where()
join()
leftJoin()
rightJoin()
crossJoin()
naturalJoin()
group()
having()
order()
limit()
offset()
union()

In addition, data definition methods:

createTable()
renameTable()
dropTable()
truncateTable()
addColumn()
renameColumn()
alterColumn()
dropColumn()是
createIndex()
dropIndex()

You can see that the methods supported by CDbCommand are basically the same as SQL The keywords of the statement correspond to each other one by one, so it is entirely up to personal preference without using Query Builder. For simple SQL statements, SQL statements can be used directly, and for more complex queries, Query Builder can be used.

This example displays the results:

The above is the content of the PHP development framework Yii Framework tutorial (25) database-Query Builder example. For more related content, please Follow the PHP Chinese website (www.php.cn)!