Create Dynamic Sitemap XML with PHP & MySQL

PHPZAG Team PHP, Tutorials

In this tutorial we will explain how to create dynamic Sitemap XML with PHP and MySQL. Sitemap is a XML file that contains website pages url and related details like change date, modified date etc.

Sitemap XML file is important for any website to inform the search engines to index pages quickly. The sitemap is added into webmaster tools to inform Search Engines with website pages details to index pages regularly with website pages add or update.

If the website is static then we only to create sitemap once and we can create this using online tools. But when website is regular updated with new pages or updated existing pages then we need dynamic sitemap to be updated regularly. So in this tutorial we will create dynamic sitemap using PHP and MySQL to update automatically when any change made to website.

Also, read:

So let’s proceed to create dynamic Sitemap XML with PHP and MySQL. We will have following file structure for this project.


  • xml-sitemap-with-php-mysql-demo
    • config
      • Database.php
    • class
      • Topic.php
    • index.php
    • .htaccess

Step1: Create MySQL Database Table

First we will create MySQL database table topics to store topics details to create SEO friendly urls and sitemap XML.

REATE TABLE `topics` (
  `id` int(11) NOT NULL,
  `subject` text NOT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `topics`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `topics`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

We will also insert few records into topics table to create sitemap xml.

INSERT INTO `topics` (`id`, `subject`, `created`) VALUES
(1, 'Build Instant Search with Ajax, PHP & MySQL', '2020-06-20 06:16:17'),
(2, 'Build Content Management System with PHP & MySQL', '2020-06-17 07:19:18'),
(3, 'Live Add Edit Delete Datatables Records with Ajax, PHP & MySQL', '2020-06-09 06:13:14'),
(4, 'Email Subscription System with PHP & MySQL', '2020-06-02 07:22:21'),
(5, 'Poll and Voting System with PHP and MySQL', '2020-06-09 03:10:13');

Step2: Create Sitemap XML

In index.php file, we will implement functionality to create sitemap XML file. We will call method $topicObj->getTopic() from Topic.php class and loop through to create sitemap XML. We will also call method createSeoUrl() to create seo friendly urls and also last modified date.

$topicObj = new Topic($db);
$topics = $topicObj->getTopic();
$baseUrl = 'http://localhost/phpzag_demo/xml-sitemap-with-php-mysql-demo/'; 
echo '<?xml version="1.0" encoding="UTF-8"?>'. PHP_EOL;
echo '<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">'. PHP_EOL;
while ($topic = $topics->fetch_assoc()) {
	$url = $topicObj->createSeoUrl($topic['id'], $topic['subject']);
	$date = str_replace('/', '-', $topic['created']);
	echo '<url>';
	echo '<loc>'.$baseUrl.$url.'</loc>'. PHP_EOL;
	echo '<lastmod>'.date("Y-m-d", strtotime($date)).'T'.date("H-i-s", strtotime($date)).'Z</lastmod>'. PHP_EOL;
	echo '</url>';
}
echo '</urlset>'. PHP_EOL;

We will implement getTopic() in Topic.php class to get topics data and return result.

public function getTopic() {	
	
	$sqlQuery = "SELECT * FROM ".$this->topicTable;			
	$stmt = $this->conn->prepare($sqlQuery);			
	$stmt->execute();
	$result = $stmt->get_result();
	return $result;
	
}

We will also implement method createSeoUrl() to convert topics into seo friendly urls.


public function createSeoUrl($id, $subject){    
	$subject = trim($subject);    
	$subject = html_entity_decode($subject);    
	$subject = strip_tags($subject);    
	$subject = strtolower($subject);    
	$subject = preg_replace('~[^ a-z0-9_.]~', ' ', $subject);    
	$subject = preg_replace('~ ~', '-', $subject);    
	$subject = preg_replace('~-+~', '-', $subject);        
	return $subject.'-'.$id;
}

Step3: Urls Rewrite for Sitemap xml

We will create file .htaccess for Url rewrite from index.php to sitemap.xml file to sitemap xml.

RewriteEngine On

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

You may also like:

You can view the live demo from the Demo link and can download the script from the Download link below.
Demo Download

One thought on “Create Dynamic Sitemap XML with PHP & MySQL”

  1. Hi,
    Really nice article. We have made a different code by refering your code in our website also. Just before echo we used header function like header(‘Content-Type: application/xml’); which shows output as xml.

Comments are closed.