Dynamic Google Sitemap using PHP and MySql

[defeated]

Hi, I want to make a sitemap but a large part of the content of my site is dynamic based on a mysql database.  I understand how to write the php to insert each page using 'while' but what I don't get is how to write the results to an xml file.  If I use php then the page has to end '.php' rather than '.xml'. Is that a problem?

 

I am also not sure if the mysql now() datetime format is right for the correct Sitemap.org format.  If not how do I change it.

 

I looked for free sitemap generators but was unclear if any of them would work with my database properly.  I have static pages that I don't need to script for to be dynamic and they can be in plain flat xml format.  I don't mind updating the sitemap manually for static pages because it will be once in a blue moon that I add or delete them. 

 

The other reason I don't want to use a generator is because by doing it myself I might learn something.  Finding it hard to get good info on what to do though.

 

All help recieved gratefully.

Ian.

 

 

 

 

[Daniel0]

The extension of the page doesn't matter, it's the format and the MIME type which you declare it as which counts. If sitemap.org uses ISO 8601 type dates, then it should be no problem using the date from the database. Otherwise you can change it using a combination of strtotime() and date().

[defeated]

yay I think that means I'm nearly done.... will have to check out the iso standart from Sitemaps.org and check that my mime types are ok...

It can't really be straight forward like that can it? Nothing else has been so far!

 

[Daniel0]

The ISO 8601 standard uses the format YYYY-MM-DD which it appears that sitemap.org also uses. MySQL uses that standard for it's dates as well.

 

Just add header('Content-type: application/xml'); to your file before outputting anything to change the mime type. Then just output valid XML and you should be fine.

[defeated]

Cheers for that... I now have a shiny new Sitemap and google hasn't rejected it. I'll clean the code a bit then post it here in case it's of use to someone..... will be about 24 hrs til I get a chance though.

Happy days.

[defeated]

ok.... here it is.

it is a jobs website called www.mysite.ie so lets assume that all jobs are kept in a MySql table in the db called mytable and they have a unique identifyer column called "unique_id_column" and a date column, that updates with the current date on every change of information in the row, called "up_date_column".  I only keep the jobs showing for 30 days from the last update so you may need to modify the mysql selects. Hope it helps.  It contains static pages too which I update manually since I don't change them that often.

 

 

<?php 
//make a mysql connection
mysql_connect("localhost", "user", "password") or die(mysql_error());
mysql_select_db("mydatabasename") or die(mysql_error());

header('Content-Type: application/xml'); //tells browser that content is xml
echo '<?xml version="1.0" encoding="UTF-8"?>' ;
?>

<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">

<url>
<loc>http://www.mysite.ie/</loc>
<lastmod>
<?php
$sql = "SELECT MAX( up_date_column ) as date FROM mytable"; //selects latest date of anything that was updated in the table 'mytable' and sets that as the last mod for home page. using this here because I have a dynamic scroller on this page based on the db contents.
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_array($result);
echo $row['date'] ;
?>
</lastmod>
<changefreq>Monthly</changefreq>
<priority>0.9</priority>
</url>

<url>
<loc>http://www.mysite.ie/contact.php</loc>
<lastmod>2008-03-14</lastmod>
<changefreq>Monthly</changefreq>
<priority>0.5</priority>
</url>


<url>
<loc>http://www.mysite.ie/about.php</loc>
<lastmod>2008-03-14</lastmod>
<changefreq>Monthly</changefreq>
<priority>0.6</priority>
</url>

<url>
<loc>http://www.mysite.ie/jobs.php</loc>
<lastmod>
<?php
$sql = "SELECT MAX( up_date_column ) as date FROM mytable";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_aarray($result);
echo $row['date'] ;
?>
</lastmod>
<changefreq>Daily</changefreq>
<priority>1.0</priority>
</url>

<?php
$result=mysql_query("SELECT * FROM mytable WHERE TO_DAYS(NOW()) - TO_DAYS(up_date_column) <= 30")or die(mysql_error()); //I only display each job for 30 days past last update.
while($row = mysql_fetch_array($result)) {
?>
<url>
<loc>http://www.mysite.ie/jobdetails.php?id=<?php echo $row['unique_id_column']; ?></loc>
<lastmod><?php echo $row['up_date_column'] ; ?></lastmod>
<changefreq>Weekly</changefreq>
<priority>1.0</priority>
</url>
<?php } ?>

<?php
$result=mysql_query("SELECT * FROM mytable WHERE TO_DAYS(NOW()) - TO_DAYS(up_date) <= 30")or die(mysql_error());
while($row = mysql_fetch_array($result)) {
?>
<url>
<loc>http://www.mysite.ie/applyform.php?id=<?php echo $row['unique_id_column']; ?></loc>
<lastmod><?php echo $row['up_date_column'] ; ?></lastmod>
<changefreq>Weekly</changefreq>
<priority>0.7</priority>
</url>
<?php } ?>

</urlset>

[defeated]

Forgot to write.... I called the Sitemap "sitemap.php", stuck it in the same directory as my homepage and then submitted it to google. All done and dusted. 74 url's submitted just like that.