salar salar - 11 days ago 6
MySQL Question

XML sitemap with data from MySQL

im trying to add data from mysql to sitemap but get this error

error on line 1 at column 1: Document is empty-----------------------------------------------------------------------------

my code is

$xmlfile = 'sitemap.xml';

// this variable will contain the XML sitemap that will be saved in $xmlfile
$xmlsitemap = '<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';

// Connection data (server_address, name, password, database_name)
$hostdb = 'localhost';
$userdb = 'root';
$passdb = '1233555';
$namedb = 'n_bankroll';

try {
// Connect and create the PDO object
$conn = new PDO("localhost=$hostdb; n_bankroll=$namedb", $userdb, $passdb);
$conn->exec("SET CHARACTER SET utf8"); // Sets encoding UTF-8

// Define and perform the SQL SELECT query
$sql = "SELECT link FROM bingo ";
$result = $conn->query($sql);

// If the SQL query is succesfully performed ($result not false)
if($result !== false) {
// Parse the result set, and add the URL in the XML structure
foreach($result as $row) {
$xmlsitemap .= '<url>
<loc>'. $row['link'] .'</loc>
<priority>0.5</priority>
<changefreq>weekly</changefreq>
</url>';
}
}

$conn = null; // Disconnect
}
catch(PDOException $e) {
echo $e->getMessage();
}

$xmlsitemap .= '</urlset>';
file_put_contents($xmlfile, $xmlsitemap); // saves the sitemap on server

// outputs the sitemap (delete this instruction if you not want to display the sitemap in browser)
echo $xmlsitemap;

Answer

I wrote a function for the generation of sitemaps a while ago, I did it with the function fopen / fwrite. The $connection variable is a custom object linked to a MySQL database. You can use this function as example to write your own script.

public function generate_users()
{
    global $_CONFIG, $connection;

    $url = $_CONFIG['WEBSITE']['URL'];
    $sitemap = '';
    $sitemap_start = '<?xml version="1.0" encoding="UTF-8"?>'."\n".'<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';
    $sitemap_end = "\n</urlset>";

    $query = $connection->preparedStatement('SELECT username FROM users WHERE rank_id <> 4');
    $users = $query->fetchAll();

    $file = fopen('sitemap_users.xml', 'w');
    fwrite($file, $sitemap_start);
    foreach($users as $user)
    {
        fwrite($file, "\n    <url>");
        fwrite($file, "\n        <loc>".$url.'Member-'.$user['username'].'</loc>');
        fwrite($file, "\n        <lastmod>".date('y-m-d').'</lastmod>');
        fwrite($file, "\n        <changefreq>hourly</changefreq>");
        fwrite($file, "\n        <priority>0.8</priority>");
        fwrite($file, "\n    </url>");
    }
    fwrite($file, $sitemap_end);
    fclose($file);

    $this->sitemaps [] = array(
        'name'      => 'users',
        'generated' => date('y-m-d')
    );
}

I should recomment to edit this script, because it uses the function fwrite a lot of times, while you are able to put all the contents into 1 string and write that into the file.