I am the guild leader of Seelund Trading Co. on the Venture Co. (US) Server in World of Warcraft. I’m also, not surprisingly, our webmaster. This means maintaining a forum with a guild news portal, informational pages and a messaging system. After much testing, I decided to go with phpbb3 as our forum software (and have not had a single reason to regret it yet). That’s just forums and messaging though. I had a little experience from another site with MX Publisher (which as of this writing seems to have exceeded it’s bandwidth. That might say something about its popularity), so I decided to use that to create an informational front end to the site.

This is where I ran into problems. Not with the package itself: Installing was a breeze; upgrading’s been easy. The interface is a little anti-usability but once I got used to it that was ok. No, the problem was in how announcements are sorted and displayed on the front page. The tools for picking which (sub)forums announcements come from and which class of posting (announcement, global or sticky) ends up as a news blurb on the front page are fine. The ability to sort these in a logical manner though? Not so much. Your basic choices are “time it was posted”, either newest first or oldest first. That’s fine if all you’re doing is posting news that will be an announcement for the same amount of time. But we post events, congratulations, site maintenance… a lot of things that have a different number of days set for the “announcement” time. If I find out today that another guild is hosting a pvp event in two days, I want it to show up at the top of the news messages… unless I posted an RP event three weeks ago that’s tomorrow. Then I want the RP event to show up first and the PVP event second. Kind of obvious when you look at it that way, yeah? But sorting by end dates for announcements are not an option in the basic install.

So.

If you are not a programmer stop here. If your site is using MX-Publisher and you want news posts to show up sorted by end date, read on.

I looked for awhile for a way to do this without modifying the files. Making this change will mean that you have to watch any upgrading because you may need to make the change again to the upgraded file, or you’ll need to do all your upgrading manually. I prefer to avoid that where feasible. I couldn’t find any programmatic way that didn’t involve changing the source code. Luckily, this is a very small change to a single sql statement in the announce block.

In /modules/mx_phpbb3blocks/mx_announce.php, look for an sql statement at or near line 109 (that’s where it is in my version, there might be minor line number discrepancies in other versions). It should look like this:

$sql = "SELECT t.*, u.username, u.user_id, u2.username as user2,
                u2.user_id as id2, p.*, pt.post_text, pt.post_subject, pt.bbcode_uid,
                p2.post_time AS last_post_time
	FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, "
                . POSTS_TABLE . " p2, " . USERS_TABLE . " u2, " . POSTS_TABLE . " pt
	WHERE p.topic_id = t.topic_id
		AND t.topic_type IN ( " . $topic_type . " )
		AND p.post_id = t.topic_first_post_id
	  	AND pt.post_id = p.post_id
		AND u.user_id = p.poster_id
		AND p.post_time >= $min_topic_time
		AND t.forum_id IN ( $auth_data_sql )
		AND t.forum_id IN ( $announce_forum )
		AND p2.post_id = t.topic_last_post_id
		AND u2.user_id = p2.poster_id
	ORDER BY p.post_time $post_time_order
	LIMIT $start, " . $announce_nbr_display;

Now, there is no end date field in any of these tables. So we have to tell the mysql statement to figure out when the end date is for each of these topics. Luckily the information you need to figure this out IS in the table. The fields you need are “topic_time” (a unix timestamp for when the topic was posted) and “topic_time_limit” (the amount of time you put in that it should show up as an announcement… converted to seconds). By adding the time it was posted to the number of days you want it to show as an announcement, you can find the last second when you want it to show up as an announcement.

First you modify what the sql statement is retrieving at the beginning by adding “t.topic_time_limit+t.topic_time as end_date” to the select. Then you ORDER BY “end_date”. Simple. Your changed sql statement looks like this:

$sql = "SELECT t.*, u.username, t.topic_time_limit+t.topic_time as end_date,
                u.user_id, u2.username as user2, u2.user_id as id2, p.*, pt.post_text, pt.post_subject,
                pt.bbcode_uid, p2.post_time AS last_post_time
	FROM " . TOPICS_TABLE . " t, " . USERS_TABLE . " u, " . POSTS_TABLE . " p, "
                . POSTS_TABLE . " p2, " . USERS_TABLE . " u2, " . POSTS_TABLE . " pt
	WHERE p.topic_id = t.topic_id
		AND t.topic_type IN ( " . $topic_type . " )
		AND p.post_id = t.topic_first_post_id
	  	AND pt.post_id = p.post_id
		AND u.user_id = p.poster_id
		AND p.post_time >= $min_topic_time
		AND t.forum_id IN ( $auth_data_sql )
		AND t.forum_id IN ( $announce_forum )
		AND p2.post_id = t.topic_last_post_id
		AND u2.user_id = p2.poster_id
	ORDER BY end_date $post_time_order
	LIMIT $start, " . $announce_nbr_display;