How To Create Automated Dynamic XML sitemap in PHP Script

ever wanted your site to be indexed by search engine but don’t know how? well, maybe you surfed the web and discovered that you have to submit your sitemap.xml which is correct or someone charged you more than 100bucks to do it for you which is wrong in my case. Well in this simple short post will try to solve your problem.

let’s say you’re building a simple unique application or you would like to index certain pages or post automatically and do it one time and daily.

First will start looking on our app database to allocated what we want to index or to query in our sitemap.

DATABASE

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31


--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `post`
--
CREATE TABLE IF NOT EXISTS `post` (
  `post_id` int(11) NOT NULL,
  `post_title` text NOT NULL,
  `post_url` text NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `post`
--
--
-- Indexes for dumped tables
--
--
-- Indexes for table `post`
--
ALTER TABLE `page`
  ADD PRIMARY KEY (`post_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `post`
--
ALTER TABLE `post`
  MODIFY `post_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=11;

Now that you have a database you can choose what to index if you’re not familiar with the database I would suggest you download your database and view it in the text editor and study it well so that you can have an idea of how it works and its structure.

Now will create a PHP file which is sitemap.php which you will add at your root DIR

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


//sitemap.php
$connect = mysqli_connect("localhost", "user, "pass", "db");
$query = "SELECT post_url FROM post";
//this will query all post url in your database in the post table
//$query = "SELECT post_title FROM post";
//this will query all post title in your database in the post table
$result = mysqli_query($connect, $query);
$base_url = "http://johnhashim.com/";
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;
while($row = mysqli_fetch_array($result))
{
echo '<url>' . PHP_EOL;
echo '<loc>'.$base_url. $row["post_url"] .'/</loc>' . PHP_EOL;
echo '<changefreq>daily</changefreq>' . PHP_EOL;
echo '</url>' . PHP_EOL;
}
echo '</urlset>' . PHP_EOL;

remember this code will

1
2
3
4
5
6
7
8


while($row = mysqli_fetch_array($result))
{
echo '<url>' . PHP_EOL;
echo '<loc>'.$base_url. $row["post_url"] .'/</loc>' . PHP_EOL;
echo '<changefreq>daily</changefreq>' . PHP_EOL;
echo '</url>' . PHP_EOL;
}
echo '</urlset>' . PHP_EOL;

loop all post URL and make it available for a search engine to crawl.

and this is done !! now you can submit your sitemap as

site.com/sitemap.php
this will update automatically but if you’re used to the old way of XML you can change that in your .htaccess.txt by adding this code this

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

will allow the search engine to visit your sitemap as sitemap.php or sitemap.xml

I got this idea from the web and thought it would be helpful to some googlers