Connecting to the database

Blog articles are stored in a database. For CakePHP to work with it, the framework needs the address of the database server, the database name, a user and a password. Together these settings are called a connection.

Creating the database

The database itself is created in advance with MySQL tools, for example in phpMyAdmin or with a query:

CREATE DATABASE blog CHARACTER SET utf8mb4;

An empty blog database appears. Its tables are created in the next lesson.

Where the settings are

Connections are described in the Datasources section. The common part, such as the database type and encoding, is in config/app.php, while the address and password go into config/app_local.php because they differ on every computer.

Parameters

Open config/app_local.php and fill in the default connection:

// config/app_local.php
'Datasources' => [
    'default' => [
        'host' => 'localhost',
        'username' => 'root',
        'password' => 'secret',
        'database' => 'blog',
    ],
],

default is the connection used by all tables of the application. Next to it the file has a test connection, a separate database for tests.

Checking

Run bin/cake server and open http://localhost:8765. In the Database section of the welcome page the line turns green: CakePHP was able to connect. If it is red, the error text is shown next to it, usually a wrong password or database name.

Another database

The database type is set by the driver key in config/app.php:

SQLite needs no server; the database is stored in a single file:

// config/app_local.php
'Datasources' => [
    'default' => [
        'driver' => \Cake\Database\Driver\Sqlite::class,
        'database' => ROOT . DS . 'tmp' . DS . 'blog.sqlite',
    ],
],

The ROOT constant is the path to the project root and DS is the folder separator.

A direct query

Usually the database is accessed through table classes, but the connection can also be used directly:

use Cake\Datasource\ConnectionManager;
 
$connection = ConnectionManager::get('default');
 
$rows = $connection->execute('SELECT 1 AS answer')->fetchAll('assoc');

$rows holds an array with one row: ['answer' => 1]. Such code goes into a controller action and the result is printed with debug($rows).

Common mistake

The password was changed in config/app.php and there is still no connection. The reason is that app_local.php is loaded later and replaces the values from app.php. Change connection parameters in config/app_local.php.