Home     Articles & Projects     Products & Web Services

Generic PHP Google Sitemap script based on any MySQL database table containing page URLs

The following PHP code will enable you to generate a Google Sitemaps compatible XML URL list from any MySQL database table containing each page URL - or at least enough information for you to construct the page URL from the fields of each row, for example by appending an ID field to a common prefix. Simply edit the first 4 lines with your database and table details as required, and then modify the following line:

    $loc = $row["url"];

...to construct the URL of the page you wish to include in the sitemap. $row will be an associative array of each of the fields in the database table. As an alternative to the above, you may have the situation where each page on your site can be generated from an id field, for example:

    $loc = "http://www.example.com/page.php?id=".$row["id"];

You can submit sitemap.php directly to Google (it doesn't matter that the filename does not end in .xml), as the correct content-type header will be sent when the page is requested.

sitemap.php

<?php
  $databaseServer
= "localhost";
 
$databaseUsername = "username";
 
$databasePassword = "password";
 
$databaseName = "database";
 
$databaseTable = "tablename";
 
header("Content-Type: text/xml");
  function
xmlentities($text)
  {
   
$search = array('&','<','>','"','\'');
   
$replace = array('&amp;','&lt;','&gt;','&quot;','&apos;');
    return
str_replace($search,$replace,$text);  
  }
  print
chr(60)."?xml version='1.0' encoding='UTF-8'?".chr(62);
  print
chr(60)."urlset xmlns='http://www.google.com/schemas/sitemap/0.84' xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xsi:schemaLocation='http://www.google.com/schemas/sitemap/0.84 http://www.google.com/schemas/sitemap/0.84/sitemap.xsd'".chr(62);
 
$sql = "SELECT * FROM `".$databaseTable."`";
 
$link = @mysql_connect($databaseServer,$databaseUsername,$databasePassword);
  @
mysql_select_db($databaseName,$link);
 
$result = mysql_unbuffered_query($sql,$link);
  while(
$row = mysql_fetch_array($result,MYSQL_ASSOC))
  {
   
// create the loc (URL) value based on the $row array, for example:
   
$loc = $row["url"];
    print
"<url>";
    print
"<loc>".xmlentities($loc)."</loc>";
    print
"</url>";
  }
  print
"</urlset>";
?>