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:

<?xs dbhtml('datasourcename','sql statement') {

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:

<ul>
     <?xs dbhtml('photoglist','select top 10 * from photographer where own_profile=1 order by own_id desc') {
          <!-- this html is parsed for every row in the resulting dataset -->
          <li>
               <a href='/contributor/<?xs dbcolumn('photoglist','id',{}) ?>/<?xs dbcolumn('photoglist','displayname',{urlmode:1}) ?>.html'>
                    <?xs dbcolumn('photoglist','displayname',{}) ?>
               </a>
          </li>
     } ?>
</ul>

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

<?xs dbcolumn('datasourcename','fieldname',{parameters}) ?>

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:

<?xs dbcolumn('photoglist','displayname',{case:upper}) ?> // output every photographer name in capitals

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.

<?xs dbhtml('photographer','select * from photographer where own_id=[sql_photographerid]') { ... } ?>

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:

<?xs dbhtml('photoglist','select top 10 firstname,lastname from photographer where lastname like '[sql_lastname]%' order by lastname desc') {
   // parse this
} ?>

Fixed value parameters

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

<?xs dbhtml('catlist1','sp_gallerytitles 1359,languageid,subdomainid,userid,userac,userdc,1,80') { ... } ?>

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.

<div class="menuwrapper fixcenterwidth">
     <div id="mainMenuContainer" xsmenu="1">
          <ul id="left" class="menuitems" style="margin-left:<?xs rproperty('mnu_left','0') ?>px">
          <?xs html('xs-leftsidemenu.inc') ?> <!-- inserts the leftside menu items as configured in backoffice -->

          <!-- Here we add a menu item for the category groups that exists for top level 1359 -->
          <li id="lmenu_80" order="20"><a id="menu_80" class="arrow pulldownitem menucatlist1" href="/categories/1359/3/1/categories.html" pdid="menucatlist1">News & Editorial</a></li>

          </ul>
     </div> 
</div>

<!--Here's the html to add the groups as menu items to the pulldown menu -->

<div id="menucatlist1" class="dropdownmenu lmenu_80">
     <ul class="counter0">
<?xs dbhtml('catlist1','sp_gallerytitles 1359,languageid,subdomainid,userid,userac,userdc,1,80') {
     <li><a href="<?xs dbcolumn('catlist1','url',{}) ?>"><?xs dbcolumn('catlist1','glg_title',{}) ?></a></li>
} ?>
     </ul>
</div>

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.