Massensuche & Ersetzen in TYPO3-Seiten & tt_content durchführen

$host   = 'localhost';
$dbName = 'YOUR_DATABASE_NAME';
$dbUser = 'YOUR_DB_USER';
$dbPass = 'YOUR_DB_PASSWORD';

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/

<?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>";
?>