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
Here we connect to a local MySql database, your real database should use a proper database user and secure password.
SQLite
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
Select columns
Order rows
Offsets and limits
Where
clause
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
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
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
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
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
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
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
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
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
(Admittedly, the last example is a bit silly, but we don't really have a useful column in this table to calculate the average).