Query Module Tags

ExpressionEngine includes a special tag that allows you to easily query and return results from your database without having to script it with PHP. The general syntax for the tag is:

{exp:query sql="SELECT screen_name FROM exp_members WHERE group_id = '1' LIMIT 4"}

{screen_name}

{/exp:query}

This tag works a little bit differently than most of the other ExpressionEngine tags, so it needs to be explained. First, you may only use SELECT statements in the query; it will not process UPDATE or DELETE queries. You may use any standard query you want and the syntax used is identical to a regular SQL query.

Selecting Fields and Variables

Each of the columns or fields you include as part of your SELECT statement will be available inside the tag pair as a {variable} with the same name as the field. So, in the above example the "screen_name" field was selected and thus the {screen_name} variable is available inside the tag.

{exp:query sql="SELECT screen_name, email, location FROM exp_members WHERE group_id = '1'"}

{screen_name}
{email}
{location}

{/exp:query}

This query would make three variables from the "exp_members" table available.

You may also have it select all available columns/fields automatically by using the asterisk character:

{exp:query sql="SELECT * FROM exp_members WHERE group_id = '1'"}

{username}
{screen_name}
{password}
{email}
{location}

{/exp:query}

Returning Multiple Rows

Unless you specifically craft your query to only return a single result, most queries will return multiple "rows" of results. In order to deal with these multiple rows of results, ExpressionEngine will automatically loop through your query tag as many times as necessary to display all the rows of returned data. Suppose you want to list all the members of one of your particular groups. You might use something like this:

{exp:query sql="SELECT screen_name FROM exp_members WHERE group_id = '6'"}

{screen_name}<br />

{/exp:query}

On your page, you might get actual results such as:

Michael Jordan
Wayne Gretzky
Joe Montana
Roger Clemens
David Beckham

This tag will allow you to easily create many results that might otherwise require plugins or special custom-coding in other applications.

Notes

While this tag gives you a great amount of power and flexibility, it can sometimes be difficult to construct a query. ExpressionEngine abstracts its database design quite a bit, which means that related data is not always stored together in the same table. For instance, the data for weblog entries are stored in two tables: the titles and basic meta data like the date are stored in one table, while the body fields are in another.

So, if you wanted to try and recreate a regular weblog query which returned data that was stored across several tables, you would need to use SQL JOINS and other advanced syntax. To take full advantage of this tag's power you will need to have a good understanding of SQL querying.