Difference between revisions of "Sql parameter type"
From Joomla! Documentation
(placing proper double-quotes for key_field and value_field according to the DTD)
m (Fixed category mess)
|Line 39:||Line 39:|
=== See also ===
=== See also ===
* [[Standard types|of standard types]]
Revision as of 13:29, 17 January 2011
The sql parameter type provides a drop down list of entries obtained by running a query on the Joomla database. If the parameter has a value saved, this value is selected when the page is first loaded. If not, the default value (if any) is selected.
- type (mandatory) must be sql.
- name (mandatory) is the unique name of the parameter. This must match the name of the query results column that contains the values that will be shown to the user in the drop-down list, unless a different name is specified in the value_field attribute.
- label (mandatory) (translatable) is the descriptive title of the field.
- query (mandatory) is the SQL query which will provide the data for the drop-down list. The query must return two columns; one called 'value' (unless overridden by the key_field attribute) which will hold the values of the list items; the other called the same as the value of the name attribute (unless overridden by the value_field attribute) containing the text to be shown in the drop-down list.
- default (optional) is the default value. This is the value of the 'value' field, unless overridden by the key_field attribute.
- description (optional) (translatable) is text that will be shown as a tooltip when the user moves the mouse over the drop-down box.
- key_field (optional) is the name of the column that will contain values for the parameter. If omitted then the column called 'value' will be used, if it exists.
- value_field (optional) is the name of the column that will contain values to be shown to the user in the drop-down list. If omitted then the column with the same name as the name attribute will be used, if it exists.
Example XML parameter definition:
<param name="title" type="sql" default="10" label="Select an article" query="SELECT id AS value, title FROM #__content" />
Notice that an AS clause has been used in this example because the jos_content table does not have a column called 'value'. In fact very few tables in the Joomla database have a column called 'value'. Alternatively, you can use a key_field attribute to define the column to be instead of 'value':
<param name="title" type="sql" default="10" label="Select an article" query="SELECT id, title FROM #__content" key_field="id" />
This will give identical results to the previous example.
Both column names may need to be aliased. For example, suppose you want your field to be called 'myfield' instead of 'title' in the previous example. Then you can do this:
<param name="myfield" type="sql" default="10" label="Select an article" query="SELECT id AS value, title AS myfield FROM #__content" />
<param name="myfield" type="sql" default="10" label="Select an article" query="SELECT id, title FROM #__content" key_field="id" value_field="title" />
You can also assemble or calculate fields in the SQL statement. For example, suppose you wanted to append the created date/time of each article to the article title in the list. Then you could use this SQL statement:
SELECT id, concat( title, ' (', created, ')') AS title FROM #__content
Note: The SQL statements will need to be correct for the type and version of the underlying database that Joomla is running on. This will most likely be a version of MySQL, but it could be something else. There is no capability to query databases other than the one Joomla itself is running on.
Note: As shown in these examples, the database prefix can be entered in the form '#_' (hash-underscore), in which case it will automatically be replaced by the database prefix used by Joomla.