[solved] PHPbb intergration

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

[solved] PHPbb intergration

Post 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
Last edited by Nay on Thu Dec 18, 2003 4:39 am, edited 1 time in total.
Nay
Forum Regular
Posts: 951
Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia

Post 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
redmonkey
Forum Regular
Posts: 836
Joined: Thu Dec 18, 2003 3:58 pm

Post 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.
Post Reply