Skip to content

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 table 'pages' 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 can be filled with individual content in the original language as well as in all translated pages.
  • For our checkbox, we'll extend the translation behavior: the state (active/inactive) will automatically be inherited to translated pages. An editor can override the value when needed.

The tutorial supports all TYPO3 versions since 9.5! In older TYPO3 versions, the database table pages_language_overlay was still required for localized pages. You can find this tutorial with the necessary additions for TYPO3 v8 and earlier in the Internet Archive (Wayback Machine).

Custom backend fields in source language
Custom backend fields in the page properties of the source language.
Custom backend fields in translated page
On the translated page, our checkbox receives complementary settings for the desired translation behavior.

tl;dr

You can find the complete extension on Github:

Download demo

Table of contents

File structure

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

File structure of a TYPO3 extension
  • In Configuration/TCA/Overrides, the new fields for already existing tables are configured. For clarity, the file names should match the name of database table.
  • 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.
  • Extension.svg ist displayed as an icon in the Extension Manager.
  • composer.json allows to install the extension with composer (explanation not part of this tutorial).
  • ext_emconf.php contains the extension's basic configuration. Without this file, the extension cannot be installed in TYPO3.
  • In ext_tables.sql we extend the database of TYPO3 with SQL.

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.

ext_emconf.php 

<?php

$EM_CONF[$_EXTKEY] = [
    'title' => 'Additional pages field(s)',
    'description' => 'Provides a basic example how to extend TYPO3 pages with your own fields.',
    'category' => 'example',
    'author' => 'Sebastian Klein',
    'author_email' => 'sebastian@sebkln.de',
    'state' => 'test',
    'version' => '3.0.0',
    'constraints' => [
        'depends' => [
            'typo3' => '11.5.0-12.4.99',
        ],
        'conflicts' => [
        ],
        'suggests' => [
        ],
    ],
];

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
);

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

The Table Configuration Array: $GLOBALS['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.
    Note the setting allowLanguageSynchronization in the checkbox: it adds the translation behavior described at the beginning.

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.

Configuration/TCA/Overrides/pages.php 

<?php
defined('TYPO3') or die(); // use 'TYPO3_MODE' in TYPO3 versions below v11

// Configure new fields:
$fields = [
    'tx_pagesaddfields_customtext' => [
        'label' => 'LLL:EXT:pages_addfields/Resources/Private/Language/locallang_db.xlf:pages.tx_pagesaddfields_customtext',
        'exclude' => 1,
        'config' => [
            'type' => 'input',
            'max' => 255
        ],
    ],
    'tx_pagesaddfields_customcheckbox' => [
        'exclude' => 1,
        'label' => 'LLL:EXT:pages_addfields/Resources/Private/Language/locallang_db.xlf:pages.tx_pagesaddfields_customcheckbox',
        'config' => [
            'type' => 'check',
            'default' => 0,
            'behaviour' => [
                'allowLanguageSynchronization' => true
            ]
        ]
    ]
];

// 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'] = [
    'showitem' => 'tx_pagesaddfields_customcheckbox,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.

Starting with TYPO3 12.0, a new option$GLOBALS['TYPO3_CONF_VARS']['SYS']['lang']['requireApprovedLocalizations'] exists. It is enabled by default. As a result, you need to add the attribute approved="yes" to your custom XLIFF translations.

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" approved="yes">
                <source>My custom fields</source>
                <target>Meine benutzerdefinierten Felder</target>
            </trans-unit>
            <trans-unit id="pages.tx_pagesaddfields_customtext" approved="yes">
                <source>Custom text field</source>
                <target>Benutzerdefiniertes Textfeld</target>
            </trans-unit>
            <trans-unit id="pages.tx_pagesaddfields_customcheckbox" approved="yes">
                <source>Custom checkbox</source>
                <target>Benutzerdefinierte Checkbox</target>
            </trans-unit>
        </body>
    </file>
</xliff>

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