Using the SQL field type with multiple selection in Joomla! 3.x

01.02.2014

In this blog post, I'll show you how we typically use the SQL field type in our custom-built Joomla! modules.

We do a ton of custom development for our clients including—from time to time—building custom Joomla! modules.

For our own site, we built a component that will manage our portfolio (work samples). Naturally, we needed the ability to show some of these work samples on various pages.

Enter a fancy new Zuno-built Joomla! Module—mod_zportfolio.

In this blog post, I'll show you how we typically use the SQL field type in our custom-built Joomla! modules.

We do a ton of custom development for our clients including—from time to time—building custom Joomla! modules.

For our own site, we built a component that will manage our portfolio (work samples). Naturally, we needed the ability to show some of these work samples on various pages.

Enter a fancy new Zuno-built Joomla! Module—mod_zportfolio.

In our component, we place individual work samples into a category and assign tags to it where appropriate.

In our module, we need the ability to show the samples based on the category they're in or based on the tags assigned to it.

Here's how the module parameters look in the admin:

For the purposes of this tutorial, I'm just going to show you how to incorporate the SQL form field type into your own module.

Let's look at the Tags field (which is in our module's XML file):

<field name="tags" type="sql" default="0" label="Tags" query="SELECT id AS value, title AS tags FROM #__tags" multiple="multiple">
	<option value="0">Show All</option> 
</field>

Attributes

  • name: This is the unique name of the field.
  • type: Must be 'sql'.
  • default: Can be 0, blank, or any ID of a tag you want selected by default.
  • label: The title of the field that will show up in the module admin.
  • query: The SQL query to get the desired data.
  • multiple: This turns your regular (boring) drop-down list into a gorgeous Joomla! multi-select list.

Important note about the query attribute:

The result of your query MUST contain data for a "value" column AND a "tags" column. The latter representing whatever you chose to name the field (i.e., if for the name attribute, you entered "myField", your query result MUST contain a column named "myField").

The "value" column contains the data that will be stored. The "tags" column contains the titles that will be shown in the multi-select list.

For clarity's sake, here's the <select> field generated by our sql field:

<select multiple="" name="jform[params][tags][]" id="jform_params_tags" style="display: none;" class="chzn-done"> 
	<option value="0">Show All</option> 
	<option value="1">Brand Development</option> 
	<option value="2">Joomla!</option> 
	<option value="3">Logo Design</option> 
	<option value="4">Web Design</option> 
	<option value="5">Mobile App Design</option> 
	<option value="6">eCommerce</option> 
	<option value="7">Magento</option> 
	<option value="8">UI design</option> 
	<option value="9">Responsive design</option> 
	<option value="10">UX</option> 
</select>

Note the values and text of the <option>s. This will output a fancy list in the admin:

Now we can access the data in our main module PHP file (mod_zportfolio.php) like so:

$tags = $params->get('tags');

In this particular module, we pass the 'tags' array to our module helper file to get our work samples:

$portfolio = modZportfolioHelper::getTags( $tags );

The above code is beyond the scope of this post, but if you'd like to learn more about creating your own module head over to the Joomla! Documentation. They have a great write up on how you can create a simple module.