This is a tiny snippet I provide on a user profile page on
Nimja.com that will show a link to all pages that a user has commented on and has gotten replies. It shows a tiny bit of the first reply after the comment and allows users to click on it to go to the relevant page.
When no user is logged in, it shows ALL pages where there are comments that have not yet been replied on by the site admin (user ID 1)
To have the page scroll down to the comments, add the following to the title above the comments (for example)
Snippet call is [!UnreadComments!]
Code:
<?php
$comment = $modx->dbConfig['table_prefix'] . 'jot_content';
$content = $modx->dbConfig['table_prefix'] . 'site_content';
$sql = '';
$id = $modx->getLoginUserID();
//Default show, only for admin, meaning unreplied comments.
if (empty($id)) {
$sql .= 'SELECT c.content, c.createdon, p.id, p.pagetitle FROM '.$content.' AS p,';
$sql .= ' ( SELECT uparent, max( createdon ) AS createdon FROM '.$comment.' GROUP BY uparent ) AS x';
$sql .= ' INNER JOIN '.$comment.' AS c ON c.uparent = x.uparent AND c.createdon = x.createdon AND c.createdby <> 1'; //"1" is the Admin user ID by default.
$sql .= ' WHERE p.id = x.uparent ORDER BY c.createdon';
} else {
$sql .= 'SELECT c.content, MAX(c.createdon) as createdon, p.id, p.pagetitle FROM '.$content.' AS p,';
$sql .= ' ( SELECT uparent, MAX( createdon ) AS createdon FROM '.$comment.' WHERE createdby = '.$id.' GROUP BY uparent ) AS x';
$sql .= ' INNER JOIN '.$comment.' AS c ON c.uparent = x.uparent AND c.createdby <> '.$id;
$sql .= ' WHERE p.id = x.uparent AND c.createdon > x.createdon GROUP BY c.uparent ORDER BY c.createdon DESC LIMIT 0, 20';
}
function cutWords($string, $length) {
$string = str_replace(array("\r\n", "\n", "\r", " "), " ", $string);
$result = $string;
if (strlen($string) > $length) {
$result = '';
$parts = explode(" ", $string);
$cur = 0;
while ( $cur < count($parts) && (strlen($result) + strlen($parts[$cur])) < $length) {
$result .= " ".$parts[$cur];
$cur++;
}
$result .= '...';
}
return $result;
}
$result = '<div class="UnreadComments">'."\n";
$rows = $modx->dbQuery($sql);
while ($row = $modx->fetchRow($rows)) {
$link = $modx->makeUrl($row['id'], '', '', 'full').'#comments';
$result .= '<div><a href="'.$link.'"><b>'.$row['pagetitle'].'</b>';
$result .= '<i>'.strftime("%a %b %d, %Y, %H:%M:%S",$row['createdon']).'</i>';
$result .= '<span>'.cutWords($row['content'], 40).'</span>';
$result .= '</a></div>';
}
$result .= '</div>';
return $result;
?>
ps. Small sidenote: The ’cutwords’ function cuts a piece of text based on a number of characters. The result will be 43 characters max (40 characters + 3 periods).
