start-databases.md 10.3 KB
Newer Older
1 2 3
Working with Databases
======================

4
This section will describe how to create a new page that displays country data fetched from
Bazilio committed
5
a database table named `country`. To achieve this goal, you will configure a database connection,
Benoît committed
6
create an [Active Record](db-active-record.md) class, define an [action](structure-controllers.md),
7
and create a [view](structure-views.md).
Qiang Xue committed
8

9
Through this tutorial, you will learn how to:
Qiang Xue committed
10

11 12 13 14
* Configure a DB connection
* Define an Active Record class
* Query data using the Active Record class
* Display data in a view in a paginated fashion
Qiang Xue committed
15

16 17
Note that in order to finish this section, you should have basic knowledge and experience using databases.
In particular, you should know how to create a database, and how to execute SQL statements using a DB client tool.
Qiang Xue committed
18 19


20
Preparing the Database <a name="preparing-database"></a>
Carsten Brandt committed
21
----------------------
Qiang Xue committed
22

23 24
To begin, create a database named `yii2basic`, from which you will fetch data in your application.
You may create an SQLite, MySQL, PostgreSQL, MSSQL or Oracle database, as Yii has built-in support for many database applications. For simplicity, MySQL will be assumed in the following description.
Qiang Xue committed
25

26
Next, create a table named `country` in the database, and insert some sample data. You may run the following SQL statements to do so:
Qiang Xue committed
27 28

```sql
Qiang Xue committed
29
CREATE TABLE `country` (
30 31 32
  `code` CHAR(2) NOT NULL PRIMARY KEY,
  `name` CHAR(52) NOT NULL,
  `population` INT(11) NOT NULL DEFAULT '0'
Qiang Xue committed
33 34
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Bazilio committed
35 36 37 38 39 40 41 42 43 44
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);
Qiang Xue committed
45 46
```

47
At this point, you have a database named `yii2basic`, and within it a `country` table with three columns, containing ten rows of data.
Qiang Xue committed
48

Larry Ullman committed
49
Configuring a DB Connection <a name="configuring-db-connection"></a>
Qiang Xue committed
50 51
---------------------------

52
Before proceeding, make sure you have installed both the [PDO](http://www.php.net/manual/en/book.pdo.php) PHP extension and
53 54 55
the PDO driver for the database you are using (e.g. `pdo_mysql` for MySQL). This is a basic requirement
if your application uses a relational database.

56 57
With those installed, open the file `config/db.php` and change the parameters to be correct for your database. By default,
the file contains the following:
58 59 60 61 62 63 64 65 66 67 68 69 70

```php
<?php

return [
    'class' => 'yii\db\Connection',
    'dsn' => 'mysql:host=localhost;dbname=yii2basic',
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8',
];
```

71
The `config/db.php` file is a typical file-based [configuration](concept-configurations.md) tool. This particular configuration file specifies the parameters
72 73 74
needed to create and initialize a [[yii\db\Connection]] instance through which you can make SQL queries
against the underlying database.

75
The DB connection configured above can be accessed in the application code via the expression `Yii::$app->db`.
76

77
> Info: The `config/db.php` file will be included by the main application configuration `config/web.php`, 
78 79
  which specifies how the [application](structure-applications.md) instance should be initialized.
  For more information, please refer to the [Configurations](concept-configurations.md) section.
Qiang Xue committed
80 81


Qiang Xue committed
82
Creating an Active Record <a name="creating-active-record"></a>
Qiang Xue committed
83
-------------------------
Qiang Xue committed
84

85 86
To represent and fetch the data in the `country` table, create an [Active Record](db-active-record.md)-derived
class named `Country`, and save it in the file `models/Country.php`.
Qiang Xue committed
87

88
```php
89 90
<?php

91 92 93 94 95 96 97 98 99
namespace app\models;

use yii\db\ActiveRecord;

class Country extends ActiveRecord
{
}
```

100 101 102
The `Country` class extends from [[yii\db\ActiveRecord]]. You do not need to write any code inside of it! With just the above code, 
Yii will guess the associated table name from the class name. 

103
> Info: If no direct match can be made from the class name to the table name, you can
104 105
override the [[yii\db\ActiveRecord::tableName()]] method to explicitly specify the associated table name.

106
Using the `Country` class, you can easily manipulate data in the `country` table, as shown in these snippets:
107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124

```php
use app\models\Country;

// get all rows from the country table and order them by "name"
$countries = Country::find()->orderBy('name')->all();

// get the row whose primary key is "US"
$country = Country::findOne('US');

// displays "United States"
echo $country->name;

// modifies the country name to be "U.S.A." and save it to database
$country->name = 'U.S.A.';
$country->save();
```

125 126
> Info: Active Record is a powerful way to access and manipulate database data in an object-oriented fashion.
You may find more detailed information in the [Active Record](db-active-record.md) section. Alternatively, you may also interact with a database using a lower-level data accessing method called [Data Access Objects](db-dao.md).
127 128


Qiang Xue committed
129
Creating an Action <a name="creating-action"></a>
Qiang Xue committed
130 131
------------------

Larry Ullman committed
132 133 134 135
To expose the country data to end users, you need to create a new action. Instead of placing the new action in the `site`
controller, like you did in the previous sections, it makes more sense to create a new controller specifically
for all actions related to the country data. Name this new controller  `CountryController`, and create
an `index` action in it, as shown in the following.
136 137

```php
138 139
<?php

140 141 142 143 144
namespace app\controllers;

use yii\web\Controller;
use yii\data\Pagination;
use app\models\Country;
Qiang Xue committed
145

146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171
class CountryController extends Controller
{
    public function actionIndex()
    {
        $query = Country::find();

        $pagination = new Pagination([
            'defaultPageSize' => 5,
            'totalCount' => $query->count(),
        ]);

        $countries = $query->orderBy('name')
            ->offset($pagination->offset)
            ->limit($pagination->limit)
            ->all();

        return $this->render('index', [
            'countries' => $countries,
            'pagination' => $pagination,
        ]);
    }
}
```

Save the above code in the file `controllers/CountryController.php`.

Larry Ullman committed
172
The `index` action calls `Country::find()`. This Active Record method builds a DB query and retrieves all of the data from the `country` table.
173
To limit the number of countries returned in each request, the query is paginated with the help of a
Larry Ullman committed
174
[[yii\data\Pagination]] object. The `Pagination` object serves two purposes:
175 176

* Sets the `offset` and `limit` clauses for the SQL statement represented by the query so that it only
Larry Ullman committed
177 178
  returns a single page of data at a time (at most 5 rows in a page).
* It's used in the view to display a pager consisting of a list of page buttons, as will be explained in
179 180
  the next subsection.

Larry Ullman committed
181
At the end of the code, the `index` action renders a view named `index`, and passes the country data as well as the pagination
182 183 184
information to it.


Qiang Xue committed
185
Creating a View <a name="creating-view"></a>
Qiang Xue committed
186 187
---------------

188
Under the `views` directory, first create a sub-directory named `country`. This folder will be used to hold all the
189
views rendered by the `country` controller. Within the `views/country` directory, create a file named `index.php`
Larry Ullman committed
190
containing the following:
191 192 193 194 195 196

```php
<?php
use yii\helpers\Html;
use yii\widgets\LinkPager;
?>
197
<h1>Countries</h1>
198 199 200 201 202 203 204 205 206 207 208 209
<ul>
<?php foreach ($countries as $country): ?>
    <li>
        <?= Html::encode("{$country->name} ({$country->code})") ?>:
        <?= $country->population ?>
    </li>
<?php endforeach; ?>
</ul>

<?= LinkPager::widget(['pagination' => $pagination]) ?>
```

Larry Ullman committed
210
The view has two sections relative to displaying the country data. In the first part, the provided country data is traversed and rendered as an unordered HTML list.
211 212 213
In the second part, a [[yii\widgets\LinkPager]] widget is rendered using the pagination information passed from the action.
The `LinkPager` widget displays a list of page buttons. Clicking on any of them will refresh the country data
in the corresponding page.
Qiang Xue committed
214

215

Qiang Xue committed
216 217
Trying it Out <a name="trying-it-out"></a>
-------------
Qiang Xue committed
218

Larry Ullman committed
219
To see how all of the above code works, use your browser to access the following URL:
220 221 222 223

```
http://hostname/index.php?r=country/index
```
Qiang Xue committed
224

225 226
![Country List](images/start-country-list.png)

Larry Ullman committed
227 228 229
At first, you will see a page showing five countries. Below the countries, you will see a pager with four buttons.
If you click on the button "2", you will see the page display another five countries in the database: the second page of records.
Observe more carefully and you will find that the URL in the browser also changes to
230 231 232 233 234

```
http://hostname/index.php?r=country/index&page=2
```

errogaht committed
235
Behind the scenes, [[yii\data\Pagination|Pagination]] is providing all of the necessary functionality to paginate a data set:
236

Larry Ullman committed
237
* Initially, [[yii\data\Pagination|Pagination]] represents the first page, which reflects the country SELECT query
238 239
  with the clause `LIMIT 5 OFFSET 0`. As a result, the first five countries will be fetched and displayed.
* The [[yii\widgets\LinkPager|LinkPager]] widget renders the page buttons using the URLs
Larry Ullman committed
240 241
  created by [[yii\data\Pagination::createUrl()|Pagination]]. The URLs will contain the query parameter `page`, which 
  represents the different page numbers.
242
* If you click the page button "2", a new request for the route `country/index` will be triggered and handled.
Larry Ullman committed
243 244
  [[yii\data\Pagination|Pagination]] reads the `page` query parameter from the URL and sets the current page number to 2.
  The new country query will thus have the clause `LIMIT 5 OFFSET 5` and return  the next five countries
245 246 247
  for display.


Qiang Xue committed
248
Summary <a name="summary"></a>
Qiang Xue committed
249
-------
250

Larry Ullman committed
251
In this section, you learned how to work with a database. You also learned how to fetch and display
252 253 254 255
data in pages with the help of [[yii\data\Pagination]] and [[yii\widgets\LinkPager]].

In the next section, you will learn how to use the powerful code generation tool, called [Gii](tool-gii.md),
to help you rapidly implement some commonly required features, such as the Create-Read-Update-Delete (CRUD)
Larry Ullman committed
256 257
operations for working with the data in a database table. As a matter of fact, the code you have just written can all
be automatically generated in Yii using the Gii tool.