Introduction:
This is a tiny tiny snippet that you can set in a page to get a list (nicely tabled) of comments do not have reactions from a specific users. Like the site admin. This is very useful in a large site where you don’t want to look if people have responded to pages but you ALSO don’t want tons of e-mails on every comment.
Warning: You have to be careful with the query, as you’re working directly on the database. I’ll explain AFTER the snippet what we’re doing for a little extra information. Also, this only works with Jot, though it could be adjusted to other comment systems.
Code:
<?php
$comment = $modx->dbConfig['table_prefix'] . 'jot_content';
$content = $modx->dbConfig['table_prefix'] . 'site_content';
$sql = '';
$sql .= 'select c.content, c.createdon, p.alias, p.longtitle from '.$content.' as p, ';
$sql .= '(select uparent, max(createdon) as createdon from ';
$sql .= $comment.' group by uparent ';
$sql .= ') as x inner join '.$comment.' as c on ';
$sql .= 'c.uparent = x.uparent and c.createdon = x.createdon and c.createdby <> 1 ';
$sql .= 'where p.id = x.uparent ';
$sql .= 'order by p.longtitle';
$result = '<table>'."\n";
$rows = $modx->dbQuery($sql);
while ($row = $modx->fetchRow($rows)) {
$result .= '<tr><td>'.substr($row['content'], 0, 40);
$result .= '</td><td>'.strftime("%c",$row['createdon']);
$result .= '</td><td><a href="'.$row['alias'].'">'.$row['longtitle'].'</a>';
$result .= '</td></tr>'."\n";
}
$result .= '</table>';
return $result;
?>
First we build a query that spans 2 tables, the jot_content and site_content. The latter is used for the actual alias/title of the page to link to. You could replace this by ID if you want to.
The only goal of the query is get a page where the LAST query on that page is NOT made by the user with ID 1 (the admin). By the way, unregistered users get ID 0.
What this means is that if there’s a page where I haven’t responded to a comment, it will show up in the query.
After that, I build a simple table with the content of the comment (for ease), the time it was posted and a clickable link to the actual page. So, instead of having to check the 400+ pages of my site for comments or get e-mail flooding into my inbox, I can just check one page daily and respond to everything.
I hope this has been educational for everyone.
Example:
My own site uses
http://nimja.com/comments but it’s of course empty until someone posts a comment again.
Have fun everyone!
