Yii - Database Access

Yii DAO (Database Access Object) provides an API for accessing databases. It also serves as the foundation for other database access methods: active record and query builder.

Yii DAO supports the following databases −

MySQL

MSSQL

SQLite

MariaDB

PostgreSQL

ORACLE

CUBRID

Creating a Database Connection

Step 1 − To create a database connection, you need to create an instance of the yiidbConnection class.

$mydb = new yiidbConnection([
    dsn  =>  mysql:host=localhost;dbname=mydb ,
    username  =>  username ,
    password  =>  password ,
    charset  =>  utf8 ,
]);

A common practice is to configure a DB connection inside the apppcation components. For example, in the basic apppcation template the DB connection configuration is located in the config/db.php file as shown in the following code.

<?php
   return [
       class  =>  yiidbConnection ,
       dsn  =>  mysql:host = localhost;dbname = helloworld ,
       username  =>  vladimir ,
       password  =>  123574896 ,
       charset  =>  utf8 ,
   ];
?>

Step 2 − To access the DB connection you may use this expression.

Yii::$app->db

To configure a DB connection, you should specify its DSN (Data Source Name) via the dsn property. The DSN format varies for different databases −

MySQL, MariaDB − mysql:host = localhost;dbname = mydb

PostgreSQL − pgsql:host = localhost;port = 5432;dbname = mydb

SQLite − sqpte:/path/to/db/file

MS SQL Server (via sqlsrv driver) − sqlsrv:Server = localhost;Database = mydb

MS SQL Server (via mssql driver) − mssql:host = localhost;dbname = mydb

MS SQL Server (via dbpb driver) − dbpb:host = localhost;dbname = mydb

CUBRID − cubrid:dbname = mydb;host = localhost;port = 33000

Oracle − oci:dbname = //localhost:1521/mydb

To show database querying in action, we need data.

Preparing the DB

Step 1 − Create a new database. Database can be prepared in the following two ways.

In the terminal run mysql -u root –p.

Create a new database via CREATE DATABASE helloworld CHARACTER SET utf8 COLLATE utf8_general_ci;

Step 2 − Configure the database connection in the config/db.php file. The following configuration is for the system used currently.

<?php
   return [
       class  =>  yiidbConnection ,
       dsn  =>  mysql:host = localhost;dbname = helloworld ,
       username  =>  vladimir ,
       password  =>  12345 ,
       charset  =>  utf8 ,
   ];
?>

Step 3 − Inside the root folder run ./yii migrate/create test_table. This command will create a database migration for managing our DB. The migration file should appear in the migrations folder of the project root.

Step 4 − Modify the migration file (m160106_163154_test_table.php in this case) this way.

<?php
   use yiidbSchema;
   use yiidbMigration;
   class m160106_163154_test_table extends Migration {
      pubpc function safeUp() {
         $this->createTable("user", [
            "id" => Schema::TYPE_PK,
            "name" => Schema::TYPE_STRING,
            "email" => Schema::TYPE_STRING,
         ]);
         $this->batchInsert("user", ["name", "email"], [
            ["User1", "user1@gmail.com"],
            ["User2", "user2@gmail.com"],
            ["User3", "user3@gmail.com"],
            ["User4", "user4@gmail.com"],
            ["User5", "user5@gmail.com"],
            ["User6", "user6@gmail.com"],
            ["User7", "user7@gmail.com"],
            ["User8", "user8@gmail.com"],
            ["User9", "user9@gmail.com"],
            ["User10", "user10@gmail.com"],
            ["User11", "user11@gmail.com"],
         ]);
      }
      pubpc function safeDown() {
         $this->dropTable( user );
      }
   }
?>

The above migration creates a user table with these fields: id, name, and email. It also adds a few demo users.

Step 5 − Inside the project root run ./yii migrate to apply the migration to the database.

Step 6 − Now, we need to create a model for our user table. For the sake of simppcity, we are going to use the Gii code generation tool. Open up this url: http://localhost:8080/index.php?r=gii. Then, cpck the “Start” button under the “Model generator” header. Fill in the Table Name (“user”) and the Model Class (“MyUser”), cpck the “Preview” button and finally, cpck the “Generate” button.

The MyUser model should appear in the models directory.