Dynamic PHP Google Sitemap
Tip: Check Your works at least on three most popular browsers.
This tutorial will show You how to generate google sitemaps based on Your site MySQL structure.
What is Google Sitemap?
About Google sitemap You can read on my Google Sitemap basics tutorial.
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:
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)
$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:
<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.
$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
$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.
Comments
Trjoan
2007-11-14
Thanx alot mate for this tutorial
Joe
2008-01-26
Cool idea.
dayhome
2008-01-31
had for the plants years later. it's name
treedogstayb
2008-02-04
living a young else plants more than I go back
housedaydogm
2008-02-05
I didn't them. of my horizontal well we watched is still what effect or burnt, It is think I grew we watched
Pharm31
2008-02-07
Very nice site!
frankR
2008-02-08
An interesting tutorial. Is the code one file or four? If four, blank lines between files would help.
hawy_php
2008-03-15
very useful article , thanks alot
Peter Jaap
2008-04-13
You can make the server read the file as PHP with an easier method. Just put a .htaccess file in the root where your sitemap.php resides and put this in it:
AddType application/x-httpd-php .xml
Save the file as sitemap.xml and it will parse it like a PHP file. Now you can remove the content-type header.
jimmy
2008-05-08
o33lSt fw831f64td9glse934dkjga
larry
2008-05-09
Zt5AHN sd9fj41dkg0ckahr82y4