Owner: Horrorshow
Type: WPMU Plugin
Created: 2008-03-12 06:08:54
Last Modified: 2008-05-08 01:38:14
sitewide_comments-0.1_fixed_sample_code.tar.gz (269 download(s))
sitewide_comments-0.1.tar.gz (222 download(s))
You can get sitewide recent comments fast, without taxing your MySQL database.
This is a plugin that grabs new comments and inserts it into a sitewide_comments table. Once the sitewide_comments is populated, then you can retrieve the comments with a simple select of one table.
What about existing comments?
You can use the UNION statement to query all comments from all blogs, export them as CSV, and then import them into the sitewide_comments table.
Advantages are:
It's fast, since you are querying just one table.
Disadvantages are:
You'll be using twice as much storage space for your comments. However, how much storage does your comments currently use?
Every time someones adds/edits/deletes a comment, the same action takes place on the sitewide_comments table. However, in most cases, adding/editing/deleting comments will incur resources spread over a period of time, so this will be less noticeable. The other approach of grabbing all comments from separate tables at once and displaying them will happen in one operation (huge spike in resource), and it'll happen as often as you query for sitewide recent comments. If it's everytime someone refreshes your index site page, then it'll be a huge resource hog. So duplicating the comments into one table is a reasonable approach.
This is a solution with the assumption that disk is cheap, but your users' time isn't.
Posted 2008-03-12 16:08:29 by horrorshow.
This is a sample SQL statement to grab comments from blogs 1, 2, and 3. The first field is the blog_id. (You have to change it for each blog.)
SELECT 1 , comment_id, comment_post_id, comment_author, comment_author_email, comment_author_url, comment_author_IP, comment_date, comment_date_gmt, comment_content, comment_karma, comment_approved, comment_agent, comment_type, comment_parent, user_id FROM `wp_1_comments` UNION ALL SELECT 2 , comment_id, comment_post_id, comment_author, comment_author_email, comment_author_url, comment_author_IP, comment_date, comment_date_gmt, comment_content, comment_karma, comment_approved, comment_agent, comment_type, comment_parent, user_id FROM `wp_2_comments` UNION ALL SELECT 3 , comment_id, comment_post_id, comment_author, comment_author_email, comment_author_url, comment_author_IP, comment_date, comment_date_gmt, comment_content, comment_karma, comment_approved, comment_agent, comment_type, comment_parent, user_id FROM `wp_3_comments` ORDER BY comment_date
Posted 2008-03-14 20:45:46 by mvkozyrev.
Thank you for great idea. And I have who questions: 1. How can I get comment text (or some first chars of comment text)? 2. Is it possible to count comments of posts and display mostcommented posts?
Posted 2008-03-15 03:12:21 by horrorshow.
1. if you look at the sample code, you'll see where it gets the first few chars of the comment. 2. yes, all posts have a comment count, so it should be easy to do this. There might even be a plugin for it already.
Posted 2008-03-28 18:47:44 by joemurphy.
Hi, thanks for the plugin. It's up and running at http://blogs.denverpost.com . Here are the fixes I made to get it working: In SAMPLE_CODE.php: In the query, the WHERE statement ought to go like WHERE comment_approved = 2 AND comment_type = '' . In the output, both href attributes have an open quote, but no close quote. That means both "#comment-" . $comment->comment_ID . ">" . lines ought to be "#comment-" . $comment->comment_ID . "\">" . -Joe
Posted 2008-03-28 18:49:51 by joemurphy.
Hey, br's work!
Here's my original comment:
Hi, thanks for the plugin. It's up and running at http://blogs.denverpost.com .
Here are the fixes I made to get it working:
In SAMPLE_CODE.php:
In the query, the WHERE statement ought to go like
WHERE comment_approved = 2 AND comment_type = '' .
In the output, both href attributes have an open quote, but no close quote. That means
"#comment-" . $comment->comment_ID . ">" .
ought to be
"#comment-" . $comment->comment_ID . "\">" .
-Joe
Posted 2008-03-29 16:36:28 by horrorshow.
Joe -
Thanks for pointing that out. I'll fix it on my next release. I checked out http://blogs.denverpost.com, and it looks great. Glad that the plugin is being used elsewhere.
I've made some improvements to the sample code since the 1st release. Checkout: http://achillesblog.com/
You know I've always wanted to live in Colorado. I hope to move there someday. 
Posted 2008-04-04 17:14:57 by Mason.
I discovered that deleting a post doesn't delete the comments under it in the sitewide comments table. Can you fix that? I built a recent comments display function around your plugin and it gets out of sync anytime someone deletes a post that had comments on it. Not a common event, but it happens sometimes.
Posted 2008-05-27 22:05:50 by horrorshow.
Mason - I've thought about this, and I decided that it was better for the comments to not get deleted from the sitewide comments table. I've had a legitimate blog that a user decided to delete after a month, and many others contributed to the comments on that blog. I thought it was a good thing that at least others' comments were preserved. I have had spam blogs, but they didn't have comments. I had spam comments on legit blogs.. but not spam comments on spam blogs. And that's the only situation where I would want the comments to get deleted. So it's worked out well for me.
You must be registered and logged in to post comments. Not registered? Click here to get your free account!