Displaying titles of last phpBB posts on home page

A script to display the titles of the last posts on a phpBB forum (version 2) on a page in the site's, usually the homepage.

The phpBB tables

We need to access three tables to get the information to display: the title, author and date.

The table of posts

In this table we will read the post identifier we use to make the link between tables.
We will also take the date of the post that will eventually be displayed and used to sort the last posts.

The table of topics

It provides the identifier of the first post in a thread, and it will be used to build the URL. The identifier of the author of the post will be used to access the table of users and retrieve his name.

The table of users

It contains the names of members of the forum. User_id is topic_poster in the table of topics.

Definition of the request

The parameter FROM selects tables: posts, topics, users. The full name is built by adding the prefix.

The parameter SELECT defines the data to be returned and displayed, it is in order the topic identifier, the title of the post, the date of the post, the name of the member who posted.

Posts are selected by the WHERE clause supplemented by AND. We select the posts among those who start a discussion, and users who corresponds to the number found in the table of topics.

$sql = "SELECT TOP.topic_id, TOP.topic_title, TOP.topic_time, USER.username 
        FROM $posts REF, $users USER, $top TOP
        WHERE REF.post_id = TOP.topic_first_post_id     
        AND TOP.topic_poster = USER.user_id
        ORDER BY REF.post_time DESC LIMIT $size";

The variable $size is a parameter of the script, it is the maximum number of titles to display.

Getting access to settings of the base

For the program to be more generic, database name, username and password are retrieved directly from the config.php file.

To do this, we simply define the path to the configuration file, the root of the forum, and we include this file in our script: variables it contains are now parts of the script...

Displaying the data

We have previously seen how to retrieve the data obtained in the database with the mysql_fetch_assoc function.

The line that displays the data is just one example. It displays the title of the post, the member's name, the date.

echo "<a href='$url'>$title</a> by 
<span class='cssuser'>$username</span>  
<span class='cssdate'>$date</span>\n";

It can be adapted as required.

Using the script

The script is included in the page where to display the titles, and where you want to display them.

It must be configured according to the site:

$site = "http://www.example.com/";   
$forumdir = "forum/";    
$formatflag = false; 

Replace the URL to that of your site, the directory of the forum if needed, and activate the option of conversion to true if the page format is different from that of the forum.

It is also possible to choose the number of titles to be displayed with the last option of the display function:

display($dbhost, $dbname , $dbuser, $dbpasswd , 10);  

Note that the variables used for the other parameters come from the config.php file included.

The complete script

• See the source code.
• Download the script with a demo page.

The forum used to test the script is located on another site, it is not possible to view directly the demo (and this site uses punBB and not phpBB).

See also

• Displaying last discussions on a fluxBB forum.

$sql = "SELECT TOP.topic_id, TOP.topic_title, TOP.topic_time, USER.username 
     FROM $posts REF, $users USER, $top TOP
     WHERE REF.post_id = TOP.topic_first_post_id     
     AND TOP.topic_poster = USER.user_id
     ORDER BY REF.post_time DESC LIMIT $size";

$sql = "SELECT TOP.topic_id, TOP.topic_title, TOP.topic_last_post_time, USER.username, REF.post_id 
     FROM $posts REF, $users USER, $top TOP
     WHERE REF.post_id = TOP.topic_last_post_id
	 AND USER.user_id = TOP.topic_last_poster_id
     ORDER BY REF.post_time DESC LIMIT $size";

// Extracting and displaying titles
      
    while($line = mysqli_fetch_assoc($results)) 
    {
       $title = $line['topic_title'];
       //$title = $line['post_subject'];
       $url = $forum . "viewtopic.php?p=" . $line['post_id'] . "#p" . $line['post_id'];
       
       if($formatflag) $title = convert($title);

       $username = $line['username'];
       $date = strftime("%A %B %e, %Y %H:%M", $line['topic_last_post_time']+(0));
     
       echo "<a href='$url'>$title</a> by <span class='cssuser'>$username</span>  <span class='cssdate'>$date</span><br>\n";
    }