User:Rvsjoen/tutorial/Developing an MVC Component/Part 06

Using a database

We would like to store our hello messages in the database, and to allow the admins to choose which message to show using a field in the menu item options. The order order of business is to create some SQL tables.

SQL install/uninstall

Let us start with creating some installation and uninstall scripts that will be executed when the component is installed or uninstalled. Afterwards we will update the installation manifest and tell it where these files are and what to do with them.

With your favorite editor, create the following files

admin/sql/install.mysql.utf8.sql

DROP TABLE IF EXISTS `#__helloworld`;

CREATE TABLE `#__helloworld` (
  `id` int(11) NOT NULL auto_increment,
  `greeting` varchar(25) NOT NULL,
   PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

INSERT INTO `#__helloworld` (`greeting`) VALUES
        ('Hello World!'),
        ('Good bye World!');

admin/sql/uninstall.mysql.utf8.sql

DROP TABLE IF EXISTS `#__helloworld`;

You may wonder why we are using #__ to prefix the table names, this is part of Joomla! builtin functionality and this string will be automatically replaced with the configured database prefix. In other words, hard-coding table names in these files is a bad idea. Stick to the program here.

Creating a table

We will also create a table class which we can use as a representation of a row in our helloworld table. This provides additional abstraction as compared to writing and executing the queries manually, this will become clearer in a second. Basically all we need to do is to create the table class, we put it in admin/tables simply because the tables folder in the administrator part of the components is the default place to look for table classes.

You will also notice that the table class is not prefixed with the component name in the same way as the model, view and controller.

site/models/helloworld.php

<?php
// No direct access to this file
defined('_JEXEC') or die;

jimport('joomla.database.table');

class TableHelloWorld extends JTable
{
        function __construct(&$db) 
        {
                parent::__construct('#__helloworld', 'id', $db);
        }
}

The call to the parent constructor is used to define which column is the primary key of the table. In our case this is the '''id''' column.

Updating the model

site/models/helloworld.php

<?php
// No direct access to this file
defined('_JEXEC') or die('Restricted access');

jimport('joomla.application.component.modelitem');

class HelloWorldModelHelloWorld extends JModelItem
{
        protected $item;

        /**
         * Get the message
         * @return string The message to be displayed to the user
         */
        public function getItem() 
        {
                if (!isset($this->item)) {
                        $id = JRequest::getInt('id');
                        // Get a TableHelloWorld instance
                        $table = $this->getTable();

                        // Load the message
                        $table->load($id);

                        // Assign the message
                        $this->item = $table->greeting;
                }
                return $this->item;
        }
}

Creating a form field

admin/models/fields/helloworld.php

<?php
// No direct access to this file
defined('_JEXEC') or die;

jimport('joomla.form.helper');

JFormHelper::loadFieldClass('list');

class JFormFieldHelloWorld extends JFormFieldList
{
        protected $type = 'HelloWorld';

        protected function getOptions()
        {
                $db = JFactory::getDBO();
                $query = $db->getQuery(true);
                $query->select('id,greeting');
                $query->from('#__helloworld');
                $db->setQuery((string)$query);
                $messages = $db->loadObjectList();
                $options = array();
                if($messages){
                        foreach($messages as $message){
                                $options[] = JHtml::_('select.option', $message->id, $message->greeting);
                        }
                }
                $options = array_merge(parent::getOptions(), $options);
                return $options;
        }
}

Installation manifest

helloworld.xml

<?xml version="1.0" encoding="utf-8"?>
<extension type="component" version="1.6.0" method="upgrade">

        <name>Hello World!</name>
        <!-- The following elements are optional and free of formatting constraints -->
        <creationDate>June 2011</creationDate>
        <author>John Doe</author>
        <authorEmail>john.doe@example.org</authorEmail>
        <authorUrl>http://www.example.org</authorUrl>
        <copyright>Copyright Info</copyright>
        <license>License Info</license>
        <!--  The version string is stored in the components table -->
        <version>0.0.6</version>
        <!-- The description is optional and defaults to the name -->
        <description>Description of the Hello World component ...</description>

        <install>
                <sql>
                        <file driver="mysql" charset="utf8">sql/install.mysql.utf8.sql</file>
                </sql>
        </install>
        <uninstall>
                <sql>
                        <file driver="mysql" charset="utf8">sql/uninstall.mysql.utf8.sql</file>
                </sql>
        </uninstall>

        <!-- Note the folder attribute: This attribute describes the folder
                to copy FROM in the package to install therefore files copied
                in this section are copied from "site/" in the package -->
        <files folder="site">
                <filename>index.html</filename>
                <filename>helloworld.php</filename>
                <filename>controller.php</filename>
                <folder>views</folder>
                <folder>models</folder>
        </files>

        <administration>
                <menu>Hello World!</menu>
                <!-- Note the folder attribute: This attribute describes the folder
                        to copy FROM in the package to install therefore files copied
                        in this section are copied from "admin/" in the package -->
                <files folder="admin">
                        <filename>index.html</filename>
                        <filename>helloworld.php</filename>
                        <folder>sql</folder>
                        <folder>tables</folder>
                        <folder>models</folder>
                </files>
        </administration>

</extension>

Testing your component

For details on how to install the component into your Joomla! site, refer to the information provided in Part 01.

In order to test this component, go to the administrator interface and create a new menu item. In the menu item type selection interface, pick Hello World as the menu item type. In the options of the menu item you will be able to choose which hello message to show from the database, the message you choose should be displayed in the frontend as in the previous part.

File listing

• helloworld.xml
• site/index.html
• site/helloworld.php
• site/controller.php
• site/views/index.html
• site/views/helloworld/index.html
• site/views/helloworld/view.html.php
• site/views/helloworld/tmpl/index.html
• site/views/helloworld/tmpl/default.php
• site/views/helloworld/tmpl/default.xml
• site/models/index.html
• site/models/helloworld.php
• admin/index.html
• admin/helloworld.php

Download this part

com_helloworld-part01.zip

Articles in this series

This tutorial is supported by the following versions of Joomla!

• Introduction
• Part 01 - Developing a Basic Component
• Part 02 - Adding a view to the frontend
• Part 03 - Adding a menu item type to the frontend
• Part 04 - Adding a model to the frontend
• Part 05 - Adding options to menu items
• Part 06 - Using a database
• Part 07 - Basic backend
• Part 08 - Adding language translation
• Part 09 - Adding actions to backend
• Part 10 - Adding decorations to the backend
• Part 11 - Adding validation
• Part 12 - Adding categories
• Part 13 - Adding component options
• Part 14 - Adding ACL
• Part 15 - Adding a script file