Queries

It is possible to connect XperienCentral to one or more external databases. The word ‘external’ is used to indicate that it is different from the internal database: The database that maintains XperienCentral’s internal content & administration. It is possible that the tables with external data are located in the same database as XperienCentral’s internal tables.

Because XperienCentral can be connected to other databases, it is, for example, possible to request data from the back office and show these on the website.

In This Topic

Use of Queries

Views on Database Content

By means of queries you can configure views on database content.

Overview Versus Detail View

You can view database content on two levels:

• The overview level shows a list of records from the database.
• The detail view level shows one specific record when it is selected.

In the screenshot above, the upper part shows an overview and the lower part shows a detail view. Note that you can also place the detail view on a different (special) page.

Sorting by Column

For the overview level, you can sort by column by clicking on a column heading which toggles the sorting between ascending and descending order. For each column in the overview, you can enable or disable column sorting separately.

Query Definitions

Queries are defined using SQL (Structured Query Language). The information returned from the database can then be shown on the website. The following is an example of an SQL query:

In XperienCentral, you manage your query definitions at a central point. Each query definition has a name, code, the name of the database, the presentation format of the retrieved data, which fields of the retrieved data are to be shown in which columns and query parameters.

To show the retrieved data on a web page, you insert a Query content element on a page. In the content element you select the desired query definition.

Query Parameters

To be more flexible with queries, you can use parameters in the query code. For instance, you may use a parameter to filter the data to be retrieved on certain characteristics. Then, to change the filter characteristics, you only have to assign another value to the parameter without changing the query code. The parameter is put between @ characters in the WHERE clause. For example:

You can assign values to a parameter at two levels:

• With the query content element
• With the query definition

About the Example Database

In this topic, the example database "Who is Who" is used to illustrate concepts. In this database, the whPerson table is the central table containing most of the personal data. The other tables are extensions used to store additional information about the person.

Back to Top

Connecting an External Database

You can connect one or more databases of the type MySQL, MSSQL and Oracle. A database is contained on a database server, which may accommodate one or more databases. Prior to configuring a database connection the database server must have been installed and started up properly. For each database connection, you have to set the name and some connection related attributes like the URL, a valid user name and a password.

To connect an external database, perform the following steps:

1. Make sure a filled external database is available and that you know the connection related attributes.
2. Navigate to Configuration -> Database Maintenance.
3. Select the [Database connections] tab.
4. Select [New database connection] from the pull-down menu (top left). A blank dialogue pane with connection related attributes is shown.
5. Enter the connection related attributes.
6. Press [Apply]. If XperienCentral can make the connection, the product and driver information is shown as well as a table content list.
   
   
   
   
   

See Database Maintenance for complete information on connecting external databases.

Back to Top

Using Queries

In this section, we setup a basic view on the whPerson table by showing the following fields: lastname, prefix, firstname and email. This section will not elaborate on all the fields in the XperienCentral screens; go to section *** for a detailed description on each field of the different panels. For this section we start with this query:

Defining an Overview Query

Select menu Configuration > Queries (Query tab):

• To create a query, select ‘New query’ from the drop-down menu (see A).
• To copy a query, select that query and click [Copy] (see B).
• To delete a query, select that query and click [Delete] (see C).
• To add a column to the overview for any field in the SELECT clause, click [All fields] (see D). Then enter the column names (headings).
• To enable sorting by column, check the ‘Sort’ box] (see E).
  
  
  
  
  
  

Defining a Query Content Element

After defining the overview query, you can place the Query content element on a page.

1. Navigate to the desired page.
2. Add the Query content element to the page.
3. Select the desired overview query from the drop-down list.
   
   
   
   
   

Showing the Basic Overview

The result may look like this. Sorting by column has been enabled for ‘Last name’ and ‘First name’. By clicking on it, you can toggle sorting between ascending and descending order. For this type of content element with this presentation format, the title in the content element is not shown.

Back to Top

Overview with Parameter

Introduction

In this section, we introduce a query parameter. However, first we extend the overview with a column ‘Location’. In the query code, the ‘AS’ statement renames a field named ‘name’ to ‘locname’. This is used to avoid possible confusion with fields in other tables that are also named ‘name’ (in example further on in this document):

Defining an Overview Query with Parameter

1. Select the desired overview query (Configuration > Queries).
2. Enter the query with a parameter.
3. Press [Apply]. A "Parameters" section is automatically added at the bottom of the screen.
4. Define the parameter options:

• • Set the type to "Integer".
  • Select "Edit in element" to be able to assign values to the parameter on the query content element.
  • Select "List" to be able to assign more than one value to the parameter on the query content element.

Connecting an Options Table to a Parameter

The goal is to let the editor choose from a list of options for the place parameter at the Query content element. The options in the list can be obtained from another table from the database.

1. Click "Connect to table" (or "Edit table" if a table already exists).
2. Enter the name of the table and the names of the ID and name fields. Click [Apply].
3. Press [Close]. You can now select a default value for the parameter. This list is also available with the query content element now.

Defining a Query Content Element with Parameter

After defining the overview query, you can place the content element ‘Query’ on a page.

1. Navigate to the desired page.
2. Add a Query content element.
3. Select an overview query that includes a parameter. A parameter pull-down box is added to the content element.
4. Select one or more parameter values on which you want to filter your view.
   
   
   

If you selected ‘Use search pop-up’ (see 3.2.3 Connecting an options table to a parameter), the following screen pops up when setting the locations on the query element:

Showing the Overview with Parameters

The result only shows records in which the location is Amsterdam or Nijmegen:

Back to Top

Overview with Link to Detail View

Introduction

In this section, first we define a detail view and then from an overview we set a link to it. A detail view is defined by three query types:

• Get information query.
• Get query
• Search query

Get Information Query

This query is used to retrieve all the details of one specific record (the record that was clicked on in the overview). The record is identified by a parameter that is referred to as @dbid@ and which is typically used in the WHERE clause. When you click on a link of a specific record in the overview, the @dbid@ parameter gets the ID value of the record after which the query is executed. In our example, we use the following query:

Get Query

This query is used to retrieve the title of the detail view. This title is typically shown in a larger font on the page. The query must contain an "id" and a "name" in the SELECT clause. In our example, we use the following query code:

Search Query

This query is used to retrieve data for search functions that are present in XperienCentral itself. The query must contain an "id" and a "name" in the SELECT clause. In addition, it needs an @search@ parameter in the WHERE clause. In our example, we use the following code:

Defining a Detail View

1. Navigate to Configuration > External Databases.
2. Enter the queries and define other settings. For example:

Linking an Overview to a Detail View

In our basic overview example, we add a link from the ‘Last name’ field to a person’s detail view. However, to be able to link to a detail view, an integer field with the unique record ID must be present in the SELECT clause. Any other type of ID than an integer type of field will not work.

So, in our overview the overview query code will be:

This record ID field must be selected in the ‘Columns settings’ as well. More than likely, it is not the intention to show this integer field at the website. So, we set it to ‘Do not show’.

1. Select the desired overview query (Configuration > Queries Queries tab).
2. Under "Column settings" add a column for the record ID field and check "NT" for "Do not show".
3. In order for the field to be linked, select "New link" under "Column settings" or "Modify link" if a link already exists.
4. From the "Field" drop-down list, select the ID field(in this example "personid").
5. From the "To" drop-down list, select the desired detail view. Click [Save].
6. Click [Close]. The overview query looks like this:

Defining a Query Content Element with Detail Link

After defining the overview query, you can place the Query content element on a page.

1. Navigate to the desired page.
2. Add a Query content element.
3. Select an overview query that includes a link to a detail view from the upper drop-down list:
   
   
   
   
   
   
   

Showing the Overview with Link to Detail View

In the example below, the upper part shows the overview. The lower part is the detail view, which appears after clicking the last name "Groot".

Using a Special Page for the Detail View

By default, the detail view is shown on the same page as the overview. If this is not desired, because one of the views needs a different presentation, you can also place the detail view on another special page. Proceed as follows:

1. Create the detail page and publish it.
2. Navigate to Configuration > Channel Configuration > Special pages.
3. Find the name of the detail view under "Database pages" and click [Search].
4. Select the page on which the detail view is to be shown and click [Apply].

The detail view will hereafter be shown on this special page.

Back to Top

More Complex Example

This section just shows the queries that belong to the introductory example in section *** Views on database content’. This example has the following characteristics:

• Parameter that filters on the locations Amsterdam and Nijmegen.
• Link to detail view, which is shown underneath the overview.
• Three columns (Department, Job and Location) which fields are not retrieved directly from whPerson table but indirectly through six other tables. This requires a more complex WHERE clause.

Overview Query

Detail Query – Get Information

Back to Top

Reference Information

Database Connection Panel

Configuration > Database Maintenance > Database connections

Query Overview Panel

Configuration > Queries > Query

The following fields are only visible if the query has @ parameters:

Detail Query Screen

Configuration > External Databases

Back to Top