Skip to content

Kirby 3.9.8

Database

As Kirby is a file-based CMS with content stored in text files, you don't need a database. However, if you want to integrate data stored in a database into your site or if you want to export data from a database into your Kirby website, we got you covered.

Kirby comes with its own Db class that allows you to connect to a MySQL or SQLite database. The class also offers some handy shortcuts for querying tables.

This guide only describes the basics using the shortcuts of the Db class. If you want to dive deeper, check out the database reference.

Database connection

You can set your database connection in your config.php.

MySQL

/site/config/config.php
<?php

return [
  'db' => [
    'host'     => '127.0.0.1',
    'database' => 'kirby',
    'user'     => 'root',
    'password' => 'root',
  ]
];

Here we connect to a local MySql database, your real database should use a proper database user and secure password.

SQLite

/site/config/config.php
<?php

return [
  'db' => [
    'type'     => 'sqlite',
    'database' => '/var/data/mydb.sqlite' #full path to file
  ]
];

With these settings in place, you can now query the database via some useful shortcuts provided in the Db class.

Select a table

Assuming your database contains a table called users, you can now start querying the database table in your code using the select method. This automatically fetches all rows from the table.

Parameters

Name Type Description
$table string The name of the table, which should be queried
$columns string|array Either a string with columns or an array of column names
$where mixed The where clause. Can be a string or an array
$order string The columns and direction to sort by
$offset int The index to start from
$limit int The number of elements to return

Example

$users = Db::select('users');

foreach ($users as $user) {
  echo $user->username();
}

Select columns

$users = Db::select('users', ['id', 'username']);

foreach ($users as $user) {
  echo $user->id() . ': ' . $user->username();
}

Order rows

$users = Db::select('users', '*', null, 'username DESC' );

foreach ($users as $user) {
  echo $user->id() . ': ' . $user->username();
}

Offsets and limits

$users = Db::select('users', '*', null, 'username DESC', 2, 2 );

foreach ($users as $user) {
  echo $user->username();
}

Where clause

$users = Db::select('users', '*', 'username like "%m%"');

foreach ($users as $user) {
  echo $user->username();
}

Fetch first row

The first shortcut works similar to the select method above, but only selects the first row.

Parameters

Name Type Description
$table string The name of the table, which should be queried
$columns string|array Either a string with columns or an array of column names
$where mixed The where clause. Can be a string or an array
$order string The columns and direction to sort by
$offset int The index to start from
$limit int The number of elements to return

Example

$user = Db::first('users');

echo $user->username();

Values from single column

The column shortcut returns values from a single column.

Parameters

Name Type Description
$table string The name of the table, which should be queried
$column string A string with the column name
$where mixed The where clause. Can be a string or an array
$order string The columns and direction to sort by
$offset int The index to start from
$limit int The number of elements to return

Example

$ids = Db::column('users', 'id');
var_dump($users);
foreach ($ids as $id) {
  echo $id;
}

Insert a row

Parameters

Name Type Description
$table string The name of the table, which should be queried
$values string An array of values

Example

$id = Db::insert('users', [
  'username' => 'moe',
  'email'    => 'moe@szyslak.com'
]);
dump($id);

If successful, the method returns the last id, otherwise false.

Update rows

Parameters

Name Type Description
$table string The name of the table, which should be queried
$values string An array of values
$where mixed An optional where clause. Can be a string or an array

Example

$bool = Db::update('users', ['username' => 'zoe'], ['username' => 'moe']);
dump($bool);

Delete rows

Parameters

Name Type Description
$table string The name of the table, which should be queried
$where mixed An optional where clause. Can be a string or an array

Example

$bool = Db::delete('users', ['username' => 'zoe']);
dump($bool);

Count rows

Parameters

Name Type Description
$table string The name of the table, which should be queried
$where mixed An optional where clause. Can be a string or an array

Example

$count = Db::count('users', 'id > 3');
dump($count);

$count = Db::count('users', 'username LIKE "%m%"');
dump($count);

Minimum value

Parameters

Name Type Description
$table string The name of the table, which should be queried
$columns string The name of the column of which the minimum should be calculated
$where mixed An optional where clause. Can be a string or an array

Example

$min = Db::min('users', 'id');
dump($min);

Maximum value

Parameters

Name Type Description
$table string The name of the table, which should be queried
$columns string The name of the column of which the maximum should be calculated
$where mixed An optional where clause. Can be a string or an array

Example

$max = Db::max('users', 'id');
dump($max);

Average value

Parameters

Name Type Description
$table string The name of the table, which should be queried
$columns string The name of the column of which the average should be calculated
$where mixed An optional where clause. Can be a string or an array

Example

$avg = Db::avg('users', 'id');
dump($avg);

(Admittedly, the last example is a bit silly, but we don't really have a useful column in this table to calculate the average).