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 tutorials, 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 tag 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 ''.PHP_EOL;
echo '' . 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 .htaccess 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:-
