dbal¶

eType¶

Therefore, you need to specify a handler here. The handlers (technically speaking, all those are represented by own PHP classes within the extension directory Classes/Dbal/) currently provided have various things in common, other settings vary from handler to handler. Which handler you should choose here, depends only on the source of data you want to show.

As the names tell, MySQL and MsSQL are handlers for the corresponding database types. TYPO3 is the handler you should choose if you want to use data from the current TYPO3 database (this is, normally, a MySQL table; anyhow this handler is faster than the MySQL handler as it does not initiate a new connection to the database). File allows to show files from a specified directory and Json is capable of reading a Json file showing single entries as rows (e.g. a Twitter stream).

sHost¶

This should be clear for MySQL and MsSQL (provide a Fully Qualified Domain Name here, which is either a DNS entry or an IP address accordingly; if you don't know what to enter, localhost is mostly a good guess), for TYPO3 and File you could leave this empty. Json finally needs the path to the Json stream here (which could also be an URL).

sUsername¶

May be empty; only needed if the specified host requires authentication (for MsSQL/MySQL tables, mostly). TYPO3 does not need anything here, even though it might be configured.

sPassword¶

Same as sUsername.

sDatabase¶

The database to connect to. If File is chosen, this is the main/parent directory (offered are fileadmin/ and uploads/ but others may be manually specified as well; anyhow, you need to use the advanced or TS settings then).

Hint: Within the flexform you could, after specifying the above settings, save the form. This will bring up a sDatabase dropdown allowing you to choose from one of the available (based on your handler, host and the authentication details) databases.

sTable¶

Within the specified database this is the table to select data from (or insert into). For File, specify the subdirectory to look for files (in case you ask yourself why to specify a main/parent and a sub directry -- this is for security reasons as the sub directory could be overwritten by the user and the main/parent directory prevents from getting higher than that hierarchy level).

Hint: As with sDatabase, in case you are setting this SQL Frontend up using flexforms, save here and a dropdown will magically appear.

sWhereGet¶

If you need some where parts that should be applied generally for all fetching data requests (thus the name, ...Get) you may enter them here. Examples could be "hidden=1" or "crdate > 1234567890". Anyhow, this gets only applied for data requests and, thus, won't be used for insertion of data. In other words: a new entry won't automatically be hidden just because it is specified here that only hidden entries shall be shown.

You could also use certain markers here which build largely upon the marker system in aDefault (next setting). So go, read that first, but afterwards don't forget to come back here!

Back? Great, because the format is slightly different as this is not an array but a string setting. This means that you neither insert a sSource nor a sField key but only markers. And they look like the following:

###fe_user:email###

So you take the sSource, add a double point, add the sField, and surround everything with the typical ### surrounders. Another example:

###GP:tx_mhomsqlio_pi1[uid]###

And another one:

###tstamp###

So, no sWrap? Nope, because you can just enter that yourself:

'sWhereGet' => 'email = "###GP:email###@foo.bar"'

And now you realize the huge security issue going on here. SQL injections have never been that easy, and even documented! Therefore, data is processed before being replaced as a marker. There are three ways to process where you can choose which one fits best (you have to choose one, though; default is s):

You may specify the processor as the first option, even before the sSource setting. If you leave it out, s is taken.

###i:GP:tx_mhomsqlio_pi1[uid]###

Keep in mind, that the s processor requires quotation marks:

email = "###s:GP:email###@foo.bar"

aDefault¶

This is, even though it seems ordinary, mighty tool. You may specify default values here that, as you can definitely guess, only make sense in case editing/creation of data is allowed (which you would do within the data section). What it basically does is specifying what default values should be used for editing/creation forms of data in case there is no user-specified data yet. So far, so unmighty, but ...

• you could also apply this to columns that are not shown in the form
• you could use other data for these default values

This allows you to, for instance, include the currently logged in user's uid without him/her noticing it. Or to use some data submitted via GET or POST. Or the current date. Or data from the current page. Or, well, see for yourself ...

The basic configuration looks like this: aDefault is, as the "a" in the beginning suggests, an (multi-dimensional) array where the column names to which the values shall be applied, are associative keys pointing either directly to a default value or to another array including their specific configuration.

Example (PHP):

'aDefault' => array(
    'my_pid' => 12,
    'my_user' => array(
        'sSource' => 'fe_user',
        'sField' => 'username',
        'sWrap' => '|'
    )
)

In the preceeding example two columns are set to have default values -- my_pid and my_user. The first is just set to have the value 12. Thus, if this column is shown in the frontend form, it will have the value 12 unless it's edited. If it is not shown at all, the column my_pid will just be set to 12 for new entries.

Slightly different is my_user though as it does not have a value set but a configuration array with all three possible options included:

• sSource specifies where to retrieve the default value. Possible values here are:

  • GP (via GET or POST submitted values)
  • fe_user (the currently logged in frontend user)
  • page (everything from the current page)
  • tt_content (information from the current content element (which is the SQL Frontend's content element)
  • environment (stuff via TYPO3's getIndpEnv function)
  • date (a date, well formatted)
  • tstamp (the current Unix timestamp)
  • tstamp2date (a date, well formatted, coming from a certain Unix timestamp)
  • strtotime (a Unix timestamp, based on an input - see PHP function description for that)

• sField specifies more detailled, which information to take - the possibilites here depend only on the chosen sSource:

  • for GP, enter the name of the variable submitted (also array naming as in tx_mhomsqlio_pi1[uid] is allowed)
  • for fe_user, enter any column name from the fe_user database table
  • for page, enter any column from the page database table
  • for tt_content, enter any column from the tt_content database table
  • for environment, enter one of the possible values for TYPO3's GeneralUtility::getIndpEnv function (http://typo3.org/api/typo3cms/class_t_y_p_o3_1_1_c_m_s_1_1_core_1_1_utility_1_1_general_utility.html#a7da866e5303aea5d4e31e71f1f8f520a)
  • for date, enter the format that gets applied to PHP's date(...) function
  • for tstamp no sSource is needed
  • for tstamp2date, enter the timestamp, enter a double point, and finally enter the format like for date (example: 12382372033:Y-m-d)
  • for strtotime, enter whatever you want from the PHP documentation according strtotime (e.g. 'now', '+1week', '1.12.2024', etc.)

• sWrap is not required but allows you to wrap your value including the marker "|" (this is not the usual TypoScript wrap function and thus does not include sub-functions or objects like dataWrap or similar)

One last thing about default values: They are default which means, they most likely get overwritten if they have anything configured that gives them the opportunity to be overwritten (like, for instance, being shown in the form).

Caution: Default settings are only applied if the configuration key matches with one of the database table fields/columns.

Entries from currently logged in user only¶

Data entries are somehow marked to indicate to which user they belong? Great, you now may just show the ones that belong to the currently logged in (frontend) user. Therefore, three settings are available but depending on your configuration you might only need one or two:

As you might have noticed, this configuration also allows you to limit entries depending on other values of the user (like his/her username or a group, for instance).

Furthermore, this value gets inserted into the corresponding field on creation/editing.

More specific configuration depending on eType¶