Actions

J1.5

Difference between revisions of "SQLMultiSelectX"

From Joomla! Documentation

m (In one of the examples, the description argument for the param was in as description="description=" ect, breaking the XML.)
m (Wilsonge moved page SQLMultiSelectX to J1.5:SQLMultiSelectX: Move to 1.5 namespace)

Revision as of 15:53, 23 May 2013

Replacement filing cabinet.png
This Namespace has been archived - Please Do Not Edit or Create Pages in this namespace. Pages contain information for a Joomla! version which is no longer supported. It exists only as a historical reference, will not be improved and its content may be incomplete.

SQLMultiListX is a modification of Multiple Select List which enables developers to create a multiple select list with an SQL query and also mix explicitly specified options along with the result of the SQL result.

Contents

Functionality

The SQLMultiListX element will create a list box which consists of the user specified options and the results from an SQL query. In addition to this, it will be possible for the users to select multiple items of the list box and save the preferences. Therefore, SQLMultiListX can be viewed as a hybrid of List parameter type, Sql parameter type and Multiple Select List parameter type. The options that are specified by the user explicitly via the <option> tags will be rendered first followed with the result of the SQL query. It is up to the developer to ensure that the explicitly specified options do not have values that conflict with the SQL result.

Rendering the parameter

The code required to render the multiple item select list is here:

<?php
/**
* @copyright    Copyright (C) 2009 Open Source Matters. All rights reserved.
* @license      GNU/GPL
*/
 
// Check to ensure this file is within the rest of the framework
defined('JPATH_BASE') or die();
 
/**
 * Renders a multiple item select element 
 * using SQL result and explicitly specified params
 *
 */
 
class JElementSQLMultiListX extends JElement
{
        /**
        * Element name
        *
        * @access       protected
        * @var          string
        */
        var    $_name = 'SQLMultiListX';
 
        function fetchElement($name, $value, &$node, $control_name)
        {
                // Base name of the HTML control.
                $ctrl  = $control_name .'['. $name .']';
 
                // Construct the various argument calls that are supported.
                $attribs       = ' ';
                if ($v = $node->attributes( 'size' )) {
                        $attribs       .= 'size="'.$v.'"';
                }
                if ($v = $node->attributes( 'class' )) {
                        $attribs       .= 'class="'.$v.'"';
                } else {
                        $attribs       .= 'class="inputbox"';
                }
                if ($m = $node->attributes( 'multiple' ))
                {
                        $attribs       .= ' multiple="multiple"';
                        $ctrl          .= '[]';
                }
 
                // Query items for list.
                                $db                     = & JFactory::getDBO();
                                $db->setQuery($node->attributes('sql'));
                                $key = ($node->attributes('key_field') ? $node->attributes('key_field') : 'value');
                                $val = ($node->attributes('value_field') ? $node->attributes('value_field') : $name);
 
                $options = array ();
                foreach ($node->children() as $option)
                {
                        $options[]= array($key=> $option->attributes('value'),$val => $option->data());
                }
 
                $rows = $db->loadAssocList();
                foreach ($rows as $row){
                        $options[]=array($key=>$row[$key],$val=>$row[$val]);
                }
                if($options){
                        return JHTML::_('select.genericlist',$options, $ctrl, $attribs, $key, $val, $value, $control_name.$name);
                }
        }
}

This should be saved in a file called sqlmultilistx.php in a separate directory within your component or module directory. It is recommended that for consistency this directory is called elements, but this is not required. For example, if you want to add support for this field into a component called mycomponent, then you would copy-paste the above code into this file:

/administrator/components/com_mycomponent/elements/sqlmultilistx.php

Don't forget to add an empty index.html file into the elements directory to prevent directory listing.

Adding the element to a form

Parameters are rendered using form definition information contained in an XML file. For example, for a component this file will typically be:

/administrator/components/com_mycomponent/config.xml

In this file, you should add one or more selection list parameters by adding a <param> element into an appropriate <params> parameter group, like this:

<param name="field-name" type="sqlmultilistx" sql="SELECT * FROM #__jos_mytable" key_field="id" value_field="name" default="OPT1"
multiple="multiple" label="Select one or more items" description="Use Ctrl-click to select more than one item.">
        <option value="-2">Title for option 1</option>
        <option value="-1">Title for option 2</option>
</param>

The arguments are as follows:

  • type (mandatory) must be sqlmultilistx.
  • name (mandatory) is the unique name of the parameter.
  • label (mandatory) (translatable) is the descriptive title of the field.
  • sql (optional) is the sql query to populate the list box.
  • key_field (optional) is the field of the SQL result that should be mapped as key. Identical to key_field of the sql parameter type.
  • value_field (optional) is the field of the SQL result that should be mapped as value. Identical to value_field of the sql parameter type.
  • default (optional) is a comma-separated list of default values.
  • size (optional) is the number of option rows that will be visible in the list. If omitted, all rows will be displayed.
  • description (optional) (translatable) is text that will be shown as a tooltip when the user moves the mouse over the drop-down box.
  • class (optional) is a CSS class name for the HTML form field. If omitted this will default to 'inputbox'.
  • multiple (optional) indicates whether the field will allow more than one option row to be selected.

The XML <param> element must include one or more <option> elements which define the list items. The text between the <option> and </option> tags is what will be shown in the option list and is a translatable string. The <option> tag takes the following argument:

  • value (mandatory) is the value that will be saved for the parameter if this item is selected.

Since this is a custom parameter type, you will also need to tell Joomla where to find the code to render the parameter. You do this by adding an addPath argument to the containing <params> element. For example, to add a parameter group containing just a single multiple element selection list to your component, the XML would look like this:

<params group="group-name" addPath="/administrator/components/com_mycomponent/elements">
        <param name="field-name" type="sqlmultilistx" sql="SELECT * FROM #__jos_mytable" key_field="id" value_field="name" 
default="OPT1" multiple="multiple" label="Select one or more items" description="Use Ctrl-click to select more than one item.">
                <option value="-2">Title for option 1</option>
                <option value="-1">Title for option 2</option>
        </param>
</params>

Only a single addPath argument is permitted per parameter group which is why it's a good idea to gather all your custom parameter code into a single elements directory.

Saving parameter values to a database

The most common use for this custom parameter type will involve saving whatever values the user selected into a field in a database table. Assuming you are doing this using the JTable API class, then you will need to add the following code to the JTable extended class for the appropriate table.

    /**
     * Overloaded bind function
     *
     * @param array $array  Array or object of values to bind
     * @param mixed $ignore Array or space separated list of fields not to bind
     *
     * @return null|string Success returns null, failure returns an error
     * @access public
     * @see    JTable:bind
     */
    function bind( $array, $ignore = '' )
    {
        if (key_exists( 'field-name', $array ) && is_array( $array['field-name'] )) {
                $array['field-name'] = implode( ',', $array['field-name'] );
        }
 
        return parent::bind( $array, $ignore );
    }

Note that field-name must match the name you gave the field in the XML form definition file and the name given to the database field.

Using this code the data will be stored as a comma-separated list of values in the database field. To use a different separator, change the comma to whatever character you want to use in the implode function call in the bind() method and the corresponding explode function call in the sqlmultilistx.php file.

See also