Creating Sitemap Generator for PHPBB3.1 using PHP

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>';
}

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>

<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>

<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.

google-webmaster-sitemap

and the final check is to allow google bots to crawel your forum apparently. By default, the bots belong to a restricted group in phpBB3.

–EOF (The Ultimate Computing & Technology Blog) —

GD Star Rating
loading...

1175 words
Last Post: VBA Script to Remove Protected Excel Files
Next Post: QuickhostUK: OpenSSL Vulnerability Notification

The Permanent URL is: Creating Sitemap Generator for PHPBB3.1 using PHP

15 Comments

andreas

Hi, isn’t there a limit of 50.000 links in a sitemap from google ??

- ACMer
  
  yes, but you can always create sub-sitemaps.
  
Gek

Hi,
May I know if this methid would work with phpbb 3.2.
Regards,
G

- ACMer
  
  Yes, it should work.
  
Hubert Farnsworth

The code works as shown to generate a list of entries like “/forum/sitemap.php?fid=1”. However when I submit the sitemap.php to google webmaster, I get 404 non found errors for each entry.

Have I missed something? Is there something else that needs to be done for the robots to correctly generate correct urls like “forum/viewtopic.php?f=4&t=44”?

Thanks in advance.

- ACMer
  
  The sitemaps are split into sub sitemaps, but Google should be able to crawl without problems. Have you tested the 404 in browser?
  
  - Hubert Farnsworth
    
    Yes, I got the same error with a browser. I then stopped troubleshooting because I found a 3.1 extension which does sitemap creation.
    
    Thanks for sharing your code and responding.
    
Md Nurullah

Its not working.. i got error… Did you test it ?

- ACMer
  
  of course it is working.
  what errors did you get?
  
  - Md Nurullah
    
    HTTP ERROR 500
    That error
    
    - ACMer
      
      have you put DB details correctly in conn.php?
      
      require_once(‘conn.php’);
      mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
      
      and, if it is 500 ERR, it usually means you need to check the apache2/nginx server log to find out the exact errors.
      
  - Md Nurullah
    
    Oh! i did not found conn.php file in my PHPBB script.. where it is?
    mysql_connect(‘MY DB NAME’, ‘DB USER NAME’, ‘DB USER PASS’);
    mysql_select_db(‘androidsma_v2’);
    
    There i used my real db information
    
    - ACMer
      
      in conn.php, you have to define your DB constants details such as
      
      Make sure you modify these details to suit your needs.
      
      - Md Nurullah
        
        Great… it works!!!! Thanks for your quick help
        
        
        ACMer
        
        no problem. Glad it helps

Algorithms, Blockchain and Cloud

Creating Sitemap Generator for PHPBB3.1 using PHP

15 Comments

Leave a Reply

Related posts:

15 Comments

Leave a Reply