Page 1 of 1

[solved] PHPbb intergration

Posted: Wed Dec 17, 2003 11:31 pm
by Nay
i've just started my forums and i plan to intergrate my front page news with a forum in the forums. so the i will have to go with phpbb's table structure. here's how:

Code: Select all

$query1 = "SELECT post_id FROM phpbb_posts WHERE forum_id = 5";
// selects all posts from the forum
$result = mysql_query($query1);
$ids = mysql_fetch_array($result); // will get all the posts

$query2 = "SELECT * FROM phpbb_posts_text WHERE post_id = {$ids[0]} ";

foreach($ids as $id) {
$query2 .= "OR post_id = $id";
}

$result = mysql_query($query2);
to me it looks kind of clumpsy. is there any other way around it?

-Nay

Posted: Thu Dec 18, 2003 4:39 am
by Nay
After much scratching head and computer-banging, I've come up with a script:

Code: Select all

<?php

$connection = mysql_connect("localhost", "myForums", "lalalalalala") or die(mysql_error());
$database = mysql_select_db("lookie!", $connection) or die(mysql_error());

$query = "SELECT DISTINCT topic_id FROM phpbb_posts WHERE forum_id = 5 ORDER BY topic_id DESC LIMIT 10";
$result = mysql_query($query, $connection) or die(mysql_error());

$ids = array();

while($rows = mysql_fetch_array($result)) {

$ids[] = $rows['topic_id'];

}

for($d = 0; $d < count($ids); $d++) {

$topic_id = $ids[$d];

$query = "SELECT * FROM phpbb_posts WHERE topic_id = $topic_id ORDER BY post_id ASC";
$result = mysql_query($query, $connection) or die(mysql_error());
$rows = mysql_fetch_array($result);
$comments = mysql_num_rows($result);
$post_id = $rows['post_id'];
$poster_id = $rows['poster_id'];

$post_date = $rows['post_time'];
$post_date = date("H,i,s,m,d,Y", $post_date);
$post_date = explode(",", $post_date);
$post_date = mktime($post_date[0],$post_date[1],$post_date[2],$post_date[3],$post_date[4],$post_date[5]);
$post_date = $post_date + (60 * 60 * 13);
$post_date = date("H:i a, d-m-y", $post_date);

$query = "SELECT username FROM phpbb_users WHERE user_id = $poster_id";
$result = mysql_query($query, $connection) or die(mysql_error());
$rows = mysql_fetch_array($result);
$poster = $rows['username'];

$query = "SELECT * FROM phpbb_posts_text WHERE post_id = $post_id ORDER BY post_id";
$result = mysql_query($query, $connection) or die(mysql_error());
$rows = mysql_fetch_array($result);

$post_text = urlencode($rows['post_text']);
$post_subject = urlencode($rows['post_subject']);

$output = <<< POST
topic_id_$d=$topic_id&postsubject_$d=$post_subject&date_$d=$post_date&poster_$d=$poster&comments_$d=$comments&post_text_$d=$post_text
POST;

echo $output;

}

?>
If you're wonder wht urlencode(), it's to pass the variables into Flash.

-Nay

Posted: Thu Dec 18, 2003 4:21 pm
by redmonkey
I make that 31 SQL queries, which is quite alot for what you are doing.

Perhaps it would be better to use mySQL's 'IN' syntax in your second query, so after you have your $ids array you could use something like this...

Code: Select all

$idlist = $ids[0];

foreach ($ids as $tid) {
	
	$idlist .= ",{$tid}";
}
With a SELECT query like.....

Code: Select all

$query = "SELECT * FROM phpbb_posts WHERE topic_id IN ($idlist) ORDER BY post_id ASC";
You could also probably reduce the query count further by joining the phpbb_users table onto the above query to get the username at the same time.