EXT: Readable name of your extension¶

Requirements:¶

This extensions does not rely on other extensions.

Installation:¶

No special installation process is required, just use the normal way via the TYPO3 extension manager.

Usage:¶

In order to display database content in a html table you need to know the following:

The database host (is mandatory) . This can be any mySQL database host at any place in the world. You just have to make sure, that the server, where your Typo3 is installed, can connect to this mySQL server.

The database port. This entry is mandatory, even if your mySQL database listens to the default port, which is usually 3306 for mySQL version 5.x servers.

The database name (is mandatory).

The database user (is mandatory).

The database user's password (is mandatory).

The table and field names you want to be rendered as html table. This is quite simple, if you just want to display the content of a single table and a little bit more tricky, if you want to display the content of joined tables.

You may want to convert the retrieved characters from the database. If your characters look somehow damaged, e.g. german umlauts are not displayed correctly or you just see funny dots instead of well formated chinese characters, you should play around with the character conversion settings.

Example 1 – display data from a single table¶

You want to display data from the following “person” table.

The field “id” and the filed “zip” should not be displayed

The other fields should be diplayed in the order lastname, firstname, street and city

The data should be sorted alphabetically by the field firstname

After you inserted all the necessary data in section “Database Settings” you fill out section “Field Mapping” with the following data:

“Mastertable” is person

“Field Description” is...

{table=>"person";field=>"id";label=>"Adress Id";display=>"no";}
{table=>"person";field=>"lastname";label=>"Lastname";sort=>"asc";}
{table=>"person";field=>"firstname";label=>"Firstname";}
{table=>"person";field=>"street";label=>"Street";}
{table=>"person";field=>"zip";label=>"Zip Code";display=>"no";}
{table=>"person";field=>"city";label=>"City";}

Example 2 – display data from joined tables¶

You want to display data from the following tables: “person”, “country” and adresstype

You want to display the following html-table:

The columns with the labels “Lastname”, “Firstname”, “Street”, “Zip- Code” and “City” can be fount in the table “person”.

The column labeled with “Country” is the column “name” in the table “country” and the column labeled with “type” is the column “name” from the table “adresstype”.

After you inserted all the necessary data in section “Database Settings” you fill out section “Field Mapping” with the following data: “Mastertable” is person

The tables “country” and “adresstype” are joined tables (actually sql left outer joins). As you can see above, the relationship between the “person” and the “country” table is many to one. The dbtable_to_htmltable syntax for joining these tables via the country primary key column “id” and the person foreign key column “country_id” is...

{table=>"person";field=>"country_id";jointable=>"country";joinfield=>"id";}

{table=>"person";field=>"adresstype_id";jointable=>"adresstype";joinfield=>"id";}

{table=>"person";field=>"id";label=>"Adress Id";display=>"no";}
{table=>"person";field=>"lastname";label=>"Lastname";sort=>"asc";}
{table=>"person";field=>"firstname";label=>"Firstname";sort=>"asc";}
{table=>"person";field=>"street";label=>"Street";}
{table=>"person";field=>"zip";label=>"Zip Code";}
{table=>"person";field=>"city";label=>"City";}
{table=>"person";field=>"country_id";jointable=>"country";joinfield=>"id";display=>"no";}
{table=>"country";field=>"name";label=>"Country";}
{table=>"person";field=>"adresstype_id";jointable=>"adresstype";joinfield=>"id";display=>"no";}
{table=>"adresstype";field=>"name";label=>"Type";}

Example 3 – filtering data according to your personal criterias¶

You don't want to display the hole table content – to show just parts of it, use the following example.

Using a filter (e.g. “New York”) for field “city” in table “person” would look like this:

[…]
{table=>"person";field=>"city";label=>"City";filter=>”'New York'”;}
[...]

Example 4 – layouting cell data¶

Not every cell should look the same. Some data should be colored, emphasized, underlined, stroked or whatever.

You may use any valid HTML-code to reach the target (please have a look at the reserved characters “{” and “}” information below).

The field description attribute is:

wrap=>"<!--<span style="color: blue;font-weight: bold;">|</span>//-->";

Example for coloring and emphasizing a certain cell:

[...]
{table=>"person";field=>"city";label=>"City";wrap=>"<!--<span style="color: blue;font-weight: bold;">|</span>//-->";}
[...]

Example 5 – generating hyperlinks and showing picture-objects¶

Creating hyperlinks with cell content is sometimes useful if your table contains filenames of photos of a person or a related url to a dataset.

To create a hyperlink just use the wrap-attribute syntax from the example above.

Example of presenting a picture in table person

[...]
{table=>"person";field=>"image";label=>"Photo";wrap=>"<!--<img src="http://www.myhost.net/persons/images/|.jpg">//-->";}
[...]

[...]
{table=>"person";field=>"curriculum_vitae";label=>"CV";wrap=>"<!--<a href="http://www.myhost.net/persons/cv/|.pdf" target="_blank">|</a>//-->";}
[...]

Example 6 – formatting date and datetime output¶

Date and datetime information is often stored in a format which does not satisfy the user's needs. Because of this, dbtable_to_htmltable gives you full control about date formatting.

The field description attributes – we need 2 of them - for formatting date and datetime columns (timestamp should also work) is the following:

type=>" datetime ";

format=>" [...mySql-format attributes...] ";

Example of formatting a datetime column for german speaking users with the example cell-output

“27.02.2010, 23:59:58 Uhr” :

[...]
{table=>"person=>;field=>"contact_date";label=>"First contact";type=>"datetime";format=>"%d.%m.%Y, %k:%i:%s Uhr";}
[...]

Example 7 – rounding numbers¶

Rounding numbers is especially useful if your field is of type double or float and the decimals should not be displayed. It can also be used if you want to present integer-values with suffixed “.00”

The field description attributes – we need 2 of them - for rounding numbers is the following:

type=>" number ";

*decimals=>"* *[...number of decimals to be displayed...]* *";*

Example of rounding a double or float value with only 1 decimal to be displayed.

[...]
{table=>"person=>;field=>"salary";label=>"Monthly Salary";type=>"number";decimals=>"1";}
[...]

Attributes overview¶

display=>”no”

field=>”mycolumn”

filter=>”'New York'”

filter=>”2”

filter=>”2,4,5”

jointable=>"myjointable"

joinfield=>"myjoinfield"

lable=>”My Column”

sort=>”asc”

sort=>”desc”

table=>”mytable”

wrap=>"<!--<span style="color: blue;">|</span>//-->";

type=>"datetime";format=>"%d.%m.%Y, %k:%i:%s Uhr";

type=>"number";decimals=>"1";

type=>"datetime";format=>"%d.%m.%Y, %k:%i:%s Uhr";

type=>"number";decimals=>"1";

CSS¶

The output table will be rendered automatically by the extension. If you declared at least one “label” attribute in the field-description section, a table head will be generated. The table can be formated with CSS. The example below shows all possibilities to declare the table layout. These styles render the table in the above screenshot:

/*** the table ***/
.tx-dbtabletohtmltable-pi1 > table{
    border: 0px solid #cccccc;
    border-collapse: collapse;
    margin: 0;
    padding: 0;
    font-size: 0.8em;
    width: 100%;
}
/*** cell in table-head ***/
.tx-dbtabletohtmltable-pi1 > table > thead > tr > td {
    border: 0px solid red;
    font-weight: bold;
    padding: 0.4em;
    border: 1px solid #000000;
    color: #ffffff;
    text-align: center;
}
/*** table-head row ***/
.tx-dbtabletohtmltable-pi1 > table > thead > tr {
    background-color: #ff8700;
}
/*** table body row even-class ***/
.tx-dbtabletohtmltable-pi1 > table > tbody > tr.even{
    background-color: #ff9f33;
}
/*** table body row odd-class ***/
.tx-dbtabletohtmltable-pi1 > table > tbody > tr.odd{
    background-color: #ffffff;
}
/*** cell in table-body ***/
.tx-dbtabletohtmltable-pi1 > table > tbody > tr > td{
    padding: 0.2em;
    border: 1px solid #000000;
}
/*** pagination/navigation box ***/
.tx-dbtabletohtmltable-pi1 > .fullnav{
    border: 1px solid #000000;
    font-size: 0.8em;
    width: 100%;
    text-align: center;
    margin: 5px 0 5px 0;
    padding: 5px 0 5px 0;
    background-color: #ff8700;
}
.tx-dbtabletohtmltable-pi1 > .fullnav > .entryFirst{
    border: 1px solid #cccccc;
    margin: 5px 5px 5px 5px;
    padding: 3px 3px 3px 3px;
}
.tx-dbtabletohtmltable-pi1 > .fullnav > .entryPrev{
    border: 1px solid #cccccc;
    margin: 5px 5px 5px 5px;
    padding: 3px 3px 3px 3px;
}
.tx-dbtabletohtmltable-pi1 > .fullnav > .entryCurrent{
    border: 1px solid #cccccc;
    margin: 5px 5px 5px 5px;
    padding: 3px 3px 3px 3px;
}
.tx-dbtabletohtmltable-pi1 > .fullnav > .entryNext{
    border: 1px solid #cccccc;
    margin: 5px 5px 5px 5px;
    padding: 3px 3px 3px 3px;
}
.tx-dbtabletohtmltable-pi1 > .fullnav > .entryLast{
    border: 1px solid #cccccc;
    margin: 5px 5px 5px 5px;
    padding: 3px 3px 3px 3px;
}