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

post_id post_time
   

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

post_id topic_id topic_first_post_id topic_poster
       

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

user_id username
   

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

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

Forum

Displaying titles of last PHPBB posts on home page

2011-04-18 13:03:40

acae

Thanks for posting that script, I managed to get it to work fine. I'm new to SQL and PHP though so bear with me. I was wondering how the script could be altered so that it only displays the last posts, rather than the last topics. I've had a look through and can see the select command:
$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";
and I'm guessing instead of selecting the TOP.topic_time I'd need to select the post-time from the posts table .... only I have no idea how to do that from this example. Can anyone give me some pointers please? I'm also a little confused at what the TOP. in the select command refers to. Thanks. ACAE
2011-04-18 13:31:22

scriptol

You use a dot when you perfom a join, i.e. you need to access several tables at once to retrieve the data you need. TOP is the name of a table and it is followed by column names. How to access the post-time depends if this column is in the right table. Do you have tried the script?
2011-04-18 13:58:52

acae

Hello, yes I tried the script after about 30 minutes of reading through I finally got it to work. I edited it to:
$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";
This way the WHERE clause checks to see if the post_id and topic_last_post_id are the same and also that the user_id and topic_last_post_id are the same. Essentially it only displays the last post of each topic. The script also needed editing so that the URL it generates points directly to the right post. It's also why I called REF.post_id above

// Extracting and displaying titles
      
    while($line = mysql_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";
    }
It all seems pretty simple now. Thanks for posting up the original script. It took me a while, and doesn't help I know the bare minimum of PHP and SQL commands but just wanted to say thanks for the script. I do have one question though, why is the posts table in PHPBB database called with the label "REF"? Is POST a reserved word or something? ACAE
2011-04-19 03:47:23

scriptol

Hello Great job, especially for a beginner. Actually, TOP, REF, USER are names created to perform the join (references to the table and not names of the tables as I said above, just before to shut down the computer and leave for home ;) ). Any words could be used instead. There is not problem to use "post" as a table name. Usually table names have a prefix that help to create unique words.