Let's start with the list page. The first step is to create a new “Query table” record. We have to complete the fields of this record:

Now we can save our changes paying attention not to exit the form: after saving, the extension will now compare a wizard button near the query field. This button can be used to enter in the Query-by-Example (QBE) wizard. The first choice available is from a guided query construction (QBE) and a simple textarea where you can, if you wish, write manually the query (RAW QUERY). To make life easier we are going to use the nice QBE wizard!

At this point we have to construct a query to retrieve the list of the fe_users which are not deleted and, for every character, the group name which the character belongs to. Try to use the wizard to obtain the following SQL:

SELECT fe_users.uid, fe_users.name, fe_users.address, fe_users.title, fe_groups.title AS race FROM ( fe_users LEFT OUTER JOIN fe_groups ON fe_users.usergroup = fe_groups.uid ) WHERE fe_users.deleted = 0 ORDER BY fe_users.name ASC

When we have finished constructing our query we can save and exit from the wizard (and from the record, of course).

Now we have to insert the “DB Integration” plugin in the page as a content element. In the “General” tab we have to specify an id for the div containing the output of the plugin (this is very important if you are using xajax), a query record (in this case the one we have just created), a template and a template type (we'll speak about this options in the following paragraphs) and finally we can activate the debug output to see the executed query.

Going to the “Results” tab, we can specify how many results per page we want, a text in case of no results, a summary and a caption to describe the results. Finally we can decide if we want to provide to the site users a link to download the results in a CSV format. The other tabs will be described in the following paragraphs, now save and close the content element.

If we open the “Characters list” page in the FrontEnd we'll see the list of fe_users records stored in our table (remember to insert at least one fe_users record if you want to see something :-)) Very easy!!!

If you take a look at the query field of the “query table” record, you'll see that it's not filled with the SQL of the constructed query but with a serialized object. This is because if you re-open the wizard it's necessary to provide you the same form that you saved before and, to do this, it's necessary to parse the output of the wizard. As you can imagine, it's easier to restore a serialized object containing the SQL structure than parsing an SQL query. For this reason I decided to save the data-structure used to manage the wizard instead of the SQL result. As you'll see in the next phases of this example, this is a solution that I adopted in a lot of cases. Before version 0.9.3, I used an XML document to describe the SQL structure.

PAY ATTENTION TO DO NOT MODIFY THE QUERY FIELD MANUALLY!!!

Now let's create the search form. We have to go inside the “Search” page and follow the same process as before. But in this case we have to choose the “search” option in the type selector. After this selection, some fields will be hidden and some others will be displayed. We have to complete these new fields:

After saving, we can open the wizard for the search module creation. Surprisingly, we receive an error message: “No marker has been used in the query”. What's wrong? What did we miss? As I said before, a search module is used to refine an existing query, so we have to modify our “select” query to allow this search form to interact with it. Let's close this wizard and return to our previously created “list query” record.

We want to permit our users to search for the characters whose name contains a particular string. For example, if a user searches for “he”, the query has to retrieve two characters: the doctor Beverlie Crusher and the Commander William Richer (Number 1 for the Captain ;-)). To do this we have to modify the query. Use again the wizard to modify the query and to obtain the following SQL:

SELECT fe_users.uid, fe_users.name, fe_users.address, fe_users.title, fe_groups.title AS race FROM ( fe_users LEFT OUTER JOIN fe_groups ON fe_users.usergroup = fe_groups.uid ) WHERE fe_users.name LIKE '%###WFQBE_NAME###%' AND fe_users.deleted = 0 ORDER BY fe_users.name ASC

As you can see, we have a new condition in the WHERE part of the query: we want to test if the name contains the code ###WFQBE_NAME###. This is a marker that is substituted with an empty string by default but, if we provide a particular parameter, it will be substituted with the value of this parameter. And this is what is going to do our search form.

So, let's return to the search query and re-open the wizard. At this point we can find a selector box with the list of the available markers (only one in this example). We can select the marker and then we can select the type of input we need. For our example we select the “input” type and we simply provide the label string.

Now we have to insert the “DB Integration” plugin in the search page (as we have done in the list page) and we have to complete the id and the query record fields. In the “Search” tab we can specify the results page (the page containing the “list query” results). If the results page is the same as the search page, it's not necessary to fill in any field but, if you use xajax, you can specify the id containing the results list (in this way, the results are loaded through an AJAX call).

After saving, we can open the “Search” page in the FrontEnd and we'll find the search form. If everything is correct, if we search for a string, we'll be redirected to the “List” page and we'll see all the characters whose name contains the string we searched for. As before, this is only a very simple example: you can construct more complex queries and more complex search forms.

Since version 1.0.1, there's a new marker: CUSTOM. This marker is not associated to any marker in the results query and can be used to introduce custom search fields that have to be managed manually with a hook (e.g. I used a CUSTOM field to provide a selection between AND and OR operators inside the query).

The next step is the configuration of the “Character details” page with the detailed view of a character. First of all we have to create a “Query table” record to retrieve the data from a single fe_user record (so we have to choose the “select” type). Open the query wizard and construct the following SQL query:

SELECT fe_users.uid, fe_users.name, fe_users.address, fe_users.title, fe_users.image, fe_groups.title AS race, fe_users.www, fe_users.crdate FROM ( fe_users LEFT OUTER JOIN fe_groups ON fe_users.usergroup = fe_groups.uid ) WHERE fe_users.uid = ###WFQBE_UID###

Pay attention to the where condition. We want to retrieve the details of a precise record, the details of a character that has been selected in the list view. We haven't spoken about the link yet, but you can imagine that in the list view we have to provide a link for every character that passes the uid of the character to the details view. With this uid we can correctly retrieve the details of the selected character and this can be done with the condition WHERE fe_users.uid = '###WFQBE_UID###'. If you open this page in the FrontEnd you'll see an error because no uid has been passed.

The solution is to modify the list page to insert a link that allows the user to select a character and to see his details. To do this, we have to create an extension template in the “Characters list” page and to configure the “uid” field to become a link (with the text “Show details”) and to pass the character uid to the “Character details” page. Take a look to the following TypoScript code that we have to insert in this new extension template:

plugin.tx_wfqbe_pi1.customProcess.1 {

0 = TEXT

0.value = Show details

0.typolink = 1

0.typolink.parameter = 7

0.typolink.additionalParams = &tx_wfqbe_pi1[uid]=###WFQBE_FIELD_0###

}

Pay attention to the customProcess number. You can see that we have set the customProcess.1 option, this “.1” is referred to the uid of the query record we have created. This is due to the fact that in the same page you can have more query records than one and for this reason the only way to refer to the correct one is with its uid.

In the previous TS code, you can see that we have defined a text object. You have to pay attention to two facts. The first one, very important, is that the “0.” (the key of the COA) is the number of the retrieved field and means that the field number 0 has to be shown based on this TS configuration. If you take a look to the list query, you'll find that we want the following fields:

fe_users.uid, fe_users.name, fe_users.address, fe_users.title, fe_groups.title as race

The query returns an array like this one:

0 => uid

1 => name

2 => address

3 => title

4 => race

Now I hope it's clear that the “uid” field is the number 0 and for this reason we created a TEXT object with key 0. The second fact to pay attention to is the ###WFQBE_FIELD_0### used in the “typolink” option. This marker will be substituted before evaluating the TS object with the value of the field 0 retrieved from the db (the uid).

The only thing to notice at this point is that the parameter has to be an array named “tx_wfqbe_pi1” and the name of the key (“uid”) has to be the same as the last part of the marker in the query (“###WFQBE_UID###”).

Now, we are able to reload the list, to see the generated links, to click on the “Show details” link of one character and to see his details in the “Character details” page. Perhaps, in this page you'll see a bad table, with some non-understandable values (e.g. the creation date is a timestamp, while we need a human-readable date). We have to create an extension template and to configure the crdate, www and image fields inside the setup field of this extension template:

plugin.tx_wfqbe_pi1.customProcess.3 {

7 = TEXT

7.value = ###WFQBE_FIELD_7###

7.date = d/m/Y

6 = HTML

6.value = <a href="###WFQBE_FIELD_6###">###WFQBE_FIELD_6###</a>

4 = IMAGE

4 {

file.maxW = 150

file = uploads/pics/###WFQBE_FIELD_4###

imageLinkWrap = 1

imageLinkWrap {

enable = 1

bodyTag = <BODY bgColor=black>

wrap = <A href="javascript:close();"> | </A>

width = 400

JSwindow = 1

JSwindow.newWindow = 1

JSwindow.expand = 17,20

}

}

}

With the previous comments, I hope you are able to understand this code. Now every field is displayed correctly but this is not enough: to obtain a detailed view like the one shown in the screenshots part of this manual, we need to customize the layout. We have to create an HTML code with the proper markers and then to associate it to the plugin. The HTML used in our example is the following one:

<!-- ###RESULT_TEMPLATE### -->

<div id="###CONF_DIVID###">

<!-- ###DATA_TEMPLATE### -->

<div class="###WFQBE_CLASS###">

<h2 class="first">###FIELD_name###</h2><br />

###FIELD_image###

<strong>Born:</strong> ###FIELD_address###<br />

<strong>Race:</strong> ###FIELD_race###<br />

<strong>Title:</strong> ###FIELD_title###<br />

<strong>Link:</strong> ###FIELD_www###<br />

<br />

<strong>Record created on:</strong> ###FIELD_crdate###<br />

###FIELD_uid###

</div>

<!-- ###DATA_TEMPLATE### -->

</div>

<!-- ###RESULT_TEMPLATE### -->

<!-- ###EMPTY_RESULT_TEMPLATE### -->

<div id="###CONF_DIVID###">

###CONF_EMPTYTEXT###

</div>

<!-- ###EMPTY_RESULT_TEMPLATE### -->

In this template you can see that every field is marked as ###FIELD_x### where x is the name of the retrieved field. If you compare this HTML code with the SQL query of the detailed view, you'll be able to associate the correct field with the correct marker. This is what the extension does while presenting the results in the FrontEnd.

We have to save this HTML in an HTML file, to re-open the “DB Integration” content element, to associate this HTML file as “Template” and to select a “Custom” template type in the “General” tab (alternatively, you can configure it via TS).

If we have done everything correctly, in the detailed view we'll find a nice character presentation but without the configuration done via TS. This is because we used markers like “###FIELD_fieldname###” instead of “###FIELD_fieldnumber###”. We can continue to use the field number but if we construct a custom template it is much easier to use the field name. To make the TS template compliant to our new custom HTML template, we have to modify the TS in this way:

plugin.tx_wfqbe_pi1.customProcess.3 {

crdate = TEXT

crdate.value = ###WFQBE_FIELD_crdate###

crdate.date = d/m/Y

www = HTML

www.value = <a href="###WFQBE_FIELD_www###">###WFQBE_FIELD_www###</a>

image = IMAGE

image {

file.maxW = 150

file = uploads/pics/###WFQBE_FIELD_image###

imageLinkWrap = 1

imageLinkWrap {

enable = 1

bodyTag = <BODY bgColor=black>

wrap = <A href="javascript:close();"> | </A>

width = 400

JSwindow = 1

JSwindow.newWindow = 1

JSwindow.expand = 17,20

}

}

}

We simply substituted the field number with the field name. This is a great improvement (I think...) because if you change your query you don't need to remap the fields with their position. At this moment, this is not possible with custom templates.

The last step is to provide a way to manage the character records. First of all we need an insert form for our users to permit them to insert a new character in our db.

Like before, we have to create a new “query record”. This time we have to select “insert / edit” type and we can use the wizard to create the form. In the wizard the first thing to do is to select the table of the database where we want to insert the new records. After this selection, the wizard presents us the list of fields available in the table (for now we can ignore the “ID field” selection).

For every field we can decide which type of input to provide to our users or to do not provide an input possibility (simply ignoring the field). For every field remember to set that you want it in the insert form with a flag in the “Use it on insert” option. We are using the fe_users table and we want to provide the possibility to insert the name, the race (a fe_group record), the title, the place of birth, the image and the site of the character. It's not enough: we want to save the characters in a specific page (pid) and to save the creation date (crdate) but we don't want the users to modify this values (to obtain this behavior you can use the hidden type or the PHP function type). In the screenshots paragraph of the manual you can see a part of the insert form wizard.

After the “query table” record, we have to insert the “DB Integration” plugin as before. Now we can select the “Insert” tab and specify if we want to provide a confirmation request before inserting the data, a destination page after insertion (otherwise we'll see a report page about the insert operation) and some redirect parameters (if we set a destination page).

Usually it's a good idea to send the user to the detailed page of the inserted record. To do this, we have to select the “Character details” page in the “Record details page” field and we have to set the following string in the “Redirect parameters”:

&tx_wfqbe_pi1[uid]=###ID###

where the ###ID### marker is ALWAYS substituted with the new id (it doesn't depend on the name of the id field, it's a fixed marker). If you need to specify a different field you can use this syntax:

&tx_wfqbe_pi1[uid]=###WFQBE_FIELD_fieldname###

where fieldname is the name of an existing field.

In the next image you can see the resulting form in the insert page:

We reached the end of this very simple example. I think that this tool can be used in a lot of cases to include data inside our sites from every db we need (and we have access, of course).

In this example, for the race field we provided a drop-down menu. This is a common way to provide selection from multiple options but there are some other cases where this is not the best way to do this. For example, imagine that one field is not enough to decide the correct option to choose. In this case (and in a lot of other cases), it's useful the type “relation”. For this input type it's necessary to select a “Select wizard”. In this selector box, you'll see all the “select” query records you made. The procedure is this: first of all you have to construct a query to list all the fields you need (remember to include the id field of the related table), before entering the insert form wizard, then you can set this new query record in the “Select wizard” option. If you re-open the FE you'll see that the selector-box has been substituted by an icon. If you click this icon you'll see the list of record that you can associate to the fe_user record. You can associate a single fe_group (through a radio input) or more than one fe_group (through a checkbox list), it depends on your multiple selection. At this moment it's not possible to use a custom template for the selector wizard and it's not available the pagination.

Another option for the “relation” type is the “Add new wizard”. The concept is the same as the “select wizard” but, in this case, this is used to provide the user the possibility of adding a new record in the related table (in our example we can give the user the possibility to add a new fe_group record). Before selecting it, you need to create a “query record” of type “insert / edit” for the fe_group table and then you'll find it in this list.

Since version 1.0.0, the “DB Integration” extension also supports editing and deleting functionalities. Let's explain how to add these capabilities to our example.

The first step is to create a new form for editing (this form will be used for deleting too). We have two possibilities:

The second way is the one I prefer: it gives me the possibility to use the same configuration for the biggest part of fields without the need to redo the same work twice. If you choose the second way you can have different fields for inserting and editing forms. For example you may need to set the crdate field (which contains the creation date of the record) only on insert, not on edit. It's very easy to achieve this: you simply need to flag the “Use it on insert” option and not the “Use it on edit” option.

For both the possibilities, you have to select the “ID field” in the top of the wizard. This field must contain the unique identifier (id) of the table. In our example it's the “uid” field. This is necessary to provide to the extension a secure way to identify a record.

Now, what we need is a link in the detailed page to allow the users to edit the character details. If you go back in this manual, you'll find that in the query for the detailed view we retrieved the uid (which seemed to be not useful) and in the detailed template we added the ###FIELD_uid### marker (which seemed to be an error!). Now we are going to use these two objects to provide a link to edit and to delete the selected record.

In the “Character details” page, we have to modify the extension template and we need to add the following TS code:

plugin.tx_wfqbe_pi1.customProcess.75{

uid = COBJ_ARRAY

uid.10 = HTML

uid.10.value = <br />

# This object is used to provide a link to edit the record

uid.20 = TEXT

uid.20.value = Modify

uid.20.typolink = 1

uid.20.typolink.parameter = 69

uid.20.typolink.additionalParams = &tx_wfqbe_pi1[uid]=###WFQBE_FIELD_uid###&tx_wfqbe_pi1[wfqbe_editing_mode]=1

uid.30 = TEXT

uid.30.value = &nbsp;-&nbsp;

# This object is used to provide a link to delete the record

uid.40 = TEXT

uid.40.value = Delete

uid.40.typolink = 1

uid.40.typolink.parameter = 69

uid.40.typolink.additionalParams = &tx_wfqbe_pi1[uid]=###WFQBE_FIELD_uid###&tx_wfqbe_pi1[wfqbe_deleting_mode]=1

}

With these changes, the ###FIELD_uid### marker will be substituted with the following text:

Modify – Delete

and the links to modify and delete the record, of course.

If you study the code, you'll find that both the edit and delete links are to the page with uid 69, which is the page that contains the insert form (the “Characters management” page in my example). Then you need to pay attention to the additional parameters of the link:

&tx_wfqbe_pi1[uid]=###WFQBE_FIELD_uid###&tx_wfqbe_pi1[wfqbe_deleting_mode]=1

The first one is used to provide the id of the record (remember that the parameter must be the same as the id field selected in the step before). As you can see we are providing the uid parameter that will be used to identify the correct character to edit or to delete.

The second parameter is (obviously) the parameter that defines if we want to edit or to delete the record.

The final option to set is in the FlexForm configuration of the “DB Integration” plugin in the “Characters management” page. Here we can specify a different page to be redirected in case of deleting operations (the detailed page set up for the inserting and editing operations is not useful in deleting operations because you deleted the record...). In our example a good selection can be the “Characters list” page.

In the previous paragraphs we didn't speak about Typoscript Markers in our queries. Suppose for example that you need to retrieve all the fields of the logged-in user. You have to construct a query like this:

SELECT * FROM fe_users WHERE uid=###TS_WFQBE_FEUSER###

Then you have to provide a way to manage the TS_WFQBE_FEUSER marker to the extension. You can do this via Typoscript, by adding the following TS code in your template:

plugin.tx_wfqbe_pi1.customQuery.10 {

TS_WFQBE_FEUSER = TEXT

TS_WFQBE_FEUSER.data = TSFE:fe_user|user|uid

TS_WFQBE_FEUSER.overrideAlways = 1

}

where 10 is the uid of our query record. In this example we want to prevent the site users to hack our query (by providing a different FEUSER parameter than the logged in user) so we set the overrideAlways option to 1. This means that the substitution of the TS_WFQBE_FEUSER marker has to be substituted in any case. The other possibility is the overrideIfEmpty option that can be used to set a default value in case of empty parameters.

Pay attention to the markers: all the WFQBE_XXX markers are used to pass parameters (like in previous examples) and so they are reserved for that. In any case, you can provide via TS a default value for these parameters too. Imagine that you use the WFQBE_USERNAME marker in your query and you provide a search form where you can search for a specific username. If you enter in the result page without searching for a username, the marker will be substituted with an empty value (''). Via Typoscript you can provide a default value for the marker that will be used if no value is passed in GET or in POST (in this case you need to set the overrideIfEmpty option to 1).

Another example. Imagine that you want to retrieve all the pages that have an end date in the future. You can construct a query like the following one:

SELECT * FROM pages WHERE endtime > ###TS_WFQBE_TIMESTAMP### OR endtime=0 OR endtime=''

Then you have to insert the following TS code in your template:

includeLibs.functions = fileadmin/user_functions.php

plugin.tx_wfqbe_pi1.customQuery.10 {

TS_WFQBE_TIMESTAMP = USER

TS_WFQBE_TIMESTAMP.userFunc = user_functions->getTimestamp

TS_WFQBE_TIMESTAMP.overrideAlways = 1

}

Finally, you have to insert the following PHP code in a file named 'user_functions.php' in fileadmin:

class user_functions{

function getTimestamp($content, $conf){return time();}

}