Select first post

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.
Darfuria
Registered User
Posts: 22
Joined: Thu Jul 08, 2004 10:04 pm

Select first post

Post by Darfuria »

What is the best/easiest way to select the first post in a thread using PHP and SQL?

Darfuria
Registered User
Posts: 22
Joined: Thu Jul 08, 2004 10:04 pm

Re: Select first post

Post by Darfuria »

Just a general bump to this post.

User avatar
Anon
Former Team Member
Posts: 7019
Joined: Fri Jan 02, 2004 7:33 am
Location: Christchurch, New Zealand

Re: Select first post

Post by Anon »

You'll probably get a better response at the MOD Writers discussion, so moving there :)

Darfuria
Registered User
Posts: 22
Joined: Thu Jul 08, 2004 10:04 pm

Re: Select first post

Post by Darfuria »

Ah, okay. Thankyou :)

User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: Select first post

Post by drathbun »

Darfuria wrote: What is the best/easiest way to select the first post in a thread using PHP and SQL?

What else do you know?

If you know the topic_id value, then it's fairly simple as the topic_first_post_id is stored on the topic row.

Code: Select all

$sql = 'SELECT t.stuff
, p.stuff
, pt.stuff
FROM phpbb_topics t
, phpbb_posts p
, phpbb_posts_text pt
WHERE t.topic_id = ???
and t.topic_first_post_id = p.post_id
and p.post_id = pt.post_id'
Replace "stuff" with the list of columns that you want to display from each table.
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image

Darfuria
Registered User
Posts: 22
Joined: Thu Jul 08, 2004 10:04 pm

Re: Select first post

Post by Darfuria »

I'm trying to write a script to pull the first post from the Announcements forum and display it on the homepage of a website.

This is the majority of what I have so far:

Code: Select all

<?php
	mysql_connect ($dbhost, $dbuser, $dbpassword);
	mysql_select_db ($dbname) or die(mysql_error());
	
	$topics = mysql_query ("SELECT * FROM phpbb_posts INNER JOIN phpbb_posts_text ON phpbb_posts.post_id = phpbb_posts_text.post_id WHERE phpbb_posts.forum_id = " . $forumid . " ORDER BY post_time DESC") or die(mysql_error());
	
	while($row = mysql_fetch_array($topics))
	{
		echo('<strong>Post ID:</strong> ' . $row['post_id'] . ' <strong>Subject:</strong> ' . $row['post_subject'] . '<br />');
	}
		
?>
And this is what it returns. When I was using a GROUP BY, it wasn't selecting the correct posts.

I haven't seen topic_first_post_id in the database :S

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51997
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Select first post

Post by Brf »

Darfuria wrote:

Code: Select all

SELECT * FROM phpbb_posts INNER JOIN phpbb_posts_text ON phpbb_posts.post_id = phpbb_posts_text.post_id WHERE phpbb_posts.forum_id = " . $forumid . " ORDER BY post_time DESC"


Just tack on the rest of the query suggested by drathbun, so it looks like this:

Code: Select all

SELECT * FROM phpbb_posts INNER JOIN phpbb_posts_text ON phpbb_posts.post_id = phpbb_posts_text.post_id 

inner join phpbb_topics on phpbb_posts.post_id = phpbb_topics.topic_first_post_id

WHERE phpbb_posts.forum_id = " . $forumid . " ORDER BY post_time DESC"

Darfuria
Registered User
Posts: 22
Joined: Thu Jul 08, 2004 10:04 pm

Re: Select first post

Post by Darfuria »

Ah, thanks. That worked perfectly :)

Darfuria
Registered User
Posts: 22
Joined: Thu Jul 08, 2004 10:04 pm

Re: Select first post

Post by Darfuria »

Hrm. Having done this. What is the best way of parsing the BBCode?

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51997
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Select first post

Post by Brf »

Darfuria wrote: Hrm. Having done this. What is the best way of parsing the BBCode?


You can't unless your page is using the standard phpBB stuff up front, because bbcode uses the phpBB template system.

Darfuria
Registered User
Posts: 22
Joined: Thu Jul 08, 2004 10:04 pm

Re: Select first post

Post by Darfuria »

How do you mean using it up front?

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51997
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Select first post

Post by Brf »

The top of your php script has to look like this:

Code: Select all

<?php 
define('IN_PHPBB', true);
$phpbb_root_path = './'; 
include($phpbb_root_path . 'extension.inc'); 
include($phpbb_root_path . 'common.'.$phpEx); 
$userdata = session_pagestart($user_ip, PAGE_INDEX); 
init_userprefs($userdata);
Then you can also use the standard $db class to do your SQL.

You parse your message like this:

Code: Select all

include($phpbb_root_path . 'includes/bbcode.'.$phpEx);

Code: Select all

$message=bbencode_second_pass($message, $message_uid)

Darfuria
Registered User
Posts: 22
Joined: Thu Jul 08, 2004 10:04 pm

Re: Select first post

Post by Darfuria »

A rather late reply to this, but I haven't had time to work on this particular project for quite a while.

I'm still attempting to write a news page which pulls posts from the forum database. Surely this must be quite a popular thing to do.

I'm still having problems parsing the BBCode. Any advise/pointers would be much appreciated.

User avatar
Brf
Support Team Member
Support Team Member
Posts: 51997
Joined: Tue May 10, 2005 7:47 pm
Location: {postrow.POSTER_FROM}
Contact:

Re: Select first post

Post by Brf »

Darfuria wrote: I'm still having problems
Not very descriptive are we?

Darfuria
Registered User
Posts: 22
Joined: Thu Jul 08, 2004 10:04 pm

Re: Select first post

Post by Darfuria »

Sorry, I meant to link to the page I was working on.

http://www.clanedge.co.uk/news2.php

Post Reply

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