In this chapter, 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 chapter:
- 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
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
mssqldriver):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',
],
],
// ...
];Now, we need a MySQL database instance to work with. Open the DB administration panel as phpMyAdmin (if provided) or access the DB directly using a console and create a new database named my_database, associated with the username my_username and the password my_password.
In this example, we will create three database tables to manage rooms, customers, and reservations data.
A room will have the following fields:
idas an integerflooras an integerroom_numberas an integerhas_conditioneras an integerhas_tvas an integerhas_phoneas an integeravailable_fromas the dateprice_per_dayas a decimaldescriptionas text
The script of the room table will be:
CREATE TABLE `room` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `floor` int(11) NOT NULL, `room_number` int(11) NOT NULL, `has_conditioner` int(1) NOT NULL, `has_tv` int(1) NOT NULL, `has_phone` int(1) NOT NULL, `available_from` date NOT NULL, `price_per_day` decimal(20,2) DEFAULT NULL, `description` text);
A customer will have the following fields:
idas an integernameas a stringsurnameas a stringphone_numberas a string
The script of the customer table will be
CREATE TABLE `customer` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` varchar(50) NOT NULL, `surname` varchar(50) NOT NULL, `phone_number` varchar(50) DEFAULT NULL );
A reservation will have the following fields:
idas an integerroom_idas an integer that is a reference to a room tablecustomer_idas an integer that is a reference to a customer tableprice_per_dayas a decimaldate_fromas the date to specify check indate_toas the date to specify check outreservation_dateas a timestamp of creationdays_stayas an integer
The script of the reservation table will be:
CREATE TABLE `reservation` ( `id` int(11) NOT NULL AUTO_INCREMENT, `room_id` int(11) NOT NULL, `customer_id` int(11) NOT NULL, `price_per_day` decimal(20,2) NOT NULL, `date_from` date NOT NULL, `date_to` date NOT NULL, `reservation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, );
Finally, place basic/config/web.php in the components property:
$db = new yiidbConnection([
'dsn' => 'mysql:host=localhost;dbname=my_database',
'username' => 'my_username',
'password' => 'my_password',
'charset' => 'utf8',
]);Then we are ready to test the connection to the DB.
Now let's see how to test the DB connection.
Put some rooms data in the database table:
INSERT INTO `my_database`.`room` (`id`, `floor`, `room_number`, `has_conditioner`, `has_tv`, `has_phone`, `available_from`, `price_per_day`, `description`) VALUES (NULL, '1', '101', '1', '0', '1', '2015-05-20', '120', NULL), (NULL, '2', '202', '0', '1', '1', '2015-05-30', '118', NULL);
Database queries are made using the yiidbCommand object, which is created statically by the yiidbConnection::createCommand() method.
The most important methods to retrieve data from a command are:
queryAll(): This method returns all the rows of a query, where each array element is an array that represents a row of data; if the query returns no data, the response is an empty arrayqueryOne(): This method returns the first row of the query, that is, an array, which represents a row of data; if the query returns no data, the response is a false Boolean valuequeryScalar(): This method returns the value of the first column in the first row of the query result; otherwise false will be returned if there is no valuequery(): This is the most common response that returns theyiidbDataReaderobject
Now we will display the room table's content in different ways.
We will update RoomsController in basic/controllers/RoomsController.php. In this file, we will append an index action to fetch data and pass it to view:
<?php
namespace appcontrollers;
use Yii;
use yiiwebController;
class RoomsController extends Controller
{
public function actionIndex()
{
$sql = 'SELECT * FROM room ORDER BY id ASC';
$db = Yii::$app->db;
$rooms = $db->createCommand($sql)->queryAll();
// same of
// $rooms = Yii::$app->db->createCommand($sql)->queryAll();
return $this->render('index', [ 'rooms' => $rooms ]);
}
}The content of actionIndex() is very simple. Define the $sql variable with the SQL statement to be executed, then fill the $rooms array with the query result, and finally render the index view, passing the rooms variable.
In the view content, in basic/views/rooms/index.php, we will display the $rooms array in a table to exploit Bootstrap CSS's advantages, and apply the table class to the table HTML tag.
This is the content of basic/views/rooms/index.php, where we can also see the data formatter used:
<table class="table">
<tr>
<th>Floor</th>
<th>Room number</th>
<th>Has conditioner</th>
<th>Has tv</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($rooms as $item) { ?>
<tr>
<td><?php echo $item['floor'] ?></td>
<td><?php echo $item['room_number'] ?></td>
<td><?php echo Yii::$app->formatter->asBoolean($item['has_conditioner']) ?></td>
<td><?php echo Yii::$app->formatter->asBoolean($item['has_tv']) ?></td>
<td><?php echo ($item['has_phone'] == 1)?'Yes':'No' ?></td>
<td><?php echo Yii::$app->formatter->asDate($item['available_from']) ?></td>
<td><?php echo Yii::$app->formatter->asDate($item['available_from'], 'php:Y-m-d') ?></td>
<td><?php echo Yii::$app->formatter->asCurrency($item['price_per_day'], 'EUR') ?></td>
<td><?php echo $item['description'] ?></td>
</tr>
<?php } ?>
</table>The floor and room_number fields are directly displayed.
The next two fields has_conditioner and has_tv are shown by employing a Boolean formatter supplied by Yii2; the Boolean formatter will use the locale defined during the configuration of Yii2.
The next field has_phone renders its value as the previous two fields; the reason for this is to indicate how to produce the same output of a Boolean formatter in a standard PHP style.
Then, the available_from field is rendered using the date formatter in two different ways, directly and passing the format to be used. Or, if no parameter is passed, it adopts the default format.
Again, the price_per_day field is rendered through the currency formatter, passing the currency as a parameter. If no parameter is passed, the default value will be used. The last field description is displayed directly. Point your browser to http://hostname/basic/web/rooms/index to see the content as follows:
A list of rooms