Contents

Query List

Solving Unique Problems

One issue that is recurring is that advanced users want more automation and integration with data that is displayed on the website. A powerful new Query List WYSIWYG plugin has been developed which will enable users to create custom lists on the website that pull from a SQL database. This feature is automatically be rolled out to all states running the full CMS.

SQL Queries

Three databases can be selected as defaults: CMS, Society Net, and AM.NET. Joins, inner selects, and other standard SQL allow for advanced users to adjust queries for relationships between multiple databases.

Any standard SQL can be used with this plugin. All executed SQL is run through a low level SQL user account with read only access. This ensures that users do not accidentally cause data loss while developing and testing SQL. The Preview function allows the user to see the list output while they are modifying the script, this enables quick adjustments.

Session Variables

Using the Session Variables drop down you can insert reserved session variables for filtering, sorting, or selecting the values themselves in queries.  For security reasons these session variables are reserved to the 'BryceWebGroup.CMS.Session.*' namespace; however, it is possible to use custom session variables by creating them with that prefix namespace for the session key.  These will not appear in the drop down list; but, you can manually enter their keys like so: $BryceWebGroup.CMS.Session.MyCustomSessionVariable$

Note: you should not ever wrap a session variable reference, as the parser will automatically translate the reference in a wrapped form based upon data type.  This prevents script injection and lessons the task of coding an SQL query.

Request Variables

Request variables are parameters passed via GET or POST from the HTTP request.  Because a Request variable has no implied data type, you must specify the abstract data type in the Request reference, like so: $Request[requestVariableKey]number$

Notes: you should not ever wrap a Request variable reference, as the parser will automatically translate the reference in a wrapped form based upon specified data type.  This causes the page to error when Request parameters are not compatible with the specified data type.  Possible abstract data types are as follows:

  • number
  • string
  • date

 

Template HTML

There are often times when instead of displaying a table list of values, you'd like to display something functional or specific, such as a drop down list, or a templated HTML output with row values being displayed.  For this advanced requirement a Template HTML option exists.  The Template HTML uses a simple set of reserved keywords to define the structure of template HTML application and repitition.  Here is a break down of the syntax and usage:

  • $Result.Start$ - specifies the beginning of a result set, this must be used and should be the first thing in the Template HTML
  • $Result.End$ - specifies the ending of a result set, this must be used and should be the last thing in the Template HTML
  • $Row.Start$ - specifies the beginning of a row set
  • $Row.End$ - specifies the end of a row set
  • $FieldName$ - "FieldName" would be the name of a field in the row you would like to output

Notes: $FieldName$ does not output in any specifically formatted way, so for encoding quotes and other special formatting, you will need to use someting like REPLACE in the SQL query itself.  In the case of conditional template HTML, you will need to use CASE in the SQL query itself, as the Template HTML parser does not support conditional logic at this time, due to low demand.

$Result.Start$ / $Result.End$ are applied in literal order to one or more SELECT result sets.  To handle each result set from a SELECT, specify a new set of start / end symbols.  For example, to bind two result sets, you could use it like so:

$Result.Start$<label>First Result Set</label><br /><select>$Row.Start$<option value="$FieldName1$">$FieldName2$</option>$Row.End$</select>$Result.End$

$Result.Start$<label>Second Result Set</label><br/><select>$Row.Start$<option value="$FieldName1$">$FieldName2$</option>$Row.End$</select>$Result.End$

$Row.Start$ / $Row.End$ are applied in literal order to a SELECT row.  When using multiple row start / end symbols, they are used in an iterative pattern.  For example to alternate styles, you could use it like so:

$Row.Start$<div class="odd">$FieldName$</div>$Row.End$

$Row.Start$<div class="even">$FieldName$</div>$Row.End$

Display

The Display settings now support custom "title" HTML that appears when there are contents displayed for the list - this can be used to add a title to a list, while not displaying a title when the list is empty, in order to effectively hide the list when there is no content to display.

Features

For complicated queries which may be resource consuming, a caching option exists which caches for 60 minutes until the next query is run.

The last powerful feature is the Global Query flag. When this is selected, and a query is exported, it can later be imported (and the Global Query flag re-selected afterward). This causes all lists which import the exported file to share the same Query ID. By sharing the same Query ID, multiple lists with varying styles, and being displayed on different pages, will share the same Query updates when any instance is modified. This allows Query changes to flow throughout the entire website with a single update.

Note: when importing from an external CMS, do not select the Global Query flag. Instead save with it unselected, then edit it again and select the global query flag, then export, and save. It will then be safe to import the newly exported file, and use the Global Query flag with fresh imports.

Styling

Custom styling can be applied to lists through a CSS ID (div tag). By updating your website CSS, query list styles can be adapted as necessary.  For advanced / specialized output, use the Template HTML feature. Example list output:

Sharing Queries

Because SQL and schema knowledge is somewhat esoteric, an Import and Export feature exists on the plugin which enables users to share custom SQL scripts with other users. The BWG website forum contains a new thread where staff and users can share queries, which can be downloaded, and imported by seemingly un-technical users.

Practical Examples

Standard SQL is used with the Query List plugin. Here is an example of displaying a list of CMS users:

select
firstname + ' ' + lastname as [Name],
email as [E-mail Address]
from [user]
where typeid=2

Although this simple example works, it's not very interesting. One issue that comes up often is that the lists produced by the Query List plugin are not useful. Although on the surface this appears to be the case; with a little effort you can enrich the list output by adding HTML. Using HTML with queries allows you to create more advanced functionality, such as links in the list output:

select
firstname + ' ' + lastname as [Name],
'<a href="mailto:' + email + '">' + email + '</a>' as [E-mail Address]
from [user]
where typeid=2

Template HTML Examples

Here is a practical Template HTML example, which could be used within an HTML form, to facilitate drop downs with values from the database.

$Result.Start$
<table id="FormTable">
<tr>
<td class="label"><label>Keywords</label></td>
<td><input type="text" name="Keywords" size="30" length="75" /></td>
</tr>
<tr>
<td class="label"><label>Year</label></td>
<td>
<select name="Year">
<option value="0">Any</option>
$Row.Start$<option value="$ID$">$Name$</option>
$Row.End$
</select>
</td>
</tr>
$Result.End$

$Result.Start$
<tr>
<td class="label"><label>Topic</label></td>
<td>
<select name="Topic">
<option value="">All</option>
$Row.Start$<option value="$ID$">$Name$</option>
$Row.End$
</select>
</td>
</tr>
<tr>
<td></td>
<td><input type="image" value="Search" src="/img/btn-search.gif" /></td>
</tr>
</table>
$Result.End$

 

Contact this Author: < Nicholas Bott > nick@brycegroup.com