Most Commented Entries (SQL)

I was interested in listing the posts on my site with the most comments and it doesn't look like anyone's created a plugin for it in MT. Since I know nothing about Perl, that person isn't going to be me. The only solution I found was my own from a Scriptygoddess post made long, long ago. I didn't like the solution because I wanted to list entries made across the whole site, not just one blog. I could use the GlobalListings plugin to accomplish this, but that would slow the rebuild time whenever someone commented (since it would need to rebuild the page with ALL the entries in it).

I wanted to use SQL. Ideally, it would have been nice to be able to point to entry URLs by referencing the settings in MT. It all came down to the fact that (again, knowing no perl) I really don't have a good way of deciphering MT tags that can appear inside the archive template settings.

So my solution was to use trackback information. If you're not allowing trackbacks on your blog, you'll have to in order to use this. Although you can use a SQL update statement to allow trackbacks on all your backdated posts, you can't just rebuild and have the trackback information populate in the correct table. You actually have to rebuild the page manually.

So this little trick is really only for those that have already had trackback enabled for their entries or for those that want to take the time to go back and set each entry to allow pings and rebuild it.

You can see my use of this right here.

I wanted to use SQL. Ideally, it would have been nice to be able to point to entry URLs by referencing the settings in MT. It all came down to the fact that (again, knowing no perl) I really don't have a good way of deciphering MT tags that can appear inside the archive template settings.

So my solution was to use trackback information. If you're not allowing trackbacks on your blog, you'll have to in order to use this. Although you can use a SQL update statement to allow trackbacks on all your backdated posts, you can't just rebuild and have the trackback information populate in the correct table. You actually have to rebuild the page manually.

So this little trick is really only for those that have already had trackback enabled for their entries or for those that want to take the time to go back and set each entry to allow pings and rebuild it.

Once you have that setup, it's quite simple.

Download this file and unzip it. You'll find "most_commented.php" and there are a few variables that need to be set before we go any further.

The first three variables you see look like this:

include ("/home/ACCOUNT/public_html/connect.php");
$blogs = "4,7,10,13";
$results = 25;
The first line is the path to your connect.php. This file is described right here on GirlieMatter's tips and tricks site. Set the path to the correct path of your connect.php file.

The next variable is $blogs. Here, enter the blogs you want to list the most comments for separated by a comma (no space). If you just want to use this for one blog, enter the blog ID for that one blog with no comma. You can find the blog ID by hovering over blog links on your main MT page.

Finally, there is $results. Here, place the number of results you want to return. You can comment out this variable entirely if you want to return ALL entries. Here is an example of what the variable should look like commented out:

// $results = 25;
You can rename the file from most_commented.php to anything you want. You can have a separate file for each blog and a file for all blogs. The choice is yours.

The script is good to go from there without any other necessary configurations. Wherever you want the results returned, use an include:

include("/home/ACCOUNT/public_html/most_commented.php");
You may notice the results are not formatted exactly as you'd like. If you look in the file, you should see a block of text that looks like the following:

<b><a href="<?php echo $trackback_url;?>"><?php echo "$mm.$dd.$yy"; ?> - <?php echo $trackback_title;?></a></b> (<?php echo $total_comments;?> comments)<br/>
<?php echo $trackback_description; ?><br/>
Posted in <a href="<?php echo $blog_site_url;?>"><?php echo $blog_name;?></a><br/><br/>
You can use regular HTML here to format the results any way you wish. Simply use php echo tags to echo the appropriate variables where you want them to appear. Below is a list of variables you can use:

echo $blog_name
Name of the blog for that entry.

echo $blog_site_url;
The URL of the blog for that entry. This can be used in link tags. Example:
<a href="echo $blog_site_url;">echo $blog_name</a>

echo $comment_entry_id;
The entry number (not padded) for that entry.

echo $total_comments;
This returns the total number of comments for that entry.

echo $trackback_title;
The title of the entry in question.

echo $trackback_description;
The excerpt, or description of the entry.

echo $trackback_url;
The URL of the entry. This can be used in link tags. (see $blog_site_url for an example)


Date Formatting
Assuming you have the hang of using php echo commands, here are the variables you can plug in for the date:

$mm: Month
$dd: Day
$yy: Year in 2 digit format (ex: 04)
$yyyy: Year in 4 digit format (ex: 2004)

$time: Returns time of entry in hours, minutes and seconds. (ex: 02:44:13)
$hh: Hour
$ii: Minute
$ss: Seconds

When combining elements in date format, you need to use quotation marks in your echo statement. For example, of you wanted to list the month, day, year (2 digit), hour and minute, you might use the following:

echo "$mm/$dd/$yy $hh:$ii";
Please let me know if there are any questions.

Comments are closed for this entry

That’s really cool - I’m going to have to add that to my wishlist of things to add to my blog!

i’ve been trying to implement this but ive been getting an error on line 11.  not sure if you can help, but i’d love some

You’d have to tell me what line 11 is in your file.  It’s probably different than the 11th line of code above.  Also, what does the error exactly state?

maybe i am doing this wrong then.  i created the most_commented.php file and copied in the code. i changed the settings at the top.  i added nothing else, just what was in the file i downloaded.

I’d still need to know what line 11 is for you and the exact error message you’re getting to be of any help to you.

doh.  im sorry.  thank you for helping me

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/celovega/public_html/most_commented.php on line 11

line 11 is - im pretty sure - blog_site_url, trackback_title, trackback_description, trackback_url FROM

There are only three things I can think of that would be causing this problem. 

First, you might not be using the SQL database option in MT.

Second, you might not be pointing to the connect.php file correctly to connect to the database or may have some of its settings wrong.

Third, your table names in your database might not begin with the “mt_” prefix, in which case you’d need to change the tables queried to the correct names.

Might any of these apply to you?

Another option might be that your host hasn’t upgraded PHP in a very long time and it’s possible I’m using newer tags not available in older versions - might want to check the version of PHP installed.

Hi Lynda,

I’ve been trying to implement the comments code, but not having much success.  Below is a link to the errors the i am getting. Any help would be greatly appreciated. Thank you.

http://www.advancinginsights.com/testblog-1/most_commented.php

Trackback: Most Commented on Entries
UPDATE 3.29.04 Lynda has a updated version of this script. I'll leave the content of the post up - but go to her site for the most recent version of the script.

Jim, it doesn’t look like you have the connect.php file setup.  Please go over the instructions linked about about creating a connect.php file.  If you’ve set one up already, make sure you are directing the script to find it in the right place.

Hi Lynda,

Thanks so much for getting back to me soon fast. I copied the connect code into the most_commented.php file and get the following:
Warning: mysql_connect(): Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2) in /home/content/j/k/w/jkwilde/html/testblog-1/most_commented.php on line 6
I cannot connect to the database.

Jim.  I can’t help you with that part.  You apparently entered incorrect settings to your mySQL database.  Please verify you’re actually using mySQL with MT.  If so, you’ll need to check with your host if you don’t know the details that need to be filled out.

Thanks.

Trackback: most comments
I finally got this widget to work. I wanted to have a listing of the...

Trackback: a listing of tips & tricks
This a running record of all the tricks I use to make my blog special...

I finally got this to work.  i had an extra space some place apparently.

Trackback: Most Commented-on Entries
By browsing around and monitoring pages I've been able to keep track of which posts have the most comments, but...