Skip to content

Creating pages from a database

Edit database-based pages in the Panel and access them in templates.

This cookbook recipe 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 entriesm 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 a lot more complex.

The example assumes a database table called comments with the following columns:

  • id
  • text
  • user
  • slug

Create a new parent page

Create a parent page called comments in the content folder.

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',
    ]
];

Query the database

Assuming that your database contains a table called comments, you can now query the database table in your templates like this:

/site/templates/comments.php
$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:

/site/models/comments.php
<?php

class CommentsPage extends Kirby\Cms\Page
{
    public function 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(),
                ]
            ];
        }

        return Pages::factory($comments, $this);
    }
}

Here we re-define the children() method of the Page class to return the commments from the database as children pages.

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:

/site/templates/comments.php
<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

/site/blueprints/pages/comments.yml
title: Comments

sections:
  comments:
    headline: Comments
    type: pages
    info: "{{ page.user }}"
    template: comment

The children's blueprint

/site/blueprints/pages/comment.yml
title: Comment
icon: 📢

options:
  title: false
  status: false
  url: false

fields:
  user:
    label: User
    type: text
  text:
    label: text
    type: textarea

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.

/site/models/comment.php
<?php

class CommentPage extends Kirby\Cms\Page
{

    public function writeContent(array $data, string $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);
        }

    }

    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:

/site/templates/comment.php
<?php snippet('header') ?>
<div class="comment">
  <?= $page->text()->kt() ?>
  <p><small>by <?= $page->user() ?></small></p>
</div>
<?php snippet('footer') ?>