SQL to get first post_id in the last 5 topic_id's of a forum

Discussion forum for MOD Writers regarding MOD Development.
Locked
Kewi
Registered User
Posts: 2
Joined: Wed Dec 07, 2011 5:06 am

SQL to get first post_id in the last 5 topic_id's of a forum

Post by Kewi »

So.. I'm a good bit rusty on SQL, and i've been trying to figure this one out for the last day...

Forum_ID "7" is my "news" forum, I can already pull data from my database to my front page and display it based on the phpbb_topics table data..

however, if I want post_text data, I need to use phpbb_posts table, which does not define the 'first' post in topics

Unfortunately, I need to do some form of a nested query I think to identify the initial post_id of a topic with the Poster Username field, as well as grab the post_text


Currently I have:
select * from phpbb_posts as p, phpbb_topics as t where p.post_id=t.topic_first_post_id and t.forum_id=7

Which gives me the data from phpbb_topics that references my topic_first_post_id... However I can't remember how to use that as a nested query to obtain the post_text data in the phpbb_posts field...


Anyone have suggestions on the SQL query to use on this?
Last edited by marian0810 on Thu Feb 23, 2012 9:42 am, edited 1 time in total.
Reason: moved to MOD Writers Discussion
User avatar
RMcGirr83
Former Team Member
Posts: 21695
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr

Re: SQL to get first post_id in the last 5 topic_id's of a f

Post by RMcGirr83 »

Former Modifications/Extensions Team Member | My extensions | github | All requests for support via PM will be ignored
Appreciate the extensions/mods/support then buy me a beerImage
User avatar
TrumpX
Registered User
Posts: 93
Joined: Tue Nov 16, 2010 2:45 pm
Location: Online

Re: SQL to get first post_id in the last 5 topic_id's of a f

Post by TrumpX »

Code: Select all

SELECT p.* FROM phpbb_posts p, phpbb_topics t	// post data
WHERE p.post_id = t.topic_first_post_id			// of the first posts...
AND p.forum_id = 7								// ... in forum 7...
ORDER BY t.topic_id DESC LIMIT 5				// .. .of the latest 5 topics
If I got your question right
Kewi
Registered User
Posts: 2
Joined: Wed Dec 07, 2011 5:06 am

Re: SQL to get first post_id in the last 5 topic_id's of a f

Post by Kewi »

TrumpX wrote:

Code: Select all

SELECT p.* FROM phpbb_posts p, phpbb_topics t	// post data
WHERE p.post_id = t.topic_first_post_id			// of the first posts...
AND p.forum_id = 7								// ... in forum 7...
ORDER BY t.topic_id DESC LIMIT 5				// .. .of the latest 5 topics
If I got your question right

That seems like a much simpler way then I was looking at ending up doing it (which appears to work):

Code: Select all

 // Bad code, use trump's above, for anyone else in the future that stumbles on this thread
$topics = 'SELECT ..... FROM phpbb_posts p, phpbb_topics t
 WHERE p.post_id=t.topic_first_post_id
 HAVING p.post_id in (select post_id from phpbb_posts as p, phpbb_topics as t where p.post_id=t.topic_first_post_id and t.forum_id=7) // Useless subquery that's already solved by WHERE clause
 order by t.topic_time DESC'; // For some reason topic_time seemed logical, but mine as well stick to topic id's!
After sleeping on my text and coming to read yours, I see yours is a lot simpler and I'm simply running a nested query for no reason (for some reason I did not process post_id=topic_first_post_id solving the problem even though I wrote that!


As for that Practical Displaying page.. Yes, I read through it... none of them simply took the post text from the last 5 topics -- it either tried to replicate the information in a forum view, or grab latest posts..
The example sql was no help either, its littered with phpbb code's and constants that are difficult to jump into when you are not strongly familiar with the database's structure or the constants & functions phpbb uses...


Anyway, Thanks for the SQL TrumpX, think that will work nicely and make the code a bit cleaner and more efficient! I probably would have stuck with my old poor code if I had not asked here :-) And would have made future similar projects just as difficult to form code around
Locked

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