WordPress Posts by Comment Count

 

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.

  1. First, build a SQL statement WHERE clause based on input parameters (include_pages, include_posts, & period)
  2. 	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;
    	}
    
  3. Next, insert the WHERE clause into simple SQL statement if period = Total
  4. 	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
    
  5. Or if we’re working with a custom date range, insert WHERE clause into more complex query
  6. 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
    
  7. Then, Execute the SQL query
  8. 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
    
  9. Finally, place results into associative array and return
  10. 	// 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;
}

 

Customize WordPress Without Hacking the Core Code

 

Customize WordPress Without Hacking the Core Code

Making WordPress do exactly what you want can sometimes be frustrating. For example, let’s say you’re writing your own custom plugin. You want to use the WordPress Settings API, but you don’t like the way it outputs <h3></h3> tags before each settings section. How can you customize WordPress without modifying the core functions (a hack)? PHP output buffering can be a convenient solution. This is a brief tutorial on how to use the PHP output buffering functions to filter WordPress output.

How to buffer output

Buffering is done by calling the ob_start() function in PHP. This tells the server to capture all output in an internal buffer instead of returning it. You then have a couple of choices. You can modify the buffer in place via a callback function and then flush the buffer with the ob_end_flush() or retrieve the buffer contents via ob_getcontents(), change as necessary, output the result yourself, and then call ob_end_clean() to discard the buffer contents.

Logical steps

  1. Call ob_start() to start output buffering.
  2. Modify the buffer contents in place via a custom callback function, OR
  3. Retrieve the buffer contents by calling ob_get_contents()
  4. Call ob_end_flush() after modifying the buffer contents in place via a custom callback function, OR
  5. Call ob_end_clean() if you just want to silently discard the buffer contents

PHP Output Buffer Usage

ob_start() takes several parameters, all of which are optional. The primary parameter you need to worry about is the output_callback parameter, which is the first optional parameter: ob_start( $output_callback = 'my_callback_function_name' ). The callback function name is passed as a string.

There are only 2 rules for the callback function:

  1. it must accept a single parameter… a single string containing the buffer contents, AND
  2. it must return a string… which will replace the contents of the buffer.

In the code snippet below, a callback function is used to modify the buffer contents in place. The callback function is buffer_filter, which is defined within an abstract PHP class called wp_plugin_utilities.

try {
	// output beginning html for options page
	$this->output_html( SETTINGS_PAGE_BEGIN_HTML );
	settings_fields( $option_group = 'my_plugin_option_group' );
	ob_start( 'wp_plugin_utilities::buffer_filter' );
	do_settings_sections( $page = 'my_page_handle' );
	ob_end_flush();
	?>
	<script type="text/javascript">
		jQuery(document).ready(function() { 
			my_plugin_check_fields() 
			});
	</script>
	<?php 
	// create the buttons
	$this->setting_buttons( 'my_plugin_option_group' );
	// output ending html for options page
	$this->outputHtml( SETTINGS_PAGE_END_HTML );
} catch ( wp_plugin_exception $e ) {
	echo $e->getError();
}

Callback Function Definition

In this example, the callback function simply replaces the <h3> tag with a <p> tag. You can get as sophisticated as you want, using regular expressions and other tools to modify the buffer.

public function buffer_filter( $buffer ) {
	return str_replace( 'h3', 'p', $buffer );
}

And that’s it! Now you can customize WordPress output without hacking the core code (which is never a good idea).

A NOTE OF CAUTION – be careful to use these functions sparingly and on relatively small buffers, otherwise this technique can cause performance issues and page load delays.

 

Custom WordPress Functions – Hyperlink Title

Custom WordPress Functions: Hyperlink a Page or Post Title

Custom WordPress plugin and theme development often requires the writing of custom WordPress functions. linkify_title is a simple PHP function that uses 2 built-in WordPress functions get_page_by_title and get_permalink. The purpose of linkify_title is to convert a title string into hyperlinked text. It can be used by incorporating into the functions.php file in your WordPress directory. If developing a custom plugin, it can also be included in one of your plugin files.

Brief instructions follow. NOTE: If including this in function.php file, do so via a child theme in order to avoid losing it in subsequent theme or WordPress updates.

Linkify Function Code

/**
 * 
 * Custom PHP function to convert WP page or post title into hyperlinked text.
 * Function checks for title in both page and post types. Default type is post.
 * If the page/post url is not found, function returns title text only. 
 * If the page/post url IS found, function returns title wrapped in hyperlink tags.
 * 
 * @param string $title
 * @return string
 */
public function linkify_title( $title ) {
	$url = '';
	if ( $pageObj = get_page_by_title( $page_title = $title, $output = OBJECT, $post_type = 'post' ) ) {
		$url = get_permalink( $pageObj );
	} elseif ( $pageObj = get_page_by_title( $page_title = $title, $output = OBJECT, $post_type = 'page' ) ) {
		$url = get_permalink( $pageObj );
	}
	if ( !empty( $url ) ) {
		return '<a href="' . $url . '">' . $title . '</a>';
	} else {
		return $title;
	}
}

Example usage:

// retrieve page title by ID or just use a known title string
$page_title = get_the_title( $ID = $post_id );
// get the linked title
$linked_title = linkify_title( $page_title );
// output the linked title
echo "<p>The page title = $page_title</p>";
echo "<p>The linked title = $linked_title</p>";

This is a simple PHP function that can be used within or outside the loop. Generally we embed utility-type WordPress functions in an abstract PHP utilities class. The PHP utilities class is loaded during theme or plugin setup so that it can be used as needed by other classes and functions.

Googlyzer – Display Google Analytics in WordPress

Embed Google Analytics in WordPress

Googlyzer Pro includes shortcodes that enable embedding of charts with real-time data from Google Analytics. Displaying data from Google Analytics in WordPress can be a powerful visual tool to allow web site managers and their users to see the impact of their content. Embedded charts can use page-level data, or site-wide data, can be based on a wide number of metrics and can be produced for any desired date range (assuming there is data available). Examples follow.

Example 1 – Displaying Total Number of Site Visitors Over Last 30 Days

[googlyzerSparkline title='Site Visitors Last 30 Days' metrics=ga:visitors linecolor=orange fillbackground=true]

[googlyzerSparkline title='Site Visitors Last 30 Days' metrics=ga:visitors linecolor=orange fillbackground=true]

Example 2 – Displaying Total Site Pageviews Over Last 6 Months

[googlyzerSparkline title='Site Pageviews in Last 6 Months' linecolor=red minspotcolor=yellow maxspotcolor=green spotcolor=blue start=2011-08-02 end=2012-02-09]

[googlyzerSparkline title='Site Pageviews in Last 6 Months' linecolor=red minspotcolor=yellow maxspotcolor=green spotcolor=blue start=2011-08-02 end=2012-02-09]

Example 3 – Displaying Total Visitors to This Page in Last 6 Months

[googlyzerSparkline title='Page Visitors in Last 30 Days' scope=Page metrics=ga:visitors linecolor=blue fillbackground=true height=100 width=300]

[googlyzerSparkline title='Page Visitors in Last 30 Days' scope=Page metrics=ga:visitors linecolor=blue fillbackground=true height=100 width=300]

Example 4 – Displaying Total Pageviews for This Page

[googlyzerSparkline title='Total Pageviews' scope=Page linecolor=green fillbackground=true height=100 width=300 start=2005-01-01]

[googlyzerSparkline title='Total Pageviews' scope=Page linecolor=green fillbackground=true height=100 width=300 start=2005-01-01]

Supported Shortcode Tags

  • dimensions – Google Analytics dimensions. Only single value supported in this version. Must be prefixed by ‘ga:’ according to standard Google Analytics format. Incorrectly formatted input will be converted to default. Default is ga:date.
    Example:
    dimensions=ga:date
  • end – End date for date range. Must be in YYYY-MM-DD format. Default is current date.
    Example:
    end=2011-12-31
  • fillbackground – Toggle for filling chart area beneath sparkline. Must be a boolean value (true/false). Default is false.
    Example:
    fillbackground=false
  • fillcolor – Background fill color. Ignored unless fillbackground=true. Can be entered as hex value or by web color name. Default is #ADFAFF (light blue).
    Example 1:
    fillcolor=#ADFAFF

    Example 2:

    fillcolor=green
  • height – Height in pixels for sparkline chart. Can be entered as integer only, or with ‘px’ suffix. Default is 75px.
    Example:
    height=75px
  • linecolor – Sparkline color. Can be entered as hex value or by web color name. Default is #FF0000.
    Example 1:
    linecolor=#FF0000

    Example 2:

    linecolor=blue
  • max_results – Maximum number of data values to be returned from Google Analytics. Default is 1000.
    Example:
    max_results=5000
  • maxspotcolor – Maximum data point marker color. Can be entered as hex value or by web color name, set to false to disable. Default is false.
    Example 1:
    maxspotcolor=yellow

    Example 2:

    maxspotcolor=false
  • minspotcolor – Minimum data point marker color. Can be entered as hex value or by web color name, set to false to disable. Default is false.
    Example 1:
    minspotcolor=red

    Example 2:

    minspotcolor=false
  • spotcolor – Last value data point marker color. Can be entered as hex value or by web color name, set to false to disable. Default is false.
    Example 1:
    spotcolor=orange

    Example 2:

    spotcolor=false
  • metrics – Google Analytics metrics. Only single value supported in this version. Must be prefixed by ‘ga:’ according to standard Google Analytics format. Incorrectly formatted input will be converted to default. Default is ga:pageviews.
    Example:
    metrics=ga:pageviews
  • scope – Scope of data to be collected from Google Analytics. Two possible options: ‘Site’ or ‘Page’. Default is Site.
    Example:
    scope=Page
  • sort – Sort order for data retrieved from Google Analytics. Two possible options: ‘ascending’ or ‘descending’. Default is ‘ascending’.
    Example:
    sort=descending
  • sort_column – Sort column for data retrieved from Google Analytics. Can be any metric or dimension included in data request. Default is ga:date.
    Example:
    sort_column=ga:date
  • start – Start date for date range. Must be in YYYY-MM-DD format. Default is 30 days prior to current date.
    Example:
    start=2011-06-30
  • title – Title for sparkline chart.
    Example:
    title="My Chart Title" Default is blank.
  • width – Width in pixels for sparkline chart. Can be entered as integer only, or with ‘px’ suffix. Default is 250px.
    Example:
    width=250px

WP e-Commerce Shortcode – Add & Checkout Button

WP e-Commerce Shortcode: Add & Go Direct to Checkout Button for WP e-Commerce

While customizing WP e-Commerce recently, I needed to add a button that would add a specific product variation to a customer’s shopping cart and go direct to the checkout page. What I ended up doing was adding a custom WP e-Commerce Shortcode. It’s a simple function really. It creates a button that runs the custom function.

Brief instructions follow. NOTE: technically this is a WP e-Commerce hack, and will be overwritten during upgrades of WP e-Commerce. In order to preserve it, the code below will need to be re-added to the WP e-Commerce shortcodes PHP file following an upgrade.

  1. Make a backup copy of
    ../wordpress/wp-content/plugins/wp-e-commerce/wpsc-includes/shortcode.functions.php
  2. At the bottom of the WP e-Commerce shortcodes PHP file –
    ../wordpress/wp-content/plugins/wp-e-commerce/wpsc-includes/shortcode.functions.php

    Add the code listed below, without removing or editing the existing text in that file.

  3. Save and close the WP e-Commerce shortcodes PHP file.
  4. Go to Store->Settings->Presentation and click on Flush Theme Cache.
  5. Add the add_and_go_direct_to_cart shortcode to any page or post where you wish to use it.

Code to be added to the shortcode.functions.php file

/**
 * Custom shortcode function added to create a button that adds product to cart and takes customer directly to checkout page.
 */
function wpsc_add_and_go_direct_to_cart_shortcode( $atts ) {
	// extract parameters and substitute defaults if necessary
	extract( shortcode_atts( 
		array(
			'class' => '',
    			'id' => '0',
    			'name' => '',
    			'value' => 'Add to Cart'
    			),
		$atts ) 
		);
	// build form for button
	$output = array(
		"<form action='" . get_option( 'shopping_cart_url' ) . "' method='post'>n",
		"<input type='hidden' name='wpsc_ajax_action' value='add_to_cart'>n",
		"<input type='hidden' name='product_id' value='" . $id . "'>n",
		"<input type='submit' id='product__submit_button' class='" . $class . "' name='" . $name . "' value='" . $value . "'>n",
		"</form>n"
		);
	// return form for output
	return implode( "", $output );
}

add_shortcode( $tag = 'add_and_go_direct_to_cart', $func = 'wpsc_add_and_go_direct_to_cart_shortcode');

add_and_go_direct_to_cart Shortcode Syntax

[add_and_go_direct_to_cart class='my_button_class' id=1 name='buy_my_product' value='Buy My Product']

Where id = your WPSC product id, class = your CSS button class, and value = your desired button label.