tips and tricks for movable type
lastn comments from one category
September 20, 2003
I was playing around with this a while back and figured I'd better blog it before I lost track of it again.
This MySQL/PHP code gives me the last 5 comments on entries in one particular category. The display format is identical to the Recent Comments listing in my sidebar.
Note that the permalinks to the entries are customized to match my Archive File Templates, so this probably isn't something you can just cut-and-paste to use for yourself, but it gives some idea of how permalinks can be constructed.
Here's the code:
$blogurl="http://www.thegirliematters.com/tips/archives";
function dirify ($s) {
$s = strtolower($s);
$patterns = array('/<[\/\!]*?[^<>]*?>/s', '/&[^;\s]+;/', '/[^\w\s]/', '/ /');
$replace = array('', '', '', '_');
$s = preg_replace($patterns, $replace, $s);
return $s;
}
$comments = mysql_query("SELECT comment_url, comment_author, SUBSTRING_INDEX(comment_text, ' ', 10) AS comment, comment_entry_id, entry_title, date_format(entry_created_on, '%y%m') AS archive_path FROM mt_comment, mt_entry, mt_placement WHERE (comment_entry_id = placement_entry_id) AND (placement_entry_id = entry_id) AND (placement_category_id = 54) ORDER BY comment_created_on DESC LIMIT 5");
while($row = mysql_fetch_array($comments)) {
$url = $row['comment_url'];
$author = $row['comment_author'];
$text = strip_tags($row['comment']);
$title = dirify ($row['entry_title']);
$titlenodir = $row['entry_title'];
$archivepath = $row['archive_path'];
echo "• <a href=\"$url\">$author</a>: $text [<a href=\"$blogurl/$archivepath/$title.php\" title=\"$titlenodir\">entry</a>]<br />\n";
}
?>
Items in color represent variables to be changed (and of course, there's the whole permalink issue in the last echo statement).
FYI, here's the Archive File Template I use for my Individual Archives:
which gives me filenames as YYMM/entry_title.php, and that is what the code is written to mimic.
Related Entries:
• category specific recent comments listing
TrackBack: 7
(URL: http://www.thegirliematters.com/sf/mt-track.cgi/156)
» I'm here. Where are you?
Excerpt: If you see this, then you're seeing Burningbird in the new home. Expect rough times this week, as I continue the move. For instance, I've had to drop my recent comments/trackback list, though I am working an alternative. I also have several trackback p...
Weblog: Burningbird
Tracked: 11.17.03 08:21 PM
» 197 new domains added
Excerpt: I just received some blog spam via email because I was subscribed to this entry at Girlie's Tips and Tricks. The spam comment contained 197 domains. To make sure I had these domains on my blacklist, I intentionally spammed my own blog! None of them wer...
Weblog: Blog Spam Database
Tracked: 01.05.04 04:08 PM
» Faithjean dot com's photolog is up!
Excerpt: Thanks to Gabriel for helping me:-) 2 do list: *The comment on this page doesn't work right now! Hopefully I'll figure it out soon...:-) Scripts: *Add: Most commented - entries & Most commented - visitors script *Last 50 referrers(pop up)...
Weblog: .::faithjean.com::.
Tracked: 01.06.04 05:29 PM
» Photolog is up!
Excerpt: Thanks to Gabriel for helping me:-) 2 do list: *The comment on this page doesn't work right now! Hopefully I'll figure it out soon...:-) Scripts: *Add: Most commented - entries & Most commented - visitors script *Last 50 referrers(pop up)...
Weblog: .::faithjean.com::.
Tracked: 01.07.04 01:46 PM
» 20 things on My 2 Do list
Excerpt: 1. Add smilies in the 2. | Category | 3. Counting for (x) Recent entries 4. Statistics: " x entries", "x comments", "x users online", "x on this page" script 5. Last 50 referrers 6. 10 most commented entriescomments 7....
Weblog: .::faithjean.com::.
Tracked: 01.08.04 02:26 PM
» 20 things on My 2 Do list
Excerpt: 1. Add smilies in the comments 2. | Category | 3. Counting for (x) Recent entries 4. Statistics: " x entries", "x comments", "x users online", "x on this page" script 5. Last 50 referrers 6. 10 most commented entries7....
Weblog: .::faithjean.com::.
Tracked: 01.08.04 02:29 PM
» comment modifications
Excerpt: Ï've done a lot of comment modification to the site this morning. First of all, if you scroll down a bit, you'll notice that the most recent five comments are now displayed. In addition, I've installed MT-Blacklist support, which will...
Weblog: matt at lightwind
Tracked: 11.05.04 05:04 PM
Comments
Interesting. I had some trouble figuring out a way to do while trying do summaries a category-based forum view. See the example on my Seinfeld Blog. I wanted to be able to list the last person to comment in each category (as most forum software does) but I couldn't figure out a way to do this. I don't have time now, but I may give this script a try. Thanks!
Can this be done using the MTSQL plugin?
how would you set up a variable to output the date the comment was posted in day/month/year format? i have tried using the comment_created_on variable, but i get the date in 2003-10-30 14:51:18 format.
thanks very much.
In the SELECT statement:
In the WHILE loop:
In the ECHO statement:
That should take care of it.
sorry, but a couple of more. i would like to recreate these links:
<a href="<MTEntryLink archive_type="Individual">">link to entry page</a>
<a href="<MTEntryLink archive_type="Individual">#<$MTCommentID pad="1"$>link to specific comment</a>
thanks for your patience. :)
ouch, i just messed up your comments. i'm sorry. :(
Well, that's where things get trickier. It depends on how your individual files are named. As in the example, mine are named using Archive File Templates in a particular format (i.e., not the default MT uses).
If you're using the default of padded entry IDs, then this mini-tip code will do that:
and replace $title with $entryid in the echo statement for the URL.
Same thing for comment ID (you'd have to add comment_id to the SELECT statement too):
and include #$comment_id in the echo statement for the URL.
that is absolutely incredible. i have followed your instructions and with a bit of trial and error, it is working perfectly. i thank you with all of my heart.
Absolutely fantastic, what an resource you are! Found a small glitch:
For this pattern in title ' - ' the dirify generated a '__' whereas MT generates single '_'.
I fixed it with one more lin:
$s = str_replace('__','_',$s);
cheers
I figured it was going to be a bit harder than just the overall last n for the weblog... I had no idea it would be that hard though. Thanks for the good info...I may be using that in a soon to come website revision of my blog :)
Hello - is there any way to list the most recent entries that people have been commenting on but sorted by category - without using php just using MT tags or at a pinch with a plug in? I am wracking my brain trying and failing to think up a way!
Any ideas?