Skip to main content

Generate Dynamic Sitemap with PHP & MySQL

Updated on: coderszine team PHP, Tutorial

In our previous tutorial, we have explained how to develop follow and unfollow system with PHP and MySQL. In this tutorial, we will explain how to generate dynamic Sitemap XML using PHP and MySQL.

Sitemap XML is an important part of any web application. Sitemap XML is a list of page URLs which can be accessed by Search Engines. Based on Sitemap XML, the web pages indexed by Search Engines.

So if you have website and wants to index pages to Search Engines then you will have to create Sitemap XML. f your website pages are fixed then you can use sitemap creator tools from online and create sitemap xml file for their website. But your website have dynamic pages means pages increases with the passes of time then you will have to create dynamic Sitemap XML file.

So if you’re running a website and looking for the solution to create dynamic Sitemap XML for your website pages then you’re here at the right place. In this tutorial, you will learn how to create dynamic Sitemap XML using PHP and MySQL.

So let’s implement to create dynamic Sitemap XML. The major files are:

  • sitemap.php
  • .htaccess
  • Post.php: Post class to hold method related to posts.

Step1: Create MySQL Database Table

First we will create MySQL database table sitemap to store posts details.

CREATE TABLE `sitemap` (
  `id` int(11) NOT NULL,
  `title` text NOT NULL,
  `url` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

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

here is the sample dump data to store in sitemap table.

INSERT INTO `sitemap` (`id`, `title`, `url`) VALUES
(1, 'Follow and Unfollow System with PHP & MySQL', 'follow-and-unfollow-system-with-php-mysql'),
(2, 'GST Billing System with PHP & MySQL', 'gst-billing-system-with-php-mysql'),
(3, 'Restaurant Management System with PHP & MySQL', 'build-restaurant-management-system-with-php-mysql'),
(4, 'Visitor Management System with PHP & MySQL', 'visitor-management-system-with-php-mysql'),
(5, 'Student Attendance System with PHP & MySQL', 'student-attendance-system-with-php-mysql'),
(6, 'Like and Dislike System with Ajax, PHP & MySQL', 'build-vote-up-and-down-system-with-ajax-php-mysql'),
(7, 'Build Star Rating System with jQuery, PHP & MySQL', 'build-star-rating-system-with-jquery-php-mysql'),
(8, 'User Registration and Login System with PHP & MySQL', 'user-registration-and-login-system-with-php-mysql'),
(9, 'Build Contact Form with PHP and Ajax', 'build-advance-contact-form-with-ajax-php'),
(10, 'Build Dynamic Image Gallery with PHP & MySQL', 'build-image-gallery-with-jquery-php-mysql');

Step2: Get Posts Details

In class Post.php, we will implement function getPosts() to get posts details like post title, url from table sitemap and return all results.

public function getPosts(){							
	$sqlQuery = "
		SELECT id, title, url
		FROM ".$this->sitemapTable;
	$stmt = $this->conn->prepare($sqlQuery);
	$stmt->execute();			
	$result = $stmt->get_result();		
	return $result;	
	
}

Step3: Create Dynamic Sitemap XML

In sitemap.php file, we will include class Post.php and create post object.

include_once 'class/Post.php';
$postObj = new Post($db);

Now we will set header application/xml and define XML start tag as we ill display XML for sitemap. We will start with tag urlset with it’s attribute to create sitemap xml.

We will call method getPosts() from class Post.php and loop through to disply url with tags like url, loc and changefreq tag.

$baseUrl = "https://localhost/coderszine/sitemap-php/";

header("Content-Type: application/xml; charset=utf-8");
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;

$postsResult = $postObj->getPosts();
while ($post = $postsResult->fetch_assoc()) {	
	
	echo '<url>' . PHP_EOL;
	echo '<loc>'.$baseUrl. $post['url'] .'/</loc>' . PHP_EOL;
	echo '<changefreq>daily</changefreq>' . PHP_EOL;
	echo '</url>' . PHP_EOL;
	
}
echo '</urlset>' . PHP_EOL;

Step4: Url Rewriting

Finally we will handle URL write to display sitemap.php with sitemap.xml. So we will create file .htaccess and implement url rewriting.

RewriteEngine On

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

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