[wp-hackers] Re: Displaying recent comments in 'live feed' from WP and bbPress

[Austin Matzko]

On Mon, Nov 24, 2008 at 2:32 PM, Alex Hempton-Smith
<hempsworth at googlemail.com> wrote:
> I'm attempting to create a box on the homepage of my site which will display
> the latest comments from my WP site and bbPress forums, one after the other
> in a text-scroller type thing. It will show the avatar, username and comment
> for a brief period of time, and then fade out and fade in the next comment.

On Mon, Nov 24, 2008 at 8:48 PM, Alex Hempton-Smith
<hempsworth at googlemail.com> wrote:
> Is there any simple way I can get comment data for the most recent comments
> posted across the whole site?

Here is the query I use to do something similar:

		(SELECT post_id AS the_id, post_time AS the_time, poster_id AS the_user_id,
				wp_users.display_name AS the_username, wp_users.user_login AS the_login,
				bb_posts.topic_id AS the_parent,
				wp_users.user_url AS the_url,
				topic_title AS the_title
				FROM bb_posts
				LEFT JOIN wp_users
				ON bb_posts.poster_id = wp_users.ID
				LEFT JOIN bb_topics
				ON bb_posts.topic_id = bb_topics.topic_id
				WHERE bb_posts.post_status = '0'
			)
			UNION ALL
			(SELECT comment_ID AS the_id, comment_date_gmt AS the_time, user_id
AS the_user_id,
				comment_author AS the_username,	comment_author AS the_login,
				comment_post_ID AS the_parent,
				comment_author_url AS the_url, wp_posts.post_title AS the_title
				FROM wp_comments
				LEFT JOIN wp_posts
				ON wp_comments.comment_post_ID = wp_posts.ID
				WHERE comment_approved = "1" AND comment_type != "trackback" AND
comment_type != "pingback"
			)
			ORDER BY the_time DESC LIMIT 10