CakePHP database
last modified January 10, 2023
CakePHP database tutorial shows how to program databases in PHP with CakePHP's database package.
$ php -v php -v PHP 8.1.2 (cli) (built: Aug 8 2022 07:28:23) (NTS) ...
We use PHP version 8.1.2.
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 database supports MySQL/MariaDB, PostgresSQL, SQLite, and Microsoft SQL Server.
MariaDB database
In this article we work with MariaDB database
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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
<?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.
In this article we have worked with CakePHP database library.
Author
List all PHP tutorials.