dynamic sitemaps with php and mysql

Author: George Hayes
Published:

This tutorial assumes you have a good understanding of PHP and SQL and the apache webserver.

This tutorial is about creating a sitemap.xml file dynamically using php and mysql. The primary reason for doing this is so you do not need to manually update your sitemap. If you have a site with a purely static html pages then this is pretty much a waste of time for you. This is more designed around sites that are dynamic or growing such as those containing blogs, articles, profiles, which can increase in value and the primary content of each page is stored in an SQL database.

I will be using an early version of this sites sitemap as an example. To change the ending to of the file when viewed on the internet we will use a RewriteRule inside the .htaccess file. The following lines need to be in the .htaccess file in your site directory.

<IfModule mod_rewrite.c>
Options +FollowSymLinks
RewriteEngine on
RewriteRule ^sitemap.xml$ sitemap.php
</IfModule>
You should also have the following line in your robots.txt file for search engines to find it.
SITEMAP: http://ServerName/sitemap.xml
The following is a copy of the sitemap2.php file. You can see its output at sitemap2.xml

sitemap2.php
<?php session_Start();
include 'resource_v2.php';
include 'php/tutorialClass.php';
include 'php/blogClass.php';
$servername = $_SERVER['SERVER_NAME'];
header("Content-type: text/xml");
date_default_timezone_set("America/North_Dakota/Center");
$date = date("Y-m-d");
$Y = date("Y");
$m = date("m");
$j = date("d");
if($j > 0)
{

	$newdate = strtotime ( '-1 day' , strtotime ( $date ) ) ;
	$newdate = date ( 'Y-m-d' , $newdate );
	#$date = $newdate;
}
$time = date("H:i:s");
$zone = "-05:00";
#
$handler =  db_connect('tutorials');
$sql = 'SELECT * FROM tutorial';
$stmt = $handler->prepare($sql);
$stmt->setFetchMode(PDO::FETCH_CLASS,'tutorial');
$stmt->execute();
#
$map = "";
$map .= "<?xml version='1.0' encoding='UTF-8'?>\n";
$map .= "<urlset xmlns=\"http://www.sitemaps.org/schemas/sitemap/0.9\">\n";
$map .= "<url><loc>https://".$servername."/</loc><lastmod>".$date."T".$time."+00:00</lastmod>
<changefreq>daily</changefreq><priority>0.50</priority></url>\n";
#
while($r = $stmt->fetch(PDO::FETCH_CLASS)){
	$map .= "<url><loc>http://".$servername."/view_".$r->shorttitle.".html</loc>
<lastmod>".$date."T".$time."+00:00</lastmod>
<changefreq>weekly</changefreq><priority>0.50</priority></url>\n";
}
#
$handler = db_connect('grhblog');
$sql = 'SELECT * FROM blog';
$stmt = $handler->prepare($sql);
$stmt->setFetchMode(PDO::FETCH_CLASS,'blog');
$stmt->execute();
while($r = $stmt->fetch(PDO::FETCH_CLASS)){
	list($data,$time)=explode(" ",$r->timedate);
	$map .= "<url><loc>http://".$servername."/viewblog_".$r->idnumber.".html</loc>
<lastmod>".$date."T".$time."+00:00</lastmod>
<changefreq>weekly</changefreq><priority>0.50</priority></url>\n";
}
$map .= "</urlset>";
echo($map);
?>

www.sitemaps.org/ lists how sitemaps are supposed to be organized and structured.

The section of code regarding SQL passwords is not listed in the code. It is provided as an example you will need to create your own SQL code to access your own databases obviously. This is being provided to show how certain aspects are done. If you look at the code you will notice the date for the tutorials are generated where the codes for the lessons are taken from the database. The format of the dates and times are important as is the rest of the formatting.

The first line you should pay attention to is for generating the header.

header("Content-type: text/xml");
The next two lines you should pay attention to are the date and time formats.
$date = date("Y-m-d");
$time = date("H:i:s");
I have a lot of stuff you may not need such as subtracting a day from the current date time because you don't want your times to be out side of the realm of possibility when a search engine looks at it. I only use that for stuff I don't have a date stored on. Its an issue I will be correcting. If however if you have your date and time stored as I do on the lessons then it is much simple to retrieve the time stamp and split it using explode and list into date and time such as in the following line.
list($data,$time)=explode(" ",$r->timedate);
The next couple lines you will need
$map = "";
$map .= "<?xml version='1.0' encoding='UTF-8'?>\n";
$map .= "<urlset xmlns=\"http://www.sitemaps.org/schemas/sitemap/0.9\">\n";
$map .= "<url><loc>https://".$servername."/</loc><lastmod>".$date."T".$time."+00:00</lastmod>
<changefreq>daily</changefreq><priority>0.50</priority></url>\n";
The first bit of data fed into map is this an xml file and what version and encoding. The next line simply points to the sitemap schema. Then the first location you list is your sites landing page.
<url> indicating this is a url
<loc> indicating the location, this is where the url must go.
<lastmod> is the last time it was modified
<changefreq> is how often it changes
<priority> is the priority of the url compared to others on your site 0.5 is default