Using databases

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