Extract posts from PHPBB

estratosphere · 24 May 2006 at 23:53

I need to find a script that will allow me to show the latest posts from a forum on a PHPBB board on the index page of a website.

any good ones around?

paulsheff · 25 May 2006 at 01:32

Just grab em out of the DB.

Code:

<?php
$forum_query = "SELECT phpbb_topics.topic_title, phpbb_forums.forum_name, phpbb_users.username ";
$forum_query .= "FROM phpbb_topics, phpbb_forums, phpbb_users, phpbb_posts ";
$forum_query .= "WHERE phpbb_topics.forum_id=phpbb_forums.forum_id AND ";
$forum_query .= "phpbb_posts.poster_id=phpbb_users.user_id AND ";
$forum_query .= "phpbb_topics.topic_id=phpbb_posts.topic_id ";
$forum_query .= "ORDER BY phpbb_posts.post_time DESC";
$forum_result = mysql_query($forum_query) or die(mysql_error());
?>

Thrash · 25 May 2006 at 04:38

Here's one I wrote to extract the last post:

PHP:

//get the last article posted on the forum
		$sql = "SELECT * FROM phpbb_posts";
		$result = mysql_query($sql);
		$lastposted=0;
		while($row = mysql_fetch_array( $result )) {
			$timeposted=$row['post_time'];
			if ($timeposted>$lastposted)
			{
				$lastid=$row['post_id'];	
				$lastuserid=$row['poster_id'];
				$lastposted=$timeposted;
			}
		}	
		
		//get the last posted article
		$sql = "SELECT * FROM phpbb_posts_text WHERE post_id=$lastid";
		$result = mysql_query($sql);
		$row = mysql_fetch_array($result);
		$last = $row['post_text'];

estratosphere · 28 May 2006 at 13:47

will that grab just the posts from one forum on the board or every forum in the board? cos i want to grab the topics out of just one of the forums in the entire board not from the entire board

Thrash · 28 May 2006 at 13:59

You would just need to change the 1st select statement to include the forumID of the forum you want to get the information from. Get the forumID by looking at your phpBB_forums table then change the select statement to:

SELECT * FROM phpbb_posts WHERE forum_id='8'

i.e. substitute the forumID of the forum you want to select from where it says 8.

robmiller · 28 May 2006 at 15:23

That's pretty inefficient considering you can just do this:

(totally untested, I don't have phpBB lying around)

Code:

$result = mysql_query('
SELECT *
FROM phpbb_posts AS post, phpbb_posts_text AS text
WHERE post.post_id = text.post_id
ORDER BY post.post_time DESC
LIMIT 5
');

while($row = mysql_fetch_array($result)) {
    var_dump($row);
}

That should get the last 5 posts.

estratosphere · 28 May 2006 at 21:13

would i not need to open the database first? how do i do that?

open the database, read the info, close the database?

Thrash · 28 May 2006 at 22:54

To connect to the database use:

Code:

$link = mysql_connect('server', 'username', 'password');
if (!$link) {
     die('Could not connect: ' . mysql_error());
}
//select the appropriate database
mysql_select_db('database',$link);

Then when you are finished

Code:

mysql_close($link);

Beansprout · 29 May 2006 at 00:11

You don't reeeeeeeeeally need to use mysql_close() with non-persistent connections

estratosphere · 30 May 2006 at 01:12

ok i am having trouble getting this to work. could someone please write the entire php page and show me it working cos i just get a blank page or a list of jargon when i try any of these.

robmiller · 30 May 2006 at 01:15

You could try pasting the "jargon" :confused:

estratosphere · 30 May 2006 at 13:51

ok i get...

array(33) { [0]=> string(1) "1" ["post_id"]=> string(1) "1" [1]=> string(1) "1" ["topic_id"]=> string(1) "1" [2]=> string(1) "1" ["forum_id"]=> string(1) "1" [3]=> string(1) "2" ["poster_id"]=> string(1) "2" [4]=> string(9) "972086460" ["post_time"]=> string(9) "972086460" [5]=> string(8) "7F000001" ["poster_ip"]=> string(8) "7F000001" [6]=> NULL ["post_username"]=> NULL [7]=> string(1) "1" ["enable_bbcode"]=> string(1) "1" [8]=> string(1) "0" ["enable_html"]=> string(1) "0" [9]=> string(1) "1" ["enable_smilies"]=> string(1) "1" [10]=> string(1) "1" ["enable_sig"]=> string(1) "1" [11]=> NULL ["post_edit_time"]=> NULL [12]=> string(1) "0" ["post_edit_count"]=> string(1) "0" [13]=> string(1) "1" [14]=> string(0) "" ["bbcode_uid"]=> string(0) "" [15]=> NULL ["post_subject"]=> NULL [16]=> string(160) "This is an example post in your phpBB 2 installation. You may delete this post, this topic and even this forum if you like since everything seems to be working!" ["post_text"]=> string(160) "This is an example post in your phpBB 2 installation. You may delete this post, this topic and even this forum if you like since everything seems to be working!" }

with the following php page...

<html>
<head></head>

<body>

<?php
$link = mysql_connect('localhost', 'username', 'password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
//select the appropriate database
mysql_select_db('dbname',$link);

$result = mysql_query('
SELECT *
FROM phpbb_posts AS post, phpbb_posts_text AS text
WHERE post.post_id = text.post_id
ORDER BY post.post_time DESC
LIMIT 5
');

while($row = mysql_fetch_array($result)) {
var_dump($row);
}
?>

</body></html>

estratosphere · 30 May 2006 at 13:52

Thrash said:

Here's one I wrote to extract the last post:

PHP:

//get the last article posted on the forum
		$sql = "SELECT * FROM phpbb_posts";
		$result = mysql_query($sql);
		$lastposted=0;
		while($row = mysql_fetch_array( $result )) {
			$timeposted=$row['post_time'];
			if ($timeposted>$lastposted)
			{
				$lastid=$row['post_id'];	
				$lastuserid=$row['poster_id'];
				$lastposted=$timeposted;
			}
		}	
		
		//get the last posted article
		$sql = "SELECT * FROM phpbb_posts_text WHERE post_id=$lastid";
		$result = mysql_query($sql);
		$row = mysql_fetch_array($result);
		$last = $row['post_text'];

i just got blank page when using this guys one along with the db open script

elkdanger · 30 May 2006 at 16:37

K3v5t4 said:
ok i get...

with the following php page...

Code:

while($row = mysql_fetch_array($result)) {
[b]var_dump($row);[/b]
}

You're getting the "jargon" because of the line in bold. var_dump() will just print out the contents of the array, which is what the jargon is. So, everything is working correctly, and you're almost there.

Change the line in bold to something like 'echo($row['text']);' and it should print out the actual post. You can also print out any other fields you require.

estratosphere · 30 May 2006 at 17:02

even with changing this change, it just shows a blank page now.

please someone help me! i need this to work ASAP

AS_Platinum · 30 May 2006 at 17:13

Did you just replace var_dump() with echo $row['text']??

if so, change it to the field in the db that contains the "post"

ie:

PHP:

echo $row['THE_FIELD_YOU_WANT_TO_DISPLAY'];

Beansprout · 30 May 2006 at 17:16

Code:

<html>
<head></head>

<body>

<?php
$link = mysql_connect('localhost', 'username', 'password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
//select the appropriate database
mysql_select_db('dbname',$link);

$result = mysql_query('
SELECT *
FROM phpbb_posts AS post, phpbb_posts_text AS text
WHERE post.post_id = text.post_id
ORDER BY post.post_time DESC
LIMIT 5
');

while($row = mysql_fetch_array($result)) {
echo '<p>'.$row['text'].'</p>';
}
?>

</body></html>

Will put each post into a paragraph, which makes sense to me.

AS_Platinum · 30 May 2006 at 17:23

I see what you done there beansprout (with regards to the field name), didn't pay too much attention to the sql, and obviously thought the above ($row['text']) was just an example, but like you say that should work fine and dandy.

robmiller · 30 May 2006 at 17:48

SELECT * is evil, btw.

This will display the poster, the title of the thread and a small excerpt of the post's content:

Code:

<html>
<head></head>

<body>

<?php
$link = mysql_connect('localhost', 'username', 'password');
if( !$link )
	die('Could not connect: ' . mysql_error());
mysql_select_db('phpbb', $link);

$result = mysql_query("
SELECT
	topic.topic_title AS thread, post.post_username AS poster,
	CONCAT(SUBSTRING(text.post_text, 1, 75), '...') AS content
FROM
	phpbb_posts AS post, phpbb_posts_text AS text, phpbb_topics AS topic
WHERE
	post.post_id = text.post_id
	AND topic.topic_id = post.topic_id
ORDER BY
	post.post_time DESC
LIMIT 5
");

while( $row = mysql_fetch_array($result) ) {
	echo "
	<div class='post'>
		<p>Posted in {$row['thread']} by {$row['poster']}</h4>
		<p>{$row['content']}</p>
	</div>
	";
}
?>

</body></html>

With a tiny bit of effort you could make it link to the thread, post and poster too.

Edit: This is actually tested, btw; working fine on the version of phpBB that I just downloaded (which is, incidentally, still icky and horrible and disgusting).