[Translate to English:]

How to extend TYPO3 database tables with new fields

Based on two additional page properties I'll demonstrate how you can add own fields to TYPO3. We'll extend the tables 'pages' and 'pages_language_overlay' by means of a small TYPO3 extension.

Objective

We'll configure two new page properties: an additional input field, free to use, and our very own checkbox. The input field will be usable both in the original language and the alternative page language(s). However, for our example we'll assume that the checkbox is only to be set in the original language because its function should apply to all languages of the respective page.

tl;dr

You can find the complete extension on Github:

Download demo

File structure

To extend database tables in TYPO3, you'll need a determined folder and file structure in our extension:

  • in Configuration/TCA/Overrides the new fields for already existing tables are configured. The file names must match the exact database table's name.
  • in Resources/Private/Language I stored the field's labels for the Backend forms in english language and also added a german translation. While this is the proper way to handle labels, you could just write the names hardcoded (without translations) into the $TCA, too.
  • ext_emconf.php contains the extension's basic configuration. Without this file, the extension is not even shown in TYPO3.
  • ext_icon.png ist displayed as an icon in the Extension Manager
  • in ext_localconf.php we make sure that the content of our input field can be used multilingual in the Frontend
  • in ext_tables.sql we extend the MySQL database of TYPO3

I named our little extension pages_addfields.

Basic configuration of our extension

The declaration file ext_emconf.php includes title and description for the extension, as well as a version number, informations about the author and constraints like the supported TYPO3 versions, if need be. Our extension is compatible with at least the latest LTS versions 6.2 and 7.6.

ext_emconf.php

<?php
 
$EM_CONF[$_EXTKEY] = array(
    'title' => 'Additional pages field(s)',
    'description' => 'Provides a basic example how to extend pages with your own fields.',
    'category' => 'example',
    'author' => 'Sebastian Klein',
    'author_email' => 'sebastian@sklein-medien.de',
    'state' => 'alpha',
    'internal' => '',
    'uploadfolder' => '0',
    'createDirs' => '',
    'clearCacheOnLoad' => 0,
    'version' => '1.0.0',
    'constraints' => array(
        'depends' => array(
            'typo3' => '6.2.0-7.6.99',
        ),
        'conflicts' => array(
        ),
        'suggests' => array(
        ),
    ),
);

Create the new database fields

The new fields are set within ext_tables.sql. While we have to use the command CREATE TABLE, TYPO3 checks for existing tables with this name and merely adds the new fields there.

It's not mandatory to use the extension key as part of the new field's names (here: tx_pagesaddfields). But it could circumvent problems with another extension which itself adds a field with the same name.

ext_tables.sql

#
# Modifying pages table
#
CREATE TABLE pages (
    tx_pagesaddfields_customtext varchar(255) DEFAULT '' NOT NULL,
    tx_pagesaddfields_customcheckbox TINYINT(1) UNSIGNED DEFAULT '0' NOT NULL
);
 
#
# Modifying pages_language_overlay table
#
CREATE TABLE pages_language_overlay (
    tx_pagesaddfields_customtext varchar(255) DEFAULT '' NOT NULL
);

If you subsequently edit the SQL, you'll have to uninstall the extension and activate it again to have TYPO3 recognize the changes.

Table Configuration Array ($TCA)

To use database tables in TYPO3, they must have a corresponding entry in $TCA.

In the first step we configure our new fields as an input field and a checkbox. A brief overview of the configuration:

  • label: the name of the field in the Backend. Could be a hardcoded string; here we'll use the locallang.xlf
  • exclude: if set, this field is part of the so-called "Allowed excludefields" and is not available to non-admin users. These fields must be enabled through a Backend usergroup
  • config: the field type. In this case we use very simple settings for our fields, nonetheless we could extend this configuration

Afterwards we append the fields to the existing database table, using the TYPO3 API method addTCAcolumns. Then we use addToAllTCAtypes to to make the fields visible in the TCEforms. Finally our new palette is added to the page properties.

As we assume in this example that the checkbox is set globally for all languages of a page, we solely need to configure the input field for the alternate page languages.

Configuration/TCA/Overrides/pages.php

<?php
if (!defined('TYPO3_MODE')) {
  die ('Access denied.');
}
 
// Configure new fields:
$fields = array(
  'tx_pagesaddfields_customtext' => array(
    'label' => 'LLL:EXT:pages_addfields/Resources/Private/Language/locallang_db.xlf:pages.tx_pagesaddfields_customtext',
    'exclude' => 1,
    'config' => array(
      'type' => 'input',
      'max' => 255
    ),
  ),
  'tx_pagesaddfields_customcheckbox' => array(
    'exclude' => 1,
    'label' => 'LLL:EXT:pages_addfields/Resources/Private/Language/locallang_db.xlf:pages.tx_pagesaddfields_customcheckbox',
    'config' => array(
      'type' => 'check',
      'default' => 0
    )
  )
);
 
// Add new fields to pages:
\TYPO3\CMS\Core\Utility\ExtensionManagementUtility::addTCAcolumns('pages', $fields);
 
// Make fields visible in the TCEforms:
\TYPO3\CMS\Core\Utility\ExtensionManagementUtility::addToAllTCAtypes(
  'pages', // Table name
  '--palette--;LLL:EXT:pages_addfields/Resources/Private/Language/locallang_db.xlf:pages.palette_title;tx_pagesaddfields', // Field list to add
  '1', // List of specific types to add the field list to. (If empty, all type entries are affected)
  'after:nav_title' // Insert fields before (default) or after one, or replace a field
);
 
// Add the new palette:
$GLOBALS['TCA']['pages']['palettes']['tx_pagesaddfields'] = array(
  'showitem' => 'tx_pagesaddfields_customcheckbox,tx_pagesaddfields_customtext'
);

Configuration/TCA/Overrides/pages_language_overlay.php

<?php
if (!defined('TYPO3_MODE')) {
  die ('Access denied.');
}
 
// Configure new field:
$fields = array(
  'tx_pagesaddfields_customtext' => array(
    'label' => 'LLL:EXT:pages_addfields/Resources/Private/Language/locallang_db.xlf:pages.tx_pagesaddfields_customtext',
    'exclude' => 1,
    'config' => array(
      'type' => 'input',
      'max' => 255
    ),
  )
  // In this example, we assume that the custom checkbox is only used in the original language. So, no need to configure it here.
);
 
// Add new field to translated pages:
\TYPO3\CMS\Core\Utility\ExtensionManagementUtility::addTCAcolumns('pages_language_overlay', $fields);
 
// Make field visible in the TCEforms:
\TYPO3\CMS\Core\Utility\ExtensionManagementUtility::addToAllTCAtypes(
  'pages_language_overlay', // Table name
  '--palette--;LLL:EXT:pages_addfields/Resources/Private/Language/locallang_db.xlf:pages.palette_title;tx_pagesaddfields', // Field list to add
  '', // List of specific types to add the field list to. (If empty, all type entries are affected)
  'after:nav_title' // Insert fields before (default) or after one, or replace a field
);
 
// Add the new palette:
$GLOBALS['TCA']['pages_language_overlay']['palettes']['tx_pagesaddfields'] = array(
  'showitem' => 'tx_pagesaddfields_customtext'
);

Localization of the Backend labels

The usage of these localization files has nothing to do with the output in the Frontend. Instead they are used to support multilingualism in the Backend. In this way, the labels of our new fields can be localized for foreign Backend users.

As seen above, you can use the file like this: LLL:EXT:pages_addfields/Resources/Private/Language/locallang_db.xlf:, followed by the ID of the localized data. When setting German as Backend language, TYPO3 looks for a file which is prepended with the german language code and a dot within the same folder as the original file, and then scans it for the translation in the target element.

Resources/Private/Language/locallang_db.xlf

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<xliff version="1.0">
    <file source-language="en" datatype="plaintext" original="messages" date="2016-06-18T12:03:24Z"
          product-name="pages_addfields">
        <header/>
        <body>
            <trans-unit id="pages.palette_title">
                <source>My custom fields</source>
            </trans-unit>
            <trans-unit id="pages.tx_pagesaddfields_customtext">
                <source>Custom text field</source>
            </trans-unit>
            <trans-unit id="pages.tx_pagesaddfields_customcheckbox">
                <source>Custom checkbox</source>
            </trans-unit>
        </body>
    </file>
</xliff>

Resources/Private/Language/de.locallang_db.xlf

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
<xliff version="1.0">
    <file source-language="en" datatype="plaintext" original="messages" date="2016-06-18T12:03:24Z"
          product-name="pages_addfields" target-language="de">
        <header/>
        <body>
            <trans-unit id="pages.palette_title">
                <source>My custom fields</source>
                <target>Meine benutzerdefinierten Felder</target>
            </trans-unit>
            <trans-unit id="pages.tx_pagesaddfields_customtext">
                <source>Custom text field</source>
                <target>Benutzerdefiniertes Textfeld</target>
            </trans-unit>
            <trans-unit id="pages.tx_pagesaddfields_customcheckbox">
                <source>Custom checkbox</source>
                <target>Benutzerdefinierte Checkbox</target>
            </trans-unit>
        </body>
    </file>
</xliff>

Render the translated value of the input field in the Frontend

If we create an alternative page language and fill our input field at this time, the value is saved correctly in the database table pages_language_overlay. You would be disappointed if you tried to render this translated values in the Frontend, though: only the content of the original page language would be shown.

You have to add the input field to the list of pageOverlayFields, otherwise TYPO3 doesn't use it for page translations. You could achieve that by adapting the LocalConfiguration.php via the TYPO3 Install Tool, but it's much more convenient to set it in the extension's ext_localconf.php:

<?php
if (!defined('TYPO3_MODE')) {
  die ('Access denied.');
}
 
$TYPO3_CONF_VARS['FE']['pageOverlayFields'] .= ',tx_pagesaddfields_customtext';

Done. Now what?

It's totally up to you how you mean to use the new fields. As an example, you could use the checkbox as a condition to output the input field's value:

10 = TEXT
10.field = tx_pagesaddfields_customtext
10.if.isTrue.field = tx_pagesaddfields_customcheckbox
Back