Create a Reservation Website with Yii2 Framework – Full series

In this tutorial, you will learn how to configure and manage databases, using SQL or ActiveRecord directly, then you will see how to solve common tasks, such as saving single and multiple models from a form, and how to create data aggregation and filtered views.

We will cover the following topics in this Series :

  • Configuring a DB connection:
    • For example, creating rooms, customers, and reservations tables
  • For example, testing a connection and executing a SQL query
  • Using Gii to create room, customer, and reservation models
  • Using ActiveRecord to manipulate data:
    • For example, querying rooms list with ActiveRecord
  • Working with relationships:
    • For example, using relationships to connect rooms, reservations, and customers
  • How to save a model from a form:
    • For example, creating and updating a room from a form
  • Setting up the GMT time zone
  • Using multiple database connections:
    • For example, configuring a second DB connection to export data to a local SQLite DB

    Configuring a DB connection

    Yii2 offers a high-level layer to access databases, built on top of PHP Data Objects (PDO).

    This framework allows us to manipulate a database table’s content through the use of ActiveRecord objects. This encapsulates methods to access single or multiple records, as well as filtering, joining, and ordering data in an intuitive way.

    Again, we can work with databases using plain SQL, but this means that we must handle dissimilarities in SQL languages passing through different databases (MySQL, SQL Server, Postgres, Oracle, and so on), which means losing Yii2 facilities.

    A database object connection is an instance of yiidbConnection:

    $db = new yiidbConnection([
        'dsn' => 'mysql:host=localhost;dbname=my_database',
        'username' => 'my_username',
        'password' => 'my_password',
        'charset' => 'utf8',
    ]);

    In this example, we have a connection to a MySQL Server with a mysql connection string to the database my_databases, setting my_username as username and my_password as password. Moreover, we set charset to utf8 in order to guarantee standard charset use. This is a standard database connection entry.

    Other common available connection strings are:

    • MySQL and MariaDB: mysql:host=localhost;dbname=mydatabase
    • SQLite: sqlite:/path/to/database/file
    • PostgreSQL: pgsql:host=localhost;port=5432;dbname=mydatabase
    • MS SQL Server (via mssql driver): mssql:host=localhost;dbname=mydatabase
    • Oracle: oci:dbname=//localhost:1521/mydatabase

    Note

    If we do not provide a direct driver to database and we have to use ODBC, we will have a sample of the ODBC connection object as follows:

    $db = new yiidbConnection([
         'driverName' => 'mysql',
        'dsn' => 'odbc:Driver={MySQL};Server=localhost;Database=my_database',
        'username' => 'my_username',
        'password' => 'my_password',
        'charset' => 'utf8',
    ]);

    For convenience, we will set the database connection as an application component because it will be adopted in many points of the application. In basic/config/web.php:

    return [
        // ...
        'components' => [
            // ...
            'db' => [
                'class' => 'yiidbConnection',
                'dsn' => 'mysql:host=localhost;dbname=my_database',
                'username' => 'my_username',
                'password' => 'my_password',
                'charset' => 'utf8',
            ],
        ],
        // ...
    ];

    Note

    In the basic template, database configuration is in a separate file, generally basic/config/db.php.

    If we open basic/config/web.php, we can see that the db.php file fills the db property of the main configuration.

     

About the author

Deven Rathore

I'm Deven Rathore, a multidisciplinary & self-taught designer with 3 years of experience. I'm passionate about technology, music, coffee, traveling and everything visually stimulating. Constantly learning and experiencing new things.

Add Comment
by Deven Rathore
3 min read

Pin It on Pinterest

Shares

Get the best in web dev

Join dunebook.com and recieve best in web dev , once a week FREE

An email has been Sent to your Inbox ! Please Confirm your Subscription :)