tips and tricks for movable type
comments leader board with php and mysql
January 29, 2003
Brenna gave us a PHP/MySQL version of Kiffin's Perl/SSI Comment Leader Board idea posted in this original support forum thread.
Here's the code (combining other elements requested in the thread).
Using Comment Author URL:
include ("/path/to/your/connection/file/connect.php");
$leaders = mysql_query("SELECT comment_url, comment_author, COUNT(*) as comment_count FROM mt_comment WHERE (comment_blog_id=8) AND (comment_author!='girlie') GROUP BY comment_author ORDER BY comment_count DESC LIMIT 5");
while($row = mysql_fetch_array($leaders)) {
while (list($key,$val) = each($row)) {$$key = $val;}
echo "<a href=\"$comment_url\">$comment_author</a>
($comment_count)<br />\n";
} ?>
Using Comment Author EMail:
$leaders = mysql_query("SELECT comment_email, comment_author, COUNT(*) as comment_count FROM mt_comment WHERE (comment_blog_id=8) AND (comment_author!='girlie') GROUP BY comment_author ORDER BY comment_count DESC LIMIT 5");
while($row = mysql_fetch_array($leaders)) {
while (list($key,$val) = each($row)) {$$key = $val;}
$comment_email=str_replace("@", "@", "$comment_email");
$comment_email=str_replace(".", ".", "$comment_email");
echo "<a href=\"mailto:$comment_email\">$comment_author</a> ($comment_count)<br />\n";
} ?>
Changes required to personalize:
/path/to/your/connection/file/connect.php (how to make a connect file)
8 (your blog ID)
girlie (your comment author name)
5 (number of authors to display)
(Note that "your comment author name" may not be the same as your MT login author name - it's whatever name you use to post comments to your blog. This part of the code keeps your own comments from appearing in the list.)
Update 01.31.02: Brenna added another version to the thread, which follows the same logic that MTCommentAuthorLink uses:
include ("/path/to/your/connection/file/connect.php");
$leaders = mysql_query("SELECT comment_email, comment_url, comment_author, COUNT(*) as comment_count FROM mt_comment WHERE (comment_blog_id=8) AND (comment_author!='girlie') GROUP BY comment_author ORDER BY comment_count DESC LIMIT 5");
while($row = mysql_fetch_array($leaders)) {
while (list($key,$val) = each($row)) {$$key = $val;}
if (!empty($comment_url)) {
$authorlink = "<a href=\"$comment_url\">$comment_author</a>";
} elseif(!empty($comment_email)) {
$authorlink = "<a href=\"mailto:$comment_email\">$comment_author</a>";
} else {$authorlink = $comment_author;}
echo "$authorlink ($comment_count)<br />\n";
} ?>
TrackBack: 16
(URL: http://www.thegirliematters.com/sf/mt-track.cgi/136)
» Who comments the most?
Excerpt: Girlie has a script that will show who comments the most on your blog. (that looks really fun - I
Weblog: scriptygoddess.com
Tracked: 01.30.03 12:23 PM
» Cool - now if I only had comments
Excerpt: Track comment counts of your readers.
Weblog: AYM - Site Log
Tracked: 01.30.03 02:46 PM
» A neat little trick.
Excerpt: One file and hundreds of keystrokes saved!
Weblog: Live in the Delirious Cool
Tracked: 02.04.03 01:24 PM
» Show recent comments WITH total comments from that comment author
Excerpt: This is from another script request - Show most recent comments AND how many comments that person has made in
Weblog: scriptygoddess.com
Tracked: 02.13.03 09:58 PM
» Comment leader (month based)
Excerpt: I really liked Girlie's "comment leader board". My only problem was that, I've been blogging for nearly two years. People
Weblog: scriptygoddess.com
Tracked: 03.02.03 08:06 AM
» Top 5 Kommentatoren » Update
Excerpt: Ok, das hat nicht so gut geklappt. Aber ein wenig Recherche und schon wird man fündig, nämlich hier und hier,
Weblog: Couchblog
Tracked: 03.03.03 02:52 PM
» Comment leaders
Excerpt: Since my 2002 archives are in a separate blog, my comment leaderboard wasn't 100% accurate, and if you know me
Weblog: Unix Gal
Tracked: 03.06.03 11:04 PM
» Comment Leaders and UPDATE statements
Excerpt: Brenna released a new version (0.3) of the CommentLeaders plugin and so while I was up last night, I did
Weblog: scriptygoddess.com
Tracked: 03.21.03 02:52 PM
» Comment leaders for the current month
Excerpt: This is a variation of Girlie's Comment Leaderboard. I've seen other people who show the comment leaders for the last
Weblog: Unix Gal
Tracked: 04.18.03 05:31 PM
» Comment leaders for the current month
Excerpt: This is a variation of Girlie's Comment Leaderboard. I've seen other people who show the comment leaders for the last
Weblog: scriptygoddess.com
Tracked: 04.18.03 11:25 PM
» Wowsers
Excerpt: Sometimes, I manage to do things I was certain I'd screw up, and I just amaze myself. I have Girlie's Comment Leader Board on my Portal page. I had a couple of girls who'd changed URLs, and one name change....
Weblog: Internal: Talking to Myself
Tracked: 06.19.03 10:11 AM
» Movable Type Enhancements
Excerpt: In trying to determine whether Movable Type (MT) was a feasible backend for OK/Cancel, I found a variety of sites and discovered just how big the MT community really was. If there's a function you can think of (and many...
Weblog: my:dentity
Tracked: 09.28.03 11:34 AM
» Ongoing Projects
Excerpt: On my to-do list: Figure out how to do an RSS feed for comments. I found a useful MT template...
Weblog: Design-A-Blog
Tracked: 10.10.03 12:01 PM
» *Yearly* Comment Leaderboard
Excerpt: I just implemented the comment leaderboard script available at Scriptygoddess, but I wanted to feature the *yearly* comment leaders as opposed to the monthlies. I...
Weblog: Bloggie Broad
Tracked: 12.02.03 03:28 PM
» Comment leaders for the current month
Excerpt: This is a variation of Girlie's Comment Leaderboard. I've seen other people who show the comment leaders for the last 30 days, but I decided I'd rather show the leaders for the current month. Here's the code. The items bolded...
Weblog: Glimpse of a Grrl
Tracked: 01.07.04 07:37 AM
» Sweet New Comment Leaders Thingy
Excerpt: Make sure you're on the front page of this site. Now look to the column on the left at the section under the title "Comment Leaders." Isn't that awesome!? It uses PHP to retrieve comment counts/data from my MySQL database,...
Weblog: DigitalFlipSide.com
Tracked: 02.06.04 03:59 PM
Comments
Also check out page 2 and 3 of the original thread on the support forum for a version which uses the email instead of the url of the comments leaders!
(some smart PHP/mySQL programmer could make it work like $MTCommentAuthor$ and only use the email if no url is available...)
Lawrence
Whoops. Mixed my apples with my oranges there. Now both versions shown above.
I'm not a smart PHP/mySQL programmer (tee-hee!) but I posted another version in that thread, with some linking logic in there. This would be a perfect thing for a plugin!
i'm probably just missing it somewhere but what should connect.php contain?
It should contain this:
// name of your database
$database = "database_name";
// connect to database
$db=mysql_connect ("localhost", "database_user", "database_password") or die ('I cannot connect to the database.');
mysql_select_db("$database",$db);
?>
Save and upload to your server as connect.php.
Heh - I was just ciming here to confirm that myself, after Googling for the answer! Now my question is this: you should really protect that file in some way, right, so prying eyes can't read it. What's the best way? Store it outside of public_html CHMOD it to some number?
Well, it's a PHP file, so I believe if someone were to browse to it, they're not going to be able to see anything because the code gets executed.
Similar to mt-db-pass.cgi, ya know?
Unless they can figure out how to download the file and get a peek that way.
Hmm, then they'd also have to know your MySQL host name, right? Because it wouldn't be "localhost" for them.
Yep, it will be executed. If php would happen to go down they could see it... is all I can think of. You could try to tuck it away in a non-web-accessible directory, or in a password protected directory (that's where mine is).
there is a slight error in your connect.php
the last line should be:
mysql_select_db ($database);
Fixed! That's what I get for copying from an old text file instead of my actual connect.php file. =)
Oh, and mine looks slightly different from what you posted, as you can see.
Quick Syntax not in the connect.php file.
You have $dbh=......
then in the select:
mysql_select_db("$database",$db);
That needs to be changed to
mysql_select_db("$database",$dbh);
or else it won't connect.. :)
Fixed again - another hazard of using the old file. Damn. I should just repost the whole bit of code, haha.
It works perfectly! Thanks very much. :)
once I read all the comments and found out what the "connect.php" was, it all worked nicely! :) woo hoo! thanks
I added a link for "how to make a connect file" in the entry body - hope that helps. =)
I'm getting this error
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/royville/public_html/comment.php on line 6
You've done it again.
Thanks so much for your help!!!
Does the "comment author name" automatically also filter out commenters with the same name as your own? Because I use my own name in my comments, but I also know another Liz who also comments and I wouldn't want to leave her out :)
You should be able to use the email address instead.
Replace:
with:
replacing you@email.com with your email address, of course.
I am also getting a similar error to Brian's:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/html/topcommenters.php on line 8
What is the cause of this? Please help out if you can!
Do you have your connect file path and setup correct? Also, did you change the blog ID?
Got it working, thanks! Forgot to specify the database name in connect.php... dumb mistake!
I'm getting the following error when I try to use this script:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/akakestr/public_html/blog/footer.inc on line 157
I'm not sure what I am doing wrong. I changed the user name to my email address, the blog ID I assume is the same since mine is was the first blog created, and the path to my connect file is correct.
Any ideas??
If your blog is the first blog created, then the blog ID is rather unlikely to be 8. Have you tried 1 instead?
Yes, 1 is what I had changed my blog ID to. Sorry, I wasn't very clear about that.
The error message means the query didn't return any results.
What does the $leaders = part of your code look like? And are you positive the path to the connect file is correct (full server path)?
I'm pretty sure the path to connect.php is accurate. I logged into my MT account, went to web config, then copied the path to my index.php and added connect.php at the end of it. So it looks like:
/home/mydb/public_html/blog/connect.php
The code I'm using looks like this: (I've changed to < and >)
<div class="side">
<? include ("/home/mydb/public_html/blog/connect.php");
$leaders = mysql_query("SELECT comment_email, comment_url, comment_author, COUNT(*) as comment_count FROM mt_comment WHERE (comment_blog_id=1) AND (comment_author!='MYNAME') GROUP BY comment_author ORDER BY comment_count DESC LIMIT 5");
while($row = mysql_fetch_array($leaders)) {
while (list($key,$val) = each($row)) {$$key = $val;}
if (!empty($comment_url)) {
$authorlink = "<a href=\"$comment_url\">$comment_author</a>";
} elseif(!empty($comment_email)) {
$authorlink = "<a href=\"mailto:$comment_email\">$comment_author</a >";
} else {$authorlink = $comment_author;}
echo "$authorlink ($comment_count)<br />\n";
} ?>
< /div >
Is that where you created the connect file then? Right in your main blog folder?
Yes. Should I have put it elsewhere?