Joomla sql Form Field Type

The sql form field type provides a drop down list of entries obtained by running a query on the Joomla database. The sql field extends list field type.

XML Field Definition

<field
 name="title"
 type="sql"
 label="Select 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" />

Additional Parameters

query: It is the SQL query which will provide the data for the dropdown 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 dropdown list.

key_field: It is the name of the column that will contain values for the options in the fropdown.

value_field: It is the name of the column that will contain text to be shown to the user in the dropdown list.

translate: It will translate the output of the value_field if set to true. It defaults to false.

SQL Parameters

Instead of the query attribute, you can also provide SQL parameters to build the query.

sql_select: It is the SELECT clause of the SQL statement. Only one such clause is permitted.

sql_from: It is the FROM clause of the SQL statement.

sql_where: It is the WHERE clause of the SQL statement. Only one such clause is permitted.

sql_order: It is the ORDER BY clause of the SQL statement.

sql_join: It is the LEFT JOIN clause of the SQL statement. Only one such clause is permitted.

sql_group: It is the GROUP BY clause of the SQL statement.

sql_filter: It 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}: It is the default value used by the sql_filter attribute when the value of the {FIELD_NAME} filter has not been set.