Most Recent Posts (outside phpBB)

This forum is now closed as part of retiring phpBB2.
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

This forum is now closed due to phpBB2.0 being retired.
Post Reply
adiant
Registered User
Posts: 134
Joined: Sat Jul 05, 2008 1:48 am

Most Recent Posts (outside phpBB)

Post by adiant »

Although it could be used within a phpBB Forum and, quite frankly, my partner asked me how to modify the home page of our phpBB forum to include Most Recent Posts, I quickly realized it was easier to do this outside of phpBB. He quickly realized that it would drive traffic to the home page of his site, so was overjoyed at this solution.

In our case, this accelerated the deployment of the Etomite Open Source CMS on our Web site that includes the phpBB forum. As I had already built some additional Database infrastructure into Etomite using php (which Etomite calls Snippets), it was relatively easy to add some more to process this (and any other) SQL query.

I have successfully used the following SQL to display an ordered list of the most recent topics for which posts were made, across all Forums, with a couple of nice touchs: only lists a topic once, even if multiple posts have been made; and only shows topics that "anyone" can View or Read (auth_view and auth_read).

Code: Select all

SELECT forum_name, p1.forum_id AS forum_id, topic_title, p1.post_id, username, TIME_TO_SEC(TIMEDIFF(NOW(), FROM_UNIXTIME(p1.post_time))) DIV 3600 AS hours, MINUTE(TIMEDIFF(NOW(), FROM_UNIXTIME(p1.post_time))) AS minutes
FROM phpbb_topics, phpbb_users, phpbb_forums, phpbb_posts AS p1
JOIN (
SELECT topic_id, MAX(post_time) AS post_time FROM phpbb_posts WHERE post_time > UNIX_TIMESTAMP(SUBTIME(NOW(), '72:00:00')) GROUP BY topic_id)
AS p2 ON p1.topic_id = p2.topic_id AND p1.post_time = p2.post_time
WHERE p1.post_time > UNIX_TIMESTAMP(SUBTIME(NOW(), '72:00:00')) && p1.topic_id = phpbb_topics.topic_id && poster_id = user_id && p1.forum_id = phpbb_forums.forum_id && auth_view = 0 && auth_read = 0
ORDER BY p1.post_time desc LIMIT 0 , 40
What you might want to change: The "LIMIT 0, 40" at the end ensures you get no more than the most recent 41 topics listed. The '72:00:00' that you will find twice in the SQL, ensures that you go back no more than 72 hours (3 days), looking for "recent" posts on topics.

Performance: the two 72:00:00 WHERE clauses are not redundant. Without them, Performance is unacceptable on all but the smallest phpBB databases.

I'll edit this post when I move it, but, for now, here is a live working version of all this: http://www.edmontonbroadcastersclub.com/index.php?id=94
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: Most Recent Posts (outside phpBB)

Post by drathbun »

Why not use the topic_last_post_id column? Each topic is stamped with the last post, which is then used to join to the posts table. That way you only get each topic once, and you can avoid the expense of the inline table to get the max(post_time).
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image
adiant
Registered User
Posts: 134
Joined: Sat Jul 05, 2008 1:48 am

Re: Most Recent Posts (outside phpBB)

Post by adiant »

drathbun wrote:Why not use the topic_last_post_id column?
Fabulous information! Thank you for you time. I'll post a revised version (here and on the Etomite site) as soon as I've got it working.
adiant
Registered User
Posts: 134
Joined: Sat Jul 05, 2008 1:48 am

Most Recent Posts (outside phpBB)

Post by adiant »

Here is the rewritten version:

Code: Select all

SELECT forum_name, phpbb_posts.forum_id, topic_title, post_id, username, TIME_TO_SEC(TIMEDIFF(NOW(), FROM_UNIXTIME(post_time))) DIV 3600 AS hours, MINUTE(TIMEDIFF(NOW(), FROM_UNIXTIME(post_time))) AS minutes
FROM phpbb_posts, phpbb_topics, phpbb_users, phpbb_forums
WHERE post_time > UNIX_TIMESTAMP(SUBTIME(NOW(), '72:00:00')) &&
phpbb_posts.topic_id = phpbb_topics.topic_id && 
topic_last_post_id = post_id && 
poster_id = user_id && phpbb_posts.forum_id = phpbb_forums.forum_id &&
auth_view = 0 && auth_read = 0
ORDER BY post_time desc LIMIT 0 , 40
Unfortunately, though, it does not run as well as the previous version. It takes 0.0213 sec., compared to 0.0035 sec. Six times as much processor time.

I'll stare at it for a while to see if I see any obvious performance hits. But if anyone should notice anything, I'd appreciate hearing.
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: Most Recent Posts (outside phpBB)

Post by drathbun »

I don't know why it would be slower. You have fewer tables to reference, and based on the explain plans on my database you end up using primary key lookups for phpbb_users and phpbb_forums, and indexes on post_time and topic_last_post_id. What do you get when you do an explain?
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image
adiant
Registered User
Posts: 134
Joined: Sat Jul 05, 2008 1:48 am

Re: Most Recent Posts (outside phpBB)

Post by adiant »

Before I saw your post, I sat down and went through all the WHERE clauses, looking for any fields referenced that were not indexed. And only found three that I didn't think needed to be indexed: topic_last_post_id and the two auth_ fields.

So, I was about to reverse one of them: topic_last_post_id = post_id
And I ran the query again, before reversing, and got 0.0030 sec.! Repeating, it was 0.0037 sec.

Obviously, something strange was going on at the time of my first test. I apologize for the false alarm.

Thank you again for your help.
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: Most Recent Posts (outside phpBB)

Post by drathbun »

Good to hear, thanks. :)
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image
Post Reply

Return to “[2.0.x] MOD Writers Discussion”