1. INTRODUCTION

Kristal Framework’s Database class helps you read and change MySQL records using PHP methods. Create a class for a table, then use methods such as where(), get() and insert() to work with its records.

In this guide, we’ll use a small articles table. Database classes belong in App/Backend/Database/.

2. CONFIGURING A CONNECTION

Create your MySQL database first. Then edit DATABASES in config/config.php with your connection details:

define("DATABASES", [
    "primary" => [
        "host" => "localhost",
        "database_name" => "my_website",
        "username" => "my_database_user",
        "password" => "replace-with-your-database-password",
    ],
]);

Kristal Framework connects through PDO, so PHP needs the MySQL PDO driver enabled. The framework can create missing tables once the database exists.

To use more than one connection, add another entry alongside primary with the same four settings.

3. CREATING A DATABASE CLASS

Create App/Backend/Database/Articles.php:

<?php declare(strict_types=1);

namespace Backend\Database;

defined("ACCESS") or exit("Access Denied");

use Backend\Core\Database;

class Articles extends Database
{
    protected string $table = "articles";
    protected string $primary_key = "id";

    protected array $columns = [
        "id" => "int unsigned NOT NULL AUTO_INCREMENT",
        "title" => "varchar(200) NOT NULL",
        "published" => "tinyint(1) NOT NULL DEFAULT 0",
        "created_at" => "datetime NULL",
    ];

    public function __construct(string $database = "primary")
    {
        parent::__construct(["database" => $database]);
        $this->confirmTable();
    }
}

Here’s what each part does:

• $table: The table name.
• $primary_key: The column that identifies each record.
• $columns: The column names and their SQL definitions.
• confirmTable(): Creates the table if it doesn’t exist yet.

Add this import near the top of your PHP file, then create an Articles object where you want to run a query:

use Backend\Database\Articles;

$articles = new Articles();

The examples below use this $articles object. To choose another configured connection, use new Articles(“secondary”).

4. READING RECORDS

To read all articles and display their titles:

$rows = $articles->table()->get();

foreach ($rows as $article)
{
    echo esc_html($article->title);
}

Start a query with table(). It uses the table named in your class, so you don’t pass a table name.

Choosing Columns

To read only the columns you need:

$rows = $articles->table()
    ->select(["id", "title"])
    ->get();

Reading One Record

Use getFirst() to read the first matching record:

$article = $articles->table()
    ->where("id", 42)
    ->getFirst();

if (empty((array) $article))
{
    echo "Article not found.";
}
else
{
    echo esc_html($article->title);
}

The result is a row object, or an empty object if nothing matches. Check it with empty((array) $article) as shown above.

Reading One Value

Use getValue() when you only need one column:

$title = $articles->table()
    ->where("id", 42)
    ->getValue("title");

This returns the title, or null if no value is found. To count results from get(), use count((array) $rows).

5. FILTERING AND SORTING

Use where() to select matching records. Its arguments are the column, value and optional comparison:

$rows = $articles->table()
    ->where("published", 1)
    ->where("id", 10, ">")
    ->orderBy("id", "desc")
    ->limit(10)
    ->offset(0)
    ->get();

This reads up to ten published articles with an ID greater than 10, newest ID first. The default comparison is =. You can also use !=, <, >, <=, >= and LIKE.

Searching Text

These are three separate queries:

$matches = $articles->table()->whereLike("title", "PHP")->get();
$starting = $articles->table()->whereStartsWith("title", "Getting")->get();
$ending = $articles->table()->whereEndsWith("title", "Guide")->get();

They find titles containing, starting with or ending with the given text. The helpers add SQL wildcards for you; % and _ in search text also act as wildcards.

Other Filters

$selected = $articles->table()->whereIn("id", [1, 3, 5])->get();
$range = $articles->table()->whereBetween("id", 1, 10)->get();
$undated = $articles->table()->whereNull("created_at")->get();
$dated = $articles->table()->whereNotNull("created_at")->get();

An empty list passed to whereIn() matches no records. Add true as the fourth argument to whereBetween() to select values outside the range.

For either of two conditions, use orWhere():

$rows = $articles->table()
    ->where("id", 1)
    ->orWhere("id", 2)
    ->get();

This selects articles with ID 1 or 2. For complex combinations of AND and OR, use custom SQL so you can group the conditions with parentheses.

Use offset() with limit() for pages of results. Calling orderBy() again replaces the previous sort order.

6. INSERTING AND UPDATING

To create an article, pass its column values to insert():

$saved = $articles->table()->insert([
    "title" => "Getting Started with Kristal Framework",
    "published" => 0,
    "created_at" => date("Y-m-d H:i:s"),
]);

To change an existing article, select it with where(), then call update():

$updated = $articles->table()
    ->where("id", 42)
    ->update([
        "title" => "An Updated Title",
        "published" => 1,
    ]);

update() requires a condition and changes every matching record. Use the record’s ID when you want to update just one. limit() does not limit updates.

Both methods return true when the query runs successfully. This doesn’t tell you how many records changed. Check values before saving them; database errors can raise a PDOException.

For saving through object properties, see Entities.

7. DELETING RECORDS

Select a record and call delete():

$deleted = $articles->table()
    ->where("id", 42)
    ->delete();

A true result means the query succeeded, even if no record matched.

8. CUSTOM SQL AND TABLE TOOLS

You can write SQL directly when you need a query the helper methods don’t cover. Pass changing values separately:

$article = $articles->fetch(
    "SELECT id, title FROM articles WHERE id = :id",
    ["id" => 42]
);

$rows = $articles->fetchAll(
    "SELECT id, title FROM articles WHERE published = :published",
    ["published" => 1]
);

$articles->execute(
    "UPDATE articles SET published = :published WHERE id = :id",
    ["published" => 1, "id" => 42]
);

• fetch(): Returns one row object, or an empty object.
• fetchAll(): Returns a collection of rows as arrays. Inside a loop, read a value with $row[”title”].
• execute(): Runs the statement without returning a result.

Keep these calls separate from unfinished queries built with where() and other helper methods; they don’t clear those filters.

Counting Records

You can also pass an SQL expression to select():

$total = $articles->table()
    ->select("COUNT(*) AS total")
    ->getFirst();

echo (int) $total->total;

For table management, doesTableExist() checks for a table and getTables() lists tables. close() closes the connection. dropTable() removes a table and its data, so use it only when you intend to remove both.