Here’s how to code your own “Most commented” widget for your self-hosted Wordpress site, without the need for a single plugin. See my left sidebar for an example (”Popular Posts”).
First, find the .php files that are part of your template. They are normally in wp-content\themes\<your theme>, whatever <your theme> might be. You’ll probably want to put the widget in your sidebar, so a lot of themes probably have a sidebar.php file. Open it up in your preferred text editor.
I’ll explain the MySQL query first. Then I’ll add a bit of PHP around it, and wrap it all together at the end.
The core of the widget is the query. All the data we need is in one database table, the wp_posts table. If you know how to access your Wordpress database (probably through phpMyAdmin), you’ll see that it has (amongst others) post_title and comment_count columns, so those are the ones we want. Wordpress has a special way of accessing the tables - instead of referring to the table directly, Wordpress identifies it by a variable. The way to access the wp_posts table is to refer to a variable called $wpdb->posts:
SELECT ID, post_title, comment_count
FROM $wpdb->postsThis will list the title of all your posts, and how many comments each one has. It also lists the “ID” of each post, which you’ll need later. To get them in descending order, with the most commented at the top of the list, and the least commented at the bottom, just add an ORDER BY clause:
SELECT ID, post_title, comment_count
FROM $wpdb->posts
ORDER BY comment_count DESCIf you’ve got hundreds of posts, you don’t want them all in your widget, so limit it to a low number, like 5:
SELECT ID, post_title, comment_count
FROM $wpdb->posts
ORDER BY comment_count DESC
LIMIT 5So that’s the query. Don’t paste it into your .php file just yet though - you need to know how to present the data with PHP.
Rather than explain all about PHP, I’ll just point out a few key bits, and assume you’re following me. Otherwise I’ll end up writing a book about PHP!
First off, we need to get the results of the query into a variable. Like with the way Wordpress refers to tables, it also gives you a way of getting the results of a query, by using the $wpdb->get_results() function. Here’s how to get the results of the query into a variable called $posts:
$posts = $wpdb->get_results("SELECT ID, post_title, comment_count FROM $wpdb->posts ORDER BY comment_count DESC LIMIT 5");Now we need to loop round the results, and display them in an unordered list. I’ve used a for each loop, between a pair of <ul> tags:
echo '<ul>';foreach($posts as $post) {
}
echo '</ul>';
Inside the loop, we need to separate out the ID, title and comment-count:
echo '<ul>';
foreach($posts as $post) {
$id = $post->ID;
$title = $post->post_title;
$count = $post->comment_count;
}
echo '</ul>';
Now we’ve got the ID, title and count, we can add them to the unordered list, as a list item:
echo '<ul>';
foreach($posts as $post) {
$id = $post->ID;
$title = $post->post_title;
$count = $post->comment_count; echo '<li><a href="' . get_permalink($id) . '">' . $title . '</a> (' . $count . ')</li>';
}
echo '</ul>';
Note the use of ID, to make a link out of the post title, using the get_permalink() Wordpress function to get the actual URL of the post.
That’s it really.
All together, it looks like this, sandwiched between the <php and ?> tags:
<?php
$posts = $wpdb->get_results("SELECT ID, post_title, comment_count FROM $wpdb->posts ORDER BY comment_count DESC LIMIT 5");
echo '<ul>';
foreach($posts as $post) {
$id = $post->ID;
$title = $post->post_title;
$count = $post->comment_count;
echo '<li><a href="' . get_permalink($id) . '">' . $title . '</a> (' . $count . ')</li>';
}
echo '</ul>';
?>
Then it’s up to you how you play with the HTML and CSS. I’ve tarted up my “Popular Posts” section by removing the bullet points, adding a header, and putting grid lines around it.
Copy and paste it into your .php file (i.e., your sidebar.php file). You’ve probably got a set of <div id="sidebar"><ul> tags at the top, so paste it below those.
Feel free to comment!
Add New Comment
Viewing 5 Comments
Thanks. Your comment is awaiting approval by a moderator.
Do you already have an account? Log in and claim this comment.
Do you already have an account? Log in and claim this comment.
Do you already have an account? Log in and claim this comment.
Do you already have an account? Log in and claim this comment.
Do you already have an account? Log in and claim this comment.
Do you already have an account? Log in and claim this comment.
Add New Comment