phpBB Mod: View Topic Title of Latest Post

Wednesday, January 04, 2006

This simple phpBB Mod enables you to view the Topic Title of the Latest Post on a forum alongwith the post date/time and poster information on the index page of your phpBB forum. To preview the result of the Mod visit DLC Forums.

To make this modification, you just have to make some changes in the 'index.php' file present in the directory in which you have your phpBB files.


Step 1:

Find the following code which selects the latest post of all the forums:

//
// Define appropriate SQL
//
switch(SQL_LAYER)
{
case 'postgresql':
$sql = "SELECT f.*, p.post_time, p.post_username, u.username, u.user_id
FROM " . FORUMS_TABLE . " f, " . POSTS_TABLE . " p, " . USERS_TABLE . " u
WHERE p.post_id = f.forum_last_post_id
AND u.user_id = p.poster_id
UNION (
SELECT f.*, NULL, NULL, NULL, NULL
FROM " . FORUMS_TABLE . " f
WHERE NOT EXISTS (
SELECT p.post_time
FROM " . POSTS_TABLE . " p
WHERE p.post_id = f.forum_last_post_id
)
)
ORDER BY cat_id, forum_order";
break;

case 'oracle':
$sql = "SELECT f.*, p.post_time, p.post_username, u.username, u.user_id
FROM " . FORUMS_TABLE . " f, " . POSTS_TABLE . " p, " . USERS_TABLE . " u
WHERE p.post_id = f.forum_last_post_id(+)
AND u.user_id = p.poster_id(+)
ORDER BY f.cat_id, f.forum_order";
break;

default:
$sql = "SELECT f.*, p.post_time, p.post_username, u.username, u.user_id
FROM (( " . FORUMS_TABLE . " f
LEFT JOIN " . POSTS_TABLE . " p ON p.post_id = f.forum_last_post_id )
LEFT JOIN " . USERS_TABLE . " u ON u.user_id = p.poster_id )
ORDER BY f.cat_id, f.forum_order";
break;
}


Now you have to add the selection of the field 'topic_title' from the 'TOPIC_TABLE' where 'topic_last_post_id' is equal to the 'forum_last_post_id'. The modified code is shown below:

//
// Define appropriate SQL
//
switch(SQL_LAYER)
{
case 'postgresql':
$sql = "SELECT f.*, p.post_time, p.post_username, u.username, u.user_id, t.topic_title
FROM " . FORUMS_TABLE . " f, " . POSTS_TABLE . " p, " . USERS_TABLE . " u, " . TOPICS_TABLE . " t
WHERE p.post_id = f.forum_last_post_id
AND u.user_id = p.poster_id
AND t.topic_last_post_id = f.forum_last_post_id
UNION (
SELECT f.*, NULL, NULL, NULL, NULL
FROM " . FORUMS_TABLE . " f
WHERE NOT EXISTS (
SELECT p.post_time
FROM " . POSTS_TABLE . " p
WHERE p.post_id = f.forum_last_post_id
)
)
ORDER BY cat_id, forum_order";
break;

case 'oracle':
$sql = "SELECT f.*, p.post_time, p.post_username, u.username, u.user_id, t.topic_title
FROM " . FORUMS_TABLE . " f, " . POSTS_TABLE . " p, " . USERS_TABLE . " u, " . TOPICS_TABLE . " t
WHERE p.post_id = f.forum_last_post_id(+)
AND u.user_id = p.poster_id(+)
AND t.topic_last_post_id = f.forum_last_post_id(+)
ORDER BY f.cat_id, f.forum_order";
break;

default:
$sql = "SELECT f.*, p.post_time, p.post_username, u.username, u.user_id, t.topic_title
FROM ((( " . FORUMS_TABLE . " f
LEFT JOIN " . POSTS_TABLE . " p ON p.post_id = f.forum_last_post_id )
LEFT JOIN " . USERS_TABLE . " u ON u.user_id = p.poster_id )
LEFT JOIN " . TOPICS_TABLE . " t ON t.topic_last_post_id = f.forum_last_post_id )
ORDER BY f.cat_id, f.forum_order";
break;
}



Step 2
:

Make changes in the '$last_post' variable. Find the following code:
$last_post = $last_post_time . '<br />';

$last_post .= ( $forum_data[$j]['user_id'] == ANONYMOUS ) ? ( ($forum_data[$j]['post_username'] != '' ) ? $forum_data[$j]['post_username'] . ' ' : $lang['Guest'] . ' ' ) : '<a href="' . append_sid("profile.$phpEx?mode=viewprofile&" . POST_USERS_URL . '=' . $forum_data[$j]['user_id']) . '">' . $forum_data[$j]['username'] . '</a> ';

$last_post .= '<a href="' . append_sid("viewtopic.$phpEx?" . POST_POST_URL . '=' . $forum_data[$j]['forum_last_post_id']) . '#' . $forum_data[$j]['forum_last_post_id'] . '"><img src="' . $images['icon_latest_reply'] . '" border="0" alt="' . $lang['View_latest_post'] . '" title="' . $lang['View_latest_post'] . '" /></a>';

Now, add $forum_data[$j]['topic_title'] where you would like to display the topic title in the 'Latest Post' column, linking it to the latest post. A modified code would look like:
$last_post = '<div style="font-size:8pt"><img src="images/icon.gif" border="0"> <strong><a href="' . append_sid("viewtopic.$phpEx?" . POST_POST_URL . '=' . $forum_data[$j]['forum_last_post_id']) . '#' . $forum_data[$j]['forum_last_post_id'] . '" title="' . $lang['View_latest_post'] . '"> ' . $forum_data[$j]['topic_title'] . '</strong></a></div>';

$last_post .= '<div style="float:left">by ';
$last_post .= ( $forum_data[$j]['user_id'] == ANONYMOUS ) ? ( ($forum_data[$j]['post_username'] != '' ) ? $forum_data[$j]['post_username'] . ' ' : $lang['Guest'] . ' ' ) : '<a href="' . append_sid("profile.$phpEx?mode=viewprofile&" . POST_USERS_URL . '=' . $forum_data[$j]['user_id']) . '">' . $forum_data[$j]['username'] . '</a></div> ';

$last_post .= '<div align="right">'.$last_post_time;

$last_post .= '<a href="' . append_sid("viewtopic.$phpEx?" . POST_POST_URL . '=' . $forum_data[$j]['forum_last_post_id']) . '#' . $forum_data[$j]['forum_last_post_id'] . '"><img src="' . $images['icon_latest_reply'] . '" border="0" alt="' . $lang['View_latest_post'] . '" title="' . $lang['View_latest_post'] . '" /></a></div>';

You can change the display format of the Latest Post topic, user and date by making appropriate changes in the above code.


NOTE: You may also need to adjust the width of the 'Latest Post' column in the file 'index_body.tpl' located in your template directory. The 'Latest Post' column displays the '$last_post' variable.


This phpBB MODification is developed by me, tested on phpBB2.0.17 and used at DLC Forums which is the forum of the literary society of my college of which I am a member. I am the webmaster/site-administrator of http://www.dlangchaps.org/, website of Literary Society, GTBIT.

DISCLAIMER: I hold no responsibilities/waranties of any sort, what-so-ever for any consequenses resulting from the use of this Mod.


Update: (January 6, 2007)
I have also implemented this modification on GTBIT Forums (forum of my college, transferred from DLC Forums). I have made this modification better by fixing the size of the topic to maximum of 30 characters. Here's the modified code for the '$last_post' variable:

$last_post = '<div style="font-size:10px;text-align:left;width:200px"><img
src="images/icon.gif" border="0"> <strong><a href="'
. append_sid("viewtopic.$phpEx?" . POST_POST_URL . '=' . $forum_data[$j]['forum_last_post_id'])
. '#' . $forum_data[$j]['forum_last_post_id'] . '" title="' . $lang['View_latest_post']
. '"> ' ;

$last_post .= (strlen($forum_data[$j]['topic_title'])<30) ? $forum_data[$j]['topic_title']
: substr($forum_data[$j]['topic_title'], 0, 25) . '...';

$last_post .= '</strong></a></div>';
$last_post .= '<div style="float:left">by ';

$last_post .= ( $forum_data[$j]['user_id'] == ANONYMOUS ) ? ( ($forum_data[$j]['post_username']
!= '' ) ? $forum_data[$j]['post_username'] . ' ' : $lang['Guest'] . ' ' ) :
'<a href="' . append_sid("profile.$phpEx?mode=viewprofile&amp;"
. POST_USERS_URL . '=' . $forum_data[$j]['user_id']) . '">' . $forum_data[$j]['username']
. '</a></div> ';

$last_post .= '<div align="right">'.$last_post_time;

$last_post .= '<a href="' . append_sid("viewtopic.$phpEx?"
. POST_POST_URL . '=' . $forum_data[$j]['forum_last_post_id']) . '#' . $forum_data[$j]['forum_last_post_id'] . '"><img src="' . $images['icon_latest_reply'] . '" border="0"
alt="' . $lang['View_latest_post'] . '" title="' . $lang['View_latest_post']
. '" /></a></div>';

All queries are edited as mentioned above. Also, there's no need for modification of the template files as the width of the Last Post column is defined by the div tag within this code.

0 comments:

Post a Comment