Question for you SQL junkies lol.

[xnitro]

I am just a novice when it comes to scripting for sql. And I can do simple things with ease. But this one has me stumped. Bascially, what I am trying to do here, is have a section on my front page that lists the last 5 posts/replies from the forums. It will also show the author, replier, views and total number of replies along with the topic name and corresponding links. It is also pulling data from more than one table.

Here is the problem. It only needs to show the latest replies. Which it does, but, if one topic has four replies, it will show that same topic 4 times in the list with each reply. It only needs to show the newest one for that topic, not all 4. In other words, showing the latest and then moving to the next topic. I tried to use the DISTINCT function of SELECT so that topics werent repeated, but that doesnt quite work right either. Each post is recorded in the database with a post id, post time, topic_id, etc.

Here is what I have so far and can be viewed on the front page of xnitro.com. Currently on the site, in that center block, you can see when the same topic is listed twice, but only because it happens to have two of the last five replies. I want it to skip or omit duplicate topic_id 's. Hope someone out there understands and can help. I know it can be done possibly with a subquery in the first select string, but im lost with that -
[code]<?php

/************************************************************************/
/* Simple Last 5 Block                                                  */
/* ===========================                                          */
/*                                                                      */
/* Copyright (c) 2006 by KatFishGR                                      */
/* http://www.xnitro.com                                                */
/*                                                                      */
/* This program is free software. You can redistribute it and/or modify */
/* it under the terms of the GNU General Public License as published by */
/* the Free Software Foundation; either version 2 of the License.      */
/*                                                                      */
/* Tested with PHP-Nuke 7.8                                            */
/************************************************************************/

if (eregi("block-Forums.php", $_SERVER['PHP_SELF'])) {
    Header("Location: index.php");
    die();
}



global $prefix, $db, $sitename;
$content = "<br>";
$content .= "<tr><td align=\"center\"><table width=\"100%\" border=\"0\" cellspacing=\"0\" cellpadding=\"0\">";
$content .= "<tr><td bgcolor=\"#000000\" align=\"center\">";
$content .= "<table width=\"100%\" border=\"0\" cellspacing=\"1\" cellpadding=\"3\">";
$content .= "<tr>";
$content .= "<td align=\"center\" background=\"images/last_bg.jpg\"><font class=\"lastthreads\">Topic Title</font></td>";
$content .= "<td align=\"center\" background=\"images/last_bg.jpg\"><font class=\"lastthreads\">Views</font></td>";
$content .= "<td align=\"center\" background=\"images/last_bg.jpg\"><font class=\"lastthreads\">Replies</font></td>";
$content .= "<td align=\"center\" background=\"images/last_bg.jpg\"><font class=\"lastthreads\">Author</font></td>";
$content .= "<td align=\"center\" background=\"images/last_bg.jpg\"><font class=\"lastthreads\">Last Reply</font></td>";
$content .= "</tr>";

$result = $db->sql_query("SELECT topic_id, forum_id, poster_id FROM ".$prefix."_bbposts ORDER BY post_time DESC LIMIT 5");
while ($row = $db->sql_fetchrow($result)) {
    $forum_id = intval($row['forum_id']);
    $topic_id = intval($row['topic_id']);
    $poster_id = intval($row['poster_id']);
    $row1 = $db->sql_fetchrow($db->sql_query("SELECT username FROM ".$prefix."_users WHERE user_id='$poster_id'"));
    $reply_id = $row1['username'];
    $row2 = $db->sql_fetchrow($db->sql_query("SELECT topic_poster, topic_title, topic_views, topic_replies FROM ".$prefix."_bbtopics WHERE topic_id='$topic_id'"));
    $topic_title = $row2['topic_title'];
    $views = intval($row2['topic_views']);
    $author_id = intval($row2['topic_poster']);
    $replies = intval($row2['topic_replies']);
    $row3 = $db->sql_fetchrow($db->sql_query("SELECT username FROM ".$prefix."_users WHERE user_id='$author_id'"));
    $author = $row3['username'];
    $row4 = $db->sql_fetchrow($db->sql_query("SELECT auth_view, auth_read FROM ".$prefix."_bbforums WHERE forum_id='$forum_id'"));
    $auth_view = intval($row4['auth_view']);
    $auth_read = intval($row4['auth_read']);
    if (($auth_view < 2) OR ($auth_read < 2)) {
$content .= "<tr bgcolor=\"#5B5B5B\">";
$content .= "<td>";
$content .= "<img src=\"images/arrow2.gif\" width=\"9\" height=\"9\" border=\"0\" alt=\"\">&nbsp;<a href=\"modules.php?name=Forums&amp;file=viewtopic&amp;t=$topic_id\">$topic_title</a>";
$content .= "</td>";
$content .= "<td align=\"center\">";
$content .= "<font class=\"content\">$views</font>";
$content .= "</td>";
$content .= "<td align=\"center\">";
$content .= "<font class=\"content\">$replies</font>";
$content .= "</td>";
$content .= "<td align=\"center\">";
$content .= "<a href=\"modules.php?name=Forums&file=profile&mode=viewprofile&u=$author_id\">$author</a>";
$content .= "</td>";
$content .= "<td align=\"center\">";
$content .= "<a href=\"modules.php?name=Forums&file=profile&mode=viewprofile&u=$poster_id\">$reply_id</a>";
$content .= "</td>";
$content .= "</tr>";}
}

$content .= "</table>";
$content .= "</td>";
$content .= "</tr>";
$content .= "</table>";
$content .= "<br>";
$content .= "</td></tr>";

?>[/code]

[xnitro]

In other words, if Topic 1 has had 3 replies, Topic 2 has had 2 replies and Topics 3, 4 and 5 have each had 1 reply... the above will show the first 5, 3 from topic 1, and 2 from topic 2. I would rather it be able to show the latest of topic 1 and skip the other 2, the latest of topic 2 and skip the 1 and then show the latest for topics 3, 4 and 5.... just trying to explain it a little clearer lol.

[fenway]

That's because your LIMIT 5 doesn't discriminate between topic_ids.  A subquery could handle this rather easily.