Literatur zu verschenken II

Bereits zum zweiten Mal gibts IT-Fachliteratur, die ich nicht mehr brauche und deshalb verschenke. Alle Bücher sind gelesen und entsprechend leicht gebraucht, aber in ausgezeichnetem Zustand. Geld nehme ich keines, alle sind zu verschenken (Über das Porto unterhalten wir uns noch …).

Hier die Liste. Wer Interesse hat, hinterlässt einen Kommentar, schreibt mir per Twitter, Facebook oder schickt eine E-Mail. Und natürlich gilt: Wer zuerst kommt, mahlt zuerst.

  • Levy, Steven (2012): Google Inside. Wie Google denkt, arbeitet und unser Leben verändert. Heidelberg: mitp.
    544 Seiten, UVP: 24,95 €
    ISBN/Link: 978-3-8266-9243-7
  • Mayerhofer, Josef (2012): Apps erfolgreich verkaufen. Vermarktungsstrategien für Apps auf iPhone, iPad, Android und Co. München: Hanser.
    346 Seiten, UVP: 24,90 €
    ISBN/Link: 978-3-446-43028-0
  • Zenner, Roman & Busch, Jürgen (2012): Online-Shops mit OXID eShop. Praxiswissen für die eigene Shop-Lösung. Köln: O’Reilly.
    272 Seiten, UVP: 34,90 €
    ISBN/Link: 978-3-86899-153-6

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` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`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,
`seats` INT(5) UNSIGNED NOT NULL,
`tstamp_add` INT(35) UNSIGNED NOT NULL DEFAULT '0',
`tstamp_edit` INT(35) UNSIGNED NOT NULL DEFAULT '0',
`deleted` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) 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` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL,
`parent_id` INT(10) UNSIGNED NOT NULL,
`deleted` TINYINT(2) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
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

TYPO3 Extensions

Ever since people visiting my website reach it through search engines using query terms like ‘typo3′, ‘extension’ or the names of my extensions ‘mh_ajaxsearch’ or ‘mh_omsqlio’ (AJAX Searching Engine or SQL Frontend, respectively) – the ones developed by me with the most downloads. With this post I would like to help those visitors find what they might be looking for: some sort of FAQ, based on the questions reaching me (mostly) via email (nevertheless, this post shall and will not replace the documentations here and here).

  1. How to configure the extensions?
    RTFM. Seriously, I spend a lot of time on writing the documentations because I don’t have the time to answer the same questions all the time (basically, this is also the reason why I am doing this blog post).
  2. SQL Frontend: I only see the spinner but nothing loads. Why?
    Mostly, this is a problem due to certain AJAX loading problems. One might be the caching of TYPO3 – you have to switch it off for this special page (edit page properties > no caching). Another one is the character set settings. In case you have special characters in your data (and believe me, most of the time you have – special characters are, for instance, German Umlaute like ‘ö’ or ‘Ä’, but also special characters created by your office products like three dots as one character ‘…’) this might also cause JavaScript to prevent the AJAX stream to load properly and hence results in a never-ending spinner.
    In order to debug that you may use a JavaScript debugger like built-in developer tools in Google’s Chrome (CTRL + Shift + J > Network) or Firebug for Mozilla’s Firefox (small bug icon in lower right corner > Network). Scan the result from the POST loading AJAX call (for index.php) for such signs and try to repair it inside the database or change the charset settings in the backend.
  3. I want more features. Do you implement them for me?
    Maybe. There is a small chance that I need the feature as well or that it is very easy for me to implement them which could make you happy. Otherwise no, sorry.
  4. And if I pay you?
    Maybe. There is a small chance that I find the time to do so, but mostly I don’t have so much free time. Sorry.
    You may, nevertheless, be thankful for the extension and want to express that. If so, feel free to use my Amazon wishlists for photography or reading, but please keep in mind that Amazon is not only good and consider showing your generosity through any other bookstore or photography shop.
  5. But what if I developed some more features. Do you implement my code?
    Maybe. That depends if I also see the need for this feature or if it just makes the extension bigger (and – thus – slower) but doesn’t help anything. And I also want to check your code for security issues. To sum up: There is a chance that I implement your code.
  6. Do you translate the extension?
    I won’t.
  7. May I translate the extension?
    Please feel free to do so. And if you do so, send me the translation files. I will be happy to integrate them (please keep in mind that the mh_lib extension is needed for the SQL Frontend, so consider translating that as well – it’s not very much).

This list is subject to change and will be continued …