Automating sitemaps.xml with Object Oriented Database

Complete PHP source code below.

Technologies Involved: PHP 5, XML, SQL Database, Recursion, Google Sitemaps

Programming Level: 5/5

The Objective: Write a dynamic sitemaps.xml page to the CMS behind hgarland.com (this site) using PHP with a backend mySQL database.

Sitemaps Background: Google has a new program that lets webmasters publish a complete list of all the pages in the site.  This is the sitemaps program, and it works by simply placing a sitemaps.xml page on your website.  The advantage to having a sitemap.xml is that Google can see as my site changes and spend its time only crawling the pages that I've changed recently.  This saves Google time, and that means my site gets indexed more frequently.

hgarland.com Background: This site, hgarland.com is built on a database-driven Content Management System, Mercury 80.  All of the pages in the site are stored in a single mySQL table, called NavPageObject.  It is a single table that uses object oriented fields to create an unlimited navigation heirarchy.  (The heirarchy is not important in this example since Google's sitemaps.xml protocol only necessitates one flat list of pages.)

Project Description: PHP 5 has new support for native XML.  The new way of creating XML is to construct the entire data structure before we send content to the browser, instead of sending data as we are extracting it.

Notice that the PHP code listed below has only one "print" statement, at the end of the "Level 1" processing.  This is the only line of code that sends XML to the browser.  All the other lines construct the XML.

The reason this is a better practice than the old way of sending PHP to the browser is that you can do multiple phases of processing on the XML, and you don't have to twist your code around to output all the tags and attributes in the right sequence.

In this case, we are able to recursively process the XML data object before we output the final XML to the browser.  The code contains one function, insertNavXML(), and after this function is called once, it calls itself to process its child objects.


PHP Source Code for sitemaps.xml:

<?php
$username="---";
$password="---";
$database="hgarland_com";
$host="localhost.localdomain";

mysql_connect($host,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");


//
// LEVEL 1:
//

$dom=new DomDocument();
$topelement=$dom->createElement("urlset");
$topelement->setAttribute("xmlns", http://www.google.com/schemas/sitemap/0.84);
insertNavXML($dom, $topelement, "25", "");
$dom->appendChild($topelement);

print $dom->saveXML();


//
// LEVEL 2+:
//

$urlnum=0;

function insertNavXML($dom, $inXML, $parentnavid, $inParentURL)
{
     global $urlnum;
// Select recordset:
$query="SELECT NavID, DefaultURL AS loc, DateModified AS lastmod, priority FROM NavPageObject WHERE ParentNavID=" . $parentnavid . " ORDER BY SortOrder";
$result=mysql_query($query);
  $hasparenturl=0;

  //print $query;

$num=mysql_numrows($result);
//$r=0;
while ($i < $num)
  {
  $id=mysql_result($result,$i,"NavID");
     $loc=mysql_result($result,$i,"loc");
     $lastmod=mysql_result($result, $i, "lastmod");

     if($loc==$inParentURL) $hasparenturl=1;

  $record=$dom->createElement("url");

  for($f=1; $f<mysql_num_fields($result); $f++)
  {
       // get name and value of field:
       $fieldname = mysql_field_name($result, $f);
       $fieldvalue= mysql_result($result, $i, $f);

       // modify value if we need to:
       if($fieldname=="loc")
          $fieldvalue="http://www.hgarland.com" . $fieldvalue;
       if($fieldname=="lastmod")
          $fieldvalue=date("c", strtotime($fieldvalue));
       if($fieldname=="priority")
          {
          $fieldvalue=($fieldvalue/200)+0.7-($urlnum/100);
          if($fieldvalue>1) $fieldvalue=1;
          if($fieldvalue<0) $fieldvalue=0;
          }

       // construct dom dee dom dom:
       $fieldnode=$dom->createElement($fieldname);
       $fieldtextnode=$dom->createTextNode($fieldvalue);
  $fieldnode->appendChild($fieldtextnode);
  $record->appendChild($fieldnode);
  }

   
  if(insertNavXML($dom, $inXML, $id, $loc)==0) $inXML->appendChild($record);

  $i++;
     $urlnum++;
  }

  return $hasparenturl;
}


?>