Backend Development
PHP Tutorial
PHP development framework Yii Framework tutorial (25) Database-Query Builder example
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)!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to use PHP framework Yii to develop a highly available cloud backup system
Jun 27, 2023 am 09:04 AM
With the continuous development of cloud computing technology, data backup has become something that every enterprise must do. In this context, it is particularly important to develop a highly available cloud backup system. The PHP framework Yii is a powerful framework that can help developers quickly build high-performance web applications. The following will introduce how to use the Yii framework to develop a highly available cloud backup system. Designing the database model In the Yii framework, the database model is a very important part. Because the data backup system requires a lot of tables and relationships
Symfony vs Yii2: Which framework is better for developing large-scale web applications?
Jun 19, 2023 am 10:57 AM
As the demand for web applications continues to grow, developers have more and more choices in choosing development frameworks. Symfony and Yii2 are two popular PHP frameworks. They both have powerful functions and performance, but when faced with the need to develop large-scale web applications, which framework is more suitable? Next we will conduct a comparative analysis of Symphony and Yii2 to help you make a better choice. Basic Overview Symphony is an open source web application framework written in PHP and is built on
How to use Yii3 framework in php?
May 31, 2023 pm 10:42 PM
As the Internet continues to develop, the demand for web application development is also getting higher and higher. For developers, developing applications requires a stable, efficient, and powerful framework, which can improve development efficiency. Yii is a leading high-performance PHP framework that provides rich features and good performance. Yii3 is the next generation version of the Yii framework, which further optimizes performance and code quality based on Yii2. In this article, we will introduce how to use Yii3 framework to develop PHP applications.
Data query in Yii framework: access data efficiently
Jun 21, 2023 am 11:22 AM
The Yii framework is an open source PHP Web application framework that provides numerous tools and components to simplify the process of Web application development, of which data query is one of the important components. In the Yii framework, we can use SQL-like syntax to access the database to query and manipulate data efficiently. The query builder of the Yii framework mainly includes the following types: ActiveRecord query, QueryBuilder query, command query and original SQL query
Yii2 vs Phalcon: Which framework is better for developing graphics rendering applications?
Jun 19, 2023 am 08:09 AM
In the current information age, big data, artificial intelligence, cloud computing and other technologies have become the focus of major enterprises. Among these technologies, graphics card rendering technology, as a high-performance graphics processing technology, has received more and more attention. Graphics card rendering technology is widely used in game development, film and television special effects, engineering modeling and other fields. For developers, choosing a framework that suits their projects is a very important decision. Among current languages, PHP is a very dynamic language. Some excellent PHP frameworks such as Yii2, Ph
Yii2 Programming Guide: How to run Cron service
Sep 01, 2023 pm 11:21 PM
If you're asking "What is Yii?" check out my previous tutorial: Introduction to the Yii Framework, which reviews the benefits of Yii and outlines what's new in Yii 2.0, released in October 2014. Hmm> In this Programming with Yii2 series, I will guide readers in using the Yii2PHP framework. In today's tutorial, I will share with you how to leverage Yii's console functionality to run cron jobs. In the past, I've used wget - a web-accessible URL - in a cron job to run my background tasks. This raises security concerns and has some performance issues. While I discussed some ways to mitigate the risk in our Security for Startup series, I had hoped to transition to console-driven commands
Yii2 vs Symfony: Which framework is better for API development?
Jun 18, 2023 pm 11:00 PM
With the rapid development of the Internet, APIs have become an important way to exchange data between various applications. Therefore, it has become increasingly important to develop an API framework that is easy to maintain, efficient, and stable. When choosing an API framework, Yii2 and Symfony are two popular choices among developers. So, which one is more suitable for API development? This article will compare these two frameworks and give some conclusions. 1. Basic introduction Yii2 and Symfony are mature PHP frameworks with corresponding extensions that can be used to develop
How to convert yii objects into arrays or directly output to json format
Jan 08, 2021 am 10:13 AM
Yii framework: This article introduces Yii's method of converting objects into arrays or directly outputting them into json format. It has certain reference value and I hope it can help you.
