Dynamic PHP Google Sitemap

Author: bwebi.com Author's URL: bwebi.com More by this author

Added:Jan 16, 2008 Rating:

Level: Experienced Software: PHP Editors

Now some theory

First - we need a site which we want to be "mapped". I'll show a basic one:

index.php - main page which is showing news.
tutorials.php - this page is showing a list of tutorials.
vievtutorial.php - and this one is showing selected tutorial.
contact.php - just a static page.

Schema:

Dynamic PHP Google Sitemap

vievtutorial.php file shows tutorial which ID is typed in ?id=NUMBER.

All news and tutorials are stored in MySQL database. Here are sample tables:

Tutorials:

tutorials_id	tutorials_name	tutorials_text	tutorials_date
1	tutorial 1	some txt	2007-09-03 08:36:27
2	tutorial 2	and more...	2007-09-15 17:06:16

News:

news_id	news_title	news_text	news_date
1	news1	some text	2007-09-03 08:36:27
2	news2	and more...	2007-09-15 17:06:16

Database details:

Username: username
Password: password
Database: database

Time to write some code.

First we have to make our map skeleton with some needed stuff (for example database connection)

<?php

$user = 'username';
$pwd = 'password';
$conn = mysql_connect('localhost', $user, $pwd) or die ('Cannot connect to server');
mysql_select_db('database') or die ('Cannot open database');

header('Content-Type: application/xml');
echo '<?xml version="1.0" encoding="UTF-8"?>'."n";
?>
<urlset xmlns="http://www.google.com/schemas/sitemap/0.84">

URLs goes here

</urlset>

Explanation

First 4 lines are for MySQL connection (You may connect in diffrent way - It is Your choise)

header('Content-Type: application/xml'); - this line "tells" to browser that this document should be readed as XML.

echo '<?xml version="1.0" encoding="UTF-8"?>'."n"; - it is a simple trick. Because on this line we have <? and ?> signs, it may cause errors in PHP file - that's why we write this line in single quotes as echo.

Rest is like in normal sitemap.

Adding pages

First the index.php file:

<url>
<loc>http://www.yoursite.com/</loc>
<lastmod>
<?php
$sql = "SELECT MAX( news_date ) as date FROM news";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);
echo str_replace(' ', 'T', $row['date']).substr(date("O"), 0, -2).':00';
?>
</lastmod>
</url>

Explanation:

Here we have simple MySQL query (we take newest date from news table). One interesting command is the echo one.

echo str_replace(' ', 'T', $row['date']).substr(date("O"), 0, -2).':00'; - str_replace and substr was used to change date from 2007-09-03 08:36:27 to 2007-09-15T09:31:11-05:00 format.

This same method we can use for tutorials.php file. We can take the date of newest tutorial.

Next thing is viewtutorial.php file.

<?php
$sql = "SELECT * FROM `tutorials`";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result)) {
?>
<url>
<loc>http://www.yoursite.com/viewtutorial.php?id=<?php echo $row['tutorials_id']; ?></loc>
<lastmod><?php echo str_replace(' ', 'T', $row['tutorials_date']).substr(date("O"), 0, -2).':00'; ?></lastmod>
</url>
<?php } ?>

We used a while loop here to take every single ID from tutorials table and make URL from it. In this example it will be two URLs: vievtutorial.php?id=1 and viewtutorial.php?id=2

Last thing is contact.php but this is only static file - there will be no problem with it.

Now it's time for full code

<?php

$user = 'username';
$pwd = 'password';
$conn = mysql_connect('localhost', $user, $pwd) or die ('Cannot connect to server');
mysql_select_db('database') or die ('Cannot open database');

header('Content-Type: application/xml');
echo '<?xml version="1.0" encoding="UTF-8"?>'."n";
?>
<urlset xmlns="http://www.google.com/schemas/sitemap/0.84">
<url>
<loc>http://www.yoursite.com/</loc>
<lastmod>
<?php
$sql = "SELECT MAX( news_date ) as date FROM news";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);
echo str_replace(' ', 'T', $row['date']).substr(date("O"), 0, -2).':00';
?>
</lastmod>
</url>

<url>
<loc>http://www.yoursite.com/tutorials.php</loc>
<lastmod>
<?php
$sql = "SELECT MAX( tutorials_date ) as date FROM tutorials";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_assoc($result);
echo str_replace(' ', 'T', $row['date']).>substr(date("O"), 0, -2).':00';
?>
</lastmod>
</url>

<?php
$sql = "SELECT * FROM `tutorials`";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result)) {
?>
<url>
<loc>http://www.yoursite.com/viewtutorial.php?id=<?php echo $row['tutorials_id']; ?></loc>
<lastmod><?php echo str_replace(' ', 'T', $row['tutorials_date']).substr(date("O"), 0, -2).':00'; ?></lastmod>
</url>
<?php } ?>

<url>
<loc>http://bwebi.com/contact.php</loc>
<lastmod>2007-09-14T21:56:53<?php echo substr(date("O"), 0, -2).':00'; ?></lastmod>
</url>
</urlset>

With this knowlage You can make dynamic sitemaps for any page You want.