Content from a database

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 database of products or articles. You can transform data from such 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 the content.

This guide covers the basic steps, such as establishing a connection to the database, accessing one or more tables, and converting database entries into children of a parent page in read-only mode. In this setup, the pages are generated dynamically from the database and cannot be modified through the Panel or written to the file system.

To add advanced functionality (editing, adding, deleting pages, etc.), please refer to our our database storage plugin (currently in alpha).

Our example assumes a database table called comments with the following schema:

You can, of course, work with an existing table if you adapt this guide accordingly.

Create a new parent page

Now create a new parent page called comments in the content folder and add a comments.txt file inside it. The file system should look like this:

This file will connect the page with the `comments.php template and – more importantly – a new CommentsPage model we will create in the next steps.

Create a template and page model

Create the following files:

• /site/templates/comments.php
• /site/models/comments.php.

Connect to the database

In your config.php, connect to the database:

<?php

return [
    'db' => [
        'host'     => '127.0.0.1',
        'database' => 'kirby',
        'user'     => 'root',
        'password' => 'root',
    ]
];

Query the database

Let's check in the template that the connection to the database and the table works :

<?php

$comments = Db::select('comments');

foreach ($comments as $comment) {
    dump($comment);
};

Provided that the table contains any data, this should output several Kirby\Toolkit\Obj objects (one for each database row).

The page model

Now it is time to create a model that converts the data from the table into virtual pages:

<?php

use Kirby\Cms\Page;

class CommentsPage extends 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'  => [
                    'title' => $comment->title(),
                    'text'  => $comment->text(),
                    'email' => $comment->email(),
                    'uuid'  => $comment->slug(),
               ],
            ];
        }

        return $this->children = Pages::factory($comments, $this);
    }
}

Here we redefine the children() method of the Page class to return the comments from the database as child 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:

<ul class="comments">
    <?php foreach ($page->children() as $comment): ?>
    <li>
      <a href="<?= $comment->url() ?>">
        <?= $comment->text() ?> <small>by <?= $comment->email() ?></small>
      </a>
    </li>
    <?php endforeach ?>
</ul>

A template for the individual comment

To access individual comments on their own pages, we add a template for the comment:

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