Sql parameter type
From Joomla! Documentation
Revision as of 13:07, 16 August 2008 by Chris Davenport
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.
[[[ Note: This parameter type does not currently work – see tracker #12260. ]]]
- type (mandatory) must be sql.
- name (mandatory) is the unique name of the parameter. This must be the same as the query results column that you want to be displayed in the drop-down list.
- 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 'id' which will hold the values of the list items; the other called 'title' containing the text shown in the drop-down list.
- default (optional) is the default value. This is the value of the 'id' field.
- description (optional) (translatable) is text that will be shown as a tooltip when the user moves the mouse over the drop-down box.
Example XML parameter definition:
<param name="title" type="sql" default="" label="Select an article" description="" query="SELECT id, title FROM #__content" />
Note that the SQL statement 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 that 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
If the table you are querying does not have an 'id' or 'title' column, you can use an AS clause to alias another column. For example, suppose for some reason you wanted to list the date/time of each article in the list. Then you could use this SQL statement:
SELECT id, created AS title FROM #__content
You can also assemble or calculate fields in SQL. 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