Topic: Getting the last 5 posts in a category blog-style

Hey all.

I had this utterly brilliant idea to use PunBB both as my forum and my blog and kill two birds with one highly efficient stone.  I am a novice/intermediate guy at database programming, and I'm looking to design a query that is as efficient as possible to do this.  I think I can figure out how to turn what I get into posts and HTML code easily enough (I kid, that's a cakewalk), but I do need help with this query.  From what I understand I'll be crossing as many as 3 tables, and that seems really messy to me.

Here's the code I've been using to grab the latest posts within the forum on my localhost test server.  It grabs the last five posts, the poster, message, subject, and date--and sorts that by date and topic ID.  In the end I'd like to sort by forum ID and date. wink  Here's the code:

SELECT t.id, p.poster, p.message, p.posted, t.subject FROM `pun_posts` AS p INNER JOIN `pun_topics` AS t WHERE t.id=p.topic_id AND t.posted=p.posted AND t.moved_to IS NULL ORDER BY p.topic_id, p.posted LIMIT 5;

I know this isn't portable as you'll have to tailor the table prefix, however it works really well and only uses 2 simple queries.

So, I want to select a category ID, the name isn't important, and save that as a "configuration" variable.  But how do I blend the _forum selection into this whole query I've been making?  Lets just use ID 1 as a category basis for good measure.

Is this (roughly) correct?  And if so, is there anything I can do to increase its efficiency?  I just tried it in phpMyAdmin, but I got duplicate results (IE, post 1 twice, post 2 twice, etc.)...

SELECT pf.forum_name, pp.poster, pp.message, pp.posted, pt.id, pt.subject FROM `pun_forums` AS pf INNER JOIN `pun_posts` AS pp INNER JOIN `pun_topics` AS pt WHERE pt.id=pp.topic_id AND pt.posted=pp.posted AND pt.moved_to IS NULL AND pf.cat_id=1 ORDER BY pf.id, pp.posted LIMIT 5;

As far as I can tell this is right, but duplicitous results tells me I'm wrong.  Can a fresh set of eyes give me a bit of help?  I know for absolute certain there are only two rows in the DB.  The following is a picture of my results for those away from their respective testbeds:
http://img444.imageshack.us/img444/9356/strangequeryph7.th.jpg

Re: Getting the last 5 posts in a category blog-style

Try adding GROUP BY pt.id. I think that should solve it.

Re: Getting the last 5 posts in a category blog-style

Awesome. smile Thanks for your help.  I'll probably make a blog-style new generator based off the standard PunBB news generator plugin.

4

Re: Getting the last 5 posts in a category blog-style

Do a search for Quaker & Bingiman's frontpage index mod. By the sounds of it, that may be close to what you're wanting to do, or easily modded to your needs if not.

Re: Getting the last 5 posts in a category blog-style

It's not quite what I'm after, as this is a plugin for front page news generation and acting as a "blog".  I hate the speed of Wordpress, so I'm going to switch to PunBB and integrate the concept of forum with blogging. wink I'll twiddle with CronJobs to make the news "cache" refresh every six hours or so.

And you know what it's like ... learning this way is the best!  I want a very narrow featureset, and not half the things I found in the brief search I made at Punres.