The sql form field type provides a drop down list of entries obtained by running a query on the Joomla database. If the field has a value saved, this value is selected when the page is first loaded. If not, the default value (if any) is selected.

Example XML field definition:

<field
name="title"
type="sql"
default="10"
label="Select an article"
query="SELECT id AS value, title FROM #__content"
/>

An AS clause has been used in this example because the _content table does not have a column called 'value'. Alternatively, you can use a key_field attribute to define the column to be used instead of 'value':

<field
name="title"
type="sql"
default="10"
label="Select an article"
query="SELECT id, title FROM #__content"
key_field="id"
value_field="title"
multiple="true"
/>

Parameters

Mandatory Parameters

  • type must be sql.
  • name is the unique name of the field.
  • label (translatable) is the descriptive title of the field.
  • query 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.

Optional Parameters

  • default is the default value. This is the value of the 'value' column, unless overridden by the key_field attribute.
  • description (translatable) is text that will be shown as a tooltip when the user moves the mouse over the drop-down box.
  • multiple turns the field into a multi-selector. Use multiple="true".
  • key_field 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 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.
  • translate will translate the output of the value_field if set to true. It defaults to false.
  • header (translatable) will add an entry, with an empty value, at the top of the list of options. This is usually used to add a "- Select something -" entry to the list.

SQL Query Parameters

  • sql_select (mandatory if not using the query attribute) is the SELECT clause of the SQL statement. Only one such clause is permitted.
  • sql_from (mandatory if not using the query attribute) is the FROM clause of the SQL statement.
  • sql_join is the LEFT JOIN clause of the SQL statement. Only one such clause is permitted.
  • sql_where is the WHERE clause of the SQL statement. Only one such clause is permitted.
  • sql_group is the GROUP BY clause of the SQL statement.
  • sql_order is the ORDER BY clause of the SQL statement.
  • sql_filter filters the list by the value of another field. A field name or a comma-separated list of field names can be given. The field names must correspond to column names in the database table being queried.
  • sql_default_{FIELD_NAME} is the default value used by the sql_filter attribute when the value of the {FIELD_NAME} filter has not been set.