Recent Comments in a portal block

[cosmicx]

how to accomplish this? i have adkportal in my dev site

[SMFHacks]

Would need to write a query to get all recent comments from listings that are not removed and then have it displayed it would be php code.

[cosmicx]

i have no idea how to do that? any instructions in English? don't know sql and php.

[SMFHacks]

Example code:

Code: [Select]

global $smcFunc;
  $dbresult = $smcFunc['db_query']('', "
  SELECT
  c.ID_COMMENT, c.ID_LISTING, c.comment, c.date, c.ID_MEMBER, m.member_name,
  m.real_name FROM {db_prefix}class_comment as c
  LEFT JOIN {db_prefix}members AS m ON (c.ID_MEMBER = m.ID_MEMBER)
  WHERE c.approved = 1 ORDER BY c.ID_COMMENT DESC LIMIT 10");
while($row = $smcFunc['db_fetch_assoc']($dbresult))
{

echo '<a href="' . $scripturl . '?action=classifieds;sa=view;id=' . $row['ID_LISTING'] . '">' . substr($row['comment'], 0, 100)  .'</a><br />
by ';

if($row['real_name'] != '')
echo '<a href="' . $scripturl . '?action=profile;u=' . $row['ID_MEMBER'] . '">'  . $row['real_name'] . '</a>';
else
echo ''  . $txt['class_guest']. '';
echo '<br />
', timeformat($row['date']), '
<hr /><br />';


}


[cosmicx]

thank you for the respond....


i've tried the code, it only displays the comment and the one who give the comment and the date. In the original  comment block, it shows the Category and the Listing title.

In my case, I only want to display the Listings title. I hope you may help me further.

[SMFHacks]

Try

Code: [Select]

<?php

global $smcFunc;
   $dbresult = $smcFunc['db_query']('', "
   SELECT 
   c.ID_COMMENT, c.ID_LISTING, c.comment, c.date, c.ID_MEMBER, l.title
    FROM {db_prefix}class_comment as c, {db_prefix}class_listing as l

   WHERE l.ID_LISTING = c.ID_LISTING AND c.approved = 1 ORDER BY c.ID_COMMENT DESC LIMIT 10");
while($row = $smcFunc['db_fetch_assoc']($dbresult))
{

echo '<a href="' . $scripturl . '?action=classifieds;sa=view;id=' . $row['ID_LISTING'] . '">' . substr($row['title'], 0, 100)  .'</a><br />';


}


[cosmicx]

thank you so much for this.... more power smfhacks..

[cosmicx]

hello again... i've been struggling to put this code in a 2 row table.


what i want to accomplish is... display 20 recent comments... 10 in each row... im just learning about basic tables right now.

i don't know yet, how to incorporate it with php and sql. i hope you may help.