Create dynamic google sitemap using PHP Mysql

Article Image

Google sitemap plays an important role to index your website links on google fastly. There are a lot of online tools that can be used to generate sitemap.xml files for your website. But if your website creating links continuously like a news website then it will not be feasible to generate a new sitemap file manually and upload it to the server.

You can generate dynamic sitemaps to overcome this problem. We are using PHP and Mysql as our backend for this tutorial and will write a PHP script to render a sitemap.xml file on your website that will show all links to a sitemap that are generated.

Create sitemap.php file

Create a sitemap.php file on your project root or in a folder and create a Mysql connection using PHP. Then query the records to the table which contains your site links or posts links. The sample code can be found as follow:

<?php
    $servername = "localhost";
    $username = "username";
    $password = "password";
    $dbname = "database_name";
    $conn = new mysqli($servername, $username, $password, $dbname);

    $links = $conn->query("SELECT link, updated_date FROM table_name");

    $base_url = "https://your_website_url.com/article/";

    header("Content-Type: application/xml; charset=utf-8");
    echo '<?xml version="1.0" encoding="UTF-8" ?>' . PHP_EOL;
    echo '<urlset xmlns="http://www.google.com/schemas/sitemap/0.84" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.google.com/schemas/sitemap/0.84 http://www.google.com/schemas/sitemap/0.84/sitemap.xsd">' . PHP_EOL;
    
    while ($link = $links->fetch_assoc()) {
        $datetime = new DateTime($link['updated_date']);
        $date_final = $datetime->format('Y-m-d\TH:i:sP');

        echo '<url>' . PHP_EOL;
        echo '<loc>'. $base_url . $link['link'] .'/</loc>' . PHP_EOL;
        echo '<lastmod>'. $date_final .'</lastmod>' . PHP_EOL;
        echo '<changefreq>daily</changefreq>' . PHP_EOL;
        echo '</url>' . PHP_EOL;
    }
    echo '</urlset>' . PHP_EOL;
?>

Sometimes you may face the issue of invalid date format when submitting your sitemap to google. Because Mysql saves the date in a different format and google sitemap API requires the date in another format. You can resolve this issue by using this code snippet. We have already added the date format code in the above sample code.

Click here to read

Create or change the .htaccess file

By default, if you will type 'localhost/sitemap.xml' in your browser it will not work. To make this script work we have to create or change the .htaccess file and add a rule to rewrite the URL to serve our sitemap.xml file.

If you have created the sitemap.php file on the root you can use the below code.

RewriteEngine On

RewriteRule ^sitemap\.xml/?$ sitemap.php

If you have created the sitemap.php file inside a folder you can use this code.

RewriteEngine On

RewriteRule ^sitemap\.xml/?$ /folder_name/sitemap.php

Now type 'localhost/sitemap.xml' in your browser it will show you the sitemap file with all links added dynamically from your database table.

0 Comments
Write new article
Never leave your website again in search of code snippets by installing our chrome extension.