Creating a Dynamic Google Sitemap

My site has a Wordpress blog on it, but it does not power the whole site, just the blog. If my entire site were powered by Wordpress, then I could generate a sitemap for Google using Wordpress functions.

I did not want to have Wordpress control everything; I wanted to have more control. By doing some quick queries, you can generate a sitemap for Google that has all of your blog entries in it.

Setting it Up

First, you will need to send the correct headers, create the opening xml tag, and connect to your database.


<?php
header("Content-Type: text/xml;charset=iso-8859-1");
echo '<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';
require_once('DB CONNECTION GOES HERE'); //This is where I would require my DB connection file

If you are not sure how to connect to your database, you will need to contact your host to get that information, and then do a search for php MySQL connection. If you can’t figure it out still, let me know, and I can help.

Get the Categories

Next, we want to query the database to get the categories used in Wordpress:


$query = "SELECT cat_ID, category_nicename
FROM wp_categories
ORDER BY category_nicename";
$result = @mysql_query($query);

Then, we need to loop through the categories and display a url entry for each category:


while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo '<url>
<loc>//trevor-davis.com/blog/' . $row['category_nicename'] . '/</loc>
<changefreq>weekly</changefreq>
</url>';

Get the Entries

Next, we need to create a query to return all of the entries for each category:


$artQuery = "SELECT p.post_name, DATE_FORMAT(p.post_date, '%Y-%m-%d') AS createdOn
FROM wp_posts AS p, wp_categories AS cat, wp_post2cat AS pc
WHERE p.ID = pc.post_id AND pc.category_id = " . $row['cat_ID'] . "
GROUP BY p.ID
ORDER BY p.ID DESC";
$artResult = @mysql_query($artQuery);

Finally, we want to create a url entry for each blog entry:


while($artRow = mysql_fetch_array($artResult, MYSQL_ASSOC)) {
echo '<url>
<loc>//trevor-davis.com/blog/' . $row['category_nicename'] . '/'. $artRow['post_name'] . '.php</loc>
<lastmod>'.$artRow['createdOn'].'</lastmod>
<changefreq>weekly</changefreq>
</url>';
}

To finish it off, we just close everything up:


}
echo'</urlset>';?>

The Whole Script

Here is the finished script:


<?php
header("Content-Type: text/xml;charset=iso-8859-1");
echo '<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';
require_once('DB CONNECTION GOES HERE'); //This is where I would require my DB connection file

$query = "SELECT cat_ID, category_nicename
FROM wp_categories
ORDER BY category_nicename";
$result = @mysql_query($query);


while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

echo '<url>
<loc>//trevor-davis.com/blog/' . $row['category_nicename'] . '/</loc>
<changefreq>weekly</changefreq>
</url>';

$artQuery = "SELECT p.post_name, DATE_FORMAT(p.post_date, '%Y-%m-%d') AS createdOn
FROM wp_posts AS p, wp_categories AS cat, wp_post2cat AS pc
WHERE p.ID = pc.post_id AND pc.category_id = " . $row['cat_ID'] . "
GROUP BY p.ID
ORDER BY p.ID DESC";

$artResult = @mysql_query($artQuery);

while($artRow = mysql_fetch_array($artResult, MYSQL_ASSOC)) {

echo '<url>
<loc>//trevor-davis.com/blog/' . $row['category_nicename'] . '/'. $artRow['post_name'] . '.php</loc>
<lastmod>'.$artRow['createdOn'].'</lastmod>
<changefreq>weekly</changefreq>
</url>';
}
}
echo'</urlset>';
?>

Modifying the .htaccess file

We also want to rewrite the url for this file so that it is available at //trevor-davis.com/sitemap.xml. Open up your .htaccess file, or create one if you don’t have one. Then add the following:


RewriteEngine on
RewriteRule sitemap.xml googleSitemap.php

Upload this file along with the googleSitemap.php script to your site root, and you are set. You can also add the rest of your site’s file structure to the sitemap as well. You can see how I did this in my google sitemap.

Sitemaps Protocal

You can read more about the sitemaps protocal to see what other attributes you can apply to each url entry.

By the Way…

Let me know if you can think of any way to improve this.