Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login

You must be logged to download. Click here to login


MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation


MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

PHP Yii Framework: Working with database in practice

See in this article how to create a simple system with database and relationships through the Yii Framework, written in PHP. The intent of this text is to put into practice the basics of relational database.

About Yii Framework

Yii is a high-performance PHP framework best for developing Web 2.0 applications.

It helps Web developers build complex applications and deliver them on-time.

Yii is pronounced as Yee, and is an acroynym for "Yes It Is!". This is often the accurate, and most concise response to inquires from those new to Yii: Is it fast? ... Is it secure? ... Is it professional? ... Is it right for my next project? ... Yes, it is!

Yii is a free, open-source Web application development framework written in PHP5 that promotes clean, DRY design and encourages rapid development. It works to streamline your application development and helps to ensure an extremely efficient, extensible, and maintainable end product.

Being extremely performance optimized, Yii is a perfect choice for any sized project. However, it has been built with sophisticated, enterprise applications in mind. You have full control over the configuration from head-to-toe (presentation-to-persistence) to conform to your enterprise development guidelines. It comes packaged with tools to help test and debug your application, and has clear and comprehensive documentation.


Yii is the brainchild of its founder, Qiang Xue, who started the Yii project on January 1, 2008. Qiang previously developed and maintained the Prado framework. The years of experience gained and developer feedback gathered from that project solidified the need for an extremely fast, secure and professional framework that is tailor-made to meet the expectations of Web 2.0 application development. On December 3, 2008, after nearly one year's development, Yii 1.0 was formally released to the public.

Its extremely impressive performance metrics when compared to other PHP-based frameworks immediately drew very positive attention and its popularity and adoption continues to grow at an ever increasing rate.

To start the development of this paper, one must keep in mind the concept of MVC architecture (Model-View-Controller) in PHP and have installed on our machine the latest stable version of Yii Framework, which is 1.1. (Please, take a look at Links section to get these download links)

Application scenario

For this tutorial will be created the fictional news portal News Portal, which will be registered daily news. These news will be published only by administrators and users must have only one category.

Note: The operating system used in this example is Ubuntu 4.14 LTS, with the look changed and applied the theme Macbuntu. We're going to discuss here also the steps to who has the Windows operating system. The different steps in each system will be explained.

To start the application, open the terminal (in case of Windows is the command prompt in administrator mode) and navigate to the directory where it is located the folder Yii.

Create the application by Yiic utility. To do this, type the following code in Listing 1.

Listing 1. Creating application on Linux operating systems.

sudo ./yiic webapp ../../PortalNews/

After creating the project is displayed a message similar to that shown in Figure 1.

Application successfully created

Figure 1. Application successfully created.

If the machine's operating system is Windows, type this code in Listing 2.

Listing 2. Creating application on Windows operating systems.

 yiic webapp ../../PortalNews/

Database Structure

This is the crux of the project, the database. It is essential for a system logic modeling since is with it that will be created throughout the flow and project actions. Although it is not mandatory to create the logical model for your application, it is good practice to do so. This makes the software quite dense and cohesive. For this article we will use the MySQL database in version 5.5.37-0ubuntu0.14.04.1 with PHPMyAdmin management utility.

Note: In order to facilitate the teaching of this subject, the more specific issues related to modeling and database will have a superficial view.

In Figure 2 follows the logic model applied to the fictitious site "PortalNews". From it will be implemented the physical model, where instead of entities we have tables. The following explains how we create the logical model correctly.

PortalNews logic model

Figure 2. PortalNews logic model.

In Figure 2 above is represented the data modeling to be used in the application. The following is a short summary on the graph above:

  • In ADMINISTRATOR entity properties are assigned: id, name, email, login and password; besides the fact that a user with administrator level of PortalNews will publish articles to the site. Due to the user possibility to publish any or numerous articles and an article to be published necessarily by a user (administrator), the final cardinality between entities and ADMINISTRATOR NEWS is set to N:N.
  • The entity NEWS is the model of the key and by it is based that all other entities present in it act. It has the properties: id, author, title, date, content. This entity has two relationships to other entities: the news is published by at least one and at most one administrator, then we can consider the 1:1 relationship (NEWS > ADMINISTRATOR) and 0: N (Administrator-> NEWS), characterizing the final cardinality, already mentioned, N:N (many-to-many).
  • NEWS also has a relationship with the entity CATEGORY. Due to the fact that the news has at least one and at most one category, as well as the category is "owned" by at least one or more news, the final cardinality is 1:N (one-to-many).

Physical Model

From this moment all the software logic is ready, then it is time to go to the physical model. The physical model is basically the "conversion" of logical data (entities) into physical data (table). The feature responsible for storing these data is the physical database.

So, to create the database type the following code in Listing 3.

Listing 3. Creating PortalNews database.


Once you have created the database, it is time to put the physical model into practice. Before you start writing the queries (commands) SQL to create the tables, here's what will be the necessary changes, always based on logic model created earlier:

  • The ADMINISTRATOR entity becomes its own table containing as fields (columns): id, name, email, login and password. The id field is the primary key with auto-incrementing. The fields: name, email, login and password are all of type VARCHAR (300), ie, containing not more than 300 characters. Note: It is a good practice to store passwords in the database. It is best to encrypt the password before writing it to the bank. For educational use in this article, passwords are stored directly in the database.
  • NEWS entity will also become a table, where it will have the fields (columns): id, idAuthor, idCategory, title, date, content. As with ADMINISTRATOR, the id field is primary key with auto-incrementing. The title field is of type VARCHAR (400), ie, you may have a maximum of 400 characters, while the field content will be of type TEXT.
  • The author attribute and GRADE entity present in the logic model, become here in idAuthor and idCategory respectively. These two will be foreign keys, which are referred to Management and Categories tables, respectively. Finally, the date field will be the TIMESTAMP type, with the default value (standard) and the attribute on update CURRENT_TIMESTAMP. This means that when you insert or update any row in the table this field is changed to the current time in the TIMESTAMP format.
  • CATEGORY entity will be a simple table, which contains only two fields: id and name. The id field, as in all other tables, primary key is, with auto-incrementing. The name field is of type VARCHAR (200) and can have a maximum of 200 characters.

Now that it's clear the way in which each entity is represented in the physical model, we will be codified. See Listing 4 code to create the administrative users table in PortalNews.

Note: It is extremely important to follow the chronological order obeyed here to create up tables. Because one having foreign(s) key(s), there will be problems running their queries (commands) SQL in reverse order the proposal here.

Listing 4. Creating Administrators table

`name` varchar(300) NOT NULL, 
`email` varchar(300) NOT NULL, 
`login` varchar(300) NOT NULL, 
`password` varchar(300) NOT NULL, PRIMARY KEY (`id`) ) 

Now follow the Listing 5 code to create the table of categories.

Listing 5. Creating Categories table.

`name` varchar(200) NOT NULL, PRIMARY KEY (`id`) ) 

Finally, it's time to create the News table. Check in Listing 6.

Listing 6. Creating News table.

`idAuthor` int(11) NOT NULL, 
`idCategory` int(11) NOT NULL, 
`title` varchar(400) NOT NULL, 
`content` text NOT NULL, PRIMARY KEY (`id`), 
KEY `idAuthor` (`idAutor`,`idCategory`), 
KEY `idCategory` (`idCategory`) ) 

Enabling Gii utilities, MySQL and custom URL

Now that the application and the data model are created, it is necessary the use of Gii utility, which is a script Yii responsible for generating models, controllers, and CRUD modules. First visit localhost/PortalNews to see if the application is running smoothly. Check in Figure 3.

Website initial page

Figure 3. Website initial page.

If the application is working properly, go to the project directory and open the file main.php, into the folder protected/config. Use the text editor of your choice, here we will use the Sublime Text 2, which can be downloaded from its official website (

Search for "uncomment the following to enable the Gii tool". After finding it uncomment the corresponding code snippet. Attention is drawn to change the password via the password parameter. Here the password used is 123. The code will be commented out as in Listing 7.

Listing 7. Enabling utility Gii.

'gii'=> array( 
	// If removed, Gii defaults to localhost only. Edit carefully to taste. 

Now activate the MySQL uncommenting the lines related to it. See in Listing 8 as is the commented out code.

Listing 8. Enabling MySQL service.

	'connectionString' => 'mysql:host=localhost;dbname=PortalNews', 
	'emulatePrepare' => true, 
	'username' => 'root', 
	'password' => '123', 
	'charset' => 'utf8', 

To enable the URL style "controller/action", uncomment the Listing 9 code.

Listing 9: Activating URL style "controller/action".


Creating Models

Access localhost/PortalNews/index.php/gii to access the Gii and create models. Check in Figure 4 how is the Gii login page.

Gii's initial page

Figure 4. Gii's initial page.

After logging in the Gii with the password you set earlier, click "Model Generator", located to the left of the screen. Check in Figure 5 the page loaded.

Page 'Model Generator' charged.

Figure 5. Page 'Model Generator' charged.

In Gii is very simple to generate models, simply put the table name in "Table Name", automatically the name of the model class (model) is completed. After that, just click on "preview" and then "generate". A message similar to Figure 6 will be displayed.

Figure 6.Administrator Model successfully generated.

Perform this step two more times and create the models News and Category, according to the tables stored in the database.

Creation of Controllers and CRUD

To create Controllers and CRUD, the steps are almost the same. Fill out the "Controller ID" with the name you want. It is good practice to name the Controller the same name as the class model (model), given the fact that Yii automatically adds the "Controller" suffix to the driver file.

Perform this step two more times to generate the controllers and CRUDs Category and News. After generating the controller a message appears as in Figure 7.

Controller Administrator successfully generated.

Figure 7.Controller Administrator successfully generated.

Administrators Users Registration

Once generated the models, controllers and views needed to run the application, register a new user on the system. Access localhost/PortalNews/index.php/administrator/create, enter the username and password to register a new user.

Note: Authentication of users by login and password will not be seen in this article, as it would cause a wide extent of this. Use the default login Yii to login: admin/admin or demo/demo.

After logging into the system, the user registration and subsequently the screen with registered user will be charged. Check in Figures 8 and 9.

Form of Administrator Registration.

Figure 8. Form of Administrator Registration.

Test User registered as an Administrator.

Figure 9. Test User registered as an Administrator.

Register Categories

To submit new categories in the system to access localhost/PortalNews/index.php/category/create, name the category and then click "Create". Check in Figure 10 the registered category page.

Registered category page.

Figure 10. Registered category page.

To make the most intuitive form and easy to register news, will be placed two checkboxes with the names of authors and categories registered, instead of sending the textboxes created by default Yii. See the code Listings of 10, 11 and 12.

In order to become more dynamic forms of implementation, Yii creates the CREATE file (located in protected/views/controllerName), which is responsible for rendering a partial view, ie a view is rendered "in" the another. This action occurs through renderPartial() method, from Controller class. Because of this, you must first pass the data to the CREATE and then direct them to the _form.php.

Listing 10. Passing Authors and Categories for the CREATE file, from the news record.

// Action CREATE of NEWS model, file NewsController.php 
public function actionCreate() { 
	$model=new News; 
	$categories = Category::model()->findAll(); 
	$authors = Administrator::model()->findAll(); 
	// Uncomment the following line if AJAX validation is needed 
	// $this->performAjaxValidation($model); 
	if(isset($_POST['News'])) { 
	// Passing caught data from actionCreate to Create View. 

Listing 11. Passing the data retrieved in view Create.

// The above code has been omitted for better viewing
<h1>Create News</h1> <?php $this->renderPartial('_form', array('model'=>$model, 'authors'=>$authors, 'categories'=>$categories)); ?>

Listing 12. Form with checkboxes inserted.

// Variables authors and categories are additions of view Create.
<div class="row"> 
	<select name="News[idAuthor]"> 
		<?php for ($i=0; $i < count($authors); $i++): ?> 
			<option value="<?php echo $authors[$i]['id']; ?>"><?php echo $authors[$i]['nome']; ?></option> 
		<?php endfor; ?> 
<div class="row"> 
	<select name="News[idCategory]"> 
		<?php for ($i=0; $i < count($categories); $i++): ?> 
			<option value="<?php echo $categories[$i]['id']; ?>"><?php echo $categories[$i]['nome']; ?></option> 
		<?php endfor; ?> 

With this form was as shown in Figure 12.

Form for registering news.

Figure 11. Form for registering news.


This was a brief introduction to how the relational databases and how Yii Framework treat these data.

Did you like the article? Want to give your opinion? Leave your comment and see you soon!


Yii Framework - Official Page

Julio is a System analyst and enthusiast of Information Technology. He is currently a developer at iFactory Solutions company, working in the development of strategic systems, is also a JAVA instructor. He has knowledge and experi...

What did you think of this post?
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!

> More info about MrBool Credits
You must be logged to download.

Click here to login