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. 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: