Content from a database
Create pages from a database and edit, add or delete entries in the Panel
Content can also come from a database. This is particularly useful if you already have a large set of data stored in a MySQL or other relational database, like a huge database of products or articles. In only a few steps you can transform data from those databases into Kirby pages that behave like any Kirby page in the frontend, i.e. you can use Kirby's API to access, filter or search content, and edit it in the Panel together with all your file based content.
This guide is a short intro into how to create pages from entries in a database table. It is intended as an outline, not a final solution. With lots of entries it would also become necessary to work on a custom collection for the result set that would only fetch the rows it really needs, but this would be more complex.
The example assumes a database table called comments with the following scheme:
Column | Datatype | Primary Key | Unique |
---|---|---|---|
id |
int | yes | yes |
text |
VARCHAR (600) | no | no |
user |
VARCHAR (100) | no | no |
slug |
VARCHAR (185) | no | yes |
Create a new parent page
Create a parent page called comments
in the content folder.
content
comments
- comments.txt
Connect to the database
In your config.php
, connect to the database:
<?php
return [
'db' => [
'host' => '127.0.0',
'database' => 'kirby',
'user' => 'root',
'password' => 'root',
]
];
Here we connect to a local database, your real database should - of course - use a proper database user and secure password.
Query the database
Assuming that your database contains a table called comments, you can now query the database table in your templates like this:
$comments = Db::select('comments');
Convert to child pages
We can now convert these database entries to children of the comments parent. To do so, let's create a page model for the parent page:
<?php
use Kirby\Uuid\Uuid;
class CommentsPage extends Kirby\Cms\Page
{
public function children(): Pages
{
if ($this->children instanceof Pages) {
return $this->children;
}
$comments = [];
foreach (Db::select('comments') as $comment) {
$comments[] = [
'slug' => $comment->slug(),
'num' => 0,
'template' => 'comment',
'model' => 'comment',
'content' => [
'text' => $comment->text(),
'user' => $comment->user(),
'uuid' => Uuid::generate(),
]
];
}
return $this->children = Pages::factory($comments, $this);
}
}
Here we re-define the children()
method of the Page
class to return the comments from the database as children pages.
Unless you have disabled UUIDs in your config, you have to pass a uuid
field in the content array to prevent Kirby from generating the page in the file system when the $page->uuid()
method is called.
If you generate the UUIDs automatically like in the example above, they will change at every load. However, if you want to reference your virtual pages anywhere with their UUID, make sure to use a unique string that does not change.
Accessing the comments in the template
With this page model in place, we can now access our comments as children of the comments
page in our template:
<ul class="comments">
<?php foreach ($page->children() as $comment): ?>
<li>
<a href="<?= $comment->url() ?>">
<?= $comment->text() ?> <small>by <?= $comment->user() ?></small>
</a>
</li>
<?php endforeach ?>
</ul>
Create the Panel blueprint
We can now access these database-based pages in the Panel. To do this properly, we should create blueprints for the parent and its children first:
The parent's blueprint
title: Comments
sections:
comments:
label: Comments
type: pages
info: "{{ page.user }}"
template: comment
The children's blueprint
title: Comment
icon: 📢
options:
changeTitle: false
changeStatus: false
changeSlug: false
fields:
user:
label: User
type: text
text:
label: text
type: textarea
maxlength: 600
Create a model for the comment child page
While these comments are now automatically available in the Panel, we cannot yet edit them. To make sure that they can be edited and deleted, we have to create a CommentPage
model and modify the code of the parent model:
The CommentPage
model would need to overwrite the writeContent()
and readContent()
methods to get the comment details from the table instead of the text file.
<?php
class CommentPage extends Kirby\Cms\Page
{
public function writeContent(array $data, string|null $languageCode = null): bool
{
unset($data['title']);
if ($comment = Db::first('comments', '*', ['slug' => $this->slug()])) {
return Db::update('comments', $data, ['slug' => $this->slug()]);
}
$data['slug'] = $this->slug();
return Db::insert('comments', $data);
}
public function delete(bool $force = false): bool
{
return Db::delete('comments', ['slug' => $this->slug()]);
}
public function title(): Kirby\Cms\Field
{
return $this->text()->excerpt(100)->or('New comment');
}
}
A template for the individual comment
If we want to access each comment on its own page, we can add an additional template for the comment:
<?php snippet('header') ?>
<div class="comment">
<?= $page->text()->kt() ?>
<p><small>by <?= $page->user() ?></small></p>
</div>
<?php snippet('footer') ?>
Advanced setup
If we want to enable features like changing the slug via the Panel or use the status workflow with drafts, unlisted and listed entries, we have to extend our models.
But first, our table needs a new column called status
, which can have the values null
, unlisted
and listed
. We also add a title for our comments, so that we end up with the following table scheme:
Column | Datatype | Primary Key | Unique |
---|---|---|---|
id |
int | yes | yes |
text |
VARCHAR (600) | no | no |
user |
VARCHAR (100) | no | no |
slug |
VARCHAR (185) | no | yes |
title |
VARCHAR (185) | no | no |
status |
VARCHAR (8) | no | no |
Extended comment.yml
blueprint
We can show the status field in the Panel as a disabled field for control purposes, but it is not required. The options are only spelled out for clarity, true
is the default value anyway.
title: Comment
icon: 📢
options:
changeTitle: true
changeStatus: true
changeSlug: true
fields:
user:
label: User
type: text
text:
label: text
type: textarea
maxlength: 600
# just for control purposes
status:
label: Status
type: text
disabled: true
Extended comments.php
model
<?php
use Kirby\Uuid\Uuid;
class CommentsPage extends Kirby\Cms\Page
{
public function children(): Pages
{
if ($this->children instanceof Pages) {
return $this->children;
}
$comments = [];
foreach (Db::select('comments') as $comment) {
$comments[] = [
'slug' => $comment->slug(),
'num' => $comment->status() === 'listed' ? 0 : null,
'template' => 'comment',
'model' => 'comment',
'content' => [
'title' => $comment->title() ?? 'New Comment',
'text' => $comment->text(),
'user' => $comment->user(),
'status' => is_null($comment->status()) ? 'draft' : $comment->status(),
'uuid' => Uuid::generate(),
]
];
}
return $this->children = Pages::factory($comments, $this);
}
}
There are three differences here compared to the model above:
- We set the
num
property depending on the value of thestatus
field in the database. - We add the
status
field to thecontent
array. - We add the
title
field to thecontent
array.
The extended comment.php
model
The model for a single comment needs more significant changes:
<?php
class CommentPage extends Kirby\Cms\Page
{
public function changeSlug(string $slug, string|null $languageCode = null): static
{
// always sanitize the slug
$slug = Str::slug($slug);
$data['slug'] = $slug;
if ($comment = Db::first('comments', '*', ['slug' => $this->slug()])) {
Db::update('comments', $data, ['slug' => $this->slug()]);
}
return $this;
}
protected function changeStatusToDraft(): static
{
$data['status'] = 'null';
if ($comment = Db::first('comments', '*', ['slug' => $this->slug()])) {
Db::update('comments', $data, ['slug' => $this->slug()]);
}
return $this;
}
protected function changeStatusToListed(int|null $position = null): static
{
// create a sorting number for the page
$num = $this->createNum($position);
// don't sort if not necessary
if ($this->status() === 'listed' && $num === $this->num()) {
return $this;
}
$data['status'] = 'listed';
if ($comment = Db::first('comments', '*', ['slug' => $this->slug()])) {
Db::update('comments', $data, ['slug' => $this->slug()]);
}
if ($this->blueprint()->num() === 'default') {
$this->resortSiblingsAfterListing($num);
}
return $this;
}
protected function changeStatusToUnlisted(): static
{
if ($this->status() === 'unlisted') {
return $this;
}
$data['status'] = 'unlisted';
if ($comment = Db::first('comments', '*', ['slug' => $this->slug()])) {
Db::update('comments', $data, ['slug' => $this->slug()]);
}
$this->resortSiblingsAfterUnlisting();
return $this;
}
public function changeTitle(string $title, string|null $languageCode = null): static
{
$data['title'] = $title;
if ($comment = Db::first('comments', '*', ['slug' => $this->slug()])) {
Db::update('comments', $data, ['slug' => $this->slug()]);
}
return $this;
}
public function delete(bool $force = false): bool
{
return Db::delete('comments', ['slug' => $this->slug()]);
}
public function isDraft(): bool
{
return in_array($this->content()->status(), ['listed', 'unlisted']) === false;
}
public function writeContent(array $data, string|null $languageCode = null): bool
{
unset($data['title']);
if ($comment = Db::first('comments', '*', ['slug' => $this->slug()])) {
return Db::update('comments', $data, ['slug' => $this->slug()]);
} else {
$data['slug'] = $this->slug();
return Db::insert('comments', $data);
}
}
}
We have to override all methods that are needed for changing the status and the slug, because we have to change the values for each action in the database, not in the local file system.
changeSlug()
changeStatusToDraft()
changeStatusToListed()
changeStatusToUnlisted()
changeTitle()
Since we now have an explicit title field, we add the changeTitle()
method and remove the title()
method from the model, because that is no longer necessary.
When overriding Kirby's default methods in your models, your methods must use the same function syntax, i.e. parameters, type hints, return types, and return type declarations must be identical.