Creating Sitemap Generator for PHPBB3.1 using PHP

ACMer

11 years ago

I just recently upgrade my forum to PHPbb3.1: https://codingforspeed.com/forum/ however, the phpbb3.1 does not offer inbuilt sitemap generator, but we can write a piece of small PHP code to generate this automatically.

require_once('conn.php');
mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
mysql_select_db('forum');

$domain_root = 'https://codingforspeed.com/forum/'; 
header('Content-Type: text/xml; charset=utf-8');

$fid = -1;
if (isset($_GET['fid'])) {
  $fid = (integer)$_GET['fid'];
}

define("POSTS_TABLE", "phpbb_posts");
define("TOPICS_TABLE", "phpbb_topics");

if ($fid > 0) {
    echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
    echo "<urlset xmlns=\"http://www.google.com/schemas/sitemap/0.84\">\n";
    $sql = "
        SELECT * FROM 
        `phpbb_forums`
        where `forum_id` = '$fid'";
 
    $result = mysql_query($sql) or die(mysql_error());
        
    $row = mysql_fetch_row($result);
    echo "<url>\n";
    echo "   <loc>${domain_root}viewforum.php?f=" . $fid . "</loc>\n";
    echo "   <changefreq>hourly</changefreq>\n";
    echo "</url>\n";
   
   // Forums with more that 1 Page
   if ( $row['forum_topics_approved'] > $row['forum_topics_per_page'] ) {
      $pages = $row['forum_topics_approved'] / $row['forum_topics_per_page'];
      for ($i = 1; $i < $pages; $i++) {
        $s = $s + $row['forum_topics_per_page'];
        echo '<url>'. "\n";
        echo '   <loc>' . $domain_root .  'viewforum.php?f=' . $fid . '&start=' . $s . '</loc>'. "\n";
        echo '   <changefreq>hourly</changefreq>'. "\n";
        echo '</url>'. "\n";
      }
   }

   $sql = 'SELECT
       t.topic_title, t.topic_posts_approved, t.topic_last_post_id, t.forum_id, t.topic_type, t.topic_id, 
       p.post_time, p.post_id
       FROM `' . TOPICS_TABLE . '` as `t`, `' . POSTS_TABLE . '` as `p`
       WHERE t.forum_id = '.$fid.'
          AND p.post_id = t.topic_last_post_id
          ORDER BY t.topic_type DESC, t.topic_last_post_id DESC';
    
    $result = mysql_query($sql) or die(mysql_error());
    
    while ($data = mysql_fetch_array($result)) {
       // Topics
      echo '<url>'. "\n";
      echo '   <loc>'. $domain_root . 'viewtopic.php?f=' . $fid . '&t=' . $data['topic_id'] . '</loc>'. "\n";
      echo '   <lastmod>'.date('Y-m-d', $data['post_time']),'</lastmod>'. "\n";
      echo '</url>'. "\n";

      // Topics with more that 1 Page
      if ( $data['topic_replies'] > $row['forum_topics_per_page'] ) {
        $s = 0;
        $pages = $data['topic_replies'] / $row['forum_topics_per_page'];
        for ($i = 1; $i < $pages; $i++) {
          $s = $s + $config['posts_per_page'];
          echo '<url>'. "\n";
          echo '   <loc>'. $domain_root . 'viewtopic.php?f=' . $fid . '&t=' . $data['topic_id'] . '&start=' . $s . '</loc>'. "\n";
          echo '   <lastmod>'.date('Y-m-d', $data['post_time']),'</lastmod>'. "\n";
          echo '</url>'. "\n";
        }
      }
    }
    echo '</urlset>';
} else { // overall sitemap index
  echo '<?xml version="1.0" encoding="UTF-8"?>'."\n";
  echo '<sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">'."\n";
  $sql = 'SELECT * from `phpbb_forums`';
  $result = mysql_query($sql) or die(mysql_error());
  while ($row = mysql_fetch_array($result)) {
      echo '<sitemap>' . "\n";
      echo '<loc>'. $domain_root . 'sitemap.php?fid=' . $row['forum_id'] . '</loc>' . "\n";
      echo '</sitemap>'. "\n";
  }
  echo '</sitemapindex>';
}

Save the above PHP code to e.g. sitemap.php and you can test it, which generates something like this for overall sitemap index:

<sitemapindex xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
<sitemap>
<loc>https://codingforspeed.com/forum/sitemap.php?fid=1</loc>
</sitemap>
<sitemap>
<loc>https://codingforspeed.com/forum/sitemap.php?fid=4</loc>
</sitemap>
<sitemap>
<loc>https://codingforspeed.com/forum/sitemap.php?fid=3</loc>
</sitemap>
<sitemap>
<loc>https://codingforspeed.com/forum/sitemap.php?fid=6</loc>
</sitemap>
<sitemap>
<loc>https://codingforspeed.com/forum/sitemap.php?fid=5</loc>
</sitemap>
<sitemap>
<loc>https://codingforspeed.com/forum/sitemap.php?fid=7</loc>
</sitemap>
</sitemapindex>

And the robots should be able to follow the sitemap index, e.g.:

<urlset xmlns="http://www.google.com/schemas/sitemap/0.84">
<url>
<loc>https://codingforspeed.com/forum/viewforum.php?f=4</loc>
<changefreq>hourly</changefreq>
</url>
<url>
<loc>
https://codingforspeed.com/forum/viewtopic.php?f=4&t=59
</loc>
<lastmod>2015-06-28</lastmod>
</url>
<url>
<loc>
https://codingforspeed.com/forum/viewtopic.php?f=4&t=44
</loc>
<lastmod>2015-06-28</lastmod>
</url>
<url>
<loc>
https://codingforspeed.com/forum/viewtopic.php?f=4&t=53
</loc>
<lastmod>2015-06-28</lastmod>
</url>
<url>
<loc>
https://codingforspeed.com/forum/viewtopic.php?f=4&t=52
</loc>
<lastmod>2014-03-20</lastmod>
</url>
<url>
<loc>
https://codingforspeed.com/forum/viewtopic.php?f=4&t=51
</loc>
<lastmod>2014-03-20</lastmod>
</url>
<url>
<loc>
https://codingforspeed.com/forum/viewtopic.php?f=4&t=47
</loc>
<lastmod>2014-01-27</lastmod>
</url>
</urlset>

Another recommended action is to notify the bot in robots.txt by simply adding the following line:

Sitemap: https://codingforspeed.com/forum/sitemap.php

Please note that the above PHP works well and produces same output for humans and search bots. If you are using functions/APIs based on PHPBB then it is highly likely that the search bots will be prevented from accessing the forum data.