Creating a web application with the Yii Framework and MariaDB

Yii is a fast and secure framework for developing PHP applications. It has excellent support for various databases, including MariaDB, as we’ll illustrate here.

Yii installation is simple and straightforward. Just download the framework archive and extract it to a directory outside your website’s document root. For example, if your site resides in /var/www/html/, extract the archive to /var/www/ so that you can access the framework at /var/www/framework. Then run the shell command /var/www/framework/yiic webapp /var/www/html/ to create the skeleton of a new web application in /var/www/html.

To make sure the PHP applications you create with Yii can work with MariaDB, make sure your PHP installation has MySQL PDO support, which means it will also have MariaDB support. In CentOS, the package that provides this is called php-mysql.

Once you have the skeleton of your new application created, configure it to use MariaDB as its database. If you don’t have a good test database at hand, you can use the well-known employees database. In Yii, database configuration goes to the file protected/config/main.php inside your site’s web root (e.g. /var/www/html/protected/config/main.php). There, in the components array, delete the default db connection, which is for SQLite, and create a new one for MariaDB that looks like this:

'db'=>array(
        'connectionString' => 'mysql:host=localhost;dbname=mariadbtest',
        'emulatePrepare' => true,
        'username' => 'maria',
        'password' => 'testpass',
        'charset' => 'utf8',
),

Since MariaDB is backwards-compatible with MySQL, you can use the MySQL driver as shown in the connection string above. Notice two interesting settings inside the db array. First, emulatePrepare allows you to emulate prepare statements, which can be useful when you want to bypass native PHP prepare support if it does not work properly for you. Second, charset in most cases should be utf8, unless your database and web server is configured with a different character set.

Connect Yii to MariaDB

Like many modern programming frameworks, Yii adheres to the Model View Controller (MVC) concept, which means you have to create models, views, and controllers to build a web applicatino. In regards to databases and MariaDB, the most important part is the model, which determines the interaction with the database.

If you are new to MVC and Yii as a whole, I suggest using Gii, Yii’s web-based code generation tool. To use Gii, edit the main.php configuration file (/var/www/html/protected/config/main.php) and uncomment the modules array pertaining to Gii:

'modules'=>array(

            'gii'=>array(
                    'class'=>'system.gii.GiiModule',
                    'password'=>'some_password',
                    'ipFilters'=>array('127.0.0.1','YOUR_IP'),
            ),
    ),

Ensure that you specify a strong password, and allow your IP to connect. Then open your browser at http://yoursite/index.php?r=gii. Once you enter the password you specified, you should see an intuitive web interface that allows you to generate models, along with everything else (view, controllers, and even web forms) for your web application. To generate a model you just have to specify the name of your database table. The screenshot below shows how to generate a model called Employees for the employees table inside the example employees database.

One important option in the interface is called Build Relations. Make sure to leave it checked so that the relations between the tables are taken into consideration in the code generation.

Upon successful completion of the code generation, you should have a new file called protected/models/Employees.php (/var/www/html/protected/models/Employees.php) where the model for the employees is fully described. The new model is essentially a new PHP class that extends the CActive Record, Yii’s base class for representing relational data.

For your MariaDB database, the most important part is the relations function, which should look like this for the employees table:

public function relations()
{
        return array(
//...skipped
        'salaries' => array(self::HAS_MANY, 'Salaries', 'emp_no'),
//...skipped
        );
}

Each pair in the array describes a new relation. The salaries relation is type HAS_MANY. This means that an employee may have many salaries. In the database, this is defined as a constraint with foreign key in the salaries table CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE.

Other types of relations are determined by the tables structure. For example, if you generate a model for the salaries table from the example employees database, there the relation will be different: 'empNo' => array(self::BELONGS_TO, 'Employees', 'emp_no'), meaning that a salary entry belongs to an employee and is referenced to the emp_no column in the employees table as a foreign key. This information is taken from the previously mentioned constraint.

Once all the relations for your tables are defined, your work with the database will be straightforward and simple through objects and properties. For example, to get the values for an employee, you can use the method Employees::model()->findByPk() and search by the primary key, or you can get the values from the related tables similarly by extending the method Employees::model()->findByPK(GxActiveRecord::extractPkValue($relatedModel, true)).

With the Gii module you can create models for the rest of your tables, which is an excellent way to start web development with Yii. Just as easy, you can create the necessary controllers and views for your web application. Thus it’s easy to create a powerful web application for a relational MariaDB database with Yii.