Dynamic PHP Google Sitemap

Rate this tutorial:
  • Currently Note: 3/5
  • 1
  • 2
  • 3
  • 4
  • 5

Tip: Check Your works at least on three most popular browsers.

opera msie

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:


Automatic google sitemap with php


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.

Comments

Trjoan

2007-11-14

Thanx alot mate for this tutorial

Joe

2008-01-26

Cool idea.

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.

Desh

2008-06-19

totaly bogus......You should need more work on this.

parser

2008-07-28

please use me(c mi name) to analyse comments and avoid unwanted fuckers keystrokin their ass in here...

Tramadol_Lerorgake

2008-10-26

Your Web Site is really wonderful and I bookmarked it. Thank your for the hard work you must have put in to create this wonderful facility. Keep up the excellent work!

kuwar

2008-11-11

very nice code it is use full

HairyMan

2008-11-11

Not bad... Not bad.

Satyendra

2008-11-19

Thnks alot of webmasters

grey580

2008-11-19

as of php 5 the ISO 8601 date can be calculated just by doing this.

date('c',strtotime(row[date]);

It's so easy :)

WTF

2009-02-18

What the F... are these comments........ hotels....... cute teens wtf!!!

barat

2009-06-01

Ok ... had some time and cleaned comments :)

name

2009-07-29

Very interesting sites.,

name

2009-07-29

Help me to find the,

name

2009-07-29

Give somebody the to a site about the,

name

2009-07-29

Nise site,

name

2009-07-29

Nise site,

name

2009-07-29

Real,

name

2009-07-29

What is it,

name

2009-07-29

I want to say thanks!,

name

2009-07-30

So where it to find,

name

2009-07-30

Good Job,

makodef

2009-10-23

Great tutorial, thanks for sharing and keep up the good work it always pays off big time. Peace

Anton

2010-06-16

thank you. nice tutorial

wdw

2011-07-23

wdwd

Manoj

2011-12-21

Thanks for Tutorial.. it's very helpful

sudha kumari

2012-02-26

thank

M.Abooali

2013-04-30

thanks.
simple but useful..

http://www.redbottomshoes2013.co.uk

2013-08-31

Youre so insightful, have so much real stuff to bring to the table. I hope that more people read this and get what I got from it: chills. great job and great web site. I cant wait to read more, keep em comin!

XRumerTest

2013-09-28

Hello. And Bye.

XRumerTest

2013-09-28

Hello. And Bye.

Vusquigunsey

2013-10-23

ipfrymysi

2013-12-30


unfqsikjf

2013-12-30


gmzofqjxi

2014-01-02


ememxtwmc

2014-01-04


ipfmfqpmn

2014-01-06


qgcsexlaf

2014-01-08


tedlmnevl

2014-01-11


vyzvwldbf

2014-01-15