Hello TYPO3 Enthusiasts! If you’ve ever faced the challenge of needing to replace certain strings, URLs, or text across a large number of TYPO3 pages, you know it can be quite a task. In this tutorial, we’ll walk you through a custom PHP script that does a massive find & replace operation in your TYPO3 database.
Why Would You Need a Mass Find & Replace?
- Migrating Domains: Perhaps you’ve moved from example.com to newdomain.com, and you want to update all references.
- Fixing Old URLs: Maybe you changed your site structure, or you’ve discovered broken links.
- Changing Custom Text or Links: You might want to replace certain text or brand names across many pages.
Rather than opening 100+ pages manually, a quick script can save you hours (or even days!) of repetitive work.
Important Tips Before Starting
- Back Up Your Database: Any direct manipulation of the database comes with risks. Always create a full backup of your database (e.g., via phpMyAdmin or MySQL dump) before running scripts that update records.
- Work in a Test Environment: Run and test your script on a development/staging environment first. Only move to production once you’re confident.
- Verify Table/Column Names: Your TYPO3 installation might have slightly different table or column names if you customized it. Double-check everything.
Guide to Mass Find & Replace in TYPO3 Pages & tt_content
1. Set Up Your Database Credentials
You’ll need to connect to the same database that your TYPO3 installation uses. You can usually find this info in typo3conf/LocalConfiguration.php.
$host = 'localhost';
$dbName = 'YOUR_DATABASE_NAME';
$dbUser = 'YOUR_DB_USER';
$dbPass = 'YOUR_DB_PASSWORD';2. Create a CSV File of Replacements
For convenience, create a comma-separated CSV file with three columns:
- Page URL (Slug) – This will be used to find the correct page in the pages table.
- Old String/URL – The text you want to replace.
- New String/URL – The new text that will replace the old one.
Example CSV (each line has 3 columns):
https://t3planet.de/en/blog/learn-typo3-resources/,/en/customer/account,/en/enn3/
https://t3planet.de/blog/learn-typo3-resources/,/en/customer/account,/en/dee2/Note: This CSV can be anywhere you like—on your local machine, server, etc. In our tutorial, we’ll just use a hard-coded string for simplicity, but you can easily replace that part with a file_get_contents('/path/to/your-file.csv').
3. Understand the Table Structure
In this tutorial, we’re focusing on two core TYPO3 tables:
Pages
- Contains your page records.
- We’ll look up the field slug (or uid if you prefer) to find the correct page.
- Additionally, we might filter by sys_language_uid if you’re dealing with multi-language sites.
tt_content
- Stores the content elements for each page.
- Typical fields include header, bodytext, pi_flexform, etc.
- We’ll be updating bodytext (and optionally pi_flexform) by doing a find & replace.
- The tt_content table links back to pages via pid (or page_id in some custom cases).
4. The PHP Script
Below is the full script. Make sure you place it in a secure location, because anyone who accesses it could perform changes in your database! Adjust the credentials, table names, and CSV data as needed.
<?php
/**
* Mass Find & Replace Script for TYPO3
*
* Requirements:
* - pages table with fields: uid, slug, sys_language_uid
* - tt_content table with fields: pid (matching pages.uid), bodytext, pi_flexform
*
* This script:
* 1) Looks up the pages.uid based on the CSV's page slug and language.
* 2) Runs REPLACE() on tt_content.bodytext & tt_content.pi_flexform
* for records matching pid = pages.uid (and a sys_language_uid if desired).
*/
$host = 'localhost';
$dbName = 'YOUR_DATABASE_NAME';
$dbUser = 'YOUR_DB_USER';
$dbPass = 'YOUR_DB_PASSWORD';
try {
// 1. Connect to database using PDO
$pdo = new PDO(
"mysql:host=$host;dbname=$dbName;charset=utf8",
$dbUser,
$dbPass,
[
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // Throw exceptions on errors
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]
);
} catch (PDOException $e) {
die("Database connection failed: " . $e->getMessage());
}
// 2. Load CSV data (example uses a hardcoded string)
$baseDomain = "https://t3planet.de";
// $csvData = file_get_contents('/path/to/your-file.csv');
$csvData = <<<CSV
https://t3planet.de/en/blog/learn-typo3-resources/,/en/learn-typo3,/en/new-learn-typo3
https://t3planet.de/blog/lernen-typo3-tessourcen/,/en/lernen-typo3,/en/new-lernen-typo3
CSV;
// 3. Parse CSV lines
$lines = explode("\n", trim($csvData));
foreach ($lines as $lineIndex => $line) {
$columns = str_getcsv($line); // Split a CSV line into an array
if (count($columns) !== 3) {
echo "Line " . ($lineIndex + 1) . ": Invalid CSV format, skipping.<br>";
continue;
}
list($pageUrl, $oldUrl, $newUrl) = $columns;
// Remove domain from the pageUrl
$pageUrl = str_replace($baseDomain, '', $pageUrl);
// Determine sys_language_uid (example: /en/blog => sys_language_uid=0, otherwise 1)
if (str_contains($pageUrl, '/en/blog')) {
$pageUrl = str_replace("/en/blog", "", $pageUrl);
$sys_language_uid = 0;
} else {
$pageUrl = str_replace("/blog", "", $pageUrl);
$sys_language_uid = 1;
}
// Clean up trailing slashes & whitespace
$pageUrl = rtrim($pageUrl, '/');
$pageUrl = trim($pageUrl);
$oldUrl = trim($oldUrl);
$newUrl = trim($newUrl);
// Basic sanity check
if (empty($pageUrl) || empty($oldUrl) || empty($newUrl)) {
echo "Line " . ($lineIndex + 1) . ": One or more columns are empty, skipping.<br>";
continue;
}
// 4. Find the page UID based on slug & language
try {
$pageQuery = $pdo->prepare("
SELECT uid
FROM pages
WHERE slug = :slug
AND sys_language_uid = :sys_language_uid
");
$pageQuery->bindValue(':slug', $pageUrl, PDO::PARAM_STR);
$pageQuery->bindValue(':sys_language_uid', $sys_language_uid, PDO::PARAM_INT);
$pageQuery->execute();
$page = $pageQuery->fetch();
if (!$page) {
echo "NO PAGE FOUND: slug '{$pageUrl}' does not exist for sys_language_uid = {$sys_language_uid}.<br>";
continue;
}
$pageId = (int)$page['uid'];
} catch (Exception $e) {
echo "ERROR retrieving page uid for slug '{$pageUrl}': " . $e->getMessage() . "<br>";
continue;
}
// 5. Replace oldUrl with newUrl in tt_content for that page
try {
$stmt = $pdo->prepare("
UPDATE tt_content
SET bodytext = REPLACE(bodytext, :oldUrl, :newUrl),
pi_flexform = REPLACE(pi_flexform, :oldUrl, :newUrl)
WHERE pid = :pid
AND sys_language_uid = :sys_language_uid
");
$stmt->bindValue(':oldUrl', $oldUrl, PDO::PARAM_STR);
$stmt->bindValue(':newUrl', $newUrl, PDO::PARAM_STR);
$stmt->bindValue(':pid', $pageId, PDO::PARAM_INT);
$stmt->bindValue(':sys_language_uid', $sys_language_uid, PDO::PARAM_INT);
$stmt->execute();
$affectedRows = $stmt->rowCount();
if ($affectedRows > 0) {
echo "SUCCESS: Updated {$affectedRows} row(s) for Page UID: {$pageId}, Slug: {$pageUrl}<br>";
} else {
echo "NO CHANGE: No rows updated for Page UID: {$pageId}, Slug: {$pageUrl}<br>";
}
} catch (Exception $e) {
echo "ERROR updating content for slug '{$pageUrl}': " . $e->getMessage() . "<br>";
}
}
// Script completion message
echo "<br>--- Script Completed ---<br>";
?>5. Test Thoroughly!
- Run on a Test Environment: The script updates database records, so test on a non-production copy first.
- Verify Changes: Look at a few pages on your TYPO3 site to ensure the old string was correctly replaced.
Conclusion
That’s it! You now have a powerful script to perform mass find & replace on your TYPO3 content. This is a lifesaver when dealing with large-scale changes—just remember to always back up and test thoroughly.
If you found this helpful, feel free to share with other TYPO3 users and contribute your improvements back to the community! If you have any questions or suggestions, drop a comment on our T3Planet blog.
Happy TYPO3-ing!
Post a Comment