>, Customisation, Tutorials>Using SQL in your pages

Using SQL in your pages

If you are developing custom pages, then you can use SQL to retrieve data from the database. For example to list contributor names in the page footer.

Embedding SQL output

You can embed SQL by putting the dbhtml() tag:

on a line of its own. You must then close the call on a separate line with

Anything between the above two lines will be parsed and repeated for every row in the database result set.

The below example lists the 10 newest photographers that have a profile page, with a link to their profile page:

dbcolumn()

The dbhtml() tag opens a dataset and parses the html for every row in the dataset. To output data from columns, you can use the dbcolumn() tag. Its syntax is in this format

datasourcename is the name that you have specified in the dbhtml() tag
fieldname is the name of the database column that you want to output
{parameters} are the parameters that you can use to format the output, parameters are comma separated and in the format name=value. For example {case:upper,urlmode:1}

For example:

SQL

The dbhtml() tag supports SQL statements and Stored procedure calls. We will not publish names of the available stored procedures or names of the database tables for reasons of security. If you want to use SQL in your pages then post a ticket explaining what you want to achieve, and we’ll send you the information. The dbhtml() function only supports read-only data.

Using parameters

You can use parameters in your SQL statement for which values were supplied in the URL.
Any URL parameters that you want to use in your query must start with sql_ and must be between square brackets.

The parameter that you add to your url must be without the sql_ part. For example, if your query has a parameter [sql_lastname], then the URL must have the parameter lastname.

If your query uses string parameters, then use apostrophes. For example where lastname='[sql_lastname]’.
And the URL for your custom page would look something like /c/2/0/names.html?lastname=jones

Another example, using a string parameter and a SQL wildcard:

Fixed value parameters

You can use fixed value (session based parameters).
Example:

In the above example, the stored procedure SP_GALLERYTITLES is used to retrieve data and this stored procedure requires a number of parameters, i.e.

languageid The id of the active language/locale
subdomainid The id of the active subdomain (or 0 for www)
userid The id of the logged in user or 0 if guest
userac The active access codes
userdc The active deny codes

An example

Here’s an example that adds a pulldown menu to the main menu and populates the menu items with groups (from the gallery manager) that are created for toplevel 1359.

The stored procedure SP_GALLERYTITLES (used in the example) takes the following parameters:

toplevelid The id of the top level for which you want to retrieve groups or galleries
languageid The current language id, automatically replaced
subdomainid The current domain id, automatically replaced
userid The user id, automatically replaced
userac The access codes, automatically replaced
userdc The deny codes, automatically replaced
groupsgalleries Requires a value of 0,1 or 2. 0 to get all child items, 1 to get groups only, 2 to get galleries only
menuid The id of the menu item that is to be highlighted after opening a URL

Also have a look at the article: Building search categories with the gallery manager.

2017-05-07T16:05:07+01:00 February 18th, 2017|Categories: Configuration, Customisation, Tutorials|Tags: , , , |