• Tools News Blog How To
  • Home (current)
  • Categories
  • Submit Articles
  • Submit Software
    • Register Login

    How to create SitemapIndex sitemap from database

    • Qayyum
    • February 6, 2018
    • 3,506

    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.

    Note: SitemapIndex is basically index of all the sitemaps we have on our site, so I'm calling it SitemapIndex sitemap.
    <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

    https://standaloneinstaller.com/sitemap-softwares-1.xmlTohttps://standaloneinstaller.com/sitemap-softwares-53.xml

    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.


    You May Also Like View all


    UberEats Clone is the Perfect Way to Start Your Online Food Delivery Business

    UberEats Clone is the Perfect Way to Start Your On...

    How to download Classic Skype?

    How to download Classic Skype?

    VirusTotal Scanner PHP

    VirusTotal Scanner PHP

    Cyclance is a smart antivirus that uses artificial intelligence to keep your system fully protected

    Cyclance is a smart antivirus that uses artificial...

    Create Amazon S3 Bucket if does not exist C#

    Create Amazon S3 Bucket if does not exist C#

    Big list of various character length usernames

    Big list of various character length usernames

    Most Read
    • Big list of sample videos for testers
    • 17 Best Free Word to PDF Converter Software for Windows
    • How to remove Omiga Plus?
    • Cyclance is a smart antivirus that uses artificial intelligence to keep your system fully protected
    • 61 Most Used VLC Keyboard Shortcuts (Hotkeys)
    Popular Downloads
    • Adobe Flash PlayerAdobe Flash Player
    • CyberLink YouCamCyberLink YouCam
    • 7-PDF Maker7-PDF Maker
    • GoAnimateGoAnimate
    • PDF24 CreatorPDF24 Creator
    • SketchBook Pro (32-Bit) SketchBook Pro (32-Bit)
    • Foxit ReaderFoxit Reader
    • Excel 2016 Password Remover FreeExcel 2016 Password Remover Free
    • Free Word to PDF ConverterFree Word to PDF Converter
    • Ghost Mouse Auto ClickerGhost Mouse Auto Clicker
    • WinRARWinRAR
    • Free PDF ReaderFree PDF Reader
    • MP4 Video Repair ToolMP4 Video Repair Tool
    • PowerISOPowerISO
    • Google Web DesignerGoogle Web Designer

    Submit Softwares - Contact - Privacy Policy - © StandaloneInstaller.com 2018

    Download this file