Create Dynamic Sitemap XML with PHP & MySQL

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:

• Build Invoice System with PHP & MySQL
• Build Live Chat System with Ajax, PHP & MySQL
• Build Comment System with Ajax, PHP & MySQL

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:

• Star Rating System with Ajax, PHP and MySQL
• Create Event Calendar with jQuery, PHP and MySQL
• Build Your Own CAPTCHA Script with PHP
• Convert Unix Timestamp To Readable Date Time in PHP
• Inventory Management System with Ajax, PHP & MySQL
• Create Live Editable Table with jQuery, PHP and MySQL
• Live Add Edit Delete datatables Records with Ajax, PHP and MySQL
• Stripe Payment Gateway Integration in PHP
• Export Data to Excel with PHP and MySQL
• Star Rating System with Ajax, PHP and MySQL
• Create Dynamic Bootstrap Tabs with PHP & MySQL
• How To Create Simple REST API in PHP

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