How to execute SQL statements using a DB client tool in Yii Framework 2.0

Hi guys, today I want to speak again about Yii Framework 2.0 and in this tutorial I will explain to you how you can execute SQL statements using a DB client tool in Yii Framework 2.0. To create this tutorial, I will follow only the documentation of Yii Framework 2.0.

First of all, you must create a new database, and add this code:

CREATE TABLE `country` (
`code` CHAR(2) NOT NULL PRIMARY KEY,
`name` CHAR(52) NOT NULL,
`population` INT(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

to create a new table.

Now you must insert data by using this code:

INSERT INTO `country` VALUES ('AU','Australia',18886000);
INSERT INTO `country` VALUES ('BR','Brazil',170115000);
INSERT INTO `country` VALUES ('CA','Canada',1147000);
INSERT INTO `country` VALUES ('CN','China',1277558000);
INSERT INTO `country` VALUES ('DE','Germany',82164700);

INSERT INTO `country` VALUES ('FR','France',59225700);
INSERT INTO `country` VALUES ('GB','United Kingdom',59623400);
INSERT INTO `country` VALUES ('IN','India',1013662000);
INSERT INTO `country` VALUES ('RU','Russia',146934000);
INSERT INTO `country` VALUES ('US','United States',278357000);

Open the file config/db.php and change the parameters to be correct for your database.

Create a new model and add this code:

<?php

namespace app\models;

use yii\db\ActiveRecord;

class Country extends ActiveRecord
{
}

Create a new action in your controller:

$query = Country::find()->all();
return $this->render('entry', [ // entry will be a parameter in your URL also it will display results in views/entry.php file. You can rename it.
'countries' => $query,
]);

Create a new php file in your views, called entry.php and add this code:

<?php
use yii\helpers\Html;
use yii\widgets\LinkPager;
?>
<h1>Countries</h1>
<ul>
<?php foreach ($countries as $country): ?>
<li>
<?= Html::encode("{$country->name} ({$country->code})") ?>:
<?= $country->population ?>
</li>
<?php endforeach; ?>
</ul>

Now, go to http://localhost/yii/web/index.php?r=site%2Fentry and you will see the data from your database!

Video tutorial:

In my next tutorial I will explain to you how you can use pagination in Yii 2.0.









Leave a Comment