Working with database

Lightnote CMS uses MySQL Server as storage engine. You have to define database tables you assume to use in the configuration file of your extension. The fastest and easiest way to do this is with use of Extension Wizard. To illustrate, let's create simple News extension, which makes use of database.

Creating extension

Go to Administration > New Extension Wizard.
Enter the Name: "news" and Friendly name: "News".
Click "Database" link at left side.

Note: All database tables tables will have "ext_[EXTENSION_NAME]_" prefix to prevent use of the same database tables in different extensions.
Let's create database table which will contain news items. Name it ext_news_items and click Add table button.
Type a name of the table which will be visible in CMS backend: "News" (Friendly name).
Table will consists of 5 fields: id (uniquie auto increment integer), header, image, text and date. Type below Name "id", then click "Add field" button or hit "+" on your keyboard and add the rest of the fields:
header - VARCHAR(255)
image - VARCHAR(255) (will contain path to image)
text - TEXT
date - DATETIME
Now let's specify Backend editor for each field. For header, author and date it will be single line textbox, for text - rich text editor. Click on "gear" icon near cross, it will expand field options.
To make extension table visible in Data manager, we need to create a relation to data node id. To do that, add one more field named ln_pid, type of INTEGER, which will contain this reference. Choose
Click Finish. And then click Save.
Click "Install" button in extension list to install extension.

Adding a new row to table

Click Data tab on treeview at left .
Right click in treeview and then click Add Folder. Name it News.
Now click Add Database table and then choose News.
Add first entry.

Using database from your extension

You can use standard PHP MySQL functions to read and write to a database.

<?php
class Ext_news extends Extension
{
    function Render($smarty)
    {
        $newsContent = "";
        $result = mysql_query("SELECT * FROM ext_news_items ORDER BY id DESC");
        while($row = mysql_fetch_assoc($result))
        {
              // --- Header ---
              $newsContent .= sprintf("<h3>%s</h3>", $row["header"]);

              // --- Date ---
             $newsContent .= date("d.m.Y", $row["date"]);

              // --- Image ---

              // REL_PATH - constant indicating path to your 
              // website relative to DOCUMENT ROOT
    
              // DATA_PATH - constant indicating path to folder with data 
              // (where all images are uploaded). By default: data/
              $pathToImage = REL_PATH.DATA_PATH.$row["image"];
              $newsContent .= sprintf('<img src="%s" />', $pathToImage);

              // --- Text ---
             $newsContent .= sprintf("<p>%s</p>", $row["text"]);


        }
        
        mysql_free_result($result);
    }
}
?>

But you can also use DB Class to work with database. There is a global variable $DB, which represents an instance of this class:

<?php
class Ext_news extends Extension
{
    function Render($smarty)
    {
        global $DB;
    	
        $newsCount = $DB->GetScalar("SELECT count(id) FROM ext_news_items");
    	
        return "News count: ".$newsCount;
    }
}
?>

Lightnote CMS - web 2.0 content management system