Database Service

If you need a direct access to the database of the Content Management System or the kitFramework, you can use the Database Service within your php template script.

The Database Service is using the Doctrine Database Abstraction Layer (DBAL). Doctrine is well documented, you have access to all supported functions.

In practice you will need only three functions to query a MySQL statement:

• $template['db']->fetchAll()
• $template['db']->fetchAssoc()
• $template['db']->fetchColumn()

You will need the following constants:

• CMS_TABLE_PREFIX
• FRAMEWORK_TABLE_PREFIX

and you may need to unsanitize the results:

• $template['tools']->unsanitizeText()

In the following we describe the usage of the Database Service in brief examples.

###fetchAll()

$template['db']->fetchAll($SQL) execute the $SQL query and fetch all results in to an array.

Example:

<?php
    // get all page_id's of top level pages in order of the menu items 
    $SQL = "SELECT `page_id` FROM `".CMS_TABLE_PREFIX."pages` WHERE `level` = 0 ".
        "AND `visibility` = 'public' ORDER BY `position` ASC";        
    
    // fetch all page_id's
    $result = $template['db']->fetchAll($SQL);
    
    // the result must be an array to be valid
    if (is_array($result)) {
        foreach ($result as $page) {
            // do something with the retrieved page_id
            echo $page['page_id']."<br />";
        }
    }
?>

See also: fetchAll() at the Doctrine DBAL Documentation.

###fetchAssoc()

$template['db']->fetchAssoc($SQL) execute the $SQL query and return the first row of the retrieved data as associated array.

Example:

<?php
    // get the name of the sections table into a variable
    $sections = CMS_TABLE_PREFIX.'sections';
    
    // get the name of the mod_wysiwyg table into a variable
    $wysiwyg = CMS_TABLE_PREFIX.'mod_wysiwyg';
    
    // get the last WYSIWYG section of the current page
    $SQL = "SELECT * FROM `$sections`, `$wysiwyg` WHERE `$sections`.`section_id` = ".
        "`$wysiwyg`.`section_id` AND `module` = 'wysiwyg' AND `$sections`.`page_id` = ".
        PAGE_ID." ORDER BY `position` DESC";

    // fetch the first row into an associated array
    $result = $template['db']->fetchAssoc($SQL);
    
    // check if the result is valid
    if (isset($result['page_id'])) {
        // do something with the result
        echo "<pre>";
        print_r($result);
        echo "</pre>";
    }
?>

See also: fetchAssoc() at the Doctrine DBAL Documentation.

###fetchColumn()

$template['db']->fetchColumn($SQL) execute the $SQL query and retrieve only the given column of the first result row.

Example:

<?php
    // get the searching settings for the current page
    $SQL = "SELECT `searching` FROM `".CMS_TABLE_PREFIX."pages` WHERE `page_id`=".PAGE_ID;
    
    // get the value of 'searching' into $searching
    $searching = $template['db']->fetchColumn($SQL);
    
    // do something with the result
    echo sprintf('<p>The search function for this page is: <b>%s</b></p>',
        ($searching == 1) ? 'enabled' : 'disabled');
?>

See also: fetchColumn() at the Doctrine DBAL Documentation.

⇐ kitCommand Service | Droplet Service ⇒