Creating XML Sitemaps from a MySQL database with PHP automatically
Why?
Ecommerce sites can be written dynamically with the description, keywords, content and date stored in a MySQL database. This means it's easy to add new pages on whim. It also means hand writing an XML Sitemap every time a new page is added becomes a pain in the arse.
Sitemaps are an important way of getting pages indexed by search engines. Google's Webmaster Tools allows you to specify a file to use as your sitemap. The sitemap file can be stored with a number of extensions. Originally I was writing the XML sitemap by hand, adding new pages and editing and uploading the sitemap after each update.
There are plenty of websites that will provide a free sitemap by entering a URL. These are o.k but it means you have to go to the site, copy the code, save it as sitemap.xml on your computer, connect with ftp and upload the file to the server.
What I needed was a way of creating XML Sitemaps from a MySQL database with PHP automatically.
How?
Using the method below, PHP can be used to query the database of pages, extract the required information and give it to Google without me having to lift a finger.
It would be easy to modify the script to use on any database which stores the URLs of every page in a website. The script is saved on the server as sitemap.php.
Firstly the header information is sent. Then after connecting to the database the table is queried to provide the information about each page.
The XML protocol is described at www.sitemaps.org. The $xml_output variable stores all the XML data using the .= operator to add each part.
In this script the homepage is added with a priority of 1. The rest of the pages are then generated with a 'for loop' and are set a priority of 0.8.
The <lastmod>, <changefreq> and <priority> tags are all optional but I thought it would be good to include them. The date of each page is not taken from the database at the moment. Google didn't like the standard MySQL date format and I haven't got round to fixing it. Finally the end of the XML data is added and the whole of $xml_output is echoed.
PHP script to create XML sitemaps
<?php
header("Content-Type: text/xml; charset=utf-8");
include('includes/connectionsettings.php');
$query = "SELECT * FROM tablename ORDER BY date DESC";
$result = mysql_query($query) or die("No data found.");
$xml_output = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
$xml_output .= '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">';
$xml_output .= "<url>
<loc>http://www.ecommerce301.com/</loc>
<lastmod>2009-01-01</lastmod>
<changefreq>monthly</changefreq>
<priority>1</priority>
</url>";
for($x = 0 ; $x < mysql_num_rows($result) ; $x++){
$row = mysql_fetch_assoc($result);
$urltitle = $row<'urltitle'>;
$id = $row<'id'>;
$loc = "http://www.ecommerce301.com/ecommerce.php?t=$urltitle&id=$id";
$xml_output .= "\t<url>\n";
$xml_output .= "\t\t<loc>" . $loc . "</loc>\n";
$xml_output .= "\t\t<lastmod>2009-01-14</lastmod>\n";
$xml_output .= "\t\t<changefreq>monthly</changefreq>\n";
$xml_output .= "\t\t<priority>0.8</priority>\n";
$xml_output .= "\t</url>\n";
}
$xml_output .= "</urlset>";
echo $xml_output;
?>
Posted by Tom on Wed 28th Jan 2009
