This is the first option in two ways: It's the first in the flexform and it's the first on the list of potential errors.

It's the character set that is used for reformatting stuff from the database, for data exchange in AJAX calls and it's the encoding default before writing values into the database. Choose it carefully and in case the frontend doesn't work as expected (probably you get XML response errors – this is because the AJAX response isn't valid XML code mostly because of some special characters that haven't correctly been encoded → charset missetting) start playing around with this option.

Of course if your database and website are real UTF-8 always use UTF-8!

Enable it and the frontend user is able to add new entries to the table. Of course, if you hide some fields from the table, the user is only able to enter the shown fields.

The same as for 'add'.

By the way: If your edit/add mode is 'in row' (see OPTICAL) you might edit an entry by just clicking it somewhere (the cursor turns into a pointer except on 'Links'-columns).

In case adding is enabled you can offer the user to duplicate entries. In this case per row a symbol is shown for duplicating and – if clicked – an 'insert new row' form is displayed that already has the values from the duplicated entry filled in (it's not saved at that point).

Important to know here: The original record won't get changed!

Important to know here – part two: If you don't show all the fields, the hidden ones get directly copy/pasted from the duplicated entry. So if you don't show e.g. a 'name' column but the 'description' column is displayed and the frontend user duplicates an entry with name = 'foo' and description = 'bar' a form for adding a new entry is shown with 'bar' already filled in. We change this to 'beer bar' and save then the new entry will have name = 'foo' and description = 'beer bar'!

If enabled checkboxes are shown for each line on the left-hand side and a drop-down offering the options to multiple edit or multiple delete all marked entries. If you check the box in the top left corner, all currently shown entries are marked (not the ones on other pages).

Well, as the name says ... show or don't – the search form. Note: By default only all displayed fields are searched.

Create AutoFilter drop-downs for various fields. Just enter the names and separate with a coma. Example: name, description

In case you have paging enabled (see DATABASE) you might want to offer the user the possibility to see all entries on one page. Enable this option and a small icon will do so in the fronend (top left corner by default).

In case a user is logged in and you have this option activated, little icons in each column's first line are shown where a click hides the complete column. This setting is dependent on the user meaning that if a user hides a column, it is only hidden for this user, not for all users. Furthermore this setting causes the 'Edit/Add/Duplicate mode' on the 'OPTICAL' tab to be separated (just for your information, the extension takes care of this issue).

Hidden columns can be shown again by choosing the hidden column in the drop-down box beyond the table and clicking the little 'show column again' icon next to it. Note that this drop-down is only shown if columns are hidden for this user.

If enabled, the user is able to download the list as a CSV file. 'all at once' means that the user is able to download a file containing all the records he/she sees (of course only shown fields). 'only selected parts' downloads only entries that are currently marked (as in 'multiple changes') with the checkbox. This might be useful together with an AutoFilter (use AutoFilter, check all shown entries and download these as CSV).

You can get informed about changes in your database done via the SQL frontend. Enter the eMail address here ...

... and select when you want to be informed.

First of all the extension needs to know the database connection. If you want to use a table from the TYPO3 default database you don't have to define the username/password settings here again. Just check this option and ignore the next options until 'Table name'.

In case you didn't check the option above you have to enter stuff here. What database types you can select depends on your webserver and on the PHP installation (please see http://at2.php.net/manual/en/mssql.setup.php and http://at2.php.net/manual/en/intro.uodbc.php for more information).

Enter the table name here that you want to show.

This is a very important setting. Specify here what you want to see in the frontend. Just enter the column names and separate with a coma. An example on the fe_users table: uid, username, name, telephone, email

Also this is the setting that is sometimes referred to as 'the shown/displayed fields ...

The order of how the fields are entered is important here and represents the order shown in the frontend. So 'uid, username, name' is not the same as 'uid, name, username'! If you have links defined (see ADVANCED) use the name you entered there to define the position here.

As mentioned before at the 'Show search form' option only shown fields are searched by default. Check this option to search all fields. Of course only the specified ones are shown.

You want to order per default by the username column descending. Enter 'username desc' (without the apostrophes) here. The opposite would be 'username asc'. This field is SQL style, so if you want advanced configuration here look at e.g. mysql.org for details (order by section).

The famous 'paging'. Enter how many entries are shown on one page. If you leave this field, all is shown. Also the setting form GENERAL, 'Display Show All' is ignored in this case.

If you need more advanced features you can enter a SQL statement part here that will be included using 'AND' inside the 'WHERE' part. Don't use a leading or ending 'AND', 'OR' or 'WHERE' here. Example: ' username NOT LIKE 'admin%' AND uid < 1000' (it's an example, don't ask for the sense of this part ;o))

There are markers that get replaced here which you might use:

In case you need to regulate the shown entries based on the logged in frontend user you probably want to use this field. Enter the column name of the field that contains the frontend user id here and the extension does the rest for you.

Example: Your table stores books from certain people. The fields are 'id, bookname, isbn, detaillink, user' and the 'user' field contains the owner's frontend user id. So all you need to do is entering 'user' here.

What does the extension then do for you?

Same as option before but based on the username instead of the id.

In case all the options are not enough for you then this is your preferred choice. Enter a complete SQL statement here and the extension uses this return to display in the frontend. Sounds cool but where is the catch? Well, in this case not all of the other options are possible anymore. What is and what isn't shows the following table ...

Display a small key next to the primary key headline to indicate that his is the PK.BUG: Currently this doesn't work with MsSQL databases, I know that.

Enter the real headlines for the columns here – in the same order as the 'DATABASE > Show those database fields' setting. In case something should be empty, include an empty string in the coma list (e.g.: 'foo, bar, , last'). If you want to use a coma, prefix it with a backslash ('foo, bar\, beer, , last').

This setting was also mentioned before. You can choose between ...

Best here is to test both modes in order to understand what they are used for (remember: it's just optical, no functional differences). Note that with column hiding enabled this mode may change to 'separated' for users that have certain columns hidden.

For a better overview every second line can be colored in a different way. By default a SQL frontend stylesheet is included where an 'alternate' class is defined – so it's fine here leaving this class. If you want to fit the layout to your design wishes enter the class name here.

What is entered here gets included in the class-attribute inside the table-tag.

Using this option you can define width for the output table. Per default no width attribute (well, it's a style attribute with a width option) is used. To define it, use something like the following:

In case you have deleting and/or multiple edits enabled, these additional columns (the first one with the checkboxes and the last one with the delete/duplicate buttons) won't have any width defined. So if you define percentages, keep in mind that it won't be 100% the same thing as defined as HTML needs a bit space for those additional columns.

Do not include the default SQL frontend stylesheet (which is by the way located in the extension directory – typically typo3conf/ext/mh_omsqlio/ - and then inside the res/ folder (name: tx_mhomsqlio_style.css).

Well, here we are: At the docs! Normally every entry is represented by one single row in the table. You don't want that? Or you don't want a table layout? Check this option and change the template. The principle is almost the same as in serial letters: The template is used with the first record until the ###NEXT### marker appears. Then the template goes on using the next record. Until the next ###NEXT### marker is used. The record changes again to the next one. And so on until the template is finished. Then it starts again at the beginning ...

Lets have a look at the default template used here – MANUAL_LIST:

<!-- ###MANUAL_LIST### begin →<table id="###TABLE-ID###" class="###TABLE-CLASS###" cellpadding="0" cellspacing="0"><tbody><!-- ###LIST_INNER### begin →<tr><td><img src=”uploads/pics/###image###” alt=”###name###” /></td>###NEXT###<td><img src=”uploads/pics/###image###” alt=”###name###” /></td>###NEXT###<td><img src=”uploads/pics/###image###” alt=”###name###” /></td>###NEXT###<td><img src=”uploads/pics/###image###” alt=”###name###” /></td>###NEXT###<td><img src=”uploads/pics/###image###” alt=”###name###” /></td></tr>###NEXT###<!-- ###LIST_INNER### end →<!-- ###LIST_INNER_FILL### begin →<td>&nbsp;</td><!-- ###LIST_INNER_FILL### end →</tbody></table><!-- ###MANUAL_LIST### end →

The complete template is divided into the outer and the ###LIST_INNER### part. The LIST_INNER part is the one used with the records. It starts with the first one until the first 'More' link – afterwards a ###NEXT### appears so the record changes to the next entry. And so on until the end of the template where we find the fifth ###NEXT### marker. So now we go on at the beginning of the LIST_INNER template with the sixth record.

Result: We have five entries per line (in this example five images) and the fifth closes the row. The sixth entry (by the way it is used with the fe_users table) starts a new line.

So what happens in the last row? Imagine we have eleven entries → two rows and one left??? It creates the first one and then? Well the extension counts how many entries you have per LIST_INNER template and fills the last one up with the stuff inside the LIST_INNER_FILL markers. So the result using eleven lines will be:

<table id="###TABLE-ID###" class="###TABLE-CLASS###" cellpadding="0" cellspacing="0"><tbody><tr><td><img src=”uploads/pics/###image###” alt=”###name###” /></td><td><img src=”uploads/pics/###image###” alt=”###name###” /></td><td><img src=”uploads/pics/###image###” alt=”###name###” /></td><td><img src=”uploads/pics/###image###” alt=”###name###” /></td><td><img src=”uploads/pics/###image###” alt=”###name###” /></td></tr><tr><td><img src=”uploads/pics/###image###” alt=”###name###” /></td><td><img src=”uploads/pics/###image###” alt=”###name###” /></td><td><img src=”uploads/pics/###image###” alt=”###name###” /></td><td><img src=”uploads/pics/###image###” alt=”###name###” /></td><td><img src=”uploads/pics/###image###” alt=”###name###” /></td></tr><tr><td><img src=”uploads/pics/###image###” alt=”###name###” /></td><td><td>&nbsp;</td></td><td><td>&nbsp;</td></td><td><td>&nbsp;</td></td><td><td>&nbsp;</td></td></tr></tbody></table>

If you want to use your own template you might either fit the default one to your needs (not the best idea since it's overwritten at the next update) which can be found inside the extension directory and then res/tx_mhomqslio_pi1.html.

The other option is this one here – upload your own template.

If you include the plugin on more than one places in your system and have a personal (other than default) template you might want to use all the time, upload it in one plugin and use it here in all the others.

Per default every second line is colored slightly different in order to optimize overview (this behavior is editable via the 'CSS class for every second line' setting). Nevertheless sometimes it might be useful to color rows depending on their content. For example to mark urgent lines, visualize priorities or display which entries are marked with today's date. Therefore the conditional formatting can be used which is due to ultimate flexibility a bit tricky at the first point, so here are three basics to keep in mind:

A rule normally consists of two parts, the condition and the format depending on this condition, separated with a ':'. The first part has to be standard PHP code as used inside the brackets of an 'if' condition. All (not only the shown ones) columns of the database table filled with values from the current row are accessible and usable via a dollar sign and the column name (e.g. $id, $username, $another_important_column, …). In case this condition turns out to be valid and thus returns true the second part is used as color. In order to work properly this part might be some hex color code (e.g. #F00, #E1CCF3, …), a web-standard color name (e.g. orange, red, black, white) or a column name wrapped within three # signs (e.g. ###id###, ###username###, ###another_important_column###, …) that contains the color code.

Examples should make things clearer:

All examples above are just single rules. In real TYPO3 administrator's life you probably need to define more than just one rule. In this case separate them with new lines and keep in mind that the mh_omsqlio rule processor runs from top to bottom and stops immediately after a condition returns true:

$some_percent > 90 : red$some_percent > 70 : orange$some_percent > 50 : yellow

The column 'some_percent' is used. In case it's larger than 90 the line turns red, larger than 70 means orange and larger than 50 says yellow.

Let's reverse the order and see what happens:

$some_percent > 50 : yellow$some_percent > 70 : orange$some_percent > 90 : red

The first value, let's say 98, gets checked. In the example above it would be larger than 90 and thus turn red, which is what we want. In our second example it would again run from top to bottom but stop at the first point since 98 is larger than 50 and so it turns yellow. Ouch.

What about the rest? What color will a value of 35 have? White! Unless you define an absolute 'else' part which is another line just with the second argument:

$some_percent > 90 : red$some_percent > 70 : orange$some_percent > 50 : yellowgreen

Now the rest that doesn't fit to the first three rules turns green. What's going to happen if 'green' is written on top? Correct, everything is green. Remember, top to bottom. First to check is the else argument? Well, let's take it!

Get a database error in the frontend? Check this option to view the produced SQL statement and various configuration material that might give you a hint (in case you know SQL) which configuration is wrong. Don't forget to disable it again afterwards.

For very large tables this setting might improve performance a bit. Especially if you have lots of callback functions and special links defined, this option might help you a little.

Output is per default htmlentities'ed – check this to disable it. This is another very important setting if your frontend doesn't do what it's expected to (see charset option).

When adding or editing entries the extension tries to find the best solution of input box for you. Typically bool values are simulated in databases using tinyint fields with sizes from 1 to 3. So the extension provides a checkbox instead of an input field in such cases. You can disable this behavior by checking this option.

You have UNIX timestamps in your database? Bring them to a human-readable format by entering the column names here. Example (from pages table): SYS_LASTCHANGED, crdate

When editing datetime fields you might want to offer the user a calendar. Enter the column names here and have the date2cal or the erotea_date2cal extension installed.

Note that this doesn't work with unformatted UNIX timestamps. You need to convert them first by using the previous setting (Format timestamps as dates).

Choose the format you want to present the formatted timestamps in. Also this is the format used for the calendar plugin(s).

This isn't implemented yet. I tried to do so (this is why this option is still here) but didn't make it in time).

UPDATE: I tried it one more time but without a popup window it's currently not possible. Still on the ToDo list.

In case editing is enabled the user is by default allowed to edit all the fields that are shown in the table. You may want to restrict this so here is your tool. Enter those column names (not your individual titles) and separate them with a coma that you want to be editable. As an example imagine a table that consists out of the columns id, name, children and age. You only display the columns name, children and age but want the user only to be able to edit children and age. Enter 'children, age' here and the user sees name, children and age but when editing only children and age can be accessed.

In case your table provides a deleted flag and you don't want to delete records completely on a delete click this option is for you. Best example is the fe_users table. If you delete a frontend user in TYPO3 it's not completely deleted but set as deleted in the database. Same here. Example configuration for TYPO3 deletion flags: deleted; 0; 1

This tells the extension that the field deleted is the flag; if it's set to 0 a record is not deleted, if set to 1 it's deleted! What does the SQL frontend now do?

Sometimes you want to log the last changes in a table by adding an UNIX timestamp on each change. This field can do so for you. If you want to edit the TYPO3 pages table you would have to insert hert: SYS_LASTCHANGED

This column holds the UNIX timestamp when the last change has been done. SQL Frontend always sets this field on editing (well only on saving, not on aborting) and when adding a new entry; not on deletion!

Since version 2.4.0 this feature is splitted:

It's now possible to have both, a creation and a last edited date. The changedate timestamp on add will be written every time a new entry is created. The changedate timestamp on save will be set when editing one (not on deletion).

In case you have numbers that should be summed up, use this function. Just enter the column name(s), for which you want sums to be shown. Those values will be processes using the same options as the rest of this column. This means that if you have e.g. an outwards callback function defined for the column 'price' that prefixes the value with a currency symbol, also the sum is processed using this callback function.

If enabled the table gets a footer appended, which includes two values per defined column, the upper one indicates the sum of all currently shown entries (e.g. all on page one or all currently filtered ones), the number beyond this subtotal is the complete total (the one with the sum icon) of all numbers in that table. Still this number does not include entries that are excluded via a WHERE statement part.

You have foreign keys in your table? Here is what you are looking for. Configure SQL Frontend to show not the ID located in this table but the referenced value from the other table. And in editing/adding mode not an input field but a drop-down is presented containing all the possible values from the other table. What???

It's simple: A tt_content entry always has a PID which represents the page this content element is located on. This is a foreign key since it is just the page id referencing to the pages table that has a uid field holding this exact ID. Normally you don't want to display this PID but the pagetitle instead. Configure SQL Frontend to do so – in our table we want PID (own column) to be shown as the table header (show col) from the pages table (table) which holds our PID on its UID field (foreign col). Example configuration: pid, pages, uid, title

Looks good but how is it ordered? Well, actually not at all. To do so use the 'order' option. We want to sort by title ascending so we extend our configuration: pid, pages, uid, title, title asc

But what's that? Pages already deleted and also hidden ones are shown? Well, of course, they are inside the pages table. How to change? Use the where part: pid, pages, uid, title, title asc, deleted = 0 and hidden = 0

Now we have what we want. Imagine we want to show the title and and pid instead – SQL cracks know: concat is what we want to use: pid, pages, uid, concat(uid, ' ', title), title asc, deleted = 0 and hidden = 0

Now our output is completely dead – why? Well, first of all because we used comas where they are not expected. We need to prefix them with a backslash. And secondly because the extension prefixes our columns with the table name in order to prevent confusion if our main and our foreign table have same colums, so our statement would contain something like page.concat(...) - not good! To prevent it doing so, prefix it with a #. New configuration: pid, pages, uid, #concat(uid\, ' '\, title), title asc, deleted = 0 and hidden = 0

Well done. One more thing: Our drop-down now offers only not-deleted and not-hidden entries. So far so good but what if an already existing entry has a hidden page on its pid field? It's not shown in the frontend (remember: we don't want to show hidden pages). SQL Frontend allows us to distinguish between adding/editing and viewing foreign keys. So we can say that our WHERE part form the foreign key is different depending on if the user only views it or wants to change it. Parts that shouldn't be in there when viewing can be surrounded with squared brackets. We want to show hidden ones but never allow deleted pages. New configuration: pid, pages, uid, #concat(uid\, ' '\, title), title asc, deleted = 0 [ and hidden = 0 ]

And last but not least: We have two or more foreign keys in our table – what should be do? Separate them with a semicolon: pid, pages, uid, title; cruser_id, be_user, uid, username

Don't forget to prefix otherwise-used semicolons with a backslash! In case you still receive errors or simply nothing, enable the debug mode that might help you a little.

First of all: sbst means substituted!

If you want to create links out of various columns use this option. Give it a name (this is the one used in DATABASE > Show those database fields and links), enter the caption that is shown in the frontend and the link it represents. In the caption and in the link section all columns surrounded with ###_### are substituted.

An example – we list fe_users who have a homepage entered in the 'www' field. Basic configuration: link_www: click here to view my homepage: ###www###

Now in the fronend we have a link in each line saying ''click here to view my homepage' linked to the link on the field 'www'. Works but doesn't look very good. It's better to show also the homepage: link_www: ###www###: ###www###

And more links are separated with a semicolon (again here you may prefix various double points and semicolons with a backslash). Note that in editing mode links are not available.

In case the link part is numeric, it is supposed to be a page ID inside the current TYPO3 installation and the TYPO3 function to link to this page is called. This also works if the page ID comes from a database field. So in case the field ###www### contains a number, it is converted to the correct link (for example realURL speaking URLs, no_cache parameters, etc.).

This is a very advanced setting for people who understand also a bit of PHP. If you want fields to be completely reformatted on output or even on input you can define callback functions that are called using the value before going into the database or before showing it in the table. The functions need to be defined in the extension directory (typically typo3conf/ext/mh_omsqlio/) and then in the pi1/ folder inside the file 'class.tx_mhomsqlio_callback.php'. Also there are further explanations about the functions itself.

The configuration here has to include the column (or link name) for which you want to use the function, the function name itself (without any brackets or class names) and one of the keywords in or out – indicating if this function should be called in input (into the database) or on output (before showing it on the fronend. Example: telephone: out_linkCcm: out

Note that in this example the out_linkCcm is the function name, the prefixed 'out_' is part of the function name and not a needed prefix. This example will be used for the column 'telephone'.

Hint: If you need more (various) column contents for your callback function a little trick can be used: Define a link where you put all the necessary information into the caption part. Then define the callback function for this link. Probably you should separate the values with a special character and inside your callback function explode it again. So, slower:

There are already a few callback functions defined, maybe you could use one of those:

Since version 2.3.0 a separate callback file can be created which doesn't get overwritten every time the extension is updated from the repository. The file stored in the upload folder of your TYPO3 installation has to be in a certain form so you may just copy the sample file from the extension directory, pi1/class.tx_mhomsqlio_privatecallback.php to the uploads/tx_mhomsqlio/ directory. The extension then checks whether a file named class.tx_mhomsqlio_privatecallback.php exists inside the uploads/tx_mhomsqlio/ directory and if the class tx_mhomsqlio_privatecallback is defined. If so, this class is used (and has to be inherited from tx_mhomsqlio_callback), otherwise the 'normal' class.tx_mhomsqlio_callback.php inside the pi1/ folder is used. Remember that the class.tx_mhomsqlio_privatecallback.php inside the pi1/ directory won't be used at all, this is just a draft for you to understand how the new file has to look like.

Basically this function does nothing else but predefining values for the database. These columns don't have to be shown, but could be, and if so, the user has the final say. Two examples for an easier understanding: On a page news entries can be edited using the SQL Frontend plugin. A pre-defined value may be set for the page ID of the news entries, even though the user should neither be able to see nor to edit it. Example configuration: pid: 152

In this case for every entry that is added or edited, the pid of this entry is set to 152, so the news entry is stored on this page. Another useful case could be the pre-definition of a field the user should be able to edit. For example a list of attendants to a party including a column indicating how many persons one will bring. Most of the time a '2' will go here but sometimes people come alone or bring more than just one person with them. The field should be shown but a simple persons: 2 will do the trick and write 2 as a default value into the field. The user might then overwrite and change or just accept it.

You may enter manual JS code here (always use semicolons at the end since other code might be added) in order to update something everytime the list gets reordered, filtered or page switched. Basically this code is called every time the frontend view changes because of JavaScript (not on adding, not on searching, not on deleting, not on editing, etc.).

Read the function above before. Code here is executed every time the frontend changes because of PHP. This is done on editing (well, on saving respectively), adding (the time you click the disk symbol) and deleting.

File extensions entered here are forbidden and not allowed to be uploaded. This field is case-insensitive and always works in combination with the previous setting (permitted file extensions). In case this list is empty, only files which are permitted (see setting above) are allowed to be uploaded.

Notice: Always both settings (permission and prohibition list) are taken into account. If both lists include the pdf extension, pdf files are not allowed to be uploaded since they appear in the prohibition list.