The other day we created a xml sitemap using information retrieved from a database with php, we thought we’d show you the code on how to do it:
-
<?php
-
$conn = mysql_connect("dblocation", "username", "password") or die('Error connecting to mysql');
-
mysql_select_db(dbname);
-
echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
-
echo "<urlset xmlns=\"http://www.sitemaps.org/schemas/sitemap/0.9\">\n";
-
$sql = "select * from tablename GROUP BY fieldname";
-
$rs = mysql_query($sql, $conn);
-
while($row=mysql_fetch_array($rs)) {
-
echo "<url>\n";
-
echo "<loc>http://www.your-url.com/".$row['abbr'].".html</loc>\n";
-
echo "<lastmod>2008-09-29</lastmod>\n";
-
echo "<changefreq>monthly</changefreq>\n";
-
echo "
-
<priority>0.5</priority>\n";
-
echo "</url>\n";
-
}
-
echo "</urlset>" ;
-
mysql_close($conn);
-
?>
Save as sitemap.php and submit to search engines. Also in your robots.txt add: sitemap: http://www.your-url.com/sitemap.php
This code will return this xml source output for EACH record in your database:
<url>
<loc>http://www.your-url.com/page-path.html</loc>
<lastmod>2008-09-29</lastmod>
<changefreq>monthly</changefreq>
<priority>0.8</priority>
</url>
Posted under Web Design
This post was written by Coyol on September 30, 2008
That means you store every page of your site into a table. Not every site are made like this.
You’re right David, every page is stored in DB. Every site is no made like this BUT there are some that are and hopefully this will help them.
how we use it
it give this error
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/xxxx/public_html/sitemap.php on line 8
??????????????
Post your code… there is obviously something wrong in your sql request.
we use it in vbulletin forums
we change the username password dblocation dbname and url for my site
but it give this error
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/xxxx/public_html/sitemap.php on line 8
<?php
$conn = mysql_connect(”localhost”, “kuraiks_roh”, “055511″) or die(’Error connecting to mysql’);
mysql_select_db(kuraiks_vb);
echo “\n”;
echo “\n”;
$sql = “select * from table GROUP BY field”;
$rs = mysql_query($sql, $conn);
while($row=mysql_fetch_array($rs)) {
I’m not real sure if this would work with vBulletin. But to find out, you need to edit this line:
$sql = “select * from table GROUP BY field”;
You can probably just delete the ‘GROUP BY field’ part, but you need to change ‘table’ to the table you’re pulling info from. So just like this:
$sql = “SELECT * FROM yourtablename”;
Then look for the field that serves the url for each post and change this part:
http://www.your-url.com/“.$row['abbr'].”.html
with just:
“.$row['fieldname'].”
try this and see how it works.
We try to change it
and we reply to you
regards
Thank you, works like a charm
Hi
I just found your script and thought I would say thanks, its a great idea and it works perfect with my site as all the urls are already on the database.
Thanks again
Hi,
Thank you for this post, I want to generate sitemap from database, but it seems like I have a probleme with the structure of my URL :
http://dictionary-psychology.com/index.php?a=term&d=Dictionary+of+psychology&t=ABBA+design
If i use your code, Can this work ?
Thank you
And keep up the good work…