... Elxis version 2009.3 codename Aphrodite is out, download it from Elxis Download Center (EDC) ...

From Elxis Official Documentation

On Elxis 2009.2 or later you can create a new type of parameters on your module, bot or component XML parameters set. This is the database. By using the database parameter type you can execute SELECT queries on Elxis database and load the returning results on the XML rendering page in a select drop down list.

Syntax

The general syntax is as following (written in multiple lines):

<param 
name="article" 
type="database" 
default="DEFAULT_VALUE" 
query="DB_TABLE:VALUE_DB_COLUMN:TEXT_DB_COLUMN:WHERE_STATEMENT" 
groupby="GROUP_BY_COLUMN"
orderby="ORDER_BY_COLUMN"
limit="NUMBER"
label="Article" 
description="blah" 
/>

The interesting attributes are the query, groupby, orderby and limit. Let's see them one by one.

query

query is consisted by 4 parts separated by the character : The two last parts are optional.

The first part is the database table on which the select query will be performed. Note that you should not add the database prefix (by default elx_) on the table name. Sample values: content, eblog_settings, categories.

The second part is the column that will be used as the value for each option of our final drop down selection field .

The third part is the column that will be used as the text of our final drop down selection field. If not set, or it is empty (it is optional) then the value will be used as the option's text. You can also use CONCAT in this area. To do so separate the concatenation fields with comma (,) and use any separator except from the : character. Example concat: id, - ,title

The forth part is an optional WHERE statement. This statement must be simple as possible and you should make sure your query will not generate errors. Example: published=1

Here are some sample queries and how they are translated.

content:id:title:section=4

SELECT id AS elxv, title AS elxt FROM #__content WHERE section=4

content:id

SELECT id AS elxv, id AS elxt FROM #__content

content:id::section=4 AND published=1

SELECT id AS elxv, id AS elxt FROM #__content WHERE section=4 AND published=1

groupby

If you wish to group results by a specific column you can use the groupby attribute. Note that this attribute is optional. Example usage: groupby="username"

orderby

To order the results by a specific column you can use the orderby attribute. Note that this attribute is optional. Example usage: orderby="username ASC"

limit

To limit the returning result you can use the optional attribute limit. Example usage: limit="5"

Examples

Lets join all pieces together now and display a full example.

query="categories:id:title:published=1" groupby="" orderby="title ASC" limit="10"

or, as the groupby is empty we can ommit it:

query="categories:id:title:published=1" orderby="title ASC" limit="10"

The above will force Elxis to synthesize and execute the following SQL query:

SELECT id AS elxv, title AS elxt FROM #__categories WHERE published=1 ORDER BY title ASC LIMIT 10,0

The visual result of the above will be something like that:

<select name="article">
    <option value="12">A category title</option>
    <option value="5">One more category title</option>
    <option value="1">Sample title</option>
</select>

One more example

Let's list the latest 20 activated users in the system. We will also use CONCAT (the "," character) to display both user's username and real name in the options values.

query="users:id:name, - ,username:block=0" orderby="registerdate DESC" limit="20"

The generated SQL query will be:

SELECT id AS elxv, CONCAT('name', ' - ', 'username') AS elxt FROM #__users WHERE block=0 ORDER BY registerdate DESC LIMIT 20,0

The visual result of the above will be something like that:

<select name="myuser">
    <option value="78">Kostas Pirpisidis - kostas</option>
    <option value="77">Mary Golden - bunny7</option>
    <option value="75">Bruce Willis - bruce</option>
    <option value="74">Jack Lemon - leopard</option>
</select>