Jump to content

Question for you SQL junkies lol.


xnitro

Recommended Posts

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]
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.