J1.5

Difference between revisions of "SQLMultiSelectX"

From Joomla! Documentation

(First draft)
 
(Fix red links)
 
(4 intermediate revisions by 4 users not shown)
Line 3: Line 3:
 
===Functionality===
 
===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.
 
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 [[Adding_a_multiple_item_select_list_parameter_type|Multiple Select List parameter type]].
+
Therefore, SQLMultiListX can be viewed as a hybrid of [[J1.5:List parameter type|List parameter type]], [[J1.5:Sql parameter type|Sql parameter type]] and [[Adding_a_multiple_item_select_list_parameter_type|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.
 
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==
 
==Rendering the parameter==
Line 56: Line 56:
 
                 // Query items for list.
 
                 // Query items for list.
 
$db = & JFactory::getDBO();
 
$db = & JFactory::getDBO();
$db->setQuery($node->attributes('query'));
+
$db->setQuery($node->attributes('sql'));
 
$key = ($node->attributes('key_field') ? $node->attributes('key_field') : 'value');
 
$key = ($node->attributes('key_field') ? $node->attributes('key_field') : 'value');
 
$val = ($node->attributes('value_field') ? $node->attributes('value_field') : $name);
 
$val = ($node->attributes('value_field') ? $node->attributes('value_field') : $name);
Line 90: Line 90:
 
<source lang="xml">
 
<source lang="xml">
 
<param name="field-name" type="sqlmultilistx" sql="SELECT * FROM #__jos_mytable" key_field="id" value_field="name" default="OPT1"
 
<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="description="Use Ctrl-click to select more than one item.">
+
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="-2">Title for option 1</option>
 
<option value="-1">Title for option 2</option>
 
<option value="-1">Title for option 2</option>
Line 130: Line 130:
 
==Saving parameter values to a database==
 
==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
 
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
+
a database table.  Assuming you are doing this using the [[API15:JTable|JTable]] API class, then you will need to add the following code
to the [[JTable]] extended class for the appropriate table.
+
to the [[API15:JTable|JTable]] extended class for the appropriate table.
 
<source lang="php">
 
<source lang="php">
 
     /**
 
     /**
Line 158: Line 158:
 
separator, change the comma to whatever character you want to use in the ''implode'' function call in the ''bind()'' method
 
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.
 
and the corresponding ''explode'' function call in the ''sqlmultilistx.php'' file.
===See also===
+
==See also==
 
* [[Custom parameter types]]
 
* [[Custom parameter types]]
 
* [[Adding_a_multiple_item_select_list_parameter_type|Multi Select List parameter type]]
 
* [[Adding_a_multiple_item_select_list_parameter_type|Multi Select List parameter type]]
 
<noinclude>[[Category:Development]][[Category:Parameters]]</noinclude>
 
<noinclude>[[Category:Development]][[Category:Parameters]]</noinclude>

Latest revision as of 03:06, 3 July 2013

The "J1.5" namespace is an archived namespace. This page contains information for a Joomla! version which is no longer supported. It exists only as a historical reference, it will not be improved and its content may be incomplete and/or contain broken links.

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.

Functionality[edit]

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[edit]

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[edit]

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[edit]

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[edit]