connection to export data to a local SQLite DB

We now want to add a new database connection to a SQLite DB. When we use a database, we have to make sure that the PDO driver is installed in the system, otherwise PHP cannot handle it.

Open basic/config/web.php and the inner ^components attribute, and append a new attribute named dbSqlite with the following attributes:

'dbSqlite' => [

'class' => 'yii\db\Connection',

'dsn' => 'sqlite:'.dirname(__DIR__).'/../db.sqlite', ],

This entry will use a DB SQLite named ^db.sqlite, which we can fi nd in the dirname(__DIR__).'/../web/db.sqlite' path, under the /basic/web folder.

If this fi le does not exist, it will be created (if a write permission is present in the / basic/web folder).

Be sure that the /basic/web folder is writable, otherwise it will be impossible for the framework to create a db.sqlite fi le.

Create a new controller to handle actions in this new database. This will be put in ^/

Insert the fi rst action named actionCreateRoomTable in this new controller, which will create the same structure of the ^Room table from MySQL in ^dbSqlite:

<?php

namespace app\controllers;

use Yii;

use yii\web\Controller;

class TestSqliteController extends Controller {

public function actionCreateRoomTable() {

// Create room table

$sql = 'CREATE TABLE IF NOT EXISTS room (id int not null, floor int not null, room_number int not null, has_conditioner int not null, has_tv int not null, has_phone int not null, available_from date not null, price_per_day float, description text)';

\Yii::$app->dbSqlite->createCommand($sql)->execute();

echo 'Room table created in dbSqlite';

} }

Pay attention so that in actionCreateRoomTable, the database instance is taken from: \Yii::$app->dbSqlite.

Point your browser to http://hostname/basic/web/test-sqlite/create-room-table and create a db.sqlite fi le in basic/web and a room table in it.

As we have mentioned before, if the PDO driver is correctly installed, a blank page with the Room table created in dbSqlite text will be displayed.

Now we want to clone the room table from MySQL to SQLite to make a backup of this table. We need to save the records from MySQL to SQLite and verify the data stored to display it in a table.

Create a new action named actionBackupRoomTable() that executes these steps:

1. Create a room table (if it does not exist).

2. Delete all the records from the room in ^dbSqlite (alias truncate).

3. Load all the records from the room table in MySQL (using ActiveRecord).

4. Insert every single record from MySQL into SQLite.

5. Render the view to display data from SQLite with the table (to verify that the copy succeeded).

The content of the actionBackupRoomTable() action is:

use app\models\Room;

public function actionBackupRoomTable() {

// Create room table

$sql = 'CREATE TABLE IF NOT EXISTS room (id int not null, floor int not null, room_number int not null,

has_conditioner int not null, has_tv int not null, has_phone int not null, available_from date not null, price_per_day float, description text)';

\Yii::$app->dbSqlite->createCommand($sql)->execute();

// Truncate room table in dbSqlite $sql = 'DELETE FROM room';

\Yii::$app->dbSqlite->createCommand($sql)->execute();

// Load all records from MySQL and insert every single record in dbqlite

$models = Room::find()->all();

foreach($models as $m) {

\Yii::$app->dbSqlite->createCommand()->insert('room', $m->attributes)->execute();

}

// Load all records from dbSqlite $sql = 'SELECT * FROM room';

$sqliteModels = >queryAll();

return $this->render('backupRoomTable', ['sqliteModels' =>

$sqliteModels]);

}

Finally, create a view backupRoomTable in basic/views/test-sqlite/

backupRoomTable.php with the following content to display data from ^dbSqlite:

<h2>Rooms from dbSqlite</h2>

<tr>

<th>Floor</th>

<th>Room number</th>

<th>Has conditioner</th>

<th>Has phone</th>

<th>Available from</th>

<th>Available from (db format)</th>

<th>Price per day</th>

<th>Description</th>

</tr>

<?php foreach($sqliteModels as $item) { ?>

<tr>

<td><?php echo

>asBoolean($item['has_conditioner']) ?></td>

<td><?php echo >asBoolean($item['has_tv']) ?></td>

<td><?php echo >asDate($item['available_from']) ?></td>

<td><?php echo

>asDate($item['available_from'], 'php:Y-m-d') ?></td>

<td><?php echo

>asCurrency($item['price_per_day'], 'EUR') ?></td>

</tr>

<?php } ?>

</table>

Navigate your browser to http://hostname/basic/web/test-sqlite/backup-room-table, which should display a similar output to this:

The list of rooms from the SQLite database

We can now download the db.sqlite fi le from http://hostname/basic/web/

db.sqlite to preserve a backup copy of the room table!

Summary

In this chapter, you mastered how to confi gure a database connection and execute SQL queries from scratch with DAO support of the framework. Next, you found out how to use Gii and got to know about the advantages it has in creating models from the database table structure. Gii creates models that extend the ActiveRecord class and through its use, you fi nally learned to manipulate data. All the examples are accompanied with a visualization grid that shows data, which is graphically enhanced by Bootstrap's presence in Yii2.

We carefully analyzed the common topic of tables' relationships, which must be managed in models and then displayed in views.

At the end of the chapter, after you learned to manipulate data with ActiveRecord, you wrote a complete fl ow to save data from a HTML form to a database. Finally, you learned the importance of setting the GMT time zone in date/time fi elds and using other database sources in the same application in order to make a backup of the primary database.

In the next chapter, you will learn to use and customize the grid widget to improve data visualization.

In document Yii2 By Example - Sample Chapter (Page 54-59)