Cool, thanks for sharing this!

Make sure your database table name is correct.

create a file called runSitemap.php

<?php
// Define your base URL
$baseUrl = 'https://www.yourwebsite.com';

// Database connection (modify with your credentials)
$dsn = 'mysql:host=localhost;dbname=yourdatabase';
$username = 'yourusername';
$password = 'yourpassword';
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
];

try {
    $pdo = new PDO($dsn, $username, $password, $options);
} catch (PDOException $e) {
    die('Database connection failed: ' . $e->getMessage());
}

// Fetch URLs from the database
$urls = [];
try {
    $query = $pdo->query('SELECT permalink FROM cmsb__permalinks WHERE old=0');
    while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
        $urls[] = $row['permalink'];
    }
} catch (PDOException $e) {
    die('Query failed: ' . $e->getMessage());
}

// Debugging: output the URLs to ensure they are fetched correctly
if (empty($urls)) {
    die('No URLs found or there was an error fetching them.');
}

// Function to generate the sitemap XML
function generateSitemap($baseUrl, $urls) {
    $sitemap = '<?xml version="1.0" encoding="UTF-8"?>' . PHP_EOL;
    $sitemap .= '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">' . PHP_EOL;
    
    foreach ($urls as $url) {
        // Ensure there are no leading/trailing slashes in the URL
        $trimmedUrl = trim($url, '/');
        $fullUrl = $baseUrl . '/' . $trimmedUrl . '/';
        $sitemap .= '  <url>' . PHP_EOL;
        $sitemap .= '    <loc>' . htmlspecialchars($fullUrl) . '</loc>' . PHP_EOL;
        $sitemap .= '    <lastmod>' . date('Y-m-d') . '</lastmod>' . PHP_EOL;
        $sitemap .= '    <changefreq>monthly</changefreq>' . PHP_EOL;
        $sitemap .= '    <priority>0.8</priority>' . PHP_EOL;
        $sitemap .= '  </url>' . PHP_EOL;
    }
    
    $sitemap .= '</urlset>' . PHP_EOL;
    return $sitemap;
}

// Check if the script is accessed via a browser
if (php_sapi_name() !== 'cli' && isset($_SERVER['REQUEST_METHOD'])) {
    // Generate the sitemap XML content
    $sitemapContent = generateSitemap($baseUrl, $urls);

    // Save the sitemap to the root directory
    if (file_put_contents($_SERVER['DOCUMENT_ROOT'] . '/sitemap.xml', $sitemapContent) === false) {
        die('Failed to write sitemap.xml');
    }

    echo "Sitemap generated successfully.";
} else {
    echo "This script can only be run from a web browser.";
}
?>