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
Nay
Forum Regular
Posts: 951 Joined: Fri Jun 20, 2003 11:03 am
Location: Brisbane, Australia
Post
by Nay » Wed Dec 17, 2003 11:31 pm
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 » Thu Dec 18, 2003 4:39 am
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 » Thu Dec 18, 2003 4:21 pm
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.