Database lookup (multi-select) field

This field type is for more advanced use as it requires understading of SQL queries and arrays. We try to provide as many examples as possible so see if you can follow and use this field to create related articles (information).

The Database lookup (multi-select) field is used to display a drop-down list of values. Most common use will be to display a list of Joomla articles from which the user can select one or more which are related to the current article.

Populating the selection (drop-down) list

In order to populate the drop-down list you can use any SQL query. The query needs to create two variables, one the key and one the value. In the examples below are some common queries for creating Joomla article lists from the Joomla DB.

Template parameters

The selections made in the list can be rendered in the F2C template using the following template parameters. (as always, replace FIELDNAME with your own unique field name).

{$FIELDNAME} will output an ordered or unordered list of the values (Joomla title is articles are selected as shown further on).

{FIELDNAME_CSV} will output a comma separated list of the values

{$FIELDNAME_VALUES} will output an array of value and key

Current user & usergroup in Queries

The parameters {$CURRENT_USER_ID} and {$CURRENT_USER_GROUPS} can be used when building database lookup queries.

Joomla article SQL Query examples

In the examples below the Database lookup field will be populated with a list of Joomla articles from specific categories. The values saved in the array are the Joomla title and ID of the selected article(s). Please note, the queries depend on the JOOMLA VERSION!

Select articles from absolute list of categories

All the published articles from the categories 103, 104, 167, 170 and 176 will be shown in the selection list.

SELECT id, title
FROM jos_content
WHERE catid IN (103, 104, 167, 170, 176) AND
state = 1
ORDER BY title

Select articles from category and subcategories

In this example all published articles from category 80 and it's subcategories are shown in the selection list.

SELECT id, title
FROM jos_content
WHERE state = 1 AND
catid IN
(
SELECT id FROM
(
SELECT a.id
FROM jos_categories AS a
WHERE a.parent_id > 0 AND
extension = 'com_content' AND
a.published = 1 AND
a.lft >= (SELECT b.lft FROM jos_categories b WHERE b.id = 80) AND
a.rgt < = (SELECT c.rgt FROM jos_categories c WHERE c.id = 80)
) tmp
)
ORDER BY title

(Note: There is a space between < = , row 14, which should be removed but triggers html markup if I do it in the example)