[Block] Recent comments on articles.

[wildenborch]

Oh that's great..... but where do I place this code?

[tino]

Oh that's great..... but where do I place this code?

In the SQL in place of the o.memer_name

[wildenborch]

Hi Tino

You refer to this part?

      SELECT n.value2, n.value3, n.value4, n.value5, m.subject, m.author_id, m.author, m.shortname, o.member_name

and change it into:


      SELECT n.value2, n.value3, n.value4, n.value5, m.subject, m.author_id, m.author, m.shortname, IFNULL(o.member_name, 'Guest') AS member_name
   
That does not seem to work.

[tino]

Code Select Expand


SELECT n.value2, n.value3, n.value4, n.value5, m.subject, m.author_id, m.author, m.shortname, IFNULL(o.member_name, 'Guest') AS member_name
FROM smf_tp_articles m
LEFT OUTER JOIN smf_tp_variables n ON n.value5 = m.id
LEFT OUTER JOIN smf_members o ON n.value3 = o.id_member
WHERE n.type = 'article_comment'
ORDER BY n.value4 DESC


Works for me on my test site.

[wildenborch]

I copied this code into my php block but it does not show any results.

Can you post the complete code block?

[tino]

I only ran it on the MySQL Server, but it changes the member_name to guest as I would expect.

The line with the SELECT is the only one I changed from the code block.

[wildenborch]

Ah I see.

Well it's not so important (no commenter is a guest comment).

If - in the future - you have some time to look into the code it would be a nice block to implement as a predefined block in a future release.

[@rjen]

This one works...

I made a few layout changes too because it was quite bulky in the side block but you get the idea...

Code Select Expand


// *** Config ***
// Set the maximum number of comments to show.
$limit = 25;
// Set the maximum number of characters per comment to show.
$commlim = 90;
// *** 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 AS member_name
FROM {db_prefix}tp_articles m
      LEFT OUTER JOIN {db_prefix}tp_variables n ON n.value5 = m.id
      LEFT OUTER 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'],
        'id' => $row['value3']
);
if (strlen(html_entity_decode($details['comment'])) > $commlim)
             $details['comment'] = htmlentities(substr(html_entity_decode($details['comment']), 0, ($commlim-3)) . '...');
// Build the output...
echo '
<div class="tborder tp_article_frame">
<div class="catbg tp_subject" style="padding: 7px 0px 7px 0px;">

' , ($details['query'] != '' ? '<a href="' . $scripturl . '?page=' . $details['query'] . '">' . $details['subject'] . '</a>' : '<a href="' . $scripturl . '?page=' . $details['articleid'] . '">' . $details['subject'] . '</a>') , '
</div><span style="font:smalltext;">
<div class="">On
<span class="tp_thindivider"></span>
<span>' , date("l",$details['date']) , '</span>
<span>' , date("d",$details['date']) , '</span>
<span>' , $txt['months_short'][date("n", $details['date'])] , '</span>
<span> ' , date("Y",$details['date']) , '</span>
<span> ' , date("H:i:s",$details['date']) , '</span>
<span><b> ' , (($details['id'] <> 0) ? $details['commenter']  :  $txt['tp-guest']) , '</b></span>

<span class="">commented: </B></span>

<div class="" style="color: #330000; font-size: 16px""><br>

' ,$details['comment'], '

</div>
<br></div>
</div>';
}
$smcFunc['db_free_result']($request);


[tino]

(($details['id'] <> 0) ?  That looks wrong, shouldn't it be > 0

[@rjen]

(($details['id'] <> 0) ?  That looks wrong, shouldn't it be > 0

That will work too...