Display Tables within TYPO3 using SQL Frontend

As the introductionary sentence on the TYPO3.org extension repository says, the SQL Frontend (mh_omsqlio) lets you “[p]ick a table in any database which gets shown to the user. They then might order, edit, delete, add or duplicate entries using AJAX in it.” While this is true of course, the SQL Frontend is capable of a lot more than just ordering, editing, deleting or adding entries in a database table.

The full reference of features and a description for everyone is available in the official manual, which can be viewed online. This post is for you in case you probably want to use the extension and are some sort of beginner — either with the SQL Frontend or with database tables at all. It will run you through a complete example using all most of the features the extension offers.

1. Setting Up the Database Table

This first step might not be required, in case the table you want to use already exists. If not, you have to set it up first. Since I am vegetarian, in our example we will have a table of vegetarian restaurants that the owners of the restaurants are able to maintain. Therefore I create a database table with a few fields which I will explain later. For the moment the SQL code for creation (to insert for instance on the console or in phpMyAdmin or in HeidiSQL or …) should be enough.

CREATE TABLE `restaurant` (
`name` VARCHAR(150) NOT NULL,
`contact` TEXT NOT NULL,
`description` TEXT NOT NULL,
`website` VARCHAR(250) NOT NULL,
`images` TEXT NOT NULL,
`user_id` INT(11) UNSIGNED NOT NULL,
`welcome` TEXT NOT NULL,
`tstamp_add` INT(35) UNSIGNED NOT NULL DEFAULT '0',
`tstamp_edit` INT(35) UNSIGNED NOT NULL DEFAULT '0',
) COLLATE='utf8_general_ci' ENGINE=MyISAM;

2. Installing and Setting Up the SQL Frontend

Okay, now lets change to the TYPO3 backend. I am working on version 4.6.7 and have just installed mh_lib (which is required) and mh_omsqlio from the official repository in the extension manager.

Disable Caching
Disable Caching

Next, I create a new page on which the SQL Frontend shall appear. Most important here is to set the page to be a no cache page since the extension loads new data via AJAX and cannot handle cached database entries as these do not come live from the database. You do this by editing page properties and setting the Cache Disable checkbox on the Behavior tab.

Now we insert a new content element on this page and change its Type to Insert Plugin. After saving we can select SQL Frontend from the Plugin Dropdown and save again.

3. Basic Configuration

Blank setup
Not nice but working.

In order to just see something, all fields are fine except on the Database tab. Enter the database settings; in my case i created the table inside the TYPO3 database which gives me the opportunity just to check the Use TYPO3 Database checkbox and insert the table name. Save and update the frontend.

4. Choose and Name Fields

Okay, we don’t want visitors to see everything. So we choose the fields to be shown (Database > Show those database fields and links) and give them nicer names (Optical > Different column titles than in database).

5. Add, Edit and Delete Entries

Now we need the restaurants. So we enable addingediting and deleting (General), and, if you like, also duplicating and multiple editing. Save it and enter a few restaurants; leave the food and pictures fields empty.

First data
First data inserted.

6. Get into Gear

Proper formatting
Proper formatting

Okay, now lets do a couple of things at once, just to get the thing fancy. On General, enable the Search, Display ‘Show All’, enable CSV download for both and send emails on creation to your personal address (that won’t ever be shown). Inside the Database tab, set the default sorting column to `restaurant`.`name` asc and the amount of entries shown per page to 15. For optical reasons I also set fixed column width to 3%, 15%, 15%, 9%, 15%, 20%, 20%, 3%. Note that the first and last columns are the multiple checkbox and the edit columns. Finally, on the Advanced tab, enable RTE for description, set name, details to be fields that have to be filled out and set change field on delete to deleted; 0; 1 (this doesn’t delete entries but marks them deleted by setting the deleted flag from 0 to 1). Furthermore, set add timestamp to tstamp_add and edit timestamp to tstamp_edit, also show sum at the table bottom for seats.

7. File Upload

Next is to enable uploading within File Handling > Enable file upload(s) — just set it to images. In order to only allow images, set Permitted file extensions to jpg,jpeg,bmp,gif,png and also limit the maximum number of files to 3. Set really delete files when deleting, set the upload directory, create this folder and enable writing for the web user. And finally, since we don’t want to show the images directly but make them accessible for the user, use a special template for various file extensionsLINK: jpg,jpeg,bmp,gif,png

You might also want to allow users to include the images within their description. Therefore you have to Optical > Allow file references for the corresponding fields: description: images Doing so allows you (and the users) to write ###1### into the description field which then gets replaced with the first uploaded image from the images field.

8. Simple Foreign Keys

Foreign keys are a huge topic within the SQL Frontend. It allows nearly everything as long as you really know what you need. For starters, we just want to link a field within our database table to another database table. The field is called user_id and represents the frontend user’s id. It is by default set to 0 which is no user at all. In order to change that you would either set this setting before adding any entries to the table or — in our case — edit the entries manually in the database (using phpMyAdmin or HeidiSQL or something similar). So visit the database, have a look into the fe_users table and remember a uid which you insert into the user_id field of all the restaurant entries. Now it looks like this user has created all our restaurant entries (you may also vary the users of course).

In order to display this user we would first have to show the user_id field (put it into the Database > Show these columns field). Now the ID gets shown. For the username to be shown a foreign key configuration is needed. Under Advanced set Foreign Keys to be a link from restaurant’s user_id to fe_user’s uid showing the user’s username: user_id, fe_users, uid, username

When editing an entry, a dropdown now appears for the foreign key(s). If you want to sort that dropdown or just show a few of the entries, you may enhance the Foreign Keys configuration according to the manual. For now, we delete the foreign key again (from the Show Columns and the Advanced settings) since we don’t want it to be shown.

Instead, we want users only to be able to edit/delete own entries and set the user_id to be their own when creating/duplicating an entry. Got that point? Show all but only edit your own entries. And if you create one, mark it to be your’s in order to allow you to edit/delete it later. For this to be achieved, just set Current user (via ID) is only allowed to edit own entries (uid column) within the Database tab to user_id. Do the same for Current user (via ID) is only allowed to delete own entries (uid column). That’s it.

9. Advanced Foreign Keys

Now lets enable foreign keys for users in order to really use them. Therefore we want to add what the restaurant in fact offer which we want to put into a separate table. So, lets create it first (again, within phpMyAdmin or HeidiSQL or something similar).

CREATE TABLE `restaurant_food` (
`name` VARCHAR(100) NOT NULL,
`parent_id` INT(10) UNSIGNED NOT NULL,
UNIQUE INDEX `name_parent_deleted` (`name`, `parent`, `deleted`)
) COLLATE='utf8_general_ci' ENGINE=MyISAM;

While you would normally administrate this table using another instance of the SQL Frontend, we now just insert a few records in order to use this table. So execute the following statement to speed things up.

INSERT INTO `restaurant_food` VALUES
(1, 'Dessert', 0, 0),
(2, 'no gelatine', 1, 0),
(3, 'vegan (no honey, eggs, etc.)', 1, 0),
(4, 'Starters', 0, 0),
(5, 'gluten-free', 4, 0),
(6, 'vegan (no eggs, milk, etc.)', 4, 0),
(7, 'Main Courses', 0, 0),
(8, 'gluten-free', 7, 0),
(9, 'vegan (no eggs, etc.)', 7, 0),
(10, 'no meat', 1, 1);

An we are ready to use this new restaurant_food table with food types nicely linked to parental entries. In detail, this table now contains the following structure:

  • [4] Starters
    • [5] gluten-free
    • [6] vegan (no eggs, milk, etc.)
    • [10] no meat [deleted set to 1]
  • [7[ Main Courses
    • [8] gluten-free
    • [9] vegan (no eggs, etc.)
  • [1] Dessert
    • [2] no gelatine
    • [3] vegan (no honey, eggs, etc.)

You already know that but here’s a small reminder: for a simple foreign key selection field, nicely sorted by name and showing only non-deleted entries, we would enter on the Advanced tab into the Foreign Key field: welcome, restaurant_food, id, name, a.`name` ASC, a.`deleted` = 0 But of course, you already know that. So we don’t do that.

Instead we want the restaurants to offer more than one thing (allow multiple usage with unlimited options). To achieve this, we would use the Multiple foreign keys via comma-list field entering welcome, restaurant_food, id, name, , name ASC, deleted = 0

Multiple foreign tree keys
Multiple foreign tree keys

Nevertheless, we even want to go another step further and make use of the parental entries. Therefore lets use the Foreign tree key option and include the settings for multiple items and group collectors but not the own parent col setting: welcome, restaurant_food, id, parent_id, restaurant_food, id, name, name, a.name ASC, b.name ASC, a.deleted = 0, b.deleted = 0 AND b.parent_id = 0, , 1, 1

10. Additional Links

Finally, lets go for something easier. In order to show links instead of usual text as column content, you may use the Additional Links field within the Advanced tab. To show you I just edited the database table one more time directly, adding a website to one of the entries. It’s now possible to create an additional link that directs to the website but reads as the name: link_website: ###name###: ###website###

Additional links
Additional links

Still, this link isn’t shown. It’s just there — named link_website (take whatever name you like but no column names). To show it, we need to include it in our Database > Show columns and links field (I replaced the name column which isn’t that intelligent when trying to add or edit an entry. To avoid that you may either use two instances of the Frontend, one for editing, one for showing, or you help yourself using callback functions. Both options are explained inside the manual.). That’s it.

Complete Configuration

Hinterlasse eine Antwort

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind markiert *

Du kannst folgende HTML-Tags benutzen: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>