Difference between revisions of "Creating a simple module/Using the Database"

From Joomla! Documentation

< J2.5:Creating a simple module
(Finishing the section)
(Fix typo)
Line 55: Line 55:
<source lang="sql">
<source lang="sql">
DROP TABLE IF EXISTS `#__shoutbox`
DROP TABLE IF EXISTS `#__helloworld`

Revision as of 11:02, 10 May 2013

Many modules in Joomla require using a database. It is assumed in this tutorial that you already understand the basics of using the JDatabase class. If you don't please read the documentation on accessing the database using JDatabase before continuing this tutorial

Creating a table on install

To create the xml table on install we are going to add the following lines into mod_helloworld.xml:

         <file driver="mysql" charset="utf8">sql/mysql/install.mysql.utf8.sql</file>
         <file driver="sqlazure" charset="utf8">sql/sqlazure/install.sqlazure.utf8.sql</file>
         <file driver="mysql" charset="utf8">sql/mysql/uninstall.mysql.utf8.sql</file>
         <file driver="sqlazure" charset="utf8">sql/sqlazure/uninstall.sqlazure.utf8.sql</file>
        <schemapath type="mysql">sql/mysql/updates</schemapath> 
	<schemapath type="sqlazure">sql/sqlazure/updates</schemapath> 

There are 3 sections to this code:

  • The install tag adds the database table
  • The uninstall tag removes the database table if the module is uninstalled. Note that not all modules will want to use this feature (and it's not required).
  • The update tag will update the databases if a database needs to be amended when updating the module.

Note that we have both schemas for MySQL and Microsoft SQL - again you can choose to tailor your module for one or both of these systems.

In this example we will just show the example files for the MySQL database. Creating the Microsoft SQL Server will be left as an exercise for the reader.

In our install.mysql.utf8.sql file we will create the table and place some hellos into it

CREATE TABLE IF NOT EXISTS `#__helloworld` (
	`hello` text NOT NULL,
  PRIMARY KEY (`id`)
INSERT INTO `#__helloworld` (`hello`) VALUES ('Hello');
INSERT INTO `#__helloworld` (`hello`) VALUES ('Bonjour');
INSERT INTO `#__helloworld` (`hello`) VALUES ('Hola');

In the uninstall file we'll just remove the table.

DROP TABLE IF EXISTS `#__helloworld`

Finally we'll just leave a placeholder in the updates file. There is an SQL file for each component version. Each file name must match the version string in the manifest file for that version. Joomla uses this string to determine which SQL files(s) to execute, and in what order they will be executed.

Important Note: These files are also used to set the version number in the #__schemas table. This version number must be present in the current version of the component in order for the new SQL files to be run during the update. For example, if you have version 1.0 and are updating to version 1.1, the 1.1.sql file will not be executed if there was no 1.0.sql file in the 1.0 release. For this reason, it is good practice to have a SQL update file for each version, even if there is no SQL change in that version.

# Placeholder file for database changes for version 1.0.0

Making the request in the helper file