Generate sitemap for blog in PHP and MYSQL | Part 11

You must have known about SEO. You must have known about the sitemap. Sitemaps are generated to link the URL of a blog website to Google or other search engines. Here we have been learning to create a blog from the beginning"how to create a blog from scratch in PHP" tutorial.

In this tutorial, we will learn to generate a sitemap using PHP and MYSQL database. PHP and MYSQL are used to create sitemaps. In this, we will also create an .htaccess file and redirect it from the PHP file to the XML file. In the current time, a sitemap is built to index website pages in Google or other search engines. A sitemap is created only once, with new blog posts, tags, and categories added to it, which are indexed by Google or other search engines. To generate a sitemap, the blog post URL has to be defined in the location. We fetch the data from the MYSQL database and use it in the sitemap file.
Let’s create another PHP file inside the link folder.


blog/link/sitemap.php


Google or other search engines require a sitemap path. We can generate a sitemap in any folder. Here we are going to create a sitemap inside the link folder.
First of all, understand the code lines below. It will help to understand the process of creation sitemap.

 $pages = $db->query('SELECT pageSlug FROM techno_pages ORDER BY pageId ASC');
$article = $db->query('SELECT articleSlug FROM techno_blog ORDER BY articleId ASC');
$category = $db->query('SELECT categorySlug FROM techno_category ORDER BY categoryId ASC');
$tag= $db->query('SELECT articleTags FROM techno_blog ORDER BY articleId ASC');


In the code lines above, we are fetching slugs of pages and blog posts. We are also fetching the title of the tags and categories. We will use this slug with the base URL. The sitemap contains the location of every URL. We will use this post slug.

$base_url = "http://localhost/blog/";
//Page base URL 
$page_base_url = "http://localhost/blog/page/";
//Category base URL
$category_base_url = "http://localhost/blog/category/";
//tag base URL
$tag_base_url = "http://localhost/blog/tag/";
 


In the code lines above , we are defining base URL .A complete URL contains the base URL . We are defining base URL for page , blog post , category and tag.

 echo '' . PHP_EOL;
 echo ''.$base_url.'' . PHP_EOL;
 echo 'daily' . PHP_EOL;
 echo '' . PHP_EOL;


while($row = $pages->fetch()){

 echo '' . PHP_EOL;
 echo ''.$page_base_url. $row["pageSlug"] .'' . PHP_EOL;
 echo 'daily' . PHP_EOL;
 echo '' . PHP_EOL;

}

In the code above , you can see that all URLs should be defines inside the tags of xml .
First of all, define the base URL and use then add the slug or title in liner
Close the URL.

Complete PHP code for sitemap


blog/link/sitemap.php

<?php 
//sitemap.php to sitemap.xml using .htaccess file 
require_once('../includes/config.php');

$pages = $db->query('SELECT pageSlug FROM techno_pages ORDER BY pageId ASC');
$article = $db->query('SELECT articleSlug FROM techno_blog ORDER BY articleId ASC');
$category = $db->query('SELECT categorySlug FROM techno_category ORDER BY categoryId ASC');
$tag= $db->query('SELECT articleTags FROM techno_blog ORDER BY articleId ASC');
               

//define your base URLs 
//Main URL 
$base_url = "http://localhost/blog/";
//Page base URL 
$page_base_url = "http://localhost/blog/page/";
//Category base URL
$category_base_url = "http://localhost/blog/category/";
//tag base URL
$tag_base_url = "http://localhost/blog/tag/";



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;
echo '' . PHP_EOL;
 echo ''.$base_url.'' . PHP_EOL;
 echo 'daily' . PHP_EOL;
 echo '' . PHP_EOL;


while($row = $pages->fetch()){

 echo '' . PHP_EOL;
 echo ''.$page_base_url. $row["pageSlug"] .'' . PHP_EOL;
 echo 'daily' . PHP_EOL;
 echo '' . PHP_EOL;
}


 while($row = $article->fetch()){

 echo '' . PHP_EOL;
 echo ''.$base_url. $row["articleSlug"] .'' . PHP_EOL;
 echo 'daily' . PHP_EOL;
 echo '' . PHP_EOL;
}
while($row = $category->fetch()){

 echo '' . PHP_EOL;
 echo ''.$category_base_url. $row["categorySlug"] .'' . PHP_EOL;
 echo 'daily' . PHP_EOL;
 echo '' . PHP_EOL;
}
while($row = $tag->fetch()){

 echo '' . PHP_EOL;
 echo ''.$tag_base_url. $row["articleTags"] .'' . PHP_EOL;
 echo 'daily' . PHP_EOL;
 echo '' . PHP_EOL;
}



  $tagsArray = [];
  $stmt = $db->query('select distinct LOWER(articleTags) as articleTags from techno_blog where articleTags != "" group by articleTags');
   while($row = $stmt->fetch()){
        $parts = explode(',', $row['articleTags']);
        foreach ($parts as $tag) {
            $tagsArray[] = $tag;
        }
    }
    $finalTags = array_unique($tagsArray);
foreach ($finalTags as $tag) {
        
        echo '' . PHP_EOL;
 echo ''.$tag_base_url.$tag.'' . PHP_EOL;
 echo 'daily' . PHP_EOL;
 echo '' . PHP_EOL;
    }

echo '' . PHP_EOL;

?>
 

Google and other search engines support the XML file. The sitemap format should be XML. Let's use the access file to redirect PHP extension to XML extension.
Create a file and save as .htaccess inside the link

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

Now, you can open sitemap URL as

http://localhost/blog/link/sitemap.xml

In this way , you can create and generate sitemap using PHP and MYSQL database.

Recommended Posts:-

Previous Posts:-