finding the last person to post in a thread?

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
whofarted
Registered User
Posts: 387
Joined: Tue Apr 09, 2002 8:19 am
Location: Lost, If found return to St.Louis, MO

finding the last person to post in a thread?

Post by whofarted » 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.
you smell something? :)

whofarted
Registered User
Posts: 387
Joined: Tue Apr 09, 2002 8:19 am
Location: Lost, If found return to St.Louis, MO

Post by whofarted » Wed May 15, 2002 3:47 pm

awe come on, I helped like 6 threads yesterday. :cry:

All I really need is the MySQL query to get everything lined up. :(
you smell something? :)

whofarted
Registered User
Posts: 387
Joined: Tue Apr 09, 2002 8:19 am
Location: Lost, If found return to St.Louis, MO

Post by whofarted » Thu May 16, 2002 7:20 pm

:cry:
you smell something? :)

gameofhit
Registered User
Posts: 986
Joined: Sun Oct 07, 2001 10:29 pm
Location: Michigan Tech
Contact:

Post by gameofhit » 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.

romutis
Registered User
Posts: 142
Joined: Wed Jan 30, 2002 9:35 am
Location: Milan, Italy

Post by romutis » 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.
// Romutis

whofarted
Registered User
Posts: 387
Joined: Tue Apr 09, 2002 8:19 am
Location: Lost, If found return to St.Louis, MO

Post by whofarted » Fri May 17, 2002 12:21 am

Thank you romutis, worked like a charm. :D
you smell something? :)

Locked

Return to “2.0.x Support Forum”