Connecting Kirby to a database

Wait, what!? Did I just write the bad "D" word? Sure, Kirby is a file-based system and I'm convinced that file-based is the new black, but in some cases you might want to consider to move away from the file-based approach and rather organize data in a database.

Here are some examples where a database is definitely a better idea than using the file system:

- a self-made comment system
- a newsletter list
- tracking downloads/page views or other statistics
- a contact form, which stores the sent messages
- huge lists of data, which may end up with thousands of entries
- in general most kinds of user generated content

etc.

So you are lost when you want to build a site with such features and you are using Kirby, right? Wrong!

Kirby is based on the Kirby Toolkit. The Kirby Toolkit is a library of handy classes a functions, which make your life as a PHP developer easier. I like to refer to it as some kind of jQuery for PHP.

The Toolkit has a built-in database class, which is very easy to use and supports MySQL and SQLite by default. Let me show you how you make your first database queries in your Kirby-powered project in a matter of seconds.

Connect

First you need to setup the connection to your database. I assume that you already know how to setup a MySQL-server and how to setup a database and tables. If not, please learn more about it first.

The best place for your database connection setup is site/config/config.php Just put the following lines at the bottom of the config file:

c::set('db.host', 'localhost');
c::set('db.user', 'mydbuser');
c::set('db.password', 'mypassword');
c::set('db.name', 'mydb');

That's it! Now you are ready to add your first entry to your database.

A super simple comment system

For this tutorial I'm going to show you how to build a very rudimentary comment system. This is far from perfect, so please don't use it in production.

Add a comments table to your mydb with the following columns:

- id (int + primary key + autoincrement)
- name (varchar 255)
- email (varchar 255)
- comment (text)
- date (timestamp)

Adding comments

The database class has shortcut functions for the most common tasks.
To insert a new row into a table, all you need to do is…

db::insert('comments', array(
  'name'    => 'Bastian',
  'email'   => 'bastian@getkirby.com'
  'comment' => 'This is my first comment',
  'date'    => 'NOW()',
));

Because we setup the connection in the config file, you don't need to initiate the database class or do anything else, just use the code above to add your first row.

Listing comments

Add some more rows to get a better set of results. To fetch all comments from your comments table, use the shortcut for selects:

$comments = db::select('comments', '*');

This will select all columns and all rows from your comments table.
$comments will contain an associative array like this…

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Bastian
            [email] => bastian@getkirby.com
            [text] => This is my first comment
            [date] => 2012-03-08 17:45
        )

    [1] => Array
        (
            [id] => 2
            [name] => Peter
            [email] => peter@acme.com
            [text] => Comments suck big time!
            [date] => 2012-03-08 19:45
        )

)

You can now use a foreach loop to create the HTML for your comments.
Here's a very rough example:

<h2>Comments</h2>
<ul class="comments">

  <?php foreach($comments as $comment): ?>
  <li>
    <h3><?= html($comment['name']) ?> said…</h3>
    <p><?= html($comment['text']) ?></p>
  </li>
  <?php endforeach ?>

</ul>

To only select some of the columns in the table, you can adjust the select function like this:

$comments = db::select('comments', 'name, text');

If you only need name and text for your HTML, it's better to reduce the number of returned columns. The result array will then be smaller as well:

Array
(
    [0] => Array
        (
            [name] => Bastian
            [text] => This is my first comment
        )

    [1] => Array
        (
            [name] => Peter
            [text] => Comments suck big time!
        )

)

If you want to select all comments from a specific email address, you can easily add a where clause to your select:

$comments = db::select('comments', '*', array('email' => 'bastian@getkirby.com'));

This will only return comments, with bastian@getkirby.com in the email column.

Comments are most often sorted by date – either ascending or descending:

// sort them descending
$comments = db::select('comments', '*', false, 'date desc');

// sort them ascending
$comments = db::select('comments', '*', false, 'date asc');

And finally you probably want to limit the number of results so your page won't end up with a list of thousands of comments:

// only return 10 comments starting with the first
$comments = db::select('comments', '*', false, 'date desc', 0,10);

// only return 20 comments starting with the number 21 (second page)
$comments = db::select('comments', '*', false, 'date desc', 20,20);

Updating comments

Maybe it's a good idea to offer an edit form, so your users can fix typos or change their mind about what they just posted.

The update function is almost identical to the insert function, but this time you need to pass a where clause, so it's clear which row should be edited:

$update = array(
  'text' => 'This is my fixed comment text'
);

db::update('comments', $update, array('id' => 1));

This will replace the text of the comment with ID 1. Of course you could update all the other columns as well, by adding them to the $update array, but for a comment system, most of the time it's all going to be about fixing the text.

Deleting comments

Add a comment box to your site and you are most probably going to end up with loads of comment spam. That needs to be deleted somehow. Here's how:

db::delete('comments', array('id' => 1));

This will delete the comment with ID 1. If you found out that the user with the email address bastian@getkirby.com is a spambot and all of his comments are spam, you can delete them at once like this:

db::delete('comments', array('email' => 'bastian@getkirby.com'));

Complex queries

There are a couple of other shortcuts for common SQL queries, but if you have some complex query or you just feel more comfortable by using normal SQL, you can use the query function:

$result = db::query('SELECT * FROM comments JOIN users ON comment_user = user_id ORDER BY comment_date DESC LIMIT 0,10');

Warning: If you write your own SQL queries, you must make sure that they are protected against SQL injections.

Learn more…

This is really just a very short overview of what you can do with the built-in database class. Please read the docs, to find out more. There are plenty more methods you can use and you can do quite powerful database fiddling with it.

You should also give the other features and functions of the Kirby Toolkit a try. I promise it will definitely make your life with PHP much easier :) The Toolkit is open-source so you can always use it for other projects as well.

Conclusion

Connecting to a database can give you an entire new world of possibilities, to add features to your Kirby-powered site. The basics of the Kirby CMS are very simple, but it can be way more than just a file-based CMS.