phpBB.com

Posted: **Tue May 14, 2002 11:16 pm**

I'm using a little scripts to get the 5 most recent replied to/started threads to display on my homepage.

I show the topic title as a link to the thread, the user who started the thread, the number of replies, & i'm trying to get the last poster of that thread but don't know how?

here's the code I have so far:

Code: Select all

$resultbb = mysql_query("SELECT topic_id, topic_title, topic_replies, topic_last_post_id, username
FROM phpbb_topics
LEFT JOIN phpbb_users ON topic_poster = user_id
LEFT JOIN phpbb_forums ON phpbb_forums.forum_id = phpbb_topics.forum_id
WHERE auth_view=0
ORDER BY 'topic_last_post_id' DESC 
LIMIT 5");

$countit = 0;
while ($rowinfo = mysql_fetch_array($resultbb))
{
	if($countit == 0){$bcolor = "#222222";} else {$bcolor = "";}
echo ("<tr bgcolor=\"$bcolor\">
<td><font size=\"1\"><a href='phpBB2/viewtopic.php?t=" . $rowinfo["topic_id"] . "'Target=\"blank\">" . $rowinfo["topic_title"] . "</a></font></td>
<td><font size=\"1\">" . $rowinfo["username"] . "</font></td>
<td><font size=\"1\">" . $rowinfo["user_id"] . "</font></td>
<td align=\"CENTER\"><font size=\"1\">" . $rowinfo["topic_replies"] . "</font></td></tr>");

$countit++;
	if($countit == 2)
	{
	$countit = 0;
	}
}//End while

Here's the site. I need to know what to do to get the last poster to work.

TIA for any help.

Posted: **Wed May 15, 2002 3:47 pm**

awe come on, I helped like 6 threads yesterday.

All I really need is the MySQL query to get everything lined up.

Posted: **Thu May 16, 2002 7:20 pm**

Posted: **Thu May 16, 2002 8:09 pm**

Code: Select all

SELECT poster_id FROM phpbb_posts WHERE topic_id=$topic ORDER BY post_time DESC

$topic must be the topic_id of the topic you are looking for the last poster of.

This will return the all of the posts in that topic, but the first row returned will be the most recent one. the value returned is "poster_id" (as you can see) so you will need to use that to get the poster's username from the user table. Use a join to reduce query count if you like.

Posted: **Thu May 16, 2002 8:32 pm**

Code: Select all

define('IN_PHPBB', true);
$phpbb_root_path = './'; // change to your path to phpBB2 if you run this PHP-script not from forum-directory
include($phpbb_root_path . 'extension.inc');
include($phpbb_root_path . 'common.'.$phpEx);


$sql="SELECT a.topic_id,a.topic_title, d.forum_id, d.forum_name, a.topic_replies, b.user_id as starter_id, b.username as starter, b1.user_id as last_poster_id, b1.username as last_poster, a.topic_last_post_id, c.post_time 
FROM ".TOPICS_TABLE." a, ".USERS_TABLE." b, ".USERS_TABLE." b1, ". POSTS_TABLE." c, ".FORUMS_TABLE." d  
WHERE  a.topic_last_post_id = c.post_id
and b.user_id=a.topic_poster 
and b1.user_id=c.poster_id 
and d.forum_id=a.forum_id 
and d.auth_view=0
ORDER BY topic_last_post_id DESC 
LIMIT 5";

if ( !($result = $db->sql_query($sql)) )
{
   message_die(GENERAL_ERROR, 'Could not obtain information');
}

$total_topics=0; 
while( $row = $db->sql_fetchrow($result) )
{
	$topic[] = $row;
	$total_topics++;
}
$db->sql_freeresult($result);

for($i = 0; $i < $total_topics; $i++)
{
/*
$topic[$i]['topic_id'] = Topic ID
$topic[$i]['topic_title'] = TopicName
$topic[$i]['forum_id'] = Forum ID
$topic[$i]['forum_name'] = Forum Name
$topic[$i]['topic_replies'] = Replies in topic
$topic[$i]['starter_id'] = ID of user who started this topic
$topic[$i]['starter'] = Name of user who started this topic
$topic[$i]['last_poster_id'] = ID of user who wrote the last message in topic
$topic[$i]['last_poster'] = Name of user who wrote the last message in topic
$topic[$i]['topic_last_post_id'] = ID of the last message in topic
create_date($board_config['default_dateformat'], $topic[$i]['post_time'], $board_config['board_timezone']) = Date of the last message in "forum compatible" format

Just print these values. :)
*/

}

Should work with any DB.
N.B. Written from memory and not tested! Check it, please.

Posted: **Fri May 17, 2002 12:21 am**

Thank you romutis, worked like a charm.

phpBB.com

finding the last person to post in a thread?

finding the last person to post in a thread?