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.
Info:
This guide only describes the basics using the shortcuts of the Db
class. If you want to dive deeper, check out the database reference .
You can set your database connection in your config.php
.
/site/config/config.php
<?php
return [
'db' => [
'host' => '127.0.0.1',
'database' => 'kirby',
'user' => 'root',
'password' => 'root',
]
];
Info:
Here we connect to a local MySql database, your real database should use a proper database user and secure password.
/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.
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.
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
$users = Db::select('users');
foreach ($users as $user) {
echo $user->username();
}
$users = Db::select('users', ['id', 'username']);
foreach ($users as $user) {
echo $user->id() . ': ' . $user->username();
}
$users = Db::select('users', '*', null, 'username DESC' );
foreach ($users as $user) {
echo $user->id() . ': ' . $user->username();
}
$users = Db::select('users', '*', null, 'username DESC', 2, 2 );
foreach ($users as $user) {
echo $user->username();
}
$users = Db::select('users', '*', 'username like "%m%"');
foreach ($users as $user) {
echo $user->username();
}
The first
shortcut works similar to the select
method above, but only selects the first row.
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
$user = Db::first('users');
echo $user->username();
The column
shortcut returns values from a single column.
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
$ids = Db::column('users', 'id');
var_dump($users);
foreach ($ids as $id) {
echo $id;
}
Name
Type
Description
$table
string
The name of the table, which should be queried
$values
string
An array of values
$id = Db::insert('users', [
'username' => 'moe',
'email' => 'moe@szyslak.com'
]);
dump($id);
If successful, the method returns the last id
, otherwise false
.
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
$bool = Db::update('users', ['username' => 'zoe'], ['username' => 'moe']);
dump($bool);
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
$bool = Db::delete('users', ['username' => 'zoe']);
dump($bool);
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
$count = Db::count('users', 'id > 3');
dump($count);
$count = Db::count('users', 'username LIKE "%m%"');
dump($count);
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
$min = Db::min('users', 'id');
dump($min);
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
$max = Db::max('users', 'id');
dump($max);
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
$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).