|

Connect To A MySQL Database With Yii

Connect To A MySQL Database With Yii

We’re moving along with the wonderful Yii 2 Framework and we’ve learned a bit about Actions, Views, and Forms. With these basic concepts, we’ve been able to create a simple application in our development environment, and it’s a lot of fun. Most applications are going to need a persistance layer at some point, and this is where the database connectivity comes in. Yii has absolutely amazing and powerful tools for working with databases. In this episode we’ll get our database connection configured, create a new model that extends the Yii ActiveRecord Class, then make some queries and display the results with pagination. Let’s get started!

Creat A Database In MySQL

Before we can do anything else, we’re going to need a database to work with! We will use MySQL and our database will be named yii_framework_database. Visit your friendly MySQL terminal or GUI tool of your choice and run this SQL statement.

Now with the database in place, we need a table. We’ll create a table that holds information about companies. To do this we’ll create a table called company and here is the SQL to do it.

Configure The Database Connection

All you need to do is edit config/db.php like so.

Create A Yii Model That Extends ActiveRecord

In a prior example, we created a model that did not actually use a database. It was used to simply grab data from a form and run validation on it. In that instance, all have to do is extend the Yii Base Model. In this example, we want to work with the database so we will extend the Yii Db ActiveRecord. Let’s create a model that will be able to easily work with the database and table we just created.

Company.php source

That’s all you have to do friends! This simple snippet of code provides an absolute wealth of power and expression to your application due to inheritance. Your Company model now has access to all of the methods available in the yiidbActiveRecord class. If you ever need to know what methods you have access to and you don’t have the documentation readily available, you can use this trick with PHP’s built in Reflection Class. Using something like the following will print out all 126 methods of the class.

Create A Yii Controller

With a database, a database connection, and an active record model in place, we are ready to create a controller that leverages these things to read and write data to the database. This is a big snippet so we’ll just paste it in, then discuss it.

What we do here is put a little bit of logic into the controller to determine if we have any data to work with in the database yet. If we do not have any rows in the database yet, then we create all those new objects and save them to the database. Look at how slick and expressive that syntax is! This is the power of an Object Relational Mapper. Once the data is created, we’ll use the Pagination class to create a nice paginated output of our query results, then render a view to show this.

Create The Yii View

We’re almost there, but we need a view to be able to see this data in a pleasing way. Here is the view we will use.

company/index.php source

Load It Up!

We’re ready to test this out. Let’s see what happens.

http://localhost/yii/basic/web/index.php?r=company

Yii Pagination

Awesome! Looks to be working quite well. We have all of the companies, their industry, as well as their ticker symbol and some nice pagination. Looking great! Let’s make one more addition. Imagine that we don’t like the way the ticker symbols are in lowercase. We can use our Model to loop through all of the records in the database and apply a strtoupper() function to the value in the ticker field. Here is the snippet to accomplish that.

Now if we load up our page again, let’s see the result.
Yii Active Record Loop Example

Very Nice! We got the exact result we were looking for.

Conclusion

The Yii Framework is proving to be a pleasure to work with. Yii 2 has been in development for three years with over 300 developers contributing code over the course of 10,000 commits. Their hard work is definitely paying off for all of us who enjoy using Yii.