I ran across this and I see people are having problems.
I really want this working so I have started working on it.
Update 1: I have noticed that there is no place for a prefix, which a lot of us use. So I have added that to the variables.
Update 2: I noticed a few of the queries combined multiple queries into one. mysql_num_rows for one. I have split these up for debugging purposes.
For example:
$result = mysql_query("select * from " . $prefix . "thread WHERE visible = 1",$link);
$num_rows = mysql_num_rows($result);
$query = "select * from " . $prefix . "thread WHERE visible = 1 ORDER BY dateline desc" ;
$result = mysql_query($query) or die("Query failed");
$num_rows2 = mysql_num_rows(mysql_query("select * from " . $prefix . "forum"));
$query2 = "select * from " . $prefix . "forum ORDER BY forumid desc" ;
$result2 = mysql_query($query2) or die("Query failed");
Update 3: It is failing validation (http://validator.w3.org/), but adding the necessary information to it causes the file to fail.
Update 4: It would be really nice if this file pulled from the config automatically.
Giving me the following file:
<?php
///////////////////////Configure this/////////////////////////////////////////////////////
$username="username"; //your db user name
$password="password"; // the pass for the db
$database="dbname"; // name of db
$server="localhost"; // server
$prefix = "vb_";
$sitename="http://something.com/"; // sitename including www
$maxkey = "8"; // max key words as you specified in tfseo control panel
$home_freq = "daily"; //always, hourly, daily, weekly, monthly, yearly, never
$home_priority = "1"; // google priority for home (www.yoursite.com)
$forum_freq = "daily"; //always, hourly, daily, weekly, monthly, yearly, never
$forum_priority = "0.8"; // google priority for forums (www.yoursite.com/f4)
$thread_freq = "daily"; //always, hourly, daily, weekly, monthly, yearly, never
$thread_priority = "0.4"; // google priority for threads (www.yoursite.com/f4/your-thread)
function remove_accents($string){ ////this strings must be the same you are using as character replacements, these are the "wide range"
return strtr($string,
"?????????????????????????????????????????????????? ?????????????@?",
"YuAAAAAAACEEEEIIIIDNOOOOOOUUUUYsaaaaaaaceeeeiiiion oooooouuuuyyEan");
}
////////////////////////////DONT NEED TO TOUCH ANYTHING BELOW//////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////
function unhtmlspecialchars($text)
{
$text = preg_replace('/&#([0-9]+);/esiU', "convert_int_to_utf8('\\1')", $text);
return str_replace(array('<', '>', '"', '&'), array('<', '>', '"', '&'), $text);
}
//connect to the database
$link = mysql_connect($server,$username,$password);
mysql_select_db($database,$link) or die( "Unable to select database");
$result = mysql_query("select * from " . $prefix . "thread WHERE visible = 1",$link);
$num_rows = mysql_num_rows($result);
$query = "select * from " . $prefix . "thread WHERE visible = 1 ORDER BY dateline desc" ;
$result = mysql_query($query) or die("Query failed");
$num_rows2 = mysql_num_rows(mysql_query("select * from " . $prefix . "forum"));
$query2 = "select * from " . $prefix . "forum ORDER BY forumid desc" ;
$result2 = mysql_query($query2) or die("Query failed");
//this is the normal header applied to any Google sitemap.xml file
echo '<?xml version="1.0" encoding="ISO-8859-1"?>
<urlset xmlns="http://www.google.com/schemas/sitemap/0.84">';
//HOME RESULTS
$url_product ="http://" . $sitename;
$realdate = date("Y-m-d");
$year = substr($realdate,0,4); //work out the month
$mon = substr($realdate,5,2); //work out the month
$day = substr($realdate,8,2); //work out the day
$displaydate = ''.$year.'-'.$mon.'-'.$day.'';
echo
'
<url>
<loc>'.$url_product.'</loc>
<lastmod>'.$displaydate.'</lastmod>
<changefreq>'.$home_freq.'</changefreq>
<priority>'.$home_priority.'</priority>
</url>
';
//FORUM RESULTS
$i=0;
for($i=0;$i<$num_rows2; $i++)
{
$url_product ="http://" . $sitename . "/f" .mysql_result($result2,$i,"forumid");
/*you need to assign a date to the entity. if you don't
store a timestamp in the Database then you need slapping*/
$realdate = date("Y-m-d");
$year = substr($realdate,0,4); //work out the month
$mon = substr($realdate,5,2); //work out the month
$day = substr($realdate,8,2); //work out the day
$displaydate = ''.$year.'-'.$mon.'-'.$day.'';
echo
'
<url>
<loc>'.$url_product.'</loc>
<lastmod>'.$displaydate.'</lastmod>
<changefreq>'.$forum_freq.'</changefreq>
<priority>'.$forum_priority.'</priority>
</url>
';
}
//THREAD RESULTS
for($i=0;$i<$num_rows; $i++)
{
//cleanurl
$title = mysql_result($result,$i,"title");
$a = strtolower($title);
$a = remove_accents($a);
$a = unhtmlspecialchars($a);
$a = str_replace("'", '', $a);
$a = preg_split("#[^a-z0-9]#", $a, -1, PREG_SPLIT_NO_EMPTY);
$a = array_slice($a, 0, $maxkey);
$a = implode("-",$a);
if (empty($a))
{
$a = 'thread';
}
//your url-product as we worked out in #4
$url_product ="http://" . $sitename . "/f" .mysql_result($result,$i,"forumid") . "/" . $a . '-t' .mysql_result($result,$i,"threadid");
/*you need to assign a date to the entity. if you don't
store a timestamp in the Database then you need slapping*/
$date = mysql_result($result,$i,"dateline"); //the date stored
$realdate = date('Y-m-d H:i:s', $date);
$year = substr($realdate,0,4); //work out the month
$mon = substr($realdate,5,2); //work out the month
$day = substr($realdate,8,2); //work out the day
/*display the date in the format Google expects:
2006-01-29 for example*/
$displaydate = ''.$year.'-'.$mon.'-'.$day.'';
//you can assign whatever changefreq and priority you like
echo
'
<url>
<loc>'.$url_product.'</loc>
<lastmod>'.$displaydate.'</lastmod>
<changefreq>'.$thread_freq.'</changefreq>
<priority>'.$thread_priority.'</priority>
</url>
';
}
mysql_close(); //close connection
//close the XML attribute that we opened in #3
echo
'</urlset>';
?>
Errors I'm still receiving:
1) IE 7 says invalid XML
2) Awaiting Google Webmaster Tools to validate Sitemap.
Wants:
1) To pass validation
2) Pull info from config file
If I have time (which I don't have much of) - I'll come back here and update this and see if I can't help some people out :) by adding wants.
Note: I'm running 3.8.1 and I get output, but I'm not 100% sure that Google will accept it.
Also note: I'm not officially supporting this, like I said I don't have much time. I will try to get it working for myself and put it here and put notes, but that's about all I'm going to have time to do :)