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.

Database connection

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

MySQL

<?php

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

SQLite

<?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

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

Example

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

echo $user->username();

Values from single column

The column shortcut returns values from a single column.

Parameters

Example

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

Insert a row

Parameters

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

Example

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

Delete rows

Parameters

Example

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

Count rows

Parameters

Example

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

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

Minimum value

Parameters

Example

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

Maximum value

Parameters

Example

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

Average value

Parameters

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).