Posted on Monday 9th of April 2007 at 12:23 in Tutorials

Writing a PHP Google Sitemap generator without using fopen

The importance of the Google sitemap is a commonly discussed thing but if you don't use a pre-built solution (like Wordpress) then how do you keep your sitemap.xml file up to date? Here is a tutorial explaining how to write your own sitemap.xml generator in PHP (without using fopen).

(And yes I am fully aware the formatting of comments is rather nasty but it'll do)

The Google sitemap is a sitemap.xml file that you place in the document root of your website (which you inform Google of). It enables the popular search engine to index the pages in your site more accurately - rather than relying on the Googlebot to do all the hard work.

Automate the process
There are plugins for Wordpress that update the sitemap.xml file every time you publish (as there are for other content-managed solutions) but if you build your own site then you have to generate the file manually. Previously I've relied on giving the URL to a sitemap generator, saving it's output and uploading it to the server via FTP. I got bored of this so wrote my own generator that I could give to Google and forget about it. I'll explain how...

#1 - Set the header
Create a new file called sitemap.php. You need to set the header so that when you view the sitemap.php file it outputs as XML:

header("Content-Type: text/xml;charset=iso-8859-1");

#2 - Open the connection to your database and do the query
However you store your information, it'll still be databased so the normal PHP connection/query code applies - this code is lifted directly off my site directly:

//connect to the database mysql_connect("mysql.yoursite.com","username","password"); @mysql_select_db("databaseName") or die("Unable to select DB"); //count all the articles that are current and published $num_rows = mysql_num_rows(mysql_query("select * from Articles WHERE Time < CURRENT_TIMESTAMP")); //select them and put them into a dataset called $result $query = "select * from Articles WHERE Time < CURRENT_TIMESTAMP ORDER BY ArticleID DESC" ; $result = mysql_query($query) or die("Query failed");

#3 - Start the XML document
Now that we've got all the current/published articles selected into a dataset we need to write the start of the XML document:

//this is the normal header applied to any Google sitemap.xml file echo '<?xml version="1.0" encoding="UTF-8"?> <urlset xmlns="http://www.google.com/schemas/sitemap/0.84">';

#4 - Work out the URL-Product
This is the thing that's most likely to differ depending on how you've developed your site. At Seopher.com I use URL-rewriting to associate a clean-URL with the ID of the article. Therefore what is realistically "http://www.seopher.com/viewarticle.php?id=5" becomes "http://www.seopher.com/articles/an_article_title". So the code I use to produce the URL-product is:

//don't worry this code makes more sense in step #5 $url_product = 'http://www.seopher.com/articles/'.mysql_result($result,$i,"CleanTitle");

Whereas if you're using Wordpress-esq conventions (ie. www.seopher.com/article.php?id=5) then the code would look more like:

$url_product = 'http://www.seopher.com/viewarticle.php?id='.mysql_result($result,$i,"ID");

So all you need to do is work out how to make a real URL out of your databased content and then move on to step 5.

#5 - Output a list of your databased URLs
Now that you've worked out how to create a working URL-product of your content, it's time to output that into an XML schema that Google can make sense of.

//loop through the entire resultset for($i=0;$i<$num_rows; $i++) { //your url-product as we worked out in #4 $url_product = 'http://www.seopher.com/articles/'.mysql_result($result,$i,"CleanTitle"); /*you need to assign a date to the entity. if you don't store a timestamp in the Database then you need slapping*/ $date = mysql_result($result,$i,"Time"); //the date stored $year = substr($date,0,4); //work out the year $mon = substr($date,5,2); //work out the month $day = substr($date,8,2); //work out the day /*display the date in the format Google expects: 2006-01-29 for example*/ $displaydate = ''.$year.'-'.$mon.'-'.$day.''; //you can assign whatever changefreq and priority you like echo ' <url> <loc>'.$url_product.'</loc> <lastmod>'.$displaydate.'</lastmod> <changefreq>daily</changefreq> <priority>0.8</priority> </url> '; }

The above code loops through the resultset and outputs the content into the XML schema that Google expects. The "lastmod" field is populated by re-formatting the timestamp you *should* have against when your article was posted. The "loc" assett is populated using the URL-product we made earlier.

#6 - Close everything down
It's just a case of closing the connection and ending the XML document.

mysql_close(); //close connection //close the XML attribute that we opened in #3 echo '</urlset>';

And that's it as far as outputting everything in XML format. So you can upload the sitemap.php file to your server, load it into the browser and you should (hopefully) see a mess of all your content. View the source of the page and you should see something like:

<?xml version="1.0" encoding="UTF-8"?> <urlset xmlns="http://www.google.com/schemas/sitemap/0.84"> <url> <loc>http://www.seopher.com/articles/review_of_the_o2_xda_orbit_pda_phone</loc> <lastmod>2007-04-09</lastmod> <changefreq>daily</changefreq> <priority>0.8</priority> </url> <url> <loc>http://www.seopher.com/articles/review_of_dreamlinux_2_2_media_edition_sort_of_</loc> <lastmod>2007-04-08</lastmod> <changefreq>daily</changefreq> <priority>0.8</priority> </url> </urlset>

Obviously that's my sitemap.php output (which has more than two items in it I might add) but you should see something to that effect. If you don't you'll need to troubleshoot what's causing problems. However, once you've got sitemap.php outputting something like you can see in the area above - then you can move on to step #7.

#7 - Modify the .htaccess file so sitemap.php becomes sitemap.xml
This is a crucial step because Google needs to see a SITEMAP.XML file and all you've got is SITEMAP.PHP. What you need to do is either edit or create a .htaccess file with the following logic in it:

RewriteEngine on RewriteRule (.*)\.xml(.*) $1.php$2 [nocase]

What this does is it turns on the rewrite rule (allowing you to modify how URLs are handled, essentially) and adds the logic that allows a file.php to be intepreted as file.xml.

This now means that if you put sitemap.xml into your browser you'll be viewing the output from sitemap.php and that's crucial because now when Google looks for sitemap.xml it's viewing live data from your PHP script. This means that your sitemap.xml file will never be inaccurate.

Conclusion of what you should have
A sitemap.php file on your server that you can access by entering "www.yourwebsite.com/sitemap.php" or "www.yourwebsite.com/sitemap.xml" into your browser. This means that you now have a constantly up-to-date sitemap.xml file because you're not having to get it generated by a third party and upload it to your server.

How to improve it
My sitemap generator doesn't index my static pages (or even the homepage) because the homepage is already indexed sufficiently and I consider the other pages (contact, about etc) to be of no use to search engines. They're easily accessible from the navigation too so Googlebot shouldn't have any problems indexing them anyway.

Why it's good
Most hosts disable the use of the PHP function fopen which you need to write a physical sitemap.xml file, so this method bipasses the physical creation and instead references the PHP file as an XML document.

Hope this was useful.

 

Enjoy this article? Why not subscribe to the full RSS feed?