Web development with Yii3 Packages: Part 1 – Database

Welcome to Web development with Yii3 packages. In this series I will show you how you can use Yii packages in a non-Yii application. In this series I will use a pure PHP application but you can use same packages with any other framework: CakePHP, Laravel, Symfony, et al. In this first part, I will show you how to use database packages. Let us jumpstart without further ado.

(i) INSTALL PACKAGES
We will need three packages: migration, database, and cache packages. The migration package will enable us to create and update our database schema, adding tables, columns or dropping them. Database package will be our database abstraction layer. It will help us run queries to our databases with easy. Caching is required by our database component for its internal schema caching which makes things faster. For now you can ignore it.

To install the packages, make sure you have composer installed. You can install composer from Composer website. I will assume you already know how to use composer. If you do not, make sure you read get started from composer documentation. After installing composer, make sure you execute the following commands in your favorite terminal app:

composer require yiisoft/db-migration
composer require yiisoft/cache
composer require yiisoft/db-mysql

We are not explicitily going to install yiisoft/db package. It is a dependency to migration package and as such get installed for us. Next add db extension package for the database of your choice. We will use mariadb, but the package supports SQLite, Postgres, MSSQL and many others. Feel free to use database of your choice.
With all packages installed, we are ready for the next stage.

(ii) SETTING THINGS UP
Ours will be a simple project that lists all students giving you an option to add new one. We will need to create a folder where our source codes will go. While at project root, create new folder named src. We are going to add two files to start with index.php and db.php. The index.php will list all students and the db.php will be where we setup our db connection.
Here is the code for db.php

<?php
declare(strict_types=1);
require_once(__DIR__ . '/../vendor/autoload.php');

use Yiisoft\Cache\ArrayCache;
use Yiisoft\Db\Cache\SchemaCache;
use Yiisoft\Db\Mysql\Connection;
use Yiisoft\Db\Mysql\Driver;
use Yiisoft\Db\Mysql\Dsn;

$dsn = (new Dsn('mysql', '127.0.0.1', 'students', '3306', ['charset' => 'utf8mb4']))->asString();

$arrayCache = new ArrayCache();
$schemaCache = new SchemaCache($arrayCache);

$pdoDriver = new Driver($dsn, 'root', '123456');
$db = new Connection($pdoDriver, $schemaCache);

Everything is pretty self-explanatory. If you find anything difficult, I will be happy to explain further. Now that we have our database setup, we can test if our database connection is working correctly. For that we will use index.php. Here is the code for testing. We basically query the version of Mariadb database. One note is, I assume you know how to create database with a tool of your choice and that your database is named students.

<?php
declare(strict_types=1);

require_once('../vendor/autoload.php');
require_once('db.php');

var_dump($db->createCommand('SELECT VERSION()')->queryOne());

If you do all things correctly you should have a dump of your Mariadb version. Here is mine:
array(1) { ["VERSION()"]=> string(14) "11.1.3-MariaDB" }

(iii)CREATING TABLES WITH Yii MIGRATION
Next step will be creating migrations. Migrations allows you to create new table and add or remove columns and tables. We are going to keep things simple, so we will create single table named students which will hold student’s name, and class he is in. Feel free to expand it as you wish.
To do that we will need copy file from ./vendor/yiisoft/db-migration/bin/yii-db-migration.php to the root of your project (not in the src folder, but rather its parent folder). Then you configure it to use our own db connection. Feel free to read the comments generated by Yii on that file, for simplicity, I have mowed them and the file should look like this:

<?php
declare(strict_types=1);

require_once('../vendor/autoload.php');
require_once('db.php');

return [
    'db' => $db,
    'newMigrationNamespace' => '',

    'sourceNamespaces' => [],

    'newMigrationPath' =>  __DIR__ . '/src/migrations',

    'sourcePaths' => [__DIR__ . '/src/migrations'],

    'historyTable' => '{{%migration}}',

    'migrationNameLimit' => 180,

    'useTablePrefix' => true,

    'container' => null,

    'maxSqlOutputLength' => null,
];

Next we are going to generate our migration files, but before doing that we have to make sure that our folder for storing migrations is existing. If we miss this we will get an error “Invalid path directory”. Create migrations folder inside our src. Now we will generate one migration file that will setup our table. Go ahead and execute this command in the console while at the root of the project:

./vendor/bin/yii-db-migration migrate:create students_table

It will generate our migration file in migrations folder. We need to tell the package to create our student table on migration up and drop it when going downward. Here is the code for the migration file:

<?php
declare(strict_types=1);

use Yiisoft\Db\Migration\MigrationBuilder;
use Yiisoft\Db\Migration\RevertibleMigrationInterface;

/**
 * Class M231224122122StudentsTable
 */
final class M231224122122StudentsTable implements RevertibleMigrationInterface
{
    public function up(MigrationBuilder $b): void
    {
        $b->createTable(
            'students',
            [
                'id' => 'INT PRIMARY KEY AUTO_INCREMENT',
                'name' => 'VARCHAR(200)',
                'classroom' => 'VARCHAR(200)',
            ]
        );
    }

    public function down(MigrationBuilder $b): void
    {
        $b->dropTable('students');
    }
}
?>

Now you can run command to apply migrations and create the table with:
./vendor/bin/yii-db-migration migrate:up
You can also drop the table any moment with command replacing up with down. You can also add as many migrations as you wish. Just make sure migration file contains only related schema changes. Do not bundle bunch of unrelated migration in one file. One more comment before we move on, I have purposely avoided namespaces to make this example easier, but you could easily configure them in the migration utility config file.

(iv) LIST ALL STUDENTS
Now back to our index file. We can easily query all students and display them as a table. For now we will use of database access objects to run queries as well as inserting data. In future, we will make use of active record. Here is our simple SQL command we will be sending to the database:

SELECT * FROM students

We will then query all rows and present them as table. Here is updated index.php:

<?php
declare(strict_types=1);

require_once('../vendor/autoload.php');
require_once('db.php');

$sql = 'SELECT * FROM students';
$rows = $db->createCommand($sql)->queryAll();
?>

<a href="new-student.php">Add Student</a>
<table>
    <tr>
        <td><strong>ID</strong></td>
        <td><strong>Name</strong></td>
        <td><strong>Class</strong></td>
    </tr>

    <?php foreach ($rows as $row) : ?>
        <tr>
            <td><?= $row['id'] ?></td>
            <td><?= $row['name'] ?></td>
            <td><?= $row['classroom'] ?></td>
        </tr>
    <?php endforeach; ?>
</table>
<style>
    table {
        border-collapse: collapse;
        width: 100%;
    }

    th,
    td {
        padding: 8px;
        text-align: left;
        border-bottom: 1px solid #ddd;
    }
</style>

I have added CSS to make a table look better, but it is completely unnecessary (As you might already know);
Now our last part for creating new students. I will skip all explanations on creating form and submitting data. In fact I will skip even explanation on how to insert data as it is as straight forward as a,b,c. Feel free to ask if you find anything confusing.

That being said, we are going to add new file named new-student.php and link it from our index file with simple a tag. Here is a full new-student.php code:

<?php
require_once('db.php');

if (isset($_POST['name']) && isset($_POST['classroom'])) {
    $affected = $db->createCommand()
        ->insert('students', [
            'name' => $_POST['name'],
            'classroom' => $_POST['classroom'],
        ])
        ->execute();

    if ($affected > 0) {
        header('Location: index.php');
        exit(0);
    }
}

?>
<form action="new-student.php" method="post">
    <label for="name">Full name:</label>
    <input type="text" id="name" name="name"><br><br>

    <label for="classroom">Class name:</label>
    <input type="text" id="class" name="classroom"><br><br>

    <input type="submit" value="Add Student">
</form>

(v) CLOSING REMARKS
If you followed the tutorial to the very end, then you must have a working app. And as you can see, it is quiet easy to use Yii3 packages to work with non Yii application. We have barely scratched the surface of what yiisoft/db package provides. Also Yii have a lot of other packages you can use in your PHP application. Conside checking Yiisoft github. Follow Yii Framework handle for releases and other official announcements and Yii Updates for regular Yii updates. If you didn’t know, Yii have quiet a presence in social media space, make sure to visit the page outlining official channels here

Please let me know if you have any comments on our X account @hosannahtech
Until next part of this series,
Merry Christmass and Happy New Year!