AHP Sitewide Recent Posts – hack 2.0

After completing the initial hack, I went back to my production site and noticed that only one post from each site was being displayed. I believe this was the original intent of the plug-in, but I think that needs to be changed, so I made a little improvement. So now, the plug-in lets you set a max limit on the number of post per site. If you have a lot of sites, you’ll probably want to up the number of posts to display in general. Here is what I did.

The first thing I did was to split up the sql code into different parts.
The first part was changed to a simple count of the number of posts available.

$sqlCount = "SELECT count($blogPostsTable.ID) ";

The second part is the actual full select statement to get the post information.

$sqlSelect = "SELECT $blogPostsTable.post_date, $blog AS this_blog, $blogPostsTable.ID, $blogPostsTable.post_title,
			$blogPostsTable.post_content, wp_users.display_name,
			wp_users.user_email, wp_users.user_login ";

The third part is the rest of the select statement, except the order by, which will be used by both select statements.

$sqlFrom = "	FROM $blogPostsTable, wp_users
			WHERE wp_users.ID = $blogPostsTable.post_author
			AND post_status = 'publish' AND post_type = 'post'
			AND post_date >= DATE_SUB(CURRENT_DATE(), INTERVAL $how_long DAY)
			AND $blogPostsTable.id > 1 ";

The final part is the order by and limit statement that will be dynamically created based on the number of posts found.

$sqlOrder = "ORDER BY $blogPostsTable.post_date DESC limit $x,1";

As before, there is an initial query to get the number of posts available. But, I changed it to a var query, which will reduce the call to the database.

// build the count query
$sql = $sqlCount . '' .  $sqlFrom;
$postcount = $wpdb->get_var($sql);

Once I get that value back, if there are more than 0 posts, then I loop through the call getting each post and inserting it into the array.
I have to first make sure that the number of posts is not less than the per site post limit. If it is less, I use that number, but if it is more, then I just use the per site limit. The “x” counter lets me start the limit at the proper post and only go 1 row from that point. For example, when $x is 4, it goes to row 4 and gets only that one row. I rebuild the query and then get the post data to insert into the array, with the $i counting the array rows.

if($postcount > 0) {
	if ($postcount > $per_site) {
		$site_limit = $per_site;
	} else {
		$site_limit = $postcount;
	}
	for($x = 0; $x < $site_limit; $x++){
		$sqlOrder = "	ORDER BY $blogPostsTable.post_date DESC limit $x,1";
		$sql = $sqlSelect . '' .  $sqlFrom .''. $sqlOrder;
		$postArray[$i] = $wpdb->get_row($sql, ARRAY_A);
		$i++;
	}
}

To make sure the page formatting doesn’t break, I added the “per site” limit to the “how many” limit to make sure that the counter doesn’t go to high.
I changed out the $a counter with the $counter variable. And made sure that the “break” call was inside the while loop.

Finally I added a parameter to the function and defaulted the value at 2 per site.

function ahp_recent_posts($how_many, $how_long, $optmask = 255, $exc_size = 30, $per_site = 2, $begin_wrap = '<li class="ahp_recent-posts">', $end_wrap = '</li>')

Here is the complete code

<?php

/*
Plugin Name: AHP Sitewide Recent Posts for WordPress MU
Plugin URI: http://www.metablog.us/blogging/ahp-recent-posts-plugin-for-wordpress-mu/
Description: Retrieves a highly customizable list of the most recent sitewide posts in a WordPress MU installation. Automatically excludes blog ID 1 (main blog), and post ID 1 (first "Hello World" posts of new blogs).
Author: Aziz Poonawalla
Author URI: http://metablog.us

FUNCTION ARGUMENTS

$how_many: how many recent posts are being displayed
$how_long: time frame to choose recent posts from (in days)
$optmask: bitmask for various display options (default: 255)
	DISPLAY OPTIONS BITMASK
	1;  // gravatar
	2;  // date
	4;  // author name
	8;  // comment count
	16; // blog name
	32; // post name
	64; // post excerpt
	128; // excerpt capitalization
$exc_size: size of excerpt in words (default: 30)
$begin_wrap: start html code (default: <li class="ahp_recent-posts">)
$end_wrap: end html code to adapt to different themes (default: </li>)

SAMPLE FUNCTION CALL

to show 5 posts from recent 30 days: <?php ahp_recent_posts(5, 30);  ?>

SAMPLE CSS

gravatar styling:  img.avatar-24 { float: left; padding: 0px; border: none; margin: 4px; clear: left; }
LI styling: li.ahp-recent-posts { list-style-type: none ;}
excerpt styling: .ahp-excerpt { margin-top: 2px }

TODO:

- link gravatar icon to Extended Profile in buddypress, if installed
- widgetize
- show more than one post per blog

CHANGELOG
Version 0.8
Update Author: Dean Logan
Update Author URI: http://www.dean-logan.com
- Altered the post query to be a count
- added a "per site" limit for the number of posts per site
- modified closing call to include the "per site" limit

Version 0.7
Update Author: Dean Logan
Update Author URI: http://www.dean-logan.com
- altered loops add an array to store comments
- sort comment array and then display
- altered display layout

Version 0.6
Update Author: Aziz Poonawalla
Update Author URI: http://metablog.us
- added comment count display option
- added enable/disable excerpt capitalization
- consolidated title/name of post display options into bitmask
- reduced number of required arguments
- added class name ahp-recent-posts to default start html LI tags
- added class ahp-excerpt to excerpt block

Version 0.5
Update Author: Aziz Poonawalla
Update Author URI: http://metablog.us
- changed gravatar link to point to all posts by author on main blog (ID = 1).
- added date string, author output
- implemented bitmask to control gravatar, date, author output
- consolidated numwords argument with display argument

Version 0.4.1
Update Author: Aziz Poonawalla
Update Author URI: http://metablog.us
- added gravatar support, icon size 24px
- gravatar can be styled by img.avatar-24 in your css file
- gravatar image links to author's blog
- capitalization of first five words of the excerpt

Version 0.4.0
Update Author: Aziz Poonawalla
Update Author URI: http://metablog.us
- added exclusions for first blog, first post, enabled post excerpt

Version: 0.32
Update Author: G. Morehouse
Update Author URI: http://wiki.evernex.com/index.php?title=Wordpress_MU_sitewide_recent_posts_plugin

Version: 0.31
Update Author: Sven Laqua
Update Author URI: http://www.sl-works.de/

Version: 0.3
Author: Ron Rennick
Author URI: http://atypicalhomeschool.net/
*/

function ahp_recent_posts($how_many, $how_long, $optmask = 255, $exc_size = 30, $per_site = 5, $begin_wrap = '<li class="ahp_recent-posts">', $end_wrap = '</li>') {
	global $wpdb;
	$counter = 0;

	// EDIT THESE TO CUSTOMIZE THE OUTPUT
	$debug = 0;
	$blog_prefix = "Posted from ";
	$post_prefix = "";
	$auth_prefix = 'Posted by ';
	$com_prefix = ' &nbsp; currently ';
	$date_format = 'D M jS, Y';
	$grav_size = 60;

	// DISPLAY OPTIONS BITMASK
	$grav_flag = 1;  // gravatar
	$date_flag = 2;  // date
	$auth_flag = 4;  // author name
	$com_flag  = 8;  // comment count
	$name_flag = 16; // blog name
	$post_flag = 32; // post name
	$exc_flag  = 64; // post excerpt
	$cap_flag  = 128; // excerpt capitalization

	// set the various option flags
	if ($optmask & $grav_flag) { $use_grav = 1; } else { $use_grav = 0; }
	if ($optmask & $date_flag) { $use_date = 1; } else { $use_date = 0; }
	if ($optmask & $auth_flag) { $use_auth = 1; } else { $use_auth = 0; }
	if ($optmask & $com_flag)  { $use_com  = 1; } else { $use_com = 0;  }
	if ($optmask & $name_flag) { $use_name = 1; } else { $use_name = 0; }

	if ($optmask & $post_flag) { $use_post = 1; } else { $use_post = 0; }
	if ($optmask & $exc_flag)  { $use_exc  = 1; } else { $use_exc = 0;  }
	if ($optmask & $cap_flag)  { $use_cap  = 1; } else { $use_cap = 0;  }

	// debug block
	if ($debug) {
		echo '<hr>'.'opt = '.$optmask.': grav = '.$use_grav.', date = '.$use_date
		.', auth = '.$use_auth.', use_com = '.$use_com.', use_name = '.$use_name
		.', use_post = '.$use_post.', use_exc = '.$use_exc.', use_cap = '.$use_cap;
	}

	// get a list of blogs in order of most recent update. show only public and nonarchived/spam/mature/deleted
	$blogs = $wpdb->get_col("SELECT blog_id FROM $wpdb->blogs WHERE
		public = '1' AND archived = '0' AND mature = '0' AND spam = '0' AND deleted = '0' AND
		last_updated >= DATE_SUB(CURRENT_DATE(), INTERVAL $how_long DAY)
		ORDER BY last_updated DESC");

	$postArray = array();
	$i = 0;
	if ($blogs) {
		echo '<ul class="ahp_recent-posts">' . "\n";
		foreach ($blogs as $blog) {

			// we need _posts, _comments, and _options tables for this to work
		    $blogPostsTable = "wp_".$blog."_posts";

			// debug block
			if ($debug) {
				echo '<hr>processing blog '.$blog.' = <a href="'.
				$options[0]->option_value.'">'.$options[1]->option_value.'</a> <br>';
			}

			// fetch the ID, post title, post content, post date, and user's email for the latest post
			$sqlCount = "SELECT count($blogPostsTable.ID) ";
			$sqlSelect = "SELECT $blogPostsTable.post_date, $blog AS this_blog, $blogPostsTable.ID, $blogPostsTable.post_title,
				$blogPostsTable.post_content, wp_users.display_name,
				wp_users.user_email, wp_users.user_login ";
			$sqlFrom = "	FROM $blogPostsTable, wp_users
				WHERE wp_users.ID = $blogPostsTable.post_author
				AND post_status = 'publish' AND post_type = 'post'
				AND post_date >= DATE_SUB(CURRENT_DATE(), INTERVAL $how_long DAY)
				AND $blogPostsTable.id > 1 ";
			// build the count query
			$sql = $sqlCount . '' .  $sqlFrom;
			$postcount = $wpdb->get_var($sql);
			// if posts are found, cycle through the count and get the correct number of posts
			if($postcount > 0) {
				if ($postcount > $per_site) {
					$site_limit = $per_site;
				} else {
					$site_limit = $postcount;
				}
				for($x = 0; $x < $site_limit; $x++){
					$sqlOrder = "	ORDER BY $blogPostsTable.post_date DESC limit $x,1";
					$sql = $sqlSelect . '' .  $sqlFrom .''. $sqlOrder;
					$postArray[$i] = $wpdb->get_row($sql, ARRAY_A);
					$i++;
				}
			}
		}

		if(count($postArray) > 0){
			array_multisort($postArray, SORT_DESC);
			while ($counter < count($postArray)){

				// debug block
				if ($debugflag) {
					echo 'processing thispost ID = ' . $postArray[$counter]['ID'] . '<br>';
					echo 'post_title = ' . $postArray[$counter]['post_title'] . '<br>';
					echo 'post_content = ' . $postArray[$counter]['post_content'] . '<br>';
					echo 'post_date = ' . $postArray[$counter]['post_date'] . '<br>';
					echo 'display_name = ' . $postArray[$counter]['display_name'] . '<br>';
					echo 'user_email = ' . $postArray[$counter]['user_email'] . '<br>';
					echo 'user_login = ' . $postArray[$counter]['user_login'] . '<br>';
					echo 'site_url = ' . $postArray[$counter]['option_value'] . '<br>';
				}

				// get post ID
				$thispostID = $postArray[$counter]['ID'];

				// get permalink by ID.  check wp-includes/wpmu-functions.php
				$thispermalink = get_blog_permalink( $postArray[$counter]['this_blog'], $thispostID);

				// set blog tables
				$blogOptionsTable = "wp_" . $postArray[$counter]['this_blog'] . "_options";
				$blogCommentsTable = "wp_" . $postArray[$counter]['this_blog'] . "_comments";

				// get blog name,  URL
				if ($use_name) {

					$options = $wpdb->get_results("SELECT option_value FROM
					$blogOptionsTable WHERE option_name IN ('siteurl','blogname')
					ORDER BY option_name DESC");

					$blog_link = $options[0]->option_value;
					$blog_name = $options[1]->option_value;
					$this_blogname = $blog_prefix.'<a href="' . $blog_link . '">' . $blog_name . '</a>';

				} else { $this_blogname = ''; }

				// get comments
				if ($use_com) {

					// sql query for all comments on the current post
					$thesecomments = $wpdb->get_results("SELECT comment_ID
					FROM $blogCommentsTable
					WHERE comment_post_ID = $thispostID");

					// count total number of comments
					$num_comments = sizeof($thesecomments);

					// pretty text
					if ($num_comments == 0) { $thiscomment = $com_prefix.'no comments'; }
					elseif ($num_comments == 1) { $thiscomment = $com_prefix.'one comment'; }
					else { $thiscomment = $com_prefix.$num_comments.' comments'; }

				} else { $thiscomment = ''; }

				// get author
				if ($use_auth) {
					$thisauthor = $auth_prefix.$postArray[$counter]['display_name'];
				} else { $thisauthor = ''; }

				// get author's posts link
				$thisuser = $thispost[0]->user_login;
				$thisuser_url = $thisbloglink."author/".$thisuser;

				// get gravatar
				if ($use_grav) {
					$grav_img = get_avatar( $postArray[$counter]['user_email'] , $grav_size );
					$thisgravatar = '<a href="'.$thisuser_url.'">'.$grav_img.'</a>';
				} else { $thisgravatar = ''; }

				// get post date (nicely formatted)
				if ($use_date) {
					//$thisdate = date($date_format, strtotime( $thispost[0]->post_date )) ;
					$thisdate = '<span class="dateMonth">' . date('M', strtotime( $postArray[$counter]['post_date'] )) . '</span>';
					$thisdate .= '<span class="dateDay">' . date('d', strtotime( $postArray[$counter]['post_date'] )) . '</span>';
					$thisdate .= '<span class="dateYear">' . date('Y', strtotime( $postArray[$counter]['post_date'] )) . '</span>';
					$thistime = date('g:i a', strtotime( $postArray[$counter]['post_date'] ));
				} else { $thisdate = ''; }

				// get post name
				if ($use_post) {
					$this_postname = $post_prefix . '<a href="' . $thispermalink . '">'  .$postArray[$counter]['post_title'] . '</a><br>';
				} else {
					$this_postname = ''; }

				if ($use_exc) {
					if ($exc_size == 0) {
						$thisexcerpt = '';
					} else {
						// get post content and truncate to (numwords) words
						$thiscontent = strip_tags( $postArray[$counter]['post_content'] );
						preg_match("/([\S]+\s*){0,$exc_size}/", $thiscontent, $regs);

						if ($use_cap) {
							// build the excerpt html block, capitalize first five words
							$trunc_content = explode( ' ', trim($regs[0]) , 6 );
							$exc_str = strtoupper($trunc_content[0]).' '
							.strtoupper($trunc_content[1]).' '
							.strtoupper($trunc_content[2]).' '
							.strtoupper($trunc_content[3]).' '
							.strtoupper($trunc_content[4]).' '
							.$trunc_content[5].'... ';
						} else { $exc_str = trim($regs[0]); }

						$thisexcerpt = '<span style="ahp-excerpt">'.$exc_str
						.'<a href="'.$thispermalink.'">'
						.'&raquo;&raquo;MORE'.'</a></span>';
					}
				} else { $thisexcerpt = ''; }

					echo $begin_wrap
					. '<div class="date">' . $thisdate . '</div>'
					. '<div id="avatar" style="margin-left:65px; margin-top:14px;">' . $thisgravatar . '</div>'
					. '<h3>' . $this_postname . '</h3>'
					. '<div class="postmetadata">&nbsp;@ ' . $thistime . '<br /><div id="author">' . $thisauthor . '</div>'
					. '<br /><div id="blogname">&nbsp;' . $this_blogname . '</div><div id="comment"> | ' . $thiscomment . '</div></div>'
					. '<div id="excerpt">' . $thisexcerpt . '</div>'
					. $end_wrap  . "\n";

					$counter++;

				// don't go over the limit
				if($counter >= ($how_many)) {
					break;
				}
			}
		}
	}
	else {
		//echo "no recent posts meet the criteria...<br>";
	}
}
?>