Create XML Sitemap Using PHP and MySQL

This tutorial is about Create XML Sitemap Using PHP and MySQL. A sitemap is important for a website to inform search engine to index web page. A sitemap helps search engines for crawling web pages that case sitemap is very important for a website. Webmaster should update website's search engines regularly for indexing new pages. There are a lot of sitemap creator tools in online and these sitemap creator tools create a static sitemap for you. If you use static sitemap then you should update this regularly and add new updates every time but this a pretty hard to do. If you use dynamic sitemap for your website then this will automatically update with every new update. This tutorial helps you to create a dynamic sitemap using PHP and MySQL. If you want to make this follow step by step instructions.

Step-1. Create Database Table For Sitemap Feed

If you haven't any database to make sitemap then create instantly then create a database table for it. If you have already a database and table and if you want to make sitemap based on this database table then you can skip this step. For new table insert following command bellow.

link VARCHAR(255) NULL,
description TEXT DEFAULT NULL,
The database table is ready for use. Insert some data into this table. Then follow the second step. This step will help us to create all functions for the sitemap.

Step-2. Create Functions For Sitemap

Create a PHP file for put all necessary functions. We have created a file name as sitemap.class.php . Create your own and put down this code bellow.

class sitemap
public $conn;
public function __construct()
die('Failed to connect with MySQL');

public function conn()
$host = "localhost";
$user = "root";
$pass = "";
$name = "test";
$conn = mysqli_connect($host,$user,$pass,$name);
if (mysqli_connect_errno())
die("Failed to connect with MySQL: ".mysqli_connect_error());
return $this->conn = $conn;

public function head()
echo '<urlset xmlns=""; xmlns:xsi=""; xsi:schemaLocation="">';
public function feed($url,$freq)
echo '
public function foot()
echo '</urlset>';
public function clean($string) {
$string = strtolower( preg_replace('@[\W_]+@', '-', $string) );
$string = rtrim($string,'-');
$string = strtolower($string);
return $string;
public function pages($pages)
$allpage = explode(',',$pages);
foreach ($allpage as $page)
$link = 'http://'.$_SERVER['HTTP_HOST'].$page;
public function close()

Our full functions are ready. You should config it as yours own for connecting with the database. These functions help you to execute full sitemap. Now we are going to the main step where XML sitemap will appear.

Step-3. Display Sitemap in PHP

Create a PHP file name as sitemap.php and write down this code bellow.

header('Content-type: text/xml');
header('Pragma: public');
header('Cache-control: private');
header('Expires: -1');
$url = 'http://'.$_SERVER['HTTP_HOST'];
include_once 'sitemap.class.php';
$sitemap = new sitemap;

$query = "SELECT * FROM articles ORDER BY id";
if ($result = mysqli_query($sitemap->conn(), $query)) {
while ($row = mysqli_fetch_assoc($result)) {
$link = $url.slash.sitemap::clean($row['name']);
$pages = '/about-us,/contact-us,/privacy-policy';
Our sitemap is fully ready. If you added any data into articles table then this data will show in sitemap. You can add custom pages if you want. Just chage $pages value to your custo pages. You can able to see this sitemap normally browseing this file. Example like - If you put sitemap.php file in root directory then this PHP file will show you xml sitemap. But we want to visble our sitemap to an xml file extension. So, we have to do a simple task. This task help you to visble sitemap.php to sitemap.xml. Yes, a simple .htaccess tricks can make this for you.

Step-4. .htaccess tricks for XML sitemap

Edit your htaccess file and append this code into your website's .htaccess file.

RewriteEngine On
RewriteRule ^sitemap.xml$ sitemap.php [NC,L]
This htaccess code rewrite a rule for visble sitemap.php as sitemap.xml. You can do many interesting things with .htaccess . You may like 15 useful htaccess tips and tricks. Here you will get some awesome .htaccess tips and tricks. After completing this task you standerd sitemap is fully ready for you and now can browse as . This will show your dynamic sitemap data. Finally our sitemap has completed.

I hope you understand this. I have attached this full project with this article. You are free to use this for your project. If you have any question or comment about this XML Sitemap Using PHP and MySQL article please don't hesitate to do that, please comment your question in comment section.

Download Source Code
Unlock source code with a social share.

Did you like this article? it will be appreciated if you share a coffee or burger with the author

Sent $5 to the author
Sent $10 to the author

Sajjad Hossain
Hi, I'm Sajjad Hossain, a PHP Developer working on PHP Development since 2012. If you have a project that you want to start you can contact me. I'm currently available for freelance work. Ping me on Facebook, WhatsApp or Skype.

We use cookies on our website. To find out more about how and why they are used or opt-out, please read our Cookie Policy. By choosing "I Accept", you consent to our use of cookies. Cookie Policy