How To Create Dynamic Sitemap in PHP and MYSQL

In this Article we will discuss how to create dynamic sitemap in php . Sitemap is a Xml File which contains website all pages and Articles Urls with Created and Updated Date. This Sitemap is very important for websites because Google requires a Website sitemap XML file to crawl the Web pages which are located over the website.

Sitemap is very important to rank the website over google. Using sitemap, google checks the existing pages and articles over the website and rank the same urls over google as per their keywords. If the website is static then we create a sitemap XML file once but if the website is dynamic then the sitemap XML file will be updating pages dynamically.

How To Create Dynamic Sitemap in PHP and MYSQL

There are a lot of tools over the internet through which you can create sitemaps online for your website. you can check online here at https://www.xml-sitemaps.com but these will be static and can’t change if you add a new article or update previous article. So if your website is dynamic then we have to create a dynamic sitemap XML file which will update automatically as per website updates.

​So today we will create a Dynamic Sitemap in php and mysql. Here are the Steps to create sitemap given below:

Step 1 : Create Database Table over Mysql

First you have to create a database table over mysql to store topics to create SEO Friendly slugs and sitemap xml.

CREATE 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;

 

Steps 2 : Add Dummy Topics

You have add some Dummy Data inside Topics Table through mysql queries given below:

INSERT INTO `topics` (`id`, `subject`, `created`) VALUES
(1, 'Ways to get Disposable Phone Number India', '2022-06-20 06:16:17'),
(2, 'How To Integrate Payumoney Payment Gateway In PHP', '2022-06-17 07:19:18'),
(3, 'How To Integrate Payumoney Payment Gateway In Laravel', '2022-06-09 06:13:14'),
(4, 'Top 10 websites in the world 2022', '2022-06-02 07:22:21'),
(5, 'How to earn money from home without any investment', '2022-06-09 03:10:13');

Step 3 : Create Sitemap XML

Create Index.php file and added same code over file given below:

"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;

 

Step 4 : Create Topic.php File

Now you have to create topic.php file as well over website to get topics from database given below:

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

 

Step 5: Create google friendly Topic SEO url

Now you have to add another finction inside topic.php file to create SEO friendly urls for google sitemap.

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;
}

 

Step 6 : Urls Rewrite for Sitemap xml
Bow you have to create .htaccess file over root directory to convert url fron index.php to sitemap.xml

RewriteEngine On

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

 

I hope you enjoyed this article. If you have any issues and queries then please comment below. Also need more informational articles then you can visit my website https://brotech4u.com for more updates and knowledge.

Tags : How To Create Dynamic Sitemap in php and mysql, Create Dynamic Sitemap XML with PHP & MySQL, Dynamic Sitemap.xml file creation

One thought on “How To Create Dynamic Sitemap in PHP and MYSQL

Comments are closed.