How to create SitemapIndex sitemap from database

• Qayyum
• February 6, 2018
• 1,893

This guide How to create SitemapIndex sitemap from database will show you how we can create SitemapIndex from my-sql database. We know creating sitemap for google is super easy as described by Google, but problem comes when we have too many link, like 100 thousands of links. So let's start with basic example how SitemapIndex sitemap looks like.

<sitemapindex xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.sitemaps.org/schemas/sitemap/0.9" xsi:schemaLocation="http://www.sitemaps.org/schemas/sitemap/0.9 http://www.sitemaps.org/schemas/sitemap/0.9/siteindex.xsd">
<sitemap>	<loc>https://standaloneinstaller.com/sitemap-main.xml</loc>
</sitemap>
<sitemap>	<loc>	https://standaloneinstaller.com/sitemap-categories.xml	</loc>
</sitemap>
<sitemap>	<loc>	https://standaloneinstaller.com/sitemap-softwares-1.xml	</loc>
</sitemap>
<sitemap>	<loc>	https://standaloneinstaller.com/sitemap-softwares-2.xml	</loc>
</sitemap>
.
.
.
<sitemap>	<loc>	https://standaloneinstaller.com/sitemap-softwares-51.xml	</loc>
</sitemap>
<sitemap>	<loc>https://standaloneinstaller.com/sitemap-news.xml</loc>
</sitemap>
<sitemap>	<loc>https://standaloneinstaller.com/sitemap-blog.xml</loc>
</sitemap>
<sitemap>	<loc>https://standaloneinstaller.com/sitemap-howto.xml</loc>
</sitemap>
</sitemapindex>

So, this is sitemap of our site which you can take a look into if you want to. The important thing in above sitemap is

So creating and managing 53+ sitemaps is not super easy, so we need some kind of program that can help us to create these sitemaps automatically. And to be honest we don't have to worry about them for long time in future as well. We will use PHP which can be easily adopted in any other language you like.

Main sitemap.php file

<?php
include_once('yourdbconnection.php');
header("Content-Type: text/xml;charset=iso-8859-1");
print '<sitemapindex 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/siteindex.xsd" xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';echo PHP_EOL;
print "<sitemap> <loc>$baseUrl/sitemap-main.xml</loc> </sitemap>";echo PHP_EOL;
print "<sitemap> <loc>$baseUrl/sitemap-categories.xml</loc> </sitemap>";echo PHP_EOL;$sql = "SELECT count(*) as total FROM softwares LIMIT 1";
$resultseo=$DB->Select($sql, array());
if($row = $resultseo->fetch())
{ $total = $row["total"]; $limit = 1000; $pages = ceil($total/$limit); for ($i=1; $i <= $pages; $i++) { print "<sitemap> <loc>$baseUrl/sitemap-softwares-{$i}.xml</loc> </sitemap>";echo PHP_EOL; }
}//Remaning sitemaps
print "<sitemap> <loc>$baseUrl/sitemap-news.xml</loc> </sitemap>";echo PHP_EOL;
print "<sitemap> <loc>$baseUrl/sitemap-blog.xml</loc> </sitemap>";echo PHP_EOL;
print "<sitemap> <loc>$baseUrl/sitemap-howto.xml</loc> </sitemap>";echo PHP_EOL;
print "<sitemap> <loc>$baseUrl/sitemap-howto.xml</loc> </sitemap>";echo PHP_EOL;
print "<sitemap> <loc>$baseUrl/sitemap-searches.xml</loc> </sitemap>";echo PHP_EOL;
print '</sitemapindex>';echo PHP_EOL;

So, the main code is bold. Some info about code.

• $limit = how many links we want in a sitemap.
• $total = this gives us total links in our database.
• $pages = this gives total number of sitemaps we curretly have, whihc is 53 in our case.

Above page is just creating links to our sitemaps that we need to generate at this location $baseUrl/sitemap-softwares-{$i}.xml

We need some rewrite rules in .htaccess to redirect our .php pages to .xml, so that when we write .xml in our website, it bascally is php file in backend. For example standaloneinstaller.com/sitemap.xml is actually standaloneinstaller.com/sitemap.php

.htaccess

RewriteCond %{REQUEST_FILENAME} !-f	RewriteCond %{REQUEST_FILENAME} !-d	RewriteRule ^sitemap.xml/?$ sitemap.php [L]	RewriteRule ^sitemap-softwares-([^/]+).xml/?$ sitemap-softwares.php?type=softwares&page=$1 [L]

We still need to make sitemap-softwares.php page so that it can accept page number and software type to generate correct sitemap for us.

sitemap-softwares.php

<?php
include_once('yourdbconnection.php');
header("Content-Type: text/xml;charset=iso-8859-1");
if(!isset($_GET['type'])) header("Location: $baseUrl/404.php");$type = $_GET['type'];
print '<?xml version="1.0" encoding="UTF-8" ?>';echo PHP_EOL;
print '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9" xmlns:image="http://www.google.com/schemas/sitemap-image/1.1">';echo PHP_EOL;
if($type == "softwares"): if(isset($_GET['page'])) { $page = $_GET['page']; } $limit = 1000; if($page) $start = ($page - 1) * $limit; else { $page=1; $start=0; } $sql="SELECT * from softwares LIMIT $start, $limit"; $resultseo=$DB->Select($sql, array()); while ($row=$resultseo->fetch()) { $slug=strtolower($row['slug']); $title = htmlspecialchars($row['title']); $image = $row['image']; print '<url>'; print "<loc>$baseUrl/$slug</loc>"; if ($image!=''): print "<image:image>";echo PHP_EOL; print "<image:loc>$imagesUrl/$image</image:loc>";echo PHP_EOL; print "<image:caption>{$title}</image:caption>";echo PHP_EOL; print "</image:image>";echo PHP_EOL; endif; print '<priority>0.7</priority>';echo PHP_EOL; print '</url>';echo PHP_EOL; }
endif;

By this our links in sitemap.xml like https://standaloneinstaller.com/sitemap-softwares-2.xml will redirect to sitemap-softwares.php with two params (page, type) and generate sitemap automatically.