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