Creating a Site Module for Joomla 3.x – Part 4

Creating a Database Table for Our Module

Our random quote module, to be of any use, needs to be able to have some data associated with it. While we could include all the quotes and sources and such in the code as some sort of data structure, it would over time be difficult to manage and update. It would be better if this kind of information could be held in a database table. In this lesson we will talk about a method we can use to create our database table, and the SQL that Joomla will need to have when we install our module to additionally create a table in the database and populate it with data.

This tutorial assumes you have some knowledge with working with a database and using database queries. If I get enough requests, I may be willing to create some tutorials on that subject matter. Let’s get started.

To begin with, our random quote module will display the name of the person the quote is from, the actual quote, and the source of the quote to our visiting guest on the front end of the site. Knowing this we will create a spreadsheet in LibreOffice Calc program with three columns called Name, Quote and Source. I have decided to use quote from the Founding Fathers of the United States of America. You can use what ever quotes you like, or you can download the source files of this tutorial from my website where you downloaded this document. Now, for each row, I will include the data for each quote. See the image below:

Continue adding quotes until you have as many as you want. (I stopped at fifty quotes.) Now save your spreadsheet in case you need it again later. (I saved mine as Founder Quotes.) The purpose of entering all this information in a spreadsheet is to have a simple way of getting a comma separated value (csv) file of the data. One of the features of phpMyAdmin is that you can use just such a file to enter data into a database table. Let’s continue then.

To save your spreadsheet as a comma separated values (csv) file, click File | Save As… from the menu. In the select box that reads “All Formats” select Text CSV (.csv) from the list, give it a filename and select the location you want to save it and finally click the save button. See the image to the left.

When LibreOffice Calc asks you about the document formatting, click the button that reads “Use Text CSV Format” Next the Export Text File window opens. Make sure the following options are set. Character set: Unicode (UTF-8), Field delimiter ‘,’ (a comma), Text delimiter ‘”’ (a double quote), Save cell content as shown is checked and Quote all text cells is checked. Then click the OK button to save the file. See the image below.

We now have our quotes saved in a comma separated value (csv) file. We can use this file to populate the database table that we will create in phpMyAdmin. So let’s do that next.

Creating a table with phpMyadmin

Open your browser and point it to http://joom.dev/phpmyadmin. If the page opens to show a bunch of warnings, remember we turned the error reporting on for PHP to the highest available level. Scroll to the bottom of the page and click the Ignore All button and they will go away. Now log into phpMyAdmin with your MySQL credentials. The user name of root and the password you saved from the LAMP tutorial. Now that you are logged into phpMyAdmin, on the left click the database that you created when you installed Joomla. (I named mine “joom3”). When you select the database on the left, all the tables associated with the database will be listed on the right hand side of the screen. Scroll to the bottom of the screen and you will see a form that will allow you to create a new table for the selected database. See the image below:

To create our table we want to take note of the prefix that is appended to all the tables in Joomla. Remember when we installed Joomla that we had the option of creating our own table prefix or use the random prefix that the installer came up with. I elected to use the random one that the Joomla installer picked. That prefix is the letters and underscore that you see at the beginning of each table name in Joomla’s database. In the Name field of the Create table form, enter in the prefix for your Joomla installation followed by mod_random_quote. So in my example you see that I named mine c12q4_mod_random_quote. It does not really matter what you call your table for your module but you will want to make sure that it doesn’t collide with one of Joomla’s tables or another extension you may install later. Since I have only one table for the module, I just named it the same as the module. Now for the number of column change it to read 4. Our table will have four columns, one for an index and the other three are for the Name, Quote and Source for our data that we have in our CSV file from the last step. Finally click the go button to instruct phpMyAdmin to create the table.

The next screen displays a form for us to create our table with where each column is a row in the form. As I stated earlier, we have four columns in our table. Fill out form as follows. For the first column set the Name to id, type is int, attributes is unsigned, index is primary, check the A.I. (Auto Index) checkbox and finally give the column a comment of index. For the second column of our table set the Name to name, type to varchar, length is 75 and comment to Author Name. For the third column set the Name to quote, type to text and the comment to Author Quote. Finally, for the fourth column of our table, set the Name to source, type to varchar, length to 200 and comment to Source Document. You can leave the collation to default and set the storage engine to InnoDB. If you like, you can enter a comment on the table. Maybe, Table for mod_random_quote or similar. Lastly, click the save button on the form. See the image below.

Filling the table with our quotes

With the table created we can now import our CSV file into the table. With our table selected in Joomla’s database, click the Import tab at the top of the screen. The page displayed will give you the option of the type of data you wish to import into the table and some options of how and what to import. Start by clicking the Browse button in the file to import section and select your CSV file that you created earlier. The character set for the file should be set to UTF-8.

The Next section is labeled Partial import. Here we want to change the selector to 1, indicating we want to skip the first line of the file. Recall that the first line in our file has the field names, Name, Quote and Source.

The other options can be left alone and the section that reads file format should be CSV. Finally, we come to the last section of the page that reads Format-specific options, these can be left as they are except for column names. Our CSV file only contains three of the four columns of our table. So we need to map each of our columns as they appear in the CSV file to the table. We created the table in the same order as our CSV file so we enter in “name, quote, source”. These are the three columns our CSV file maps to and in that order. Finally, click the Go button to import our data. If everything was good you will see a successful message at the top of the screen. See the screen shots below:

Exporting our table as a SQL file

Now that we have our data imported into the table we created, we could use it as is with our module. However, if we want to give our module to someone else we cannot expect the end user to create the table and import the data. We need a way of packaging it with our archive. To do this, we need a SQL file that we can use with our module that Joomla can use to both create the table and fill it with our quotes. Before we get into the intricacies of how all that works, let’s use phpMyAdmin to help us out by exporting our table a SQL file that we can later massage into our purposes.

Log into phpMyAdmin if you are not already. Select the Joomla database and then the table we created earlier. With the table selected, click the Export tab at the top of the page. PhpMyAdmin gives a lot of options as to how you can export data and the format that it is in. Fortunately, the defaults of the quick export method meets all of our needs. On this page under export method select quick. Make sure the format is set to SQL. That is all we need to do, click the go button to export the data. Your browser should prompt you to save the file. Be sure to move this file to your project directory so you don’t lose track of it. Also note that the file name will be the name of the table with a .sql on the end of it. See the image below.

If you open the file that you just downloaded, you will see all the SQL command to create the table and insert the data into it. This is what Joomla will need to create and insert data into the table for us, only it will need to be modified a little so that Joomla can use the file. Let’s talk about Joomla and installing to the database next.

Joomla and the database

Joomla supports a growing number of databases including MySQL, MSSQL, PostgreSQL and more. However, we are only going to focus on MySQL in this tutorial. Joomla manages the installation, update and removal of tables and data for components through its manifest file. Each of these three options, install, update and uninstall [removing a table(s)] is controlled by a tag. I will cover them briefly.

The <install> tag will point to the SQL file Joomla will execute when it is installing an extension for the first time. This SQL file will create the tables and insert any data required into the tables for the extension to work. The <uninstall> tag will point to the SQL file Joomla will run when it is uninstalling an extension. This SQL file will be responsible for dropping the table from the database. Some developers do not drop their tables in the event a user wants to remove there extension and then reinstall it to fix a problem. Unless a user was playing with the code of the extension in question, I cannot think of any reason to leave the tables in the database. I think it would be better to remove them. Finally the <update> tag will point to the SQL files that would need to be run in order to update the extension to the current version. This is a topic that should be discussed in detail and we will do so when we start versioning of our component and do the first update. Now let’s add the files to our project that will enable us to install data into the database. We will go over these files in detail as we encounter them. Add the files and folders to your project marked in red.

mod_random_quote/
mod_random_quote/index.html
mod_random_quote/mod_random_quote.php
mod_random_quote/mod_random_quote.xml
mod_random_quote/language/
mod_random_quote/language/index.html
mod_random_quote/language/en-GB/
mod_random_quote/language/en-GB/index.html
mod_random_quote/language/en-GB/en-GB.mod_random_quote.ini
mod_random_quote/language/en-GB/en-GB.mod_random_quote.sys.ini
mod_random_quote/sql/
mod_random_quote/sql/index.html
mod_random_quote/sql/install.mysql.utf8.sql
mod_random_quote/sql/uninstall.mysql.utf8.sql
mod_random_quote/tmpl/
mod_random_quote/tmpl/default.php
mod_random_quote/tmpl/index.html

The sql folder will eventually hold all of our sql files to install, uninstall and update. For now though we are only concentrating on the first two. The index.html file, as you know, is to prevent the web server from displaying a directory listing. The other two files bear some discussion.

install.mysql.utf8.sql
uninstall.mysql.utf8.sql

These two files are executed when the extension is installed and uninstalled respectively and like most everything else with Joomla the filenames follow a predefined naming convention. I say that tongue in cheek because the manifest file explicitly names the files to run, but I think it is better to follow tradition. The filenames are made of the following segments. (un)install.<database-engine>.<file-encoding>.sql. So the install file for MySQL using UTF-8 encoding will be, install.mysql.utf8.sql, likewise an uninstall file for Microsoft SQL server using UTF-8 encoding will be uninstall.sqlsrv.utf8.sql.

Now that we have created the two empty files to hold the SQL commands that Joomla will need to execute when we install or uninstall our module, we need to populate these files with the actual SQL commands needed to do the job. Open the uninstall.mysql.utf8.sql file and add the following line to it and save the file.

DROP TABLE IF EXISTS `#__mod_random_quote`

This single line drops (or deletes) a table from the database if it exists. If you are observant you will have noticed that the prefix on the table start with the #__ characters (a pound sign followed by two underscore characters). Remember Joomla created a random table prefix for the tables when we installed. Because of this, we have no clue what another installed copy of Joomla would have used, so Joomla uses these special #__ characters as a macro of sorts. When Joomla sees this, it is replaced with the prefix of your tables. In my installation the tables are prefixes with c12q4_ so when Joomla runs this command it will drop a table named c12q4_mod_random_quote if it exists.

Now for the install file. Open the install.mysql.utf8.sql file and copy the contents of the SQL file you created with phpMyAdmin earlier and paste it into this file. Before we can use these SQL commands in Joomla we need to make some changes. Looking at the file, lines that start with -- and /* are comments, so feel free to delete them to make the file size a little smaller. Delete any lines that start with the key word “set”. What you should be left with is four SQL commands, One that creates a table, one that inserts the data and two that alter the table. In each of these commands you will see the name of the table that the command affects inside of backticks. Notice that when phpMyAdmin created this file it includes the table prefix as part of the table name. Mine, for example is c12q4_. Go through, either manually or using search and replace, and remove the prefix from the table name and replace it with the #__ characters. So, for example, I would change mine from `c12q4_mod_random_quote` to `#__mod_random_quote`. Recall from above, Joomla uses these characters as a macro so that your database table prefix is used. With all the changes made to the file save it.

Update the Manifest File

Open the mod_random_quote.xml file and add the following lines in red.

 

<?xml version="1.0" encoding="utf-8"?>
<extension type="module" version="3.5" client="site" method="upgrade">

   <name>MOD_RANDOM_QUOTE</name>
   <author>Joe Hildreth</author>
   <creationDate>01 JUL 2016</creationDate>
   <copyright>(c)2016 Joe Hildreth, All rights Reserved</copyright>
   <license>GNU General Public License V2 or later</license>
   <authorEmail>This email address is being protected from spambots. You need JavaScript enabled to view it.</authorEmail>
   <authorUrl>http://www.myheap.com</authorUrl>
   <version>1.0.0</version>
   <description>MOD_RANDOM_QUOTE_DESCRIPTION</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>

   <files>
      <folder>language</folder>
      <folder>sql</folder>
      <folder>tmpl</folder>
      <filename module="mod_random_quote">mod_random_quote.php</filename>
      <filename>mod_random_quote.xml</filename>
      <filename>index.html</filename>
   </files>

</extension>

Having some practice with these files, you know that the <folder> tag will copy over the sql folder and its contents. New, however, are the <install> and <uninstall> tags. I wrote briefly about them above, but I will recap. Recall that Joomla can use several databases and can create tables and data for those databases. When we install the module for the first time, the file pointed to inside the <install> tags will be run. Joomla will look for the driver that matches it current running database server and run the SQL file pointed to by the <file> tag. So examining the code above, we see there are two attributes included on the file tag. These attributes are driver and charset. The driver attribute identifies the database server that the included file is intended to run on and the charset identifies what character encoding the file is in. So above we see that the file inside the file tag above is written using utf8 encoding and is intended to be run on a MySQL server. Supporting other database servers only entails creating the SQL file for the install and adding another file tag with the driver and encoding listed. For example, a file for Microsoft SQL Server would look something like the following:

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

Testing our work

Before we can test our work, we need to delete the table we created earlier from the Joomla database. To do so, log into phpMyAdmin and select the Joomla database on the left hand column of the screen. On the right hand side of the screen, locate the table and click the drop button in the same row as the table name. (Be sure you are dropping the correct one.) The phpMyAdmin application will ask you to confirm that you want to delete the table. Click the OK button to finish the job. See the screen image below.

With the files created and and the manifest updated to include our new files, zip the project up and install it on your development Joomla site. Now, open up phpMyAdmin and confirm that Joomla created the database table. Once you are satisfied, uninstall the module and then go back to phpMyAdmin and confirm that the table was deleted.

That concludes this tutorial. In the next we will continue to develop the module by adding code to talk to the database and displaying a random quote.

END OF TUTORIAL