TYPO3 - the Enterprise Open Source CMS: Documentation: SQL Frontend (SQL Frontend)

Chapter 1. SQL Frontend

Extension Key: mh_omsqlio

Language: en

Keywords: forEditors, forAdmins, forIntermediates, forAdvanced, database, phpmyadmin, table

This document is published under the Open Content License

available from http://www.opencontent.org/opl.shtml

The content of this document is related to TYPO3

- a GNU/GPL CMS/Framework available from www.typo3.org

1.1. Introduction

What does it do?

The extension allows you to connect to a database anywhere out- or inside TYPO3 and shows a chosen table.
mh_omsqlio gives you a lot of possibilities for the frontend ...
1. you are able to edit, delete or add entries in the database.
2. you can search for anything in the database.
3. well-known database table stuff such as sorting, paging, CSV download, head renaming, etc.
4. auto filter (as known from OpenOffice Calc or Microsoft Excel)
5. re-formatting for boolean values, timestamps or foreign keys
6. comfortable editing views that offers calendars for datetime fields, checkboxes for boolean values, drop-downs for foreign keys, file upload elements, etc.
7. conditional formatting
...and backend:
1. you can define, what possibilities the frontend-user should have, such as edit, delete, add, search, duplicate, CSV download, indication for primary key
2. in case it's a user-specific table you might only present rows that fit the currently logged in frontend user
3. in order not to delete a row completely but set a special deleted flag you can tell the extension to do so
4. if you want to create links out of various table fields this is also no problem
5. and in case your needed option isn't here the extension offers callback functions per column (inwards and outwards)
The whole extension is based on the database abstraction layer 'adodb' which gives you the opportunity to connect to any SQL-like database you want (such as MySQL, MsSQL or any other ODBC connection). All you (probably) need is the PHP package therefore (please see http://at2.php.net/manual/en/mssql.setup.php and http://at2.php.net/manual/en/intro.uodbc.php for more information)

Screenshots

Picture 1 (left hand side): Part of an example output. The second line is currently in editing mode. Line three is hovered (the mouse pointer is located on it currently). The checkbox on the left-hand side is used for multiple editing.

Picture 2 (right hand side): Editing mode among each other.

Picture 3: A complete SQL frontend plug-in. 'Subject' and 'Description' columns are text-lines, 'Machine' is a foreign key to another database table, 'High Priority' is a boolean value, 'Workload [h]' represents a numeric field (recognize the sum values on the bottom of the table) and finally 'attachments' is a file upload column that has the files saved as coma-list into the database. The last column offers buttons for duplication and deletion of entries. Editing is enabled via a click into the row. The checkbox in the first column allows multiple editing (check out the dropdown below the table) like multiple deletion, partial or complete CSV download and multiple editing. Furthermore you may order the table, add new entries, filter it with the autofilter drop-down (e.g. the one on top of the 'Machine' column), hide single columns on login-basis (the currently logged in user may hide the 'Description' column via the little hiding icon next to the headline and cannot see it even after a re-login; other users are not affected by that), switch to the rest of the entries (Seen the page switching module on the right hand side?) or search the list. And if this is not enough, callback functions per line are also no problem.