[Block] Recent comments on articles.

[Freddy]

As per request here is a block that will show you the most recent comments made on your articles.  You can use this in a PHP article or block.

You can configure it to limit the number of comments to show.

I made this similar in appearance to the recent posts block code.

Demo here- not much there though as I stopped using comments as they tend to get lost, but with this maybe I will start using them again. Using my own system now, so no demo on my site.

Code: [Select]

// Block code to show recent comments made on articles.
// Freddy888 - with some of Bloc's layout code.

// *** Config ***

// Set the maximum number of comments to show.
$limit = 5;

// *** End Of Config ***


// Now the code..

global $scripturl, $db_prefix;

$request = db_query("
SELECT n.value2, n.value3, n.value4, n.value5, m.subject, m.authorID, m.author, m.shortname, o.memberName
FROM {$db_prefix}tp_articles m
INNER JOIN {$db_prefix}tp_variables n
ON n.value5 = m.id INNER JOIN {$db_prefix}members o
ON n.value3 = o.ID_MEMBER
WHERE n.type = 'article_comment'
ORDER BY n.value4 DESC
LIMIT $limit
", __FILE__, __LINE__);

while ($row = mysql_fetch_assoc($request))
{
// Let's make these variable a little more readable...
$details = array(
'commenter' => '<a href="' . $scripturl . '?action=profile;u=' . $row['value3'] . '">' . $row['memberName'] . '</a>',
'comment' => $row['value2'],
'date' => $row['value4'],
'articleid' => $row['value5'],
'subject' =>  $row['subject'],
'query' => $row['shortname']
);

// Build the output...
echo '
<div class="tborder tp_article_frame">
<div class="catbg tp_subject">
<span class="tp_month">' , $txt['months_short'][date("n", $details['date'])] , '</span>
<span class="tp_thindivider"></span>
<span class="tp_day">' , date("d",$details['date']) , '</span>
<span class="tp_year"> ' , date("Y",$details['date']) , '</span>
' , ($details['query'] != '' ? '<a href="' . $scripturl . '?page=' . $details['query'] . '">' . $details['subject'] . '</a>' : '<a href="' . $scripturl . '?page=' . $details['articleid'] . '">' . $details['subject'] . '</a>') , '
</div>
<div class="tp_articletext">
<div class="tp_details">
<span class="smalltext">' , $details['commenter'] , ' commented :</span>
</div>
<div style="overflow: auto">
' , $details['comment'] , '
</div>
</div>
</div>';
}

mysql_free_result($request);

[dimdom]

Excellent work freddy!

Here is my modified code to make it look better in a side block:

Code: [Select]

// Block code to show recent comments made on articles.
// Freddy888 - with some of Bloc's layout code.

// *** Config ***

// Set the maximum number of comments to show.
$limit = 5;

// *** End Of Config ***


// Now the code..

global $scripturl, $db_prefix;

$request = db_query("
SELECT n.value2, value3, value4, n.value5, m.subject, m.authorID, m.author, m.shortname, o.memberName
FROM {$db_prefix}tp_articles m
INNER JOIN {$db_prefix}tp_variables n
ON n.value5 = m.id INNER JOIN {$db_prefix}members o
ON n.value3 = o.ID_MEMBER
WHERE n.type = 'article_comment'
ORDER BY n.value4 DESC
LIMIT $limit
", __FILE__, __LINE__);

while ($row = mysql_fetch_assoc($request))
{
// Let's make these variable a little more readable...
$details = array(
'commenter' => '<a href="' . $scripturl . '?action=profile;u=' . $row['value3'] . '">' . $row['memberName'] . '</a>',
'comment' => $row['value2'],
'articleid' => $row['value5'],
'subject' =>  $row['subject'],
'query' => $row['shortname']
);

// Build the output...
echo '
<div class="tborder tp_article_frame">
<div class="catbg tp_subject">
<span class="smalltext"><span class="tp_thindivider"></span>
<span class="smalltext">' , ($details['query'] != '' ? '<a href="' . $scripturl . '?page=' . $details['query'] . '">' . $details['subject'] . '</a>' : '<a href="' . $scripturl . '?page=' . $details['articleid'] . '">' .

$details['subject'] . '</a>') , '</span>
</div>
<div class="tp_articletext">
<div class="tp_details">
<span class="smalltext">' , $details['commenter'] , ' σχολίασε:</span>
</div>
<div style="overflow: auto">
<span class="smalltext">' , $details['comment'] , '</span>
</div>
</div>
</div>';
}

mysql_free_result($request);
No dates and smaller letters. Thats all.

[Renegd98]

Nice work Freddy and dimdom...

[mymistake]

This is excellent - just what I was looking for. Thanks a lot!

[Freddy]

Thanks Renegd and well done Dimdom, that's perfect for side blocks

Glad it's okay mymistake, it was a good idea and worth doing.

The trickiest bit was the SQL, but it's good to learn new tricks 

[Rus]

Do you have any interest in updating this?  I'm going to hazard a guess that it doesn't work on either SMF2 or TP1 RC1 because I got errors with it.

Love the idea though.

[IchBin]

Updated code from first post. Post back if there are any errors.

Code: [Select]

// *** Config ***

// Set the maximum number of comments to show.
$limit = 5;

// *** End Of Config ***


// Now the code..

global $scripturl, $db_prefix, $smcFunc, $txt;

$request = $smcFunc['db_query']('', '
SELECT n.value2, n.value3, n.value4, n.value5, m.subject, m.author_id, m.author, m.shortname, o.member_name
FROM {db_prefix}tp_articles m
INNER JOIN {db_prefix}tp_variables n ON n.value5 = m.id
INNER JOIN {db_prefix}members o ON n.value3 = o.id_member
WHERE n.type = {string:type}
ORDER BY n.value4 DESC
LIMIT {int:limit}',
array('type' => 'article_comment', 'limit' => $limit)
);

while ($row = $smcFunc['db_fetch_assoc']($request))
{
// Let's make these variable a little more readable...
$details = array(
'commenter' => '<a href="' . $scripturl . '?action=profile;u=' . $row['value3'] . '">' . $row['member_name'] . '</a>',
'comment' => $row['value2'],
'date' => $row['value4'],
'articleid' => $row['value5'],
'subject' =>  $row['subject'],
'query' => $row['shortname']
);

// Build the output...
echo '
<div class="tborder tp_article_frame">
<div class="catbg tp_subject">
<span class="tp_month">' , $txt['months_short'][date("n", $details['date'])] , '</span>
<span class="tp_thindivider"></span>
<span class="tp_day">' , date("d",$details['date']) , '</span>
<span class="tp_year"> ' , date("Y",$details['date']) , '</span>
' , ($details['query'] != '' ? '<a href="' . $scripturl . '?page=' . $details['query'] . '">' . $details['subject'] . '</a>' : '<a href="' . $scripturl . '?page=' . $details['articleid'] . '">' . $details['subject'] . '</a>') , '
</div>
<div class="tp_articletext">
<div class="tp_details">
<span class="smalltext">' , $details['commenter'] , ' commented :</span>
</div>
<div style="overflow: auto">
' , $details['comment'] , '
</div>
</div>
</div>';
}

$smcFunc['db_free_result']($request);

[Freddy]

Well done IchBin.  I had this on my desktop to do but for most of the day I have had really bad eye strain or something.  I could barely open my left eye.  Feeling a lot better now though touch wood.

[Rus]

Worked great.  Thank you!

[MrCare]

How we can limit comment character?