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:
Create a new parent page
Create a parent page called
comments in the content folder.
Connect to the database
config.php, connect to the database:
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:
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:
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:
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
The children's blueprint
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:
CommentPage model would need to overwrite the
readContent() methods to get the comment details from the table instead of the text file.
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:
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
listed. We also add a title for our comments, so that we end up with the following table scheme:
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.
There are three differences here compared to the model above:
- We set the
numproperty depending on the value of the
statusfield in the database.
- We add the
statusfield to the
- We add the
titlefield to the
The model for a single comment needs more significant changes:
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.
Since we now have an explicit title field, we add the
changeTitle() method and remove the
title() method from the model, because that is not 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.