How to create dynamic sitemap using php and mysqli

Hello devs, is been a while, today article will be on how to successfully create a dynamic sitemap for your handmade php website using php and mysqli.

What is sitemap?

Sitemap is a dictionary or database that contain all your website links(url), this help in google ranking and display your content on search engine, that is why when we talks about SEO(search engine optimization) sitemap is very important, is a must to handle.

Valid Sitemap Format

<?xml version='1.0' encoding='UTF-8'?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
<url>
 <loc>https://codexpresslab.blogspot.com/2018/03/build-your-own-text-to-speech.html</loc>
 <lastmod>2018-03-25T12:09:53Z</lastmod>
</url>
<url>
 <loc>https://codexpresslab.blogspot.com/2018/03/google-material-spinner-loader.html</loc>
 <lastmod>2018-03-25T11:48:03Z</lastmod>
</url>
</urlset>

How to implement sitemap on your php website?

Here am going to show you guys how to successfully implement sitemap on your hand made php site. Let cut the long talk and go straight to the steps.

Step 1

First you have to create a php file name it with createsitemap.php

Step 2

Copy the below code to createsitemap.php and save

<?php
require_once ('config.php'); // database connection
$page_title=TITLE;
$site_url=URL;
$site_email=EMAIL;
$site_desc=DSC;
require 'incfiles/bbparser.php'; // phpbb code parser


$xml = new DomDocument("1.0","UTF-8");

$urlset = $xml->createElement("urlset");
$urlset = $xml->appendChild($urlset);

$url = $xml->createElement("url");
$url = $urlset->appendChild($url);
//========================================================
$sql = "SELECT * FROM `topics` ORDER BY topic_id DESC "; 
$query = $db->query($sql);

while($row = mysqli_fetch_assoc($query)) // loop all topics from databse
{
$title1=$row['title']; 
$link1=$row['link']; 
$tid1 = $row["topic_id"];

$date = date("r", $row["time"]);
$mmessage = $row["content_text"];
$mmessage = str_replace('&', 'and', $mmessage); // Replaces all & symbol with and.
$bbcodes = new bbParser(); 
$message = $bbcodes->getHtml($mmessage);
$link2 = urldecode($site_url . '/' . $tid1 . '/' . $link1);

$loc = $xml->createElement("loc","$link2");
$loc = $url->appendChild($loc);

$lastmod = $xml->createElement("lastmod","$date");
$lastmod = $url->appendChild($lastmod);

$changefreq = $xml->createElement("changefreq", "always");
$changefreq = $url->appendChild($changefreq);

$priority = $xml->createElement("priority","1.00");
$priority = $url->appendChild($priority);
}

$xml->FormatOutput = true;
$string_value = $xml->saveXML();
$xml->save("sitemap.xml"); // your desire sitemap file

?>

$xml->save("sitemap.xml");  This line of code will automatically create sitemap.xml file on your server will all your site post link(url).

Database Structure

-- phpMyAdmin SQL Dump
-- version 4.7.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: May 01, 2018 at 05:07 PM
-- Server version: 10.1.25-MariaDB
-- PHP Version: 7.1.7

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `forum`
--

-- --------------------------------------------------------

--
-- Table structure for table `topics`
--

CREATE TABLE `topics` (
  `topic_id` int(11) NOT NULL,
  `post_type` varchar(30) NOT NULL,
  `title` varchar(100) NOT NULL,
  `link` varchar(100) NOT NULL,
  `content_text` text NOT NULL,
  `tags` varchar(100) NOT NULL,
  `asked` varchar(50) NOT NULL,
  `user_id_fk` int(11) NOT NULL,
  `board_id_fk` int(11) NOT NULL,
  `created` varchar(30) NOT NULL,
  `status` int(11) NOT NULL,
  `today` varchar(20) NOT NULL,
  `month` varchar(30) NOT NULL,
  `time` varchar(100) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `topics`
--
ALTER TABLE `topics`
  ADD PRIMARY KEY (`topic_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `topics`
--
ALTER TABLE `topics`
  MODIFY `topic_id` int(11) NOT NULL AUTO_INCREMENT;COMMIT;

Database Connection
Here is your db connection

<?php
//db configuraton details 
session_start();
define('DB_SERVER', 'localhost'); 
define('DB_USERNAME', 'root'); 
define('DB_PASSWORD', '');
define('DB_DATABASE', 'forum'); 

$db = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE); 

//Connect and select the database

define('URL', 'https://christianity24.org'); // set website url
define('TITLE', 'Nairaland Official Script');
define('DSC', 'Nairaland Official Script');

define('EMAIL', 'unduworldofliving@gmail.com');  // define email
?>

https://christianity24.org/sitemap.xml

Demo

Please if you have any question comment below.

Don’t forget to share with friends and forums