J3.x:Developing an MVC Component/Using the database

2018-12-05T01:10:17Z

JoomliC: Add information for columnAlias to be used when state field is not published. See https://github.com/joomla/joomla-cms/pull/23200 & https://github.com/joomla/joomla-cms/issues/23198#issuecomment-442872331

<noinclude><languages /></noinclude>
{{:J3.1:Developing an MVC Component/<translate>
en</translate>}}
<translate>== Introduction == </translate>
<translate>
This tutorial is part of the [[S:MyLanguage/J3.3:Developing an MVC Component | Developing an MVC Component for Joomla! 3.3]] tutorial. You are encouraged to read the previous parts of the tutorial before reading this. Also while doing this part you are also encouraged to read more on database queries, selecting data from a database table and retrieving it in several formats [[S:MyLanguage/Selecting_data_using_JDatabase|here]].</translate>

<translate> There are also 3 videos associated with this step in the tutorial, covering [https://www.youtube.com/watch?v=JWS_3rx8RzY the Database Setup], [https://www.youtube.com/watch?v=rKEHDeFGlGM Displaying the message (using JTable)] and [https://www.youtube.com/watch?v=uEDNyG6pSEk Admin message selection (and JDatabase)].</translate>

{{#widget:YouTube|id=JWS_3rx8RzY}}

{{#widget:YouTube|id=rKEHDeFGlGM}}

{{#widget:YouTube|id=uEDNyG6pSEk}}

<translate>== Using the database == </translate>
<translate>
Components usually manage their contents using the database. During the install/uninstall/update phase of a component, you can execute SQL queries through the use of SQL text files.</translate>

<translate>
With your favourite file manager and editor create two files called <tt>admin/sql/install.mysql.utf8.sql</tt> and <tt>admin/sql/updates/mysql/0.0.6.sql</tt>. They should both have the same content, as follows:</translate>

<span id="admin/sql/install.mysql.utf8.sql">
<tt>'''admin/sql/install.mysql.utf8.sql''' and '''admin/sql/updates/mysql/0.0.6.sql'''</tt>
<source lang="sql">
DROP TABLE IF EXISTS `#__helloworld`;

CREATE TABLE `#__helloworld` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`greeting` VARCHAR(25) NOT NULL,
`published` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
)
ENGINE =MyISAM
AUTO_INCREMENT =0
DEFAULT CHARSET =utf8;

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

</source>
</span>

'''Note.''' Nowadays Joomla recommends specifying

<tt>ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;</tt>

instead of what is above. InnoDB is the more modern (and now default) MySQL database engine, replacing MyISAM, and utf8mb4 supports a wider range of character sets including emojis. However, I haven't tested all the steps in the tutorial with this setting, so if you use it and go through the complete tutorial series and find no problems please consider updating this file and where it occurs in other tutorial steps.

Also note that if you look at a Joomla database many of the key database tables have a field called 'title' for the sort of information which we're storing in our 'greeting' field. It's generally advisable to follow the Joomla pattern, and use 'title' as the field name, as when we try to use more complex functionality (such as ACL and associations) some of the core Joomla javascript routines we want to reuse expect a 'title' field to be present. (Something to consider changing when this tutorial series is next updated).

Often you will find that the database table has a field to keep track of the published/unpublished state of an item. Using the name 'state' within Joomla is not recommended as it can lead to conflicts, instead the name 'published' is used.<br />
Note: How to tell Joomla to store the value of the published form field into a different name database field? We do this by using the method [[Column alias|setColumnAlias()]] (since 3.4.0).

<translate>
The file <tt>install.mysql.utf8.sql</tt> will be executed when you install this component. The file ''0.0.6.sql'' is executed when you do an update.</translate>

<translate>
This is the install file. It will be executed if you put an appropriate order in the <tt>helloworld.xml</tt> file.</translate>

<translate>
'''Important Note:''' When saving the SQL files in utf8, be sure to save them as utf8 NOT BOM or the query will fail with MySQL error #1064.</translate>

<span id="helloworld.xml">
<tt>'''helloworld.xml'''</tt>
<source lang="xml" highlight="13,17-26,58-61">
<?xml version="1.0" encoding="utf-8"?>
<extension type="component" version="3.0" method="upgrade">

<name>Hello World!</name>

<creationDate>January 2018</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>

<version>0.0.6</version>

<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>
<update> 
<schemas>
<schemapath type="mysql">sql/updates/mysql</schemapath>
</schemas>
</update>



<files folder="site">
<filename>index.html</filename>
<filename>helloworld.php</filename>
<filename>controller.php</filename>
<folder>views</folder>
<folder>models</folder>
</files>

<administration>

<menu link='index.php?option=com_helloworld'>Hello World!</menu>


<files folder="admin">

<filename>index.html</filename>
<filename>helloworld.php</filename>

<folder>sql</folder>

<folder>tables</folder>

<folder>models</folder>
</files>
</administration>

</extension>
</source>
</span>

<translate>

Do the same for the uninstall file:


With your favourite file manager and editor put a file <tt>admin/sql/uninstall.mysql.utf8.sql</tt> containing:</translate>

<span id="admin/sql/uninstall.mysql.utf8.sql">
<tt>'''admin/sql/uninstall.mysql.utf8.sql'''</tt>
<source lang="sql">
DROP TABLE IF EXISTS `#__helloworld`;
</source>
</span>

<translate>== Schema Numbering ==  </translate>

<translate>

Your component has a version number (which is specified inside the <tt><version></tt> tag in your helloword.xml manifest file), and your component's database schema has its own version number (which is based on the filenames of the sql update files).</translate>

<translate>

Joomla keeps track of the database schema version of your component through a record in its <tt>#__schemas</tt> table. So when you first install a component, if there's a file called, say, <tt>admin/sql/updates/mysql/0.0.6.sql</tt>, then Joomla will store the value <tt>0.0.6</tt> it its schemas record. </translate>

<translate>

When you next install a newer version of this component - it doesn't have to be the next version, you can skip versions - Joomla will do the following:
* it will retrieve the component's latest database schema version from its <tt>#__schemas</tt> table - so it might find in our example the value 0.0.6.
* it will get the filenames of all the files in the <tt>admin/sql/updates/mysql/</tt> directory, and organise them in numerically increasing order.
* it will process in order the update files which have filenames numerically after the current schema version - so it might find files called 0.0.7.sql, 0.0.9.sql and 0.0.10.sql, and process these in order.
* it will update the schemas record to have the number of the last update file which it processed - eg 0.0.10. </translate>

<translate>

If you have already released versions of your component when you introduce database use, as we have simulated in this tutorial series, then your first update file must have exactly the same content as the install file. If you have not, then it should be empty.</translate>

<translate>

Although it may be a good idea to keep the two version numbers in step, you don't have to. Joomla takes the schema version from the name of the numerically last update file. That's why it is recommended that there should be an initial update file, even if it's empty. If you want to keep your schema numbers in step with the component version numbers when you update your code but not the database schema, you simply include an update file to go with the new release number, and that update file, too, will be empty.</translate>

<translate>

As you make subsequent releases of your component the database install file must always contain the full schema and the update files only need to contain any changes you have made to the schema since the last update.</translate>

<translate>== Adding a new field type == </translate>
<translate>
For the moment, we have used a [[S:MyLanguage/J3.3:Developing an MVC Component/Adding a menu type to the site part|hard coded field type for messages]]. We need to use our database for choosing the message, and for this we need to define a custom field type (which we call ''helloworld'' below) as described [[S:MyLanguage/Creating_a_custom_form_field_type|here]].</translate>

<translate>
Modify the <tt>site/views/helloworld/tmpl/default.xml</tt> file and put these lines</translate>

<span id="site/views/helloworld/tmpl/default.xml">
<tt>'''site/views/helloworld/tmpl/default.xml'''</tt>
<source lang="xml" highlight="8,11-16">
<?xml version="1.0" encoding="utf-8"?>
<metadata>
<layout title="COM_HELLOWORLD_HELLOWORLD_VIEW_DEFAULT_TITLE">
<message>COM_HELLOWORLD_HELLOWORLD_VIEW_DEFAULT_DESC</message>
</layout>
<fields
name="request"
addfieldpath="/administrator/components/com_helloworld/models/fields"
>
<fieldset name="request">
<field
name="id"
type="helloworld"
label="COM_HELLOWORLD_HELLOWORLD_FIELD_GREETING_LABEL"
description="COM_HELLOWORLD_HELLOWORLD_FIELD_GREETING_DESC"
/>
</fieldset>
</fields>
</metadata>
</source>
</span>

<translate>
It introduces a new field type and tells Joomla to look for the field definition in the <tt>/administrator/components/com_helloworld/models/fields</tt> folder.</translate>

<translate>
In order to learn more on database queries, selecting data from a database table and retrieving it in several formats [[S:MyLanguage/Selecting_data_using_JDatabase|click here]].</translate>
<translate>
With your favourite file manager and editor put a file ''admin/models/fields/helloworld.php'' containing:</translate>

<span id="admin/models/fields/helloworld.php">
<tt>'''admin/models/fields/helloworld.php'''</tt>
<source lang="php">
<?php
/**
* @package Joomla.Administrator
* @subpackage com_helloworld
*
* @copyright Copyright (C) 2005 - 2018 Open Source Matters, Inc. All rights reserved.
* @license GNU General Public License version 2 or later; see LICENSE.txt
*/

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

JFormHelper::loadFieldClass('list');

/**
* HelloWorld Form Field class for the HelloWorld component
*
* @since 0.0.1
*/
class JFormFieldHelloWorld extends JFormFieldList
{
/**
* The field type.
*
* @var string
*/
protected $type = 'HelloWorld';

/**
* Method to get a list of options for a list input.
*
* @return array An array of JHtml options.
*/
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;
}
}

</source>
</span>

<translate>
The new field type displays a drop-down list of messages to choose from. You can see the result of this change in the menu manager section for the helloworld item.</translate>

<translate>== Display the chosen message == </translate>
<translate>
When a menu item of this component is created/updated, Joomla stores the identifier of the message. The <tt>HelloWorldModelHelloWorld</tt> model has now to compute the message according to this identifier and the data stored in the database. To do this is uses the [https://docs.joomla.org/Using_the_JTable_class JTable] functionality, which is an alternative to JDatabase if only CRUD operations on single records are required.</translate>

<translate>
Modify the <tt>site/models/helloworld.php</tt> file:</translate>

<span id="site/models/helloworld.php">
<tt>'''site/models/helloworld.php'''</tt>
<source lang="php">
<?php
/**
* @package Joomla.Administrator
* @subpackage com_helloworld
*
* @copyright Copyright (C) 2005 - 2018 Open Source Matters, Inc. All rights reserved.
* @license GNU General Public License version 2 or later; see LICENSE.txt
*/

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

/**
* HelloWorld Model
*
* @since 0.0.1
*/
class HelloWorldModelHelloWorld extends JModelItem
{
/**
* @var array messages
*/
protected $messages;

/**
* Method to get a table object, load it if necessary.
*
* @param string $type The table name. Optional.
* @param string $prefix The class prefix. Optional.
* @param array $config Configuration array for model. Optional.
*
* @return JTable A JTable object
*
* @since 1.6
*/
public function getTable($type = 'HelloWorld', $prefix = 'HelloWorldTable', $config = array())
{
return JTable::getInstance($type, $prefix, $config);
}

/**
* Get the message
*
* @param integer $id Greeting Id
*
* @return string Fetched String from Table for relevant Id
*/
public function getMsg($id = 1)
{
if (!is_array($this->messages))
{
$this->messages = array();
}

if (!isset($this->messages[$id]))
{
// Request the selected id
$jinput = JFactory::getApplication()->input;
$id = $jinput->get('id', 1, 'INT');

// Get a TableHelloWorld instance
$table = $this->getTable();

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

// Assign the message
$this->messages[$id] = $table->greeting;
}

return $this->messages[$id];
}
}

</source>
</span>

<translate>
The model now asks the ''TableHelloWorld'' to get the message. This table class has to be defined in <tt>admin/tables/helloworld.php</tt> file</translate>

<span id="admin/tables/helloworld.php">
<tt>'''admin/tables/helloworld.php'''</tt>
<source lang="php">
<?php
/**
* @package Joomla.Administrator
* @subpackage com_helloworld
*
* @copyright Copyright (C) 2005 - 2018 Open Source Matters, Inc. All rights reserved.
* @license GNU General Public License version 2 or later; see LICENSE.txt
*/
// No direct access
defined('_JEXEC') or die('Restricted access');

/**
* Hello Table class
*
* @since 0.0.1
*/
class HelloWorldTableHelloWorld extends JTable
{
/**
* Constructor
*
* @param JDatabaseDriver &$db A database connector object
*/
function __construct(&$db)
{
parent::__construct('#__helloworld', 'id', $db);
}
}

</source>
</span>

<translate>
You shouldn't see any differences, but if you access the database you should see a table named ''jos_helloworld'' with two columns: id and greeting. And two entries: ''Hello World!'' and ''Good bye World''.</translate>

<translate>
== Packaging the component == 
</translate>

<translate>
Content of your code directory</translate>

* ''[[#helloworld.xml|helloworld.xml]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Adding_a_view_to_the_site_part#site/helloworld.php|site/helloworld.php]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Developing_a_Basic_Component#index.html|site/index.html]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Adding_a_view_to_the_site_part#site/controller.php|site/controller.php]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Developing_a_Basic_Component#index.html|site/views/index.html]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Developing_a_Basic_Component#index.html|site/views/helloworld/index.html]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Adding_a_view_to_the_site_part#site/views/helloworld/view.html.php|site/views/helloworld/view.html.php]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Developing_a_Basic_Component#index.html|site/views/helloworld/tmpl/index.html]]''
* ''[[#site/views/helloworld/tmpl/default.xml|site/views/helloworld/tmpl/default.xml]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Adding_a_view_to_the_site_part#site/views/helloworld/tmpl/default.php|site/views/helloworld/tmpl/default.php]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Developing_a_Basic_Component#index.html|site/models/index.html]]''
* ''[[#site/models/helloworld.php|site/models/helloworld.php]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Developing_a_Basic_Component#index.html|admin/index.html]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Developing_a_Basic_Component#admin/helloworld.php|admin/helloworld.php]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Developing_a_Basic_Component#index.html|admin/sql/index.html]]''
* ''[[#admin/sql/install.mysql.utf8.sql|admin/sql/install.mysql.utf8.sql]]''
* ''[[#admin/sql/uninstall.mysql.utf8.sql|admin/sql/uninstall.mysql.utf8.sql]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Developing_a_Basic_Component#index.html|admin/sql/updates/index.html]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Developing_a_Basic_Component#index.html|admin/sql/updates/mysql/index.html]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Developing_a_Basic_Component#admin/sql/updates/mysql/0.0.1.sql|admin/sql/updates/mysql/0.0.1.sql]]''
* ''[[#admin/sql/install.mysql.utf8.sql|admin/sql/updates/mysql/0.0.6.sql]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Developing_a_Basic_Component#index.html|admin/models/index.html]]''
* ''[[#admin/models/helloworld.php|admin/models/helloworld.php]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Developing_a_Basic_Component#index.html|admin/models/fields/index.html]]''
* ''[[#admin/models/fields/helloworld.php]]''
* ''[[S:MyLanguage/J3.2:Developing_an_MVC_Component/Developing_a_Basic_Component#index.html|admin/tables/index.html]]''
* ''[[#admin/tables/helloworld.php|admin/tables/helloworld.php]]''

<translate>
Create a compressed file of this directory or directly download the [https://github.com/scionescire/Joomla-3.2-Hello-World-Component/archive/step-6-using-the-database.zip archive] and install it using the extension manager of Joomla. You can add a menu item of this component using the menu manager in the backend.</translate>

{{notice|<translate>
Please create a pull request or issue at https://github.com/joomla/Joomla-3.2-Hello-World-Component for any code discrepancies or if editing any of the source code on this page.</translate>}}

<translate>
== Contributors == 
</translate>
*[[User:cdemko|Christophe Demko]]
*[[User:oaksu|Ozgur Aksu]]
*[[User:scionescire|Scionescire]]

<div class="row">
<div class="large-6 columns">{{Basic button|<translate>
S:MyLanguage/J3.x:Developing_an_MVC_Component/Adding a variable request in the menu type|Prev: Adding a variable request in the menu type</translate>|class=expand success}}</div>
<div class="large-6 columns">{{Basic button|<translate>
S:MyLanguage/J3.x:Developing_an_MVC_Component/Basic backend|Next: Basic backend</translate>|class=expand}}</div>
</div>

<noinclude>
[[Category:Joomla! 3.x{{#translation:}}]]
[[Category:Joomla! 3.0{{#translation:}}]]
[[Category:Joomla! 3.1{{#translation:}}]]
[[Category:Joomla! 3.2{{#translation:}}]]
[[Category:Joomla! 3.3{{#translation:}}]]
[[Category:Joomla! 3.4{{#translation:}}]]
[[Category:Beginner Development{{#translation:}}]]
[[Category:Component Development{{#translation:}}]]
[[Category:Tutorials{{#translation:}}]]
[[Category:Tutorials in a Series{{#translation:}}]]
</noinclude>

J3.x talk:How to add tooltips to your Joomla! website

2016-07-13T11:23:01Z

JoomliC: Update, remove, or archive ?

Hi there!
I would like to add doc for new tooltip positions and auto-rtl (Bootstrap tooltip extended), but realize the doc about tooltip is outdated.
Should i remove all by a new updated doc for Joomla 3, or keep somewhere old doc for 1.5 and 2.5 ?
Thanks!
Cyril
[[User:JoomliC|JoomliC]] ([[User talk:JoomliC|talk]]) 06:23, 13 July 2016 (CDT)

Joomla! Documentation - User contributions [en]

J3.x:Developing an MVC Component/Using the database

J3.x talk:How to add tooltips to your Joomla! website