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"
/>

Parameters

Mandatory Parameters

  • type must be sql.
  • name is the unique name of the field. 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 (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.
  • 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.

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="multiple".
  • 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_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.