Retrieving WordPress Posts by Comment Count
This is a useful PHP utility for retrieving a list of WordPress posts by comment count. This utility can be used in a page or post template, custom theme, or a custom plugin. The function is designed to take a few parameters and build a dynamic SQL query to retrieve the post list. The query is then run against the WordPress database to retrieve the list.
Explanation of Function
This function uses the global WordPress database class (wpdb) and builds a simple MySQL SELECT statement to retrieve a list of post titles in descending ranked order by comment count. The desired date range, number of posts to return, and the option to include pages and posts, are function parameters that are dynamically incorporated into the MySQL query. The function could also easily be modified to accept custom post types. It returns an associative array that uses post_title as the array keys, and comment_count as the element value. The SELECT statement can be modified to retrieve the post_ID, or other post attributes as desired. Basically, anything that the WordPress get_post function can return in the post object.
Function Building Blocks
This section lists all of the core building blocks for the function and a short description. The entire function definition can be found below.
- First, build a SQL statement WHERE clause based on input parameters (include_pages, include_posts, & period)
if ( $include_pages && $include_posts ) {
// WHERE clause for both pages & posts
if ( $period === 'Total' ) {
// WHERE clause for period = total (simpler query)
$WHERE .= "post_type='page' OR post_type='post' ";
} else {
// WHERE clause for custom date range (more complex query)
$WHERE .= "t1.post_type='page' OR t1.post_type='post' ";
}
} elseif ( $include_pages ) {
// WHERE clause for only pages
if ( $period === 'Total' ) {
// WHERE clause for period = total (simpler query)
$WHERE .= "post_type='page' ";
} else {
// WHERE clause for custom date range (more complex query)
$WHERE .= "t1.post_type='page' "; // assumes wp-posts defined as t1
}
} elseif ( $include_posts ) {
// WHERE clause for only posts
if ( $period === 'Total' ) {
// WHERE clause for period = total (simpler query)
$WHERE .= "post_type='post' ";
} else {
// WHERE clause for custom date range (more complex query)
$WHERE .= "t1.post_type='post' "; // assumes wp-posts defined as t1
}
} elseif ( !$include_posts && !$include_pages ) {
// just return empty array if we're not looking for pages or posts
return $rows;
}
- Next, insert the WHERE clause into simple SQL statement if period = Total
if ( $period === 'Total' ) {
// basic query for posts/pages from all dates
$my_query = array( "SELECT post_title, comment_count ",
"FROM {$wpdb->prefix}posts ",
"$WHERE ORDER BY comment_count DESC LIMIT $num_rows"
);
// finalize the query and execute
$posts = $wpdb->get_results( join( '', $my_query ) ); // default return type is an OBJECT
- Or if we’re working with a custom date range, insert WHERE clause into more complex query
This query requires an INNER JOIN statement for wp-posts and wp-comments on the ID and comment_post_ID columns. Note the inclusion of times for each date to make sure we return all posts published during the specified date range.
} else {
// more complex query for a selected date range
$my_query = array( "SELECT comment_count, post_title ",
"FROM {$wpdb->prefix}posts $WHERE AS t1 INNER JOIN {$wpdb->prefix}comments as t2 ON t1.ID = t2.comment_post_ID ",
"WHERE t2.comment_date >= '" . $start_date . " 00:00:00' AND t2.comment_date <= '" . $end_date . " 23:59:59' ",
"GROUP BY ID ",
"ORDER BY comment_count DESC LIMIT $num_rows"
);
// finalize the query and execute
- Then, Execute the SQL query
Here we’re joining together an array of strings via the PHP implode() function and then executing.
$posts = $wpdb->get_results( implode( '', $my_query ) ); // default return type is an OBJECT
- Finally, place results into associative array and return
// build associative array where post_title => #_of_comments and return
foreach ( $posts as $post ) {
$rows[$post->post_title] = $post->comment_count;
}
// return the result set
return $rows;
Calling the Function
// function defined as method of static object class 'wp_plugin_utilities'
// first, request the list of posts with your desired arguments
$post_list = wp_plugin_utilities::get_posts_by_comment_count(
$end_date = '2012-01-01',
$include_pages = false,
$include_posts = true,
$num_rows = 20,
$period = false,
$start_date = '2011-01-01'
);
// then output the list, or use it for another purpose...
echo "<p>The top $num_rows posts from $start_date to $end_date by comment count are:</p>";
// loop through each post item, print the title and # of comments
foreach ( $post_list as $post_title => $comment_count ) {
echo "$post_title comment count: $comment_count<br />";
}
Function Definition
This is the entire function definition, with explanatory comments inserted.
/**
*
* Method to retrieve list of WP post titles with the most comments (descending order).
*
* @param string $end_date (last date of publication for posts to be included in our list, formatted as YYYY-MM-DD)
* @param string $include_pages (true or false)
* @param string $include_posts (true or false)
* @param integer $num_rows (number of post titles we want returned in our list)
* @param string $period (if we want all posts, set this to 'Total', any other value is ignored)
* @param string $start_date (first date of publication for posts to be included in our list, formatted as YYYY-MM-DD)
* @return array $rows (formatted as $rows['post_title'] = post_comment_count, if no results returns empty array)
*/
public function get_posts_by_comment_count( $end_date, $include_pages, $include_posts, $num_rows, $period, $start_date ) {
global $wpdb; // global WordPress database class
$rows = array(); // initialize return array
$WHERE = "WHERE "; // beginning of WHERE clause for SQL statement
// determine if we're looking for pages & posts, only pages, or only posts
if ( $include_pages && $include_posts ) {
// WHERE clause for both pages & posts
if ( $period === 'Total' ) {
// WHERE clause for period = total (simpler query)
$WHERE .= "post_type='page' OR post_type='post' ";
} else {
// WHERE clause for custom date range (more complex query)
$WHERE .= "t1.post_type='page' OR t1.post_type='post' ";
}
} elseif ( $include_pages ) {
// WHERE clause for only pages
if ( $period === 'Total' ) {
// WHERE clause for period = total (simpler query)
$WHERE .= "post_type='page' ";
} else {
// WHERE clause for custom date range (more complex query)
$WHERE .= "t1.post_type='page' ";
}
} elseif ( $include_posts ) {
// WHERE clause for only posts
if ( $period === 'Total' ) {
// WHERE clause for period = total (simpler query)
$WHERE .= "post_type='post' ";
} else {
// WHERE clause for custom date range (more complex query)
$WHERE .= "t1.post_type='post' ";
}
} elseif ( !$include_posts && !$include_pages ) {
// just return empty array if we're not looking for pages or posts
return $rows;
}
// check to see if we want posts from all dates
if ( $period === 'Total' ) {
// basic query for posts/pages from all dates
$my_query = array( "SELECT post_title, comment_count ",
"FROM {$wpdb->prefix}posts ",
"$WHERE ORDER BY comment_count DESC LIMIT $num_rows"
);
// finalize the query and execute
$posts = $wpdb->get_results( join( '', $my_query ) ); // default return type is an OBJECT
// otherwise, we are looking for posts in a specific date range
} else {
// more complex query for a selected date range
$my_query = array( "SELECT comment_count, post_title ",
"FROM {$wpdb->prefix}posts AS t1 INNER JOIN {$wpdb->prefix}comments as t2 ON t1.ID = t2.comment_post_ID ",
"$WHERE AND t2.comment_date >= '" . $start_date . " 00:00:00' AND t2.comment_date <= '" . $end_date . " 23:59:59' ",
"GROUP BY ID ",
"ORDER BY comment_count DESC LIMIT $num_rows"
);
// finalize the query and execute
$posts = $wpdb->get_results( join( '', $my_query ) ); // default return type is an OBJECT
}
// build associative array where post_title => #_of_comments and return
foreach ( $posts as $post ) {
$rows[$post->post_title] = $post->comment_count;
}
// return the result set
return $rows;
}
