Dynamic PHP Google Sitemap - Tutorials Directory

(17 ratings)

Dynamic PHP Google Sitemap

By: www.bwebi.com

This tutorial will show You how to generate google sitemaps based on Your site MySQL structure. I hope this tutorial will save up some time. First we have to make our map skeleton with some needed stuff (for example database connection)

Added: 24 April 2008 Views: 2515

Path : Computers

Programming

Php

Keywords: programming php web program code coder freelancer language script dynamic google sitemap

Do you like this tutorial? Now you can support our team to add more :

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)

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 ''."n"; - it is a simple trick. Because on this line we have 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:

http://www.yoursite.com/

$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';
?>

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)) {
?>

http://www.yoursite.com/viewtutorial.php?id=

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 ''."n";
?>

http://www.yoursite.com/

$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';
?>

http://www.yoursite.com/tutorials.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';
?>

$sql = "SELECT * FROM `tutorials`";
$result = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_assoc($result)) {
?>

http://www.yoursite.com/viewtutorial.php?id=

http://bwebi.com/contact.php
2007-09-14T21:56:53

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

About the Author :

www.bwebi.com

Dynamic PHP Google Sitemap
Articles and Tutorials Directory by www.learnfobia.com

Rate this tutorial :