How to Generate a Dynamic XML Sitemap in PHP Using MySQL

An XML sitemap is an essential part of any website. It helps search engines like Google and Bing easily discover and index your web pages. If you are running an eCommerce website or a content-driven platform where new pages are added regularly, a dynamic sitemap is a must.

In this guide, we will learn how to create a dynamic XML sitemap in PHP by fetching data directly from a MySQL database. We’ll also update the .htaccess file to make the sitemap accessible at a user-friendly URL.

Why Do You Need a Dynamic Sitemap? 

Unlike a static sitemap that you have to update manually, a dynamic sitemap is generated automatically. This means:

1. Every new store, product, or page is instantly added to the sitemap.
2. Search engines always get the latest updates from your site.
3. Saves time and improves SEO performance.

PHP Code to Generate Sitemap 
Here’s the PHP code you can use:

 <?php
$joint = mysqli_connect('localhost', 'username', 'password', 'dbname');
$query = "SELECT store_slug, createdon FROM tbl_stores WHERE status='1'";
$sql = mysqli_query($joint, $query);
header('Content-type: application/xml');
// configuration
$url_prefix = 'http://www.xyz.com/';
$output = '<?xml version="1.0" encoding="UTF-8"?>' . "\n";
$output .= '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9
http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd">' . "\n";
echo $output;
?>
<?php

while($row = mysqli_fetch_assoc($sql)){
?>
<url>
<loc><?php echo $url_prefix.htmlspecialchars($row['store_slug']);?>/</loc>
<lastmod><?php echo substr($row['createdon'],0,10)."T07:26:48+00:00"; ?></lastmod>
<changefreq>daily</changefreq>
<priority>0.8000</priority>
</url>
<?php } ?> 
</urlset>

Explanation of the Code 

1. Database Connection – Connects to your MySQL database.
2. Query – Fetches store_slug and createdon values for active stores. 
3. Header – Sets the output type to XML.
4. Loop – Generates <url> entries dynamically for each store.
5. Tags:
   
   • <loc> – Full store URL.
   • <lastmod> – Last modification date from database.
   • <changefreq> – Update frequency (daily in this case).
   • <priority> – Importance level for search engines. 

    

   Update .htaccess for SEO-Friendly URL
   
   To make your sitemap accessible at www.yoursite.com/sitemap_stores.xml, add the following rule in your .htaccess file:

   Options -Indexes
   RewriteRule ^sitemap_stores\.xml$ sitemap_stores.php [L] 

    Now, instead of visiting sitemap_stores.php, you can directly access the sitemap via:

   https://www.xyz.com/sitemap_stores.xml 

   Final Output 

   When you visit the sitemap URL, you’ll see an XML file containing all your active store URLs in the correct sitemap format. Example:

   
   <url>
     <loc>http://www.xyz.com/store-name/</loc>
     <lastmod>2025-08-19T07:26:48+00:00</lastmod>
     <changefreq>daily</changefreq>
     <priority>0.8000</priority>
   </url> 
   

   Benefits of This Approach

   1. Automatic Updates – New stores are instantly added.
   2. SEO Friendly – Helps Google crawl your site faster.
   3. Scalable – Works for any number of pages.
   4. Clean URL – Thanks to the .htaccess rewrite rule.