Only comments, not the firstposts

[Angelotus]

Hi,


I use this code to fetch the most recent posts. Can somebody help me modify it so that I only get the latest comments on topics, without the first post of the topics?


Thanks in advance:

Code Select Expand



function template_recentComments($num_recent = 8, $exclude_boards = null, $output_method = 'echo', $include_boards = null, $member_id = null)
{
   global $context, $settings, $scripturl, $txt, $db_prefix, $ID_MEMBER;
   global $user_info, $modSettings, $func;


   if ($exclude_boards === null && !empty($modSettings['recycle_enable']) && $modSettings['recycle_board'] > 0)
      $exclude_boards = array($modSettings['recycle_board']);
   else
      $exclude_boards = empty($exclude_boards) ? array() : $exclude_boards;


   $stable_icons = array('xx', 'thumbup', 'thumbdown', 'exclamation', 'question', 'lamp', 'smiley', 'angry', 'cheesy', 'grin', 'sad', 'wink', 'moved', 'recycled', 'wireless');
   $icon_sources = array();
   foreach ($stable_icons as $icon)
      $icon_sources[$icon] = 'images_url';


   $extravalues = '';
   if ($output_method == 'extended') {
      // get all the extra information about topics, so we can send them to Recent.template.php later
      $extravalues = "ms.subject AS firstSubject, ms.posterTime AS firstPosterTime, ms.ID_TOPIC, t.ID_BOARD, b.name AS bname,
      t.numReplies, t.numViews, ms.ID_MEMBER AS ID_FIRST_MEMBER, m.ID_MEMBER AS ID_LAST_MEMBER,
      m.posterTime AS lastPosterTime, ms.posterName AS firstPosterName,
      m.posterName AS lastPosterName, m.subject AS lastSubject,
      m.icon AS lastIcon, ms.icon AS firstIcon, t.ID_POLL, t.isSticky, t.locked, m.modifiedTime AS lastModifiedTime,
      " . (!$user_info['is_guest'] ? "
      IFNULL(lt.ID_MSG, IFNULL(lmr.ID_MSG, -1)) + 1 AS new_from," : '') . " LEFT(m.body, 384) AS lastBody, LEFT(ms.body, 384) AS firstBody,
      m.smileysEnabled AS lastSmileys, ms.smileysEnabled AS firstSmileys, t.ID_FIRST_MSG, t.ID_LAST_MSG, ";
   }
   
   // Find all the posts in distinct topics.  Newer ones will have higher IDs.
   $request = db_query("
      SELECT
{$extravalues}
         m.posterTime, ms.subject, m.ID_TOPIC, m.ID_MEMBER, m.ID_MSG, b.ID_BOARD, b.name AS bName,
         IFNULL(mem.realName, m.posterName) AS posterName, " . ($user_info['is_guest'] ? '1 AS isRead, 0 AS new_from' : '
         IFNULL(lt.ID_MSG, IFNULL(lmr.ID_MSG, 0)) >= m.ID_MSG_MODIFIED AS isRead,
         IFNULL(lt.ID_MSG, IFNULL(lmr.ID_MSG, -1)) + 1 AS new_from') . ", LEFT(m.body, 384) AS body, m.smileysEnabled, m.icon
      FROM ({$db_prefix}messages AS m, {$db_prefix}topics AS t, {$db_prefix}boards AS b, {$db_prefix}messages AS ms)
         LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)" . (!$user_info['is_guest'] ? "
         LEFT JOIN {$db_prefix}log_topics AS lt ON (lt.ID_TOPIC = t.ID_TOPIC AND lt.ID_MEMBER = $ID_MEMBER)
         LEFT JOIN {$db_prefix}log_mark_read AS lmr ON (lmr.ID_BOARD = b.ID_BOARD AND lmr.ID_MEMBER = $ID_MEMBER)" : '') . "
      WHERE t.ID_LAST_MSG >= " . ($modSettings['maxMsgID'] - 35 * min($num_recent, 5)) . "
         AND t.ID_LAST_MSG = m.ID_MSG
         AND b.ID_BOARD = t.ID_BOARD" . (empty($exclude_boards) ? '' : "
         AND b.ID_BOARD NOT IN (" . implode(', ', $exclude_boards) . ")") . (!empty($include_boards) ? "
         AND b.ID_BOARD IN (" . implode(', ', $include_boards) . ")"  : '') . ($output_method == 'extended' && isset($_REQUEST['limit']) && is_numeric($_REQUEST['limit']) ? "
         AND m.posterTime > " . (time() - ($_REQUEST['limit']*24*60*60)) : '') . (!empty($member_id) ? "   
         AND m.ID_MEMBER = {$member_id}" : '') .   "
         AND $user_info[query_see_board]
         AND ms.ID_MSG = t.ID_FIRST_MSG
      ORDER BY " . ($output_method == 'extended' && isset($_REQUEST['sort']) ? (
         $_REQUEST['sort'] . ($context['ascending'] ? '' : ' DESC')) : ("t.ID_LAST_MSG DESC")) . "
      LIMIT " . (($output_method == 'extended' && isset($_REQUEST['start']) && isset($_REQUEST['action']) && $_REQUEST['action'] == 'recenttopics' && is_numeric($_REQUEST['start'])) ? ($_REQUEST['start'].', ') : ('')) . $num_recent, __FILE__, __LINE__);
   $posts = array();
   while ($row = mysql_fetch_assoc($request))
   {
      // this puts the extended topic information into $context['topics']. it's copied from Recent.php and can be used by template_unread() in Recent.template.php
      if ($output_method == 'extended') {
         //set these variables so there will be no undefined indexes
         $context['querystring_board_limits'] = '';
         $context['sort_by'] = '';


         if ($row['ID_POLL'] > 0 && $modSettings['pollMode'] == '0')
            continue;
   
         $topic_ids[] = $row['ID_TOPIC'];
   
         // Clip the strings first because censoring is slow :/. (for some reason?)
         $row['firstBody'] = strip_tags(strtr(parse_bbc($row['firstBody'], $row['firstSmileys'], $row['ID_FIRST_MSG']), array('<br />' => '
')));
         if ($func['strlen']($row['firstBody']) > 128)
            $row['firstBody'] = $func['substr']($row['firstBody'], 0, 128) . '...';
         $row['lastBody'] = strip_tags(strtr(parse_bbc($row['lastBody'], $row['lastSmileys'], $row['ID_LAST_MSG']), array('<br />' => '
')));
         if ($func['strlen']($row['lastBody']) > 128)
            $row['lastBody'] = $func['substr']($row['lastBody'], 0, 128) . '...';
   
         // Do a bit of censoring...
         censorText($row['firstSubject']);
         censorText($row['firstBody']);
   
         // But don't do it twice, it can be a slow ordeal!
         if ($row['ID_FIRST_MSG'] == $row['ID_LAST_MSG'])
         {
            $row['lastSubject'] = $row['firstSubject'];
            $row['lastBody'] = $row['firstBody'];
         }
         else
         {
            censorText($row['lastSubject']);
            censorText($row['lastBody']);
         }
   
         // Decide how many pages the topic should have.
         $topic_length = $row['numReplies'] + 1;
         if ($topic_length > $modSettings['defaultMaxMessages'])
         {
            $tmppages = array();
            $tmpa = 1;
            for ($tmpb = 0; $tmpb < $topic_length; $tmpb += $modSettings['defaultMaxMessages'])
            {
               $tmppages[] = '<a href="' . $scripturl . '?topic=' . $row['ID_TOPIC'] . '.' . $tmpb . ';topicseen">' . $tmpa . '</a>';
               $tmpa++;
            }
            // Show links to all the pages?
            if (count($tmppages) <= 5)
               $pages = '« ' . implode(' ', $tmppages);
            // Or skip a few?
            else
               $pages = '« ' . $tmppages[0] . ' ' . $tmppages[1] . ' ... ' . $tmppages[count($tmppages) - 2] . ' ' . $tmppages[count($tmppages) - 1];
   
            if (!empty($modSettings['enableAllMessages']) && $topic_length < $modSettings['enableAllMessages'])
               $pages .= '  <a href="' . $scripturl . '?topic=' . $row['ID_TOPIC'] . '.0;all">' . $txt[190] . '</a>';
            $pages .= ' »';
         }
         else
            $pages = '';
   
         // We need to check the topic icons exist... you can never be too sure!
         if (empty($modSettings['messageIconChecks_disable']))
         {
            // First icon first... as you'd expect.
            if (!isset($context['icon_sources'][$row['firstIcon']]))
               $context['icon_sources'][$row['firstIcon']] = file_exists($settings['theme_dir'] . '/images/post/' . $row['firstIcon'] . '.gif') ? 'images_url' : 'default_images_url';
            // Last icon... last... duh.
            if (!isset($context['icon_sources'][$row['lastIcon']]))
               $context['icon_sources'][$row['lastIcon']] = file_exists($settings['theme_dir'] . '/images/post/' . $row['lastIcon'] . '.gif') ? 'images_url' : 'default_images_url';
         }
   
         // And build the array.
         $context['topics'][$row['ID_TOPIC']] = array(
            'id' => $row['ID_TOPIC'],
            'first_post' => array(
               'id' => $row['ID_FIRST_MSG'],
               'member' => array(
                  'name' => $row['firstPosterName'],
                  'id' => $row['ID_FIRST_MEMBER'],
                  'href' => $scripturl . '?action=profile;u=' . $row['ID_FIRST_MEMBER'],
                  'link' => !empty($row['ID_FIRST_MEMBER']) ? '<a href="' . $scripturl . '?action=profile;u=' . $row['ID_FIRST_MEMBER'] . '" title="' . $txt[92] . ' ' . $row['firstPosterName'] . '">' . $row['firstPosterName'] . '</a>' : $row['firstPosterName']
               ),
               'time' => timeformat($row['firstPosterTime']),
               'timestamp' => forum_time(true, $row['firstPosterTime']),
               'subject' => $row['firstSubject'],
               'preview' => $row['firstBody'],
               'icon' => $row['firstIcon'],
               'icon_url' => $settings[$context['icon_sources'][$row['firstIcon']]] . '/post/' . $row['firstIcon'] . '.gif',
               'href' => $scripturl . '?topic=' . $row['ID_TOPIC'] . '.0;topicseen',
               'link' => '<a href="' . $scripturl . '?topic=' . $row['ID_TOPIC'] . '.0;topicseen">' . $row['firstSubject'] . '</a>'
            ),
            'last_post' => array(
               'id' => $row['ID_LAST_MSG'],
               'member' => array(
                  'name' => $row['lastPosterName'],
                  'id' => $row['ID_LAST_MEMBER'],
                  'href' => $scripturl . '?action=profile;u=' . $row['ID_LAST_MEMBER'],
                  'link' => !empty($row['ID_LAST_MEMBER']) ? '<a href="' . $scripturl . '?action=profile;u=' . $row['ID_LAST_MEMBER'] . '">' . $row['lastPosterName'] . '</a>' : $row['lastPosterName']
               ),
               'time' => timeformat($row['lastPosterTime']),
               'timestamp' => forum_time(true, $row['lastPosterTime']),
               'subject' => $row['lastSubject'],
               'preview' => $row['lastBody'],
               'icon' => $row['lastIcon'],
               'icon_url' => $settings[$context['icon_sources'][$row['lastIcon']]] . '/post/' . $row['lastIcon'] . '.gif',
               'href' => $scripturl . '?topic=' . $row['ID_TOPIC'] . ($row['numReplies'] == 0 ? '.0' : '.msg' . $row['ID_LAST_MSG']) . ';topicseen#msg' . $row['ID_LAST_MSG'],
               'link' => '<a href="' . $scripturl . '?topic=' . $row['ID_TOPIC'] . ($row['numReplies'] == 0 ? '.0' : '.msg' . $row['ID_LAST_MSG']) . ';topicseen#msg' . $row['ID_LAST_MSG'] . '">' . $row['lastSubject'] . '</a>'
            ),
            'new_from' => $row['new_from'],
            'new_href' => $scripturl . '?topic=' . $row['ID_TOPIC'] . '.msg' . $row['new_from'] . ';topicseen#new',
            'href' => $scripturl . '?topic=' . $row['ID_TOPIC'] . ($row['numReplies'] == 0 ? '.0' : '.msg' . $row['new_from']) . ';topicseen' . ($row['numReplies'] == 0 ? '' : 'new'),
            'link' => '<a href="' . $scripturl . '?topic=' . $row['ID_TOPIC'] . ($row['numReplies'] == 0 ? '.0' : '.msg' . $row['new_from']) . ';topicseen#msg' . $row['new_from'] . '">' . $row['firstSubject'] . '</a>',
            'is_read' => $row['isRead'],
            'is_sticky' => !empty($modSettings['enableStickyTopics']) && !empty($row['isSticky']),
            'is_locked' => !empty($row['locked']),
            'is_poll' => $modSettings['pollMode'] == '1' && $row['ID_POLL'] > 0,
            'is_hot' => $row['numReplies'] >= $modSettings['hotTopicPosts'],
            'is_very_hot' => $row['numReplies'] >= $modSettings['hotTopicVeryPosts'],
            'is_posted_in' => false,
            'icon' => $row['firstIcon'],
            'icon_url' => $settings[$context['icon_sources'][$row['firstIcon']]] . '/post/' . $row['firstIcon'] . '.gif',
            'subject' => $row['firstSubject'],
            'pages' => $pages,
            'replies' => $row['numReplies'],
            'views' => $row['numViews'],
            'board' => array(
               'id' => $row['ID_BOARD'],
               'name' => $row['bname'],
               'href' => $scripturl . '?board=' . $row['ID_BOARD'] . '.0',
               'link' => '<a href="' . $scripturl . '?board=' . $row['ID_BOARD'] . '.0">' . $row['bname'] . '</a>'
            )
         );
   
         determineTopicClass($context['topics'][$row['ID_TOPIC']]);
      }
      else {
         $row['body'] = strip_tags(strtr(parse_bbc($row['body'], $row['smileysEnabled'], $row['ID_MSG']), array('<br />' => '
')));
         if ($func['strlen']($row['body']) > 128)
            $row['body'] = $func['substr']($row['body'], 0, 128) . '...';
   
         // Censor the subject.
         censorText($row['subject']);
         censorText($row['body']);
   
         if (empty($modSettings['messageIconChecks_disable']) && !isset($icon_sources[$row['icon']]))
            $icon_sources[$row['icon']] = file_exists($settings['theme_dir'] . '/images/post/' . $row['icon'] . '.gif') ? 'images_url' : 'default_images_url';
   
         // Build the array.
         $posts[] = array(
            'board' => array(
               'id' => $row['ID_BOARD'],
               'name' => $row['bName'],
               'href' => $scripturl . '?board=' . $row['ID_BOARD'] . '.0',
               'link' => '<a href="' . $scripturl . '?board=' . $row['ID_BOARD'] . '.0">' . $row['bName'] . '</a>'
            ),
            'topic' => $row['ID_TOPIC'],
            'poster' => array(
               'id' => $row['ID_MEMBER'],
               'name' => $row['posterName'],
               'href' => empty($row['ID_MEMBER']) ? '' : $scripturl . '?action=profile;u=' . $row['ID_MEMBER'],
               'link' => empty($row['ID_MEMBER']) ? $row['posterName'] : '<a href="' . $scripturl . '?action=profile;u=' . $row['ID_MEMBER'] . '">' . $row['posterName'] . '</a>'
            ),
            'subject' => $row['subject'],
            'short_subject' => shorten_subject($row['subject'], 35),
            'preview' => $row['body'],
            'time' => timeformat($row['posterTime']),
            'timestamp' => forum_time(true, $row['posterTime']),
            'raw_timestamp' => $row['posterTime'],
            'href' => $scripturl . '?topic=' . $row['ID_TOPIC'] . '.msg' . $row['ID_MSG'] . ';topicseen#new',
            'link' => '<a href="' . $scripturl . '?topic=' . $row['ID_TOPIC'] . '.msg' . $row['ID_MSG'] . '#new">' . $row['subject'] . '</a>',
            'new' => !empty($row['isRead']),
            'new_from' => $row['new_from'],
            'icon' => '<img src="' . $settings[$icon_sources[$row['icon']]] . '/post/' . $row['icon'] . '.gif" align="middle" alt="' . $row['icon'] . '" border="0" />',
         );
      }
   }
   mysql_free_result($request);


   // Just return it.
   if ($output_method != 'echo' || empty($posts))
      return $posts;


   echo '<div style="border-top: 1px dashed #aaa; margin-top: 5px; padding: 5px;">
     Laatste reacties op het nieuws:
         <>
<div class="menuleftbar" style="background: white;">
    <ul>';


   foreach ($posts as $post)
              {
      echo '
         <li>
<a class="normal" href="', $post['href'], '" title="Laatste bericht van ', $post['poster']['name'],  '">
<table class="collapse">
<tr>
<td valign="top" align="left" class="smalltext">
', $post['icon'], ' ', $post['short_subject'], '
</td>
<td align="right" valign="top" nowrap="nowrap" class="smalltext">
door ', $post['poster']['name'], '
</td>
</tr>
</table>
</a>
</li>
';
}
   echo '</ul>
<>
';
}




[Angelotus]

Anyone who can help me out with this?

[Kays]

Try this. Find:

Code Select Expand


   foreach ($posts as $post)
              {


and change it to:

Code Select Expand


   foreach ($posts as $key => $post)
              {
                     if ($key == 0)
                        continue;


[Angelotus]

Thanks for your comment, Unfort. it doesn't work

[Angelotus]

Dear Kays, have any other ideas what might work to do this? Thanks in advance!!

[Kays]

Ok, I think I misunderstood your request.

What do you want it to show? The text of the last post also?

Where did you get that code from?

Move this to SMF Coding Discussion.

[Angelotus]

What I want is:

- a list of latest comments of topics (so not the firstpost of a topic)
- with an excerpt of the comment itself, so something like

>>"Hi all, this is a great topic, th..." by Angelotus on 14/01/2010 14:55

[Kays]

Where did you get that code from because it's strangely written and not behaving the way I expect it to.

The best I can do is find the following:

Code Select Expand


', $post['icon'], ' ', $post['short_subject'], '


and change it to

Code Select Expand


', $post['icon'], ' ', $post['preview'], '


[Angelotus]

Hi, that is the RecentTopics ssi code that I use for that.
Have you got an other (better) solutions to do so?

[Kays]

That's more than ssi_RecentTopics. It looks like another function was added to it making it hard to follow.

So you just want to show a list of the most recent posts?

[Angelotus]

Yes, but EXCLUDING the first post of a topic... and only of board X.

[live627]

Code (Find) Select Expand

AND ms.ID_MSG = t.ID_FIRST_MSG

Code (Add After) Select Expand

AND t.ID_MSG != m.ID_MSG

Another shot in the dark but has a remote possibility of working

[Angelotus]

Thanks for your comment.
Tried that and got this error:

Code Select Expand


Unknown column 't.ID_MSG' in 'where clause'
File: /storage/mijndomein/users/073949/public/sites/kv.nessekrekers.nl/Sources/Load.php(1747) : eval()'d code
Line: 1489


[Kays]

Hmm, too bad.

Do you want to show all of the posts but the first one in all topics on that board. Or just the last repliy for each topic?

[Angelotus]

only the last reply of each topic from board X

[Kays]

I think that can be done with some minor modifcations to ssi_RecentTopics.

How do you want it to be displayed?

{Shortened message} In {Topic} on {Date}

[Angelotus]

{Shortened message} on {date} by {Member}

Thanks Kays!

[Kays]

Here you go. It was a bit easier to modify ssi_BoardNews and have it show the last post instead.

I've renamed it and you can set all of the parameters in the first line.

Code Select Expand


// Show the last relies in a selected board
function ssi_recentComments($board = 1, $limit = 25, $length = 65, $output_method = 'echo')
{
global $scripturl, $db_prefix, $txt, $settings, $modSettings, $context;
global $func;

loadLanguage('Stats');

// Must be integers....
if ($limit === null)
$limit = isset($_GET['limit']) ? (int) $_GET['limit'] : 5;
else
$limit = (int) $limit;

if ($board !== null)
$board = (int) $board;
elseif (isset($_GET['board']))
$board = (int) $_GET['board'];

if ($length === null)
$length = isset($_GET['length']) ? (int) $_GET['length'] : 0;
else
$length = (int) $length;

$limit = max(0, $limit);

// Make sure guests can see this board.
$request = db_query("
SELECT ID_BOARD
FROM {$db_prefix}boards
WHERE " . ($board === null ? '' : "ID_BOARD = $board
AND ") . "FIND_IN_SET(-1, memberGroups)
LIMIT 1", __FILE__, __LINE__);
if (mysql_num_rows($request) == 0)
{
if ($output_method == 'echo')
die($txt['smf_news_error2']);
else
return array();
}
list ($board) = mysql_fetch_row($request);
mysql_free_result($request);

// Find the post ids.
$request = db_query("
SELECT ID_LAST_MSG
FROM {$db_prefix}topics
WHERE ID_BOARD = $board
AND ID_LAST_MSG != ID_FIRST_MSG
ORDER BY ID_LAST_MSG DESC
LIMIT $limit", __FILE__, __LINE__);
$posts = array();
while ($row = mysql_fetch_assoc($request))
$posts[] = $row['ID_LAST_MSG'];
mysql_free_result($request);

if (empty($posts))
return array();

// Find the posts.
$request = db_query("
SELECT
m.modifiedTime, m.icon, m.subject, m.body, IFNULL(mem.realName, m.posterName) AS posterName, m.posterTime,
t.numReplies, t.ID_TOPIC, m.ID_MEMBER, m.smileysEnabled, m.ID_MSG, t.locked
FROM ({$db_prefix}topics AS t, {$db_prefix}messages AS m)
LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)
WHERE t.ID_LAST_MSG IN (" . implode(', ', $posts) . ")
AND m.ID_MSG = t.ID_LAST_MSG
ORDER BY t.ID_FIRST_MSG DESC
LIMIT " . count($posts), __FILE__, __LINE__);

$return = array();
while ($row = mysql_fetch_assoc($request))
{
$row['body'] = strip_tags(parse_bbc($row['body'], false));
$row['body'] = shorten_subject($row['body'], $length);
censorText($row['subject']);
censorText($row['body']);

$return[] = array(
'id' => $row['ID_TOPIC'],
'message_id' => $row['ID_MSG'],
'icon' => '<img src="' . $settings[$icon_sources[$row['icon']]] . '/post/' . $row['icon'] . '.gif" align="middle" alt="' . $row['icon'] . '" border="0" />',
'subject' => $row['subject'],
'time' => timeformat($row['posterTime']),
'timestamp' => forum_time(true, $row['posterTime']),
'body' => $row['body'],
'href' => $scripturl . '?topic=' . $row['ID_TOPIC'] . '.0',
'link' => '<a href="' . $scripturl . '?topic=' . $row['ID_TOPIC'] . '.0">' . $row['numReplies'] . ' ' . ($row['numReplies'] == 1 ? $txt['smf_news_1'] : $txt['smf_news_2']) . '</a>',
'poster' => array(
'id' => $row['ID_MEMBER'],
'name' => $row['posterName'],
'href' => !empty($row['ID_MEMBER']) ? $scripturl . '?action=profile;u=' . $row['ID_MEMBER'] : '',
'link' => !empty($row['ID_MEMBER']) ? '<a href="' . $scripturl . '?action=profile;u=' . $row['ID_MEMBER'] . '">' . $row['posterName'] . '</a>' : $row['posterName']
),
'locked' => !empty($row['locked']),
'last_edit' => empty($row['modifiedTime']) ? '' : timeformat($row['modifiedTime'], true),
'is_last' => false
);
}
mysql_free_result($request);

if (empty($return))
return $return;

$return[count($return) - 1]['is_last'] = true;

if ($output_method != 'echo')
return $return;

foreach ($return as $news)
{
echo '
', $news['body'], ' on ', $news['time'], ' By ', $news['poster']['link'];

if (!$news['is_last'])
echo '
<br />';
}
}


[live627]

Code (Find) Select Expand

AND ms.ID_MSG = t.ID_FIRST_MSG

Code (Add After) Select Expand

AND t.ID_FIRST_MSG != m.ID_MSG

[Angelotus]

Thanks Kays,

That is almost what I need! It still just only gives the most recent topics that are commented, Not the latest comments itself.
What I mean is, that if there are more comments to one topic that are shown to. Similar to what for example wordpress does with recent comments.

[Kays]

I thought you only wanted to show  the last.

Replace the last two queries with the following. That should do the trick.

Code Select Expand


// Find the post ids.
$request = db_query("
SELECT m.ID_MSG
FROM {$db_prefix}messages AS m
LEFT JOIN {$db_prefix}topics AS t ON (t.ID_TOPIC = m.ID_TOPIC)
WHERE m.ID_BOARD = $board
AND m.ID_MSG != t.ID_FIRST_MSG
ORDER BY m.ID_MSG DESC
LIMIT $limit", __FILE__, __LINE__);
$posts = array();
while ($row = mysql_fetch_assoc($request))
$posts[] = $row['ID_MSG'];
mysql_free_result($request);

if (empty($posts))
return array();

// Find the posts.
$request = db_query("
SELECT
m.modifiedTime, m.icon, m.subject, m.body, IFNULL(mem.realName, m.posterName) AS posterName, m.posterTime,
t.numReplies, t.ID_TOPIC, m.ID_MEMBER, m.smileysEnabled, m.ID_MSG, t.locked
FROM ({$db_prefix}messages AS m)
LEFT JOIN {$db_prefix}topics AS t ON (t.ID_TOPIC = m.ID_TOPIC)
LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)
WHERE m.ID_MSG IN (" . implode(', ', $posts) . ")
ORDER BY m.ID_MSG DESC
LIMIT " . count($posts), __FILE__, __LINE__);


@ live627
That's not necessary as that filtering is done in the previous query.

[Angelotus]

That did the trick! Kays thanks so much for your time and help!!!!
You can see it in action: http://www.nessekrekers.nl/ left bar, beneath the news items

[Kays]

You're welcome.

That does look better with the formatting and without the date.

I'll mark this as solved then.

[Soul.assassin]

Hi, I was searching for this request for a long time, but unfortunatly, this code does'nt work with my SMF 2.0 RC1.2... I think I have to change a little the request but don't manage to make it work... Is this code for SMF 1 or SMF 2?

Code Select Expand

Fatal error: Call to undefined function db_query() in /home/

Could someone help me to make this code working on my SMF 2.0, please? :/

[live627]

Mod Authors: SMF 2.0 Database Functions

$smcFunc['db_query']()

[Angelotus]

Hi there,

I have tried to translate this into SMF 2.0, but I am totaly lost.
Is there someone who would be so kind to translate this code into SMF 2.0??? That would be so great!

Code Select Expand


function template_recentComments($board = 7, $limit = 5, $length = 60, $output_method = 'echo')
{
   global $scripturl, $db_prefix, $txt, $settings, $modSettings, $context;
   global $func;

   loadLanguage('Stats');

   // Must be integers....
   if ($limit === null)
      $limit = isset($_GET['limit']) ? (int) $_GET['limit'] : 5;
   else
      $limit = (int) $limit;

   if ($board !== null)
      $board = (int) $board;
   elseif (isset($_GET['board']))
      $board = (int) $_GET['board'];

   if ($length === null)
      $length = isset($_GET['length']) ? (int) $_GET['length'] : 0;
   else
      $length = (int) $length;

   $limit = max(0, $limit);

   // Make sure guests can see this board.
   $request = db_query("
      SELECT ID_BOARD
      FROM {$db_prefix}boards
      WHERE " . ($board === null ? '' : "ID_BOARD = $board
         AND ") . "FIND_IN_SET(-1, memberGroups)
      LIMIT 1", __FILE__, __LINE__);
   if (mysql_num_rows($request) == 0)
   {
      if ($output_method == 'echo')
         die($txt['smf_news_error2']);
      else
         return array();
   }
   list ($board) = mysql_fetch_row($request);
   mysql_free_result($request);

   // Find the post ids.
   $request = db_query("
      SELECT m.ID_MSG
      FROM {$db_prefix}messages AS m
         LEFT JOIN {$db_prefix}topics AS t ON (t.ID_TOPIC = m.ID_TOPIC)
      WHERE m.ID_BOARD = $board
         AND m.ID_MSG != t.ID_FIRST_MSG
      ORDER BY m.ID_MSG DESC
      LIMIT $limit", __FILE__, __LINE__);
   $posts = array();
   while ($row = mysql_fetch_assoc($request))
      $posts[] = $row['ID_MSG'];
   mysql_free_result($request);

   if (empty($posts))
      return array();

   // Find the posts.
   $request = db_query("
      SELECT
         m.modifiedTime, m.icon, m.subject, m.body, IFNULL(mem.realName, m.posterName) AS posterName, m.posterTime,
         t.numReplies, t.ID_TOPIC, m.ID_MEMBER, m.smileysEnabled, m.ID_MSG, t.locked
      FROM ({$db_prefix}messages AS m)
         LEFT JOIN {$db_prefix}topics AS t ON (t.ID_TOPIC = m.ID_TOPIC)
         LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER = m.ID_MEMBER)
      WHERE m.ID_MSG IN (" . implode(', ', $posts) . ")
      ORDER BY m.ID_MSG DESC
      LIMIT " . count($posts), __FILE__, __LINE__);

   $return = array();
   while ($row = mysql_fetch_assoc($request))
   {
      $row['body'] = strip_tags(parse_bbc($row['body'], false));
      $row['body'] = shorten_subject($row['body'], $length);
      censorText($row['subject']);
      censorText($row['body']);

      $return[] = array(
         'id' => $row['ID_TOPIC'],
         'message_id' => $row['ID_MSG'],
         'icon' => '<img src="' . $settings[$icon_sources[$row['icon']]] . '/post/' . $row['icon'] . '.gif" align="middle" alt="' . $row['icon'] . '" border="0" />',
         'subject' => $row['subject'],
         'time' => timeformat($row['posterTime']),
         'timestamp' => forum_time(true, $row['posterTime']),
         'body' => $row['body'],
         'href' => $scripturl . '?topic=' . $row['ID_TOPIC'] . '.msg' . $row['ID_MSG'] . '#msg' . $row['ID_MSG'] . '',
         'link' => '<a href="' . $scripturl . '?topic=' . $row['ID_TOPIC'] . '.0">' . $row['numReplies'] . ' ' . ($row['numReplies'] == 1 ? $txt['smf_news_1'] : $txt['smf_news_2']) . '</a>',
         'poster' => array(
            'id' => $row['ID_MEMBER'],
            'name' => $row['posterName'],
            'href' => !empty($row['ID_MEMBER']) ? $scripturl . '?action=profile;u=' . $row['ID_MEMBER'] : '',
            'link' => !empty($row['ID_MEMBER']) ? '<a href="' . $scripturl . '?action=profile;u=' . $row['ID_MEMBER'] . '">' . $row['posterName'] . '</a>' : $row['posterName']
         ),
         'locked' => !empty($row['locked']),
         'last_edit' => empty($row['modifiedTime']) ? '' : timeformat($row['modifiedTime'], true),
         'is_last' => false
      );
   }
   mysql_free_result($request);

   if (empty($return))
      return $return;

   $return[count($return) - 1]['is_last'] = true;

   if ($output_method != 'echo')
      return $return;

   echo '<div style="margin: 5px 0px 0px 0px; padding: 5px; border-top: 1px dashed #aaa;">
     <b>Laatste reacties:</b>
         </div>';
   foreach ($return as $news)
   {
      echo '<table class="collapse">
         <tr>
      <td valign="top" align="left" class="smalltext">
         <a href="', $news['href'], '">', $news['body'], '</a> </td>
      <td align="right" valign="top" nowrap="nowrap" class="smalltext">
      door  ', $news['poster']['name'], '
      </td>
      </tr>
      </table>';

      if (!$news['is_last'])
         echo '
         ';
   }
}


[Angelotus]

Anyone who can / is willing to help me out with this plz?