Overview

blaze comes with a super easy way to handle database connections. For most microsites, you won't need a database. If you need one, it's kind of annoying to set one up. Our webdesigners were always struggeling with those SQL commands and data types and connecting and catching errors and so on.

We decided to integrate a tool into blaze to make this much easier.

Setting up a database

You should setup the database via the backend. We strongly recommend using a mysql database, because it is independent from blaze, and cannot be overwritten or deleted by accident. If you have nothing really crucial to store and/or no database server, you may also use the integrated sqlite database which blaze provides.

When choosing SQLite as database type, blaze will automatically create one and store it in it's data folder.

Setup the database type and the login credentials in the backend. You will then have access to the database with this blaze component:

    $blaze->db

You don't need to connect or disconnect, blaze will automatically handle this for you.

There is a usage example at the bottom of this page!

CRUD

Here come some basic methods to work with your database.

Inserting a database entry

blaze supports basic stuff like raw SQL queries and provides some cool helper methods to make your life easier. Creating a database entry is as easy as:

    $blaze->db->insert($tablename, $array_or_object);

$array_or_object may contain an associative array (where the keys match the table's ids) or an object (where the attribute names match the ids). All given values will be escaped, so you don't have to think of that.

Reading database entries

Retrieving database entries is possible with this basic method:

    $blaze->db->select($tablename, $conditions)

This method will return the database entries which match $conditions, which could hold anything that is a valid SQL condition as string. You don't need to provide $conditions, if you don't blaze will just give you all entries of the table.

You can iterate through the results with foreach.

A quick example:

    $entries = $blaze->db->select('images', 'WHERE category = "beautifulimages"');
    foreach($entries as $entry){
        ...
    }

Helper functions

There are two helper functions to speed up development.

    $blaze->db->selectWhere($tablename, $conditions);

With this helper, you may provide $conditions as an array of WHERE conditions. Another quick example:

    $conditions = array(
        'category = "beautifulimages"',
        'viewcounter > 50'
    );
    $entries = $blaze->db->selectWhere('images', $conditions);

If you do want to retrieve only the first entry, there is another helper:

    $blaze->db->selectOne($tablename, $conditions, $offset);

$offset is omissible and will default to 0. $conditions is also optional.

All three retrieval methods will return false if there is nothing to select.

About the returned entries

You will get an object which holds the selected database entries. You can iterate through the database entries by just using the returned object with a foreach iteration. But there are some other cool functions that you can use.

    $entries = $blaze->db->select('sometable','conditions')->asObjects();

This method will transform the returned entries from associative arrays to objects with attributes. Based on the above example, lets use the $entries to get further information about the database entries.

    // Count the returned rows, returns an integer value
    $entries->countReturnedRows();

    // Get the SQL query that was called, returns a string
    $entries->getSQL();

Updating database entries

Updating database entries relies on the selection methods. This is the basic updating method:

    $blaze->db->update($table, $updated_entry, $identifier_column);

The update() method takes three parameters. $table should obviously be the table's name as string. $updated_entry must hold the entry to update as associative array or object and it must have an array value or an object attribute which matches the unique key, which is setup in the database table. $identifier_column is the name of the unique key and defaults to 'id', so you may omit it.

Please see the usage example at the bottom.

There is a helper function to update all table entries at once:

    $blaze->db->updateAll($table, $updates);

By using this method, you can make blaze update all table entries of the given table to match the $updates which could be an associative array or an object.

Deleting database entries

There are two methods to delete entries.

    $blaze->db->delete($table, $conditions_or_entry);

This method takes two parameters. $table is obviously the name of the table as string. $conditions_or_entry does take either a string holding conditions (f.i. 'WHERE id = 3') or a database entry as associative array.

If you want to delete entries based on a where query, you can also use this method:

    $blaze->db->deleteWhere($table, $conditions);

In this case, $conditions must be a non associative array of SQL conditions.

Other cool stuff

blaze's database component does give you some other methods to make your life easier.

Creating tables from arrays

This special method allows you to create database tables from a special array.

    $blaze->db->createTableFromArray($tablename, $configarray, $ifnotexists, $add_id);

There are four parameters:

$tablename string -> the name of the table to create.

$configarray associative array -> an associative array which sets up the database fields.

$ifnotexists boolean, optional, default: true, wether or not an IF NOT EXISTS flag should be added to the SQL query

$add_id boolean, optional, default: true, wether or not to add a column 'id' holding unique keys for the table

The $configarray must consist of an array holding the column names as key and their SQL setup as values. Here is a quick usage example for this:

    $setup = array(
        'imagename' => 'VARCHAR (50)',
        'viewcount' => 'INT (11)',
        'path' => 'TEXT',
    );
    $blaze->db->createTableFromArray('images', $setup, true, true);

Raw queries

You can of course call SQL queries, there is a method for this:

    $blaze->db->query($query);

Make sure to escape any user input as blaze will not make any changes to your query.

Safe values

blaze provides a quick escaping function:

    $escapedvalues = $blaze->db->escape($values);

The method does accept both simple values like strings or integer values and arrays, and it will return the escaped value or array.

Usage example

Here are some quick examples to give you an overview.

    <?php
        // Load some entries from the database and transform them to objects
        $images = $blaze->db->selectWhere('images', 'viewcount > 10')->asObjects();

        // Iterate through the entries
        foreach($images as $image)
        {   
            // Delete bad entries
            if($image->category == 'bad_image')
            {
                $blaze->db->delete($image);
            }

            // Show good ones
            echo $image->path;
            $image->viewcount++;

            // Store the entry back to the database
            $blaze->db->update($image);
        }
    ?>

This example is completely useless, but it shows how to use blaze's database component.

Plugin development

If you do want to use the database component for your plugin, please create your own instance. Take a look at the plugin tutorial to get a better idea.