Ebooks

CakePHP database tutorial

CakePHP database tutorial shows how to program databases in PHP with CakePHP's database package.

CakePHP database

CakePHP database is a flexible and powerful Database abstraction library with a familiar PDO-like API. The library helps programmers build queries, prevent SQL injections, inspect and alter schemas, and with debug and profile queries sent to the database.

CakePHP databas supports MySQL/MariaDB, PostgresSQL, SQLite, and Microsoft SQL Server.

MariaDB database

In this tutorial, we work with MariaDB database

countries_mariadb.sql
DROP TABLE IF EXISTS countries;
CREATE TABLE countries(id BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255), population INT);

INSERT INTO countries(name, population) VALUES('China', 1382050000);
INSERT INTO countries(name, population) VALUES('India', 1313210000);
INSERT INTO countries(name, population) VALUES('USA', 324666000);
INSERT INTO countries(name, population) VALUES('Indonesia', 260581000);
INSERT INTO countries(name, population) VALUES('Brazil', 207221000);
INSERT INTO countries(name, population) VALUES('Pakistan', 196626000);
INSERT INTO countries(name, population) VALUES('Nigeria', 186988000);
INSERT INTO countries(name, population) VALUES('Bangladesh', 162099000);
INSERT INTO countries(name, population) VALUES('Nigeria', 186988000);
INSERT INTO countries(name, population) VALUES('Russia', 146838000);
INSERT INTO countries(name, population) VALUES('Japan', 126830000);

These SQL commands create a countries table.

Install CakePHP database

$ composer require cakephp/database

CakePHP database is installed with the above command.

CakePHP execute

The execute() executes a query.

version.php
<?php

require __DIR__ . '/vendor/autoload.php';

use Cake\Database\Connection;
use Cake\Database\Driver\Mysql;

$driver = new Mysql([
    'database' => 'mydb',
    'username' => 'root',
    'password' => 's$cret',
]);

$conn = new Connection([
    'driver' => $driver,
]);

$stm = $conn->execute('SELECT VERSION()');
$version = $stm->fetch()[0];

echo $version . "\n";

The example prints the version of the MariaDB database.

$driver = new Mysql([
    'database' => 'mydb',
    'username' => 'root',
    'password' => 's$cret',
]);

A new MySQL driver is created. We provide the database name, username, and password.

$conn = new Connection([
    'driver' => $driver,
]);

A connection object is created.

$ php version.php
10.1.36-MariaDB

This is the output.

CakePHP fetch

The fetch() method returns the next row for the result set after executing the SQL statement. Rows can be fetched to contain columns as names or positions. If no rows are left in result set, it returns false.

fetch_rows.php
<?php

require __DIR__ . '/vendor/autoload.php';

use Cake\Database\Connection;
use Cake\Database\Driver\Mysql;

$driver = new Mysql([
    'database' => 'mydb',
    'username' => 'root',
    'password' => 's$cret',
]);

$conn = new Connection([
    'driver' => $driver,
]);

$stm = $conn->execute('SELECT * FROM countries');

while ($row = $stm->fetch('assoc')) {
    echo "{$row['id']} {$row['name']}  {$row['population']}\n";
}

The example fetches all rows from the countries table.

$stm = $conn->execute('SELECT * FROM countries');

A query is executed with execute().

while ($row = $stm->fetch('assoc')) {
    echo "{$row['id']} {$row['name']}  {$row['population']}\n";
}

We fetch all rows with fetch() in a while loop.

$ php fetch_rows.php
1 China  1382050000
2 India  1313210000
3 USA  324666000
4 Indonesia  260581000
5 Brazil  207221000
6 Pakistan  196626000
7 Nigeria  186988000
8 Bangladesh  162099000
9 Nigeria  186988000
10 Russia  146838000
11 Japan  126830000

This is the output.

CakePHP insert

A new row is added to the database with insert().

insert_row.php
<?php

require __DIR__ . '/vendor/autoload.php';

use Cake\Database\Connection;
use Cake\Database\Driver\Mysql;

$driver = new Mysql([
    'database' => 'mydb',
    'username' => 'root',
    'password' => 's$cret',
]);

$conn = new Connection([
    'driver' => $driver,
]);

$conn->insert('countries', ['name' => 'Ethiopia', 'population' => 102403196]);

The example inserts a new row into the countries table.

Parametrized queries

Parametrized queries guard against SQL injection attacks. The execute() method takes the values to be bound in the second parameter and the types of the values in the third parameter.

parametrized.php
<?php

require __DIR__ . '/vendor/autoload.php';

use Cake\Database\Connection;
use Cake\Database\Driver\Mysql;

$driver = new Mysql([
    'database' => 'mydb',
    'username' => 'root',
    'password' => 's$cret',
]);

$conn = new Connection([
    'driver' => $driver,
]);

$stm = $conn->execute('SELECT * FROM countries WHERE id = :id',
    ['id' => 1], ['id' => 'integer']);

$row = $stm->fetch();

echo "$row[0] $row[1] $row[2]\n";

The example selects a specific row using parametrized query.

$stm = $conn->execute('SELECT * FROM countries WHERE id = :id',
    ['id' => 1], ['id' => 'integer']);

The :id is a placeholder that is bound with the value specified in the second argument. The type of the argument to be bound is given in the third parameter.

$ php parametrized.php
1 China 1382050000

This is the output.

CakePHP Query builder

Query builder provides a convenient, fluent interface for creating and running database queries. It is an abstraction over low-level details of running SQL statements. It shields the programmer from the intricacies of the process.

query_builder.php
<?php

require __DIR__ . '/vendor/autoload.php';

use Cake\Database\Connection;
use Cake\Database\Driver\Mysql;

$driver = new Mysql([
    'database' => 'mydb',
    'username' => 'root',
    'password' => 's$cret',
]);

$conn = new Connection([
    'driver' => $driver,
]);

$query = $conn->newQuery();
$query->select(['id', 'name']);
$query->from('countries');
$query->where(['id >' => 4])->andWhere(['id <' => 10]);

$rows = $query->execute();

foreach ($rows as $row) {
    echo "{$row[0]} {$row[1]}\n";
}

The example retrieves rows from the countries table with Ids greather than 4 and lower than 10.

$query = $conn->newQuery();

A query builder is created with newQuery().

$query->select(['id', 'name']);
$query->from('countries');
$query->where(['id >' => 4])->andWhere(['id <' => 10]);

The query is built with fluent method calls.

$ php query_builder.php
5 Brazil
6 Pakistan
7 Nigeria
8 Bangladesh
9 Nigeria

This is the output.

Counting rows

A number of commonly used functions can be created with the func() method.

count_rows.php
<?php

require __DIR__ . '/vendor/autoload.php';

use Cake\Database\Connection;
use Cake\Database\Driver\Mysql;

$driver = new Mysql([
    'database' => 'mydb',
    'username' => 'root',
    'password' => 's$cret',
]);

$conn = new Connection([
    'driver' => $driver,
]);

$query = $conn->newQuery();
$query->select(['count'  => $query->func()->count('*')]);
$query->from('countries');

$stm = $query->execute();
$n = $stm->fetch()[0];

echo "There are {$n} countries in the table\n";

The example counts the number of rows in the table.

$ php count_rows.php
There are 12 countries in the table

This is the output.

CakePHP in expression

The in() expression allows to create IN SQL clause.

in_expr.php
<?php

require __DIR__ . '/vendor/autoload.php';

use Cake\Database\Connection;
use Cake\Database\Driver\Mysql;

$driver = new Mysql([
    'database' => 'mydb',
    'username' => 'root',
    'password' => 's$cret',
]);

$conn = new Connection([
    'driver' => $driver,
]);

$query = $conn->newQuery();
$query->select(['id', 'name', 'population']);
$query->from('countries');

$query->where(function ($exp) {
    return $exp
        ->in('id', [2, 4, 6, 8, 10]);
});

$rows = $query->execute();

foreach ($rows as $row) {
    echo "{$row[0]} {$row[1]} {$row[2]}\n";
}

The example selects rows from the given array of Ids utilizing in() expression.

$ php in_expr.php
2 India 1313210000
4 Indonesia 260581000
6 Pakistan 196626000
8 Bangladesh 162099000
10 Russia 146838000

This is the output.

You might also be interested in the following related tutorials: PHP PDO tutorial, Introduction to Symfony, PHP tutorial, or list all PHP tutorials.

In this tutorial, we have worked with Doctrine QueryBuilder and PostgreSQL database.